Hei,
I am working on User Written Transformation in SAS DI Studio which will handle Oracle packages/stored procedures.
In that sense, I am trying to catch when Oracle stored procedure raise an exception.
When that happens, I can stop the flow, continue, or something else..
At the moment, I am unable to read from proc sql when stored procedure raises an exception, although I can see it in DI studio when job is running:
- ERROR: ORACLE execute error: ORA-20020: This is a test application error / exception ORA-06512: ved "BUS_DATA_MAN_SANDBOX.DUMMY_PACKAGE", line 3 ORA-06512: ved line 1.
Is there a way to read some SAS macro variable to determinate when this happens?
Or to read some proc sql return code?
I did try with &SQLRC and &job_rc, but these are 0 regardless.
Thanks in advance!
Best regards,
Got it: one must log return code in own macro as soon as it happens, otherwise it gets back to 0 in DI code when code execution continues.
So... Example:
PROC SQL;
CONNECT USING SEQLIB AS Oracle;
execute (execute &SCHEMA_NAME..&PACKAGE_NAME..&STORED_PROCEDURE.()) by oracle;
%let RES=&sqlrc;
DISCONNECT FROM Oracle;
QUIT;
ss
%if &RES > 0 %then %do;
%end_with_error;
%end;
Got it: one must log return code in own macro as soon as it happens, otherwise it gets back to 0 in DI code when code execution continues.
So... Example:
PROC SQL;
CONNECT USING SEQLIB AS Oracle;
execute (execute &SCHEMA_NAME..&PACKAGE_NAME..&STORED_PROCEDURE.()) by oracle;
%let RES=&sqlrc;
DISCONNECT FROM Oracle;
QUIT;
ss
%if &RES > 0 %then %do;
%end_with_error;
%end;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.