The CHARSETID and CHARSETFORM properties are flags denoting information about the character set, and have the following characteristics:
Allowed external types
unsigned int (the default), unsigned short, unsigned long
Allowed PL/SQL types
VARCHAR2, CHAR, CLOB
Allowed PL/SQL modes
IN, IN OUT, OUT, RETURN
Call mode
By reference
If you are passing data to the external procedure that is expressed in a nondefault character set, these properties will let you communicate the character set's ID and form to the called C program. The values are read-only and should not be modified by the called program. Here is an example of a PARAMETERS clause that includes character set information:
PARAMETERS (CONTEXT, cmd STRING, cmd INDICATOR, cmd CHARSETID, cmd CHARSETFORM);Oracle sets these additional values automatically, based on the character set in which you have expressed the cmd argument. For more information about accommodating national language support in a C program, refer to Oracle's OCI documentation.
23.5 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 sensein PL/SQL, that isfor 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 (system(cmd)!= 0) 14 { 15 sprintf(excMsg, "Error %i during system call: %.*s", errno, 475, 16 strerror(errno)); 17 excMsgLen = (size_t)strlen(excMsg); 18 19 if (OCIExtProcRaiseExcpWithMsg(ctx, excNum, (text *)excMsg, excMsgLen) 20!= OCIEXTPROC_SUCCESS) 21 return; 22 } 23 24 }Note the following lines:
Line 4
The first of the formal parameters is the context pointer.
|
|
|
Line 6
You can use whatever number in Oracle's user-defined error number range you want; in general, I advise against hardcoding these values, but, er, this is a "do as I say, not as I do" example.
Line 7
The maximum size for a user-defined error message is 512 bytes.
Line 8
A variable to hold the length of the error message text, which will be needed in the OCI call that raises the exception.
Lines 10-11
Here, I am translating the NULL argument semantics of the earlier function into a procedure: when called with NULL, nothing happens.
Line 13
A zero return code from system() means that everything executed perfectly; a nonzero code corresponds to either an error or a warning. A more sophisticated program might check for (and ignore) various warnings, based on the value that system() returns.
Lines 15, 16
These lines prepare the variables containing the error message and its length.
Lines 19-20
This OCI function, which actually raises the user-defined exception, is where the context pointer actually gets used.
Now, how do we compile this baby? First, Unix:
/* File on web: build_extprocsh.sh */gcc -c -I$ORACLE_HOME/rdbms/public -I$ORACLE_HOME/rdbms/demo exp.cgcc -shared -o exp.so exp.oOn Microsoft, I found that I needed an explicit .def file to identify the entry point.
/* File on web: build_extprocsh.bat */echo LIBRARY extprocsh.dll > extprocsh.defecho EXPORTS >> extprocsh.defecho extprocsh >> extprocsh.def gcc -c -I%ORACLE_HOME%\oci\include extprocsh.cgcc -shared -o extprocsh.dll extprocsh.def extprocsh.o %ORACLE_HOME%\oci\lib\msvc\oci.libLet's run a simple test:
SQL> CALL shell('garbage'); CALL shell('garbage') *ERROR at line 1:ORA-20001: Error 2 during system call: No such file or directoryIt's working! The "no such file or directory" message comes from the standard C error function strerror(). This is a Unix example; I discovered that the strerror function doesn't seem to be set meaningfully using GNU C on Microsoft, where the error message always comes out as ORA-20001: Error error 0 during system call. Oh well.
A number of other OCI routines are unique to writing external procedures. Here is the complete list:
OCIExtProcAllocCallMemory
Allocates memory that Oracle will automatically free when control returns to PL/SQL.
OCIExtProcRaiseExcp
Raises a predefined exception by its Oracle error number.
OCIExtProcRaiseExcpWithMsg
Raises a user-defined exception, including a custom error message (illustrated in the previous example).
OCIExtProcGetEnv
Allows an external procedure to perform OCI callbacks to the database to execute SQL or PL/SQL.
All of these require the context pointer. Refer to Oracle's Application Developer's GuideFundamentals for detailed documentation and examples of using these routines.
