proc sql: catching oracle exception

[strsljen]

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,

[strsljen · Accepted solution]

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;