O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required