Oracle PL/SQL Programming, Third Edition

Raising an Exception from the Called C Program

If you think about it for a moment, the shell( ) program shown earlier in the chapter is arguably too “C-like” for PL/SQL: it is a function whose return value contains the status code, and the caller must check the return value to see if it succeeded. Wouldn’t it make more sense—in PL/SQL, that is—for the program to be a procedure that simply raises an exception when there’s a problem? Let’s take a brief look at how to perform the OCI equivalent of RAISE_APPLICATION_ERROR.

In addition to the easy change from a function to a procedure, there are several other things I need to do:

• Pass in the context area

• Decide on an error message and an error number in the 20001-20999 range

• Add a call to the OCI service routine that raises an exception

The changes to the call spec are trivial:

/* File on web: extprocsh.sql */
CREATE OR REPLACE PROCEDURE shell(cmd IN VARCHAR2)
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   WITH CONTEXT
   PARAMETERS (CONTEXT, cmd STRING, cmd INDICATOR);
/

(I also removed the return parameter and its indicator because I don’t need them any more.) The following code shows how to receive and use the context pointer in the call needed to raise the exception.

/* File on web: extprocsh.c */

 1 #include <ociextp.h> 2 #include <errno.h> 3 4 void extprocsh(OCIExtProcContext *ctx, char *cmd, short cmdInd) 5 { 6 int excNum = 20001; 7 char excMsg[512]; 8 size_t excMsgLen; 9 10 if (cmdInd == OCI_IND_NULL) 11 return; 12 13 if ...