Using CALL in OCI before/after to invoke a Stored Procedure

[rleishman]

The accepted DataStage method of calling a stored procedure in the before/after SQL of an OCI stage is to use the CALL keyword. eg:

Code: Select all

CALL proc_name()

We use CALL because wrapping the proc name in BEGIN/END does not work (at least I've never been able to get it to work).

The reason it works is because CALL is actually a real SQL statement, like SELECT, INSERT, and CREATE.

But there's a problem: If your procedure encounters an unhandled NO_DATA_FOUND (ORA-1403) error, then it will not be propagated to DataStage - DS will think the CALL has returned success. You can compare it for yourself in SQL*Plus: write a procedure that encounters an unhandled NO_DATA_FOUND - run it with CALL, and run it with BEGIN/END.

This is legit according to Oracle - and they have a point: SQL Statements never raise NO_DATA_FOUND - only PL/SQL statements do.

Possible actions:
1. Put an EXCEPTION WHEN NO_DATA_FOUND clause at the bottom of every procedure that calls raise_application_error(-20000, 'No data found.');
2. Never use SELECT .. INTO in your procedures
3. Call Stored Procs from the Stored Proc stage (I don't know how practical this is - I've never tried it).

If you enjoy a good farce, take a look at the history of this discovery on Oracle Metalinks at SR 5158900.992 - it redefines "level 1 support".