So here we are - working on SAS9.4M4   We use DataDirect ODBC Drivers to connect to some databases = > Oracle, etc Are the Licences provided for Datadirect perpetual or do they need to be updated every year, like the SAS Product Licenses?   In my understanding the SAS Licenses are updated every year, but DataDirect should be perpetual? Am I correct?     ... View more

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? ... View more