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

[durga]

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.

[SASKiwi]

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;

[FriedEgg]

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;

[durga]

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