topic Re: Oracle Connection Timeout in Long Stored Process in SAS Programming

Oracle Connection Timeout in Long Stored Process

DavidPhillips2 — Thu, 06 Feb 2020 15:29:00 GMT

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?

Re: Oracle Connection Timeout in Long Stored Process

AhmedAl_Attar — Thu, 06 Feb 2020 18:29:42 GMT

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

Re: Oracle Connection Timeout in Long Stored Process

DavidPhillips2 — Mon, 10 Feb 2020 13:46:50 GMT

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.

Re: Oracle Connection Timeout in Long Stored Process

AhmedAl_Attar — Mon, 10 Feb 2020 15:09:10 GMT

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