How do I use a temporary SQL table in passthrough?

[rduken]

I am a relative beginner to SAS. I am working with previously written code that creates a comma separated macro list, and then uses this in a passthrough Oracle SQL query like so (this is a simplified example):

proc sql;
    select id into :idlist separated by ',' from currentcustomerpool;
    connect to oracle (&credentials.);
    create table customerinfo as
        select * from connection to oracle (
            select * from custtables.customerAccountInfo
            where customer in (&idlist.) and age > 21
         );
    disconnect from oracle;
quit;

Using the macrovar idlist imposes a 1000 id limit because of an inherent SQL limitation on IN clauses. I would like to get rid of the idlist macrovar by using a temporary SQL table that I could join to or use as part of a subquery inside the SQL passthrough block. In the actual codebase, I have to keep the passthrough blocks; in other words, I cannot use a solution that moves away from passthrough.

I have attempted to come up with a sample piece of code to accomplish this like so (after a bit of googling):

libname tmpdat oracle connection=global dbmstemp=yes dbcommit=0 &credentials.;

proc sql;
    connect to oracle (&credentials.);
    execute (
        CREATE GLOBAL TEMPORARY TABLE tmpcustids (
            ID NUMBER
        ) ON COMMIT PRESERVE ROWS
    ) by oracle;

    insert into tmpdat.tmpcustids select id from currentcustomerpool;
    create table customerinfo as 
        select * from connection to oracle ( 
            select * from custtables.customerAccountInfo 
            where customer in (select * from tmpcustids) and age > 21
        );
    disconnect from oracle;
quit;       

This seems to "work" with no errors, but the resulting query gives a table with 0 rows, as if no actual data was inserted into the tmpcustids table on the SQL side of things. If I explicitly insert values using passthrough  into tmpcustids, the query works and gives rows back. However, I would really prefer to avoid having to make a macro that creates a dynamic insert statement in another execute block.

Is there a simple solution to this problem? Thanks in advance for the help.

[Tom · Accepted solution]

I would suggest simplifying by making only ONE connection to the database.  Then you don't have to worry about whether you are using the same connection as the one where the temporary table exists or not.

So make the libref first.  Then use the libref to make the connection.  And eliminate the DISCONNECT statement. 

libname tmpdat oracle connection=global dbmstemp=yes dbcommit=0 &credentials.;

proc sql;
    connect using tmpdat as oracle;
...

If the rest of your program is long running and/or you are running interactively and you want to be nice to the database by releasing connections you no longer need just clear the libref when you are done with everything.

libname tempdat clear;

[rduken]

Thank you! This seems to be working in my simple example, so I will see about applying it elsewhere. I am a little confused about the code I'm working with though, because all the passthrough blocks simply use `connect to oracle ` even though there are other sql librefs. Is it only necessary to specify the libref in the connection for this case because of the dbmstemp option?

[Tom]

If you use CONNECT TO ORACLE you have to tell it all of the settings for the connection (what account to use etc.).

If you use the CONNECT USING libref AS alias syntax instead then it will just re-use the connection that was already made for the libref.

Both SHOULD work when using the GLOBAL setting.  But because SAS is a little picky about what is considers compatible connections you have to be really careful when making multiple connections by issuing multiple separate CONNECT TO statements.

[Patrick]

Because you don't need any Oracle SQL specific syntax I believe below code should work for you (not tested).

libname prmdat oracle dbcommit=0 insertbuff=10000 readbuff=10000 &credentials. connection=global schema=custtables ;
libname tmpdat oracle dbcommit=0 insertbuff=10000 readbuff=10000 &credentials. connection=global schema=custtables dbmstemp=yes;

options sastrace=(,,,d) nostsuffix sastraceloc=saslog;

proc append data=work.currentcustomerpool(keep=id) base=tmpdat.tmpcustids_&sysuserid;
run;

proc sql;
  create table customerinfo as 
  select l.*
  from prmdat.customerAccountInfo l inner join tmpdat.tmpcustids_&sysuserid r
  on l.customer=r.id and l.age>24
  ;
quit;

proc datasets lib=tmpdat nolist nowarn;
  delete tmpcustids_&sysuserid;
quit;

The script is based on sample code found under DBMSTEMP= LIBNAME Statement Option 

I'm not sure if schema=... is required for the temporary library but I've used it because of Conditions for a Shared DBMS Connection

I've added &sysuserid to the name of the Oracle temp table to avoid any naming conflicts with temp tables created by other users.

I've added logic to drop the ora temp table at the end because I had to learn the hard way that with Oracle only the data attached to a temp table is temporary. The table structure as such is permanent. 

I assume that in your code the libname and connect statement don't meet the conditions for a shared DBMS connection. Because the temp table (the structure) is permanent in Oracle your query returned zero rows. With other databases like SQL Server also the table structure is temporary so there you would have received an Error that the table doesn't exist.

[rduken]

Thanks! From what I can tell, it looks like this would work - however, I am constrained by having to keep the passthrough SQL blocks and the changes be as minimal as possible. The example I gave is a relatively simple one, but there are other more complicated ones inside the codebase I'm working in.