Oracle Libname connection issue

[Lenvdb]

I have a peculiar situation whereby I support a team of SAS Programmers. They write the code. When things go wrong they ask me to fix it.

This specific issue has to do with connections to an Oracle DB and how they coded it in SAS to connect to Oracle to execute their statements. To me this feels wrong and I would like to bounce it off an expert first before taking it back to the SAS Programmers to fix it.

We have a Predefined Library to an Oracle Schema.

I assume as the SAS Session is started it would create 2 connections using this Libname statement

1) A connection for utilities (like the Explorer window in SAS EG)

2) A Connection for a Read

However this Proc SQL does the following

1) A secondary connection to the same Schema is defined before the Proc SQL.

Inside the Proc SQL as connection is made to the DB

2) A DELETE FROM statement is executed against the secondary connection to a table in this schema

3) An Insert into statement is executed against the secondary connection the same table 

4) The secondary connection is now dropped/Disconnected

5) Then a Drop Table is executed against the Predefined Library connection to a table in the Schema.

The code as follows:

%LET DB_SERVER=PROD_ORA;
%LET AUTH_DOMAIN=Ora_Primary;

PROC SQL NOPRINT;
    CONNECT TO ORACLE AS x (AUTHDOMAIN = "&AUTH_DOMAIN"
        PATH       = &DB_SERVER );
    EXECUTE(DELETE FROM TBL2
        WHERE Myfield IN (SELECT DISTINCT Myfield FROM TBL1)
            )
        BY x;
    EXECUTE(INSERT INTO TBL2
        SELECT *
            FROM TBL1
                )
            BY x;
    DISCONNECT FROM x;
/*===this line causes error====*/
    DROP TABLE PRELIB.TBL1   
;    
QUIT;

In my mind - having 2 connections in the same Proc SQL feels wrong...

The error we get is as follows:

ERROR: ORACLE prepare error: ORA-01012: not logged on
WARNING: Table PRELIB.TBL1 has not been dropped.

I know there are some interesting "rules" regarding connections to external database objects. Here we have 2 connections in the same proc SQL.

Could this be a problem?

I personally would have coded it differently - perhaps place the last Drop Table statement in a different Proc SQL and add a Check to see if it exists first.

Any thoughts or speculations on this issue?

[SASKiwi]

If you are using EXECUTE statements for all your other SQL, why not drop the table inside EXECUTE also? It is going to be more efficient that way and as you say you just need one Oracle connection.

[Tom · Accepted solution]

Why not just reuse the connection you made with the LIBNAME statement?

libname prelib oracle ..... ;
proc sql ;
  connect using prelib;
  execute (..... ) by prelib;
quit;