The CHARSETID and CHARSETFORM properties

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 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 (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.o

On 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.lib

Let'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 directory

It'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 Guide—Fundamentals for detailed documentation and examples of using these routines.