Executing Oracle Stored Procedures hanging?

[epower]

We have a some jobs running Proc SQL where we execute Oracle Stored procedures but some jobs just hang. Has anyone seen this?

Running the stored procedure from the same SQLPLUS client on the server shows a successful execution but when we run it from SAS we never get a response. 

SQLPLUS is giving this response

Our SAS logs look this and never end until we force quit (I have turned on SASTRACE with d,,, to actually get a log otherwise a log file never gets populated)

[SASKiwi]

Is this a recent problem and they all used to work OK? If so then what might have changed? The jobs that hang - do they sometimes work or do they always hang? How are you running these jobs in SAS - via EG, SAS Studio, SAS Windowing System, or batch?   

Given there is very little evidence to work with, opening a Tech Support track is probably your best option if you haven't already done so.

[Patrick]

I'd ask an Oracle DBA for the log on the Oracle side to understand what's happening there. 

Is this a long running process?

And just to make sure: You did finish the Proc SQL statement with a QUIT; Right?

It would also help if you share your full code here. Just change the names of code components that are confidential (like the name and parameter values of the stored proc).

[epower · Accepted solution]

BTW We ended up giving up with SAS an Oracle.  Full tickets through tech support ended up nowhere. Using the oracle client on the same server things could run fine and SAS would just continue to hang. We just ended up using different methods for Oracle execution of stored procedures.  

[SASKiwi]

Interesting. We call SQL Server stored procedures via SAS/ACCESS Interface to ODBC and never had problems like this.

[epower]

It's only Oracle , SQL Server runs have no issues. I'm thinking there is some sort of network issue but SAS doesn't handle it and hangs.