DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 8

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

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.

Super User
Posts: 3,100

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

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;

Trusted Advisor
Posts: 1,300

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

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;

Occasional Contributor
Posts: 8

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

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

Ask a Question
Discussion stats
  • 3 replies
  • 173 views
  • 3 likes
  • 3 in conversation