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.
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;
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;
Thank You So much for ur responce. It really helpful.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.