topic Re: Executing Oracle Stored Procedures hanging? in SAS Procedures

Executing Oracle Stored Procedures hanging?

epower — Tue, 05 Nov 2024 03:43:35 GMT

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)

Re: Executing Oracle Stored Procedures hanging?

SASKiwi — Tue, 05 Nov 2024 21:05:37 GMT

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.

Re: Executing Oracle Stored Procedures hanging?

Patrick — Wed, 06 Nov 2024 00:26:48 GMT

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).

Re: Executing Oracle Stored Procedures hanging?

epower — Tue, 20 May 2025 23:04:39 GMT

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.

Re: Executing Oracle Stored Procedures hanging?

SASKiwi — Thu, 22 May 2025 02:49:36 GMT

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

Re: Executing Oracle Stored Procedures hanging?

epower — Thu, 22 May 2025 03:21:07 GMT

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.