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

[L5ive]

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

[Kurt_Bremser]

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?

[L5ive]

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

[Kurt_Bremser]

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.

[ChrisNZ · Accepted solution]

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.

[L5ive]

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

Thank you So much

[Kurt_Bremser]

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.