Section 27.8.  Maintaining External Procedures

27.8. Maintaining External Procedures Here are some assorted bits of information that will assist you in creating, debugging, and managing external procedures. 27.8.1. Dropping Libraries The syntax for dropping a library is simply: DROP LIBRARY library_name; The Oracle user who executes this command must have the DROP LIBRARY or DROP ANY LIBRARY privilege. Oracle does not check dependency information before dropping the library. This fact is useful if you need to change the name or location of the shared object file to which the library points. You can just drop it and rebuild it, and any dependent routines will continue to function. (More useful, perhaps, would be a requirement that you use a DROP LIBRARY FORCE command, but such an option does not exist.) Before you drop the library permanently, you may wish to look in the DBA_DEPENDENCIES view to see if any PL/SQL module relies on the library. 27.8.2. Data Dictionary There are a few entries in the data dictionary that help manage external procedures. Table 27-3 shows the USER_ version of the dictionary tables, but note that there are corresponding entries for DBA_ and ALL_. Table 27-3. Data dictionary views for external procedures To answer the question... Use this view Example What libraries have I created? USER_LIBRARIES SELECT * FROM user_libraries; What stored PL/SQL programs use the xyz library in a call spec? USER_DEPENDENCIES SELECT * FROM user_dependencies WHERE referenced_name = 'XYZ'; What external procedure agents (both dedicated and multi- threaded) are currently running? V$HS_AGENT SELECT * FROM V$HS_AGENT WHERE UPPER(program) LIKE 'EXTPROC%'; What Oracle sessions are using which agents? V$HS_SESSION SELECT s.username, h.agent_id FROM V$SESSION s, V$HS_SESSION h WHERE s.sid = h.sid; 27.8.3. Rules and Warnings As with almost all things PL/SQL, external procedures come with an obligatory list of cautions: 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, and 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. If you use the multithreaded agent feature in Oracle Database 10g, there are special additional restrictions on your programs. All the calls you invoke from the C program must be thread-safe. In addition, you want to avoid using global C variables. Even in the nonthreaded version, globals may not behave as expected due to "DLL caching" by the operating system. 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 list of unsupported OCI routines.)