How to optimize the amount of db connections being opened in proc sql?

[bsas94]

Hello experts,

For example, in order to check whether a table exists or not and then create a new table, I have been doing the following:

%IF %SYSFUNC (EXIST (foo.bar)) %THEN %DO;
    PROC SQL NOPRINT;
      DROP TABLE foo.bar;
    QUIT;
  %END;

PROC SQL NOPRINT;
    CONNECT TO ORACLE AS ORA (&STRING_CONN.);
          execute by ora (
                create table foo.bar as select * from foo.oldtable 
                );
    DISCONNECT FROM ORA;

QUIT;


%IF %SYSFUNC (EXIST (foo.bar2)) %THEN
%DO;
        PROC SQL;
               DROP TABLE foo.bar2;
        QUIT;
%END;

PROC SQL NOPRINT;
    CONNECT TO ORACLE AS ORA (&STRING_CONN.);
          execute by ora (
                create table foo.bar2 as select * from foo.oldtable2 
                );
    DISCONNECT FROM ORA;

QUIT;

Is there any way to optimize the amount of connections being opened and use one proc sql?

I have been using explicit passthru to create new tables but I haven't found any way to incorporate the IF in a better way so I can group this code in one proc sql.

Thank you!

[SASJedi · Accepted solution]

If I were doing this, I would assign a libref with a LIBNAME statement and use PROC FEDSQL to do the deed. Explicit pass-through is a one-liner in FedSQL. For example:

libname ora path=&oraclePath schema=foo user=&OraUser pw=OraPW;

proc FedSQL;
/* Implicit pass-through, FORCE ignores errors if table exists */
drop table ora.bar force; 
/* Explicit pass-through by referencing a libref as the alias */
execute (create table foo.bar as select * from foo.oldTable) by ora;
quit;

[SASKiwi]

It would be a lot more efficient just to query an Oracle dictionary table to see if tables exist or not:

libname myoracle oracle noprompt = <Oracle connection string>;

proc sql;
  connect using myoracle;
  select * from connection to myoracle
  (SELECT TABLE_NAME 
FROM ALL_TABLES 
WHERE TABLE_NAME like '%OLDTABLE%'
);
quit;