topic Need Help: Facing problem when connecting to Oracle database using Libname in SAS Programming

Need Help: Facing problem when connecting to Oracle database using Libname

durga — Wed, 09 Nov 2011 13:04:23 GMT

We have SAS job which is connect to Oracle to load data using Libname. But this connection is creating unnecessary process in Oracle environment and that process is creating and executing the below query:

SELECT OBJECT_NAME, OBJECT_TYPE

FROM ALL_OBJECTS OBJ

WHERE (OBJ.OWNER = 'SCHEMA')

AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW'

And this process is not completing even though actual SAS job is completed in SAS environment.

It is causing some performance issues in Oracle side.

Could some one help me, how we can restrict to not to create Oracle process which is creating above query.

Thanks in Advance!!

Durga.

Need Help: Facing problem when connecting to Oracle database using Libname

SASKiwi — Thu, 10 Nov 2011 00:21:49 GMT

I think you are missing a right-hand bracket at the end of your query.

Have you tried running this query in an Oracle query tool such as SQL Plus or Toad directly on Oracle and if so how long did it take?

Try using SQL Passthru in SAS and you should get the same performance as in SQL Plus or Toad:

Proc sql;

connect to oracle (user = ‘userid’ password = ‘password’

path = schema)

select * from connection to oracle (

SELECT OBJECT_NAME, OBJECT_TYPE

FROM ALL_OBJECTS OBJ

WHERE (OBJ.OWNER = 'SCHEMA')

AND (OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'VIEW')

);

Disconnect from oracle;

Quit;

Need Help: Facing problem when connecting to Oracle database using Libname

FriedEgg — Thu, 10 Nov 2011 22:51:44 GMT

If you want to track down the source of a sql submission to the database use the sastrace option. Try like this:

options sastrace=',,,d' sastraceloc=log;

Need Help: Facing problem when connecting to Oracle database using Libname

durga — Fri, 18 Nov 2011 12:45:01 GMT

Thank You So much for ur responce. It really helpful.