Oracle PL/SQL Programming, 5th Edition

Raising an Exception from the Called C Program

The shell( ) program shown earlier in the chapter is very, um, “C-like:” 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, anyway—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 */
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.) 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; # a convenient number :-> 7 char excMsg[512]; 8 size_t excMsgLen; 9 10 if (cmdInd == OCI_IND_NULL) 11 return; 12 13 if (system(cmd) != 0) 14 { 15 sprintf(excMsg, "Error %i during system call: %.*s", errno, ...