topic Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555 in SAS Programming

SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

L5ive — Tue, 28 Sep 2021 08:12:48 GMT

Hi Everyone,

Please i have an issue with my code.

im trying to create a table with 20 mill row reading from oracle data base.

however i get the below log error

proc sql;
374 create table soap_detl1 as
375 select a.ssnbr ,a.indnbr ,a.contnbr ,a.item ,a.qtycl ,a.qtyapp ,a.totcl ,a.totapp ,a.paid_amt ,a.exceedlimd
375 ! ,a.netamt ,a.prov_net_amt ,a.totrcvry
376 ,a.discd ,a.hasitems ,a.covnetamt ,a.treatperiod ,a.penaltyd ,a.reqnum ,a.untprice ,a.QTY_TYPE ,a.discount
376 ! ,a.pa_flg ,a.mnsrv
377 from &OPERATION..soapdetl_up a inner join ssnbr_list b
378 on a.ssnbr=b.ssnbr ;
ERROR: Error fetching from cursor. ORACLE error is ORA-01555: snapshot too old: rollback segment number 88 with name
"_SYSSMU88_4158259342$" too smallORA-02063: preceding line from STBDBLNK.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
379 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 59:35.10
cpu time 2:26.56

Appreciate your support

Thank you,

Ahmed Maher

Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

Kurt_Bremser — Tue, 28 Sep 2021 08:28:44 GMT

This is an internal Oracle issue, see here: https://www.tekstream.com/resource-center/ora-01555-snapshot-too-old/

I don't think you will be able to solve this from the SAS side.

The problem happens when the table you access from SAS is updated while you use it, and Oracle tries to provide you with a consistent state, rolling back the changes that are made simultaneously. When it runs out of resources to do this, you get 01555.

I don't know if ACCESS to Oracle provides an option to prevent the use of a snapshot and allow inconsistent data.

Since your query takes almost an hour and is just a subset based on a SAS WORK dataset, you need to speed that up.

How large is WORK.SSNBR_LIST?

Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

ChrisNZ — Tue, 28 Sep 2021 09:25:29 GMT

The easiest way to speed your query up is to upload the SSNBR values you are interested in to a temp Oracle table, and perform the inner join in Oracle.

Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

L5ive — Tue, 28 Sep 2021 11:38:44 GMT

I tried this and it worked with only 26 Min real time run (50% Less Time)

Thank you So much

Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

Kurt_Bremser — Tue, 28 Sep 2021 11:44:53 GMT

Depending on the size of the lookup table, you could even try to make a WHERE condition out of it and put the data into the code.

Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

L5ive — Tue, 28 Sep 2021 11:50:08 GMT

SSNBR_LIST is about 10 M Row and its only one column.
i tried the option offered by @ChrisNZ and it worked as a work around.
however i cant do this in all the codes or programs that im running since the access to write tables on the Oracle is limited.
i kind of understand your explanation however if you could guide me what should i try first or look into that would be great.

Thank you

Re: SAS Error : Error fetching from cursor. ORACLE error is ORA-01555

Kurt_Bremser — Tue, 28 Sep 2021 12:42:05 GMT

This will only work for a limited number of items, as both the size of a macro variable (64K) and the size of a SQL statement have limits.

Simple proof of concept:

data names;
input name $;
datalines;
Alfred
William
;

proc sql noprint;
select quote(name) into :list separated by ","
from names;
quit;

proc sql;
select * from sashelp.class
where name in (&list.);
quit;

Alternatively, you can write the SQL code to a file (where you can have a lot more items in the IN list) and then %include that:

filename xx temp;

data _null_;
file xx;
set names end=done;
length line $100;
if _n_ = 1
then line = cats('proc sql; select * from sashelp.class where name in ("',name,'"');
else line = cats(',"',name,'"');
put line;
if done then put ");quit;";
run;

%include xx /source2;

filename xx clear;

This will allow for a much longer list, as the size limit for SQL statements will be much higher than that for macro variables. Your code in the include should of course be tailored for explicit pass-through.