[Chapter 21] 21.6 External Procedure Housekeeping

21.6 External Procedure Housekeeping

Here are some not-so-obvious bits of information that will assist you in creating, debugging, and managing external procedures.

21.6.1 Data Dictionary

There are only a handful of entries in the data dictionary that help manage external procedures. As we have mentioned elsewhere, although Table 21.2 gives the USER_ version of the dictionary table, note that there are corresponding entries for DBA_ and ALL_.

Table 21.2: Data Dictionary Views for External Procedures
To Answer the Question...	Use This View	As In
What libraries have I created?	USER_LIBRARIES	SELECT * FROM user_libraries;
What packages depend on library foo?	USER_DEPENDENCIES	SELECT * FROM user_dependencies WHERE referenced_name = 'FOO';
What is the source code for the spec of my PL/SQL package "bar" that calls the library?	USER_SOURCE	SELECT text FROM user_source WHERE name = 'BAR' AND type = 'PACKAGE' ORDER BY line;
What is the source code for my PL/SQL package body named bar that calls the library?	USER_SOURCE	SELECT text FROM user_source WHERE name = 'BAR' AND type = 'PACKAGE BODY' ORDER BY line;

21.6.2 Rules and Warnings About External Procedures

As with almost all things PL/SQL, external procedures come with an obligatory list of cautions:

Oracle supplies a script to facilitate the debugging of external procedures. The name of the script is dbgextp.sql , and it's found in $ORACLE_HOME/plsql/demo on UNIX (on Windows NT, it's in <Oracle root directory>\pls80 ).

dbgextp.sql builds a package needed for debugging, debug_extproc, and a library called debug_extproc_library. Don't look for a lot of documentation on dbgextp.sql -- you won't find it. In fact, inline comments in the file itself are the only place that the debugging process is documented in any detail.

Although it wasn't exactly a cookbook process, I was able to demonstrate that the GNU debugger (gdb 4.16) can attach to a running process, as required to debug external procedures. Here's how I tried it.

First, I compiled the shared library file with the compiler option (-g) needed to include symbolic information for the debugger.

After running dbgextp.sql , I ran the "execute" command on DEBUG_EXTPROC.STARTUP_EXTPROC_AGENT from SQL*Plus and it ran fine. However, I got a "permission denied" error every time I tried to attach the debugger to the extproc. The problem was that the extproc executable had file permissions of 0751 (i.e., -rwxr-x -- x). This means that I cannot, by default, debug unless I am logged in as the "oracle" user or am in the dba group (this is somewhat intentional because Oracle says that the extproc listener shouldn't be started by a user that can write database files -- that could be dangerous if you have a bug in your external procedure). So I issued the command:

chmod o+r $ORACLE HOME/bin/extproc

which got me past the "permission denied" error.

I then loaded the extproc program into gdb and set a breakpoint on the "pextproc" symbol per the instructions in the dbgextp.sql file. Then I typed "@tz_utl" to call the timezone external procedure from the SQL*Plus session. When the external procedure was called, extproc hit the breakpoint.

After executing a gdb "share" command so gdb would read the symbols in my just-loaded external shared library, I was able to set a breakpoint on the get_timezone external procedure. It worked pretty well after that.

I did find that gdb seemed to get confused when I tried to debug one of our shared libraries after another. Detaching the extproc and restarting gdb fixed this problem.

While the mode of each formal parameter (IN, IN OUT, OUT) may have certain restrictions in PL/SQL, C does not honor these modes. Differences between the PL/SQL parameter mode and the usage in the C module cannot be detected at compile time, and could also go undetected at runtime. The rules are what you would expect: don't assign values to IN parameters; don't read OUT parameters; always assign values to IN OUT and OUT parameters; always return a value of the appropriate datatype.
Modifiable INDICATORs and LENGTHs are always passed by reference for IN OUT, OUT, and RETURN. Unmodifiable INDICATORs and LENGTHs are always passed by value unless you specify BY REFERENCE. However, even if you pass INDICATORs or LENGTHs for PL/SQL variables by reference, they are still read-only parameters.
Although you can pass up to 128 parameters between PL/SQL and C, if any of them are float or double, your actual maximum will be lower. How much lower depends on the operating system.
Since extproc might be a multithreaded process in future releases, your external code should avoid the use of "static" variables.
Your external procedure may not perform DDL commands, begin or end a session, or control a transaction using COMMIT or ROLLBACK. (See Oracle's PL/SQL User's Guide and Reference for a complete list of illegal OCI routines.)

21.6 External Procedure Housekeeping

21.6.1 Data Dictionary

21.6.2 Rules and Warnings About External Procedures

A Debugging Odyssey