Oracle Connection Timeout in Long Stored Process

[DavidPhillips2]

I have a long-stored process with a macro loop.  It looks like my Oracle connection form my Libname stays open a little over an hour.  Then my Oracle connection closes and the stored process slowly errors out.  Should I remake my libname connection after a set amount of time?  Is there a way to do this?

[AhmedAl_Attar]

If your Oracle Library definition stored in the SAS Metadata Server, then you can try the LIBNAME Statement for the Metadata Engine.

LIBNAME <LibRef> META library="<Library Description>" metaout=data; 

For more details, check this paper https://support.sas.com/documentation/onlinedoc/guide/EG43MetaLibraries.pdf

Hope this helps,

Ahmed

[DavidPhillips2]

I'll try this.  First I"m looking at loading everything into memory first from Oracle into SAS before I start lopping in my stored process.  I'm guessing the idle time error that I'm receiving is not about the length of the query but the amount of time between a SAS call and an Oracle call.

[AhmedAl_Attar]

Depending on how big the oracle data, loading it into SAS may not be the best option, but you know better.

in all cases, I would recommend you explore the information listed in this SGF paper 

- SAS-Oracle Options and Efficiency: What You Don’t Know Can Hurt You

You can find a lot more papers on this subject by visiting this link https://lexjansen.com/search/searchresults.php?q=Oracle

Hope this helps,

Ahmed