#

| Chapter 21 External Procedures |

## 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:

- 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.5 OCI Service Routines |  | 21.7 Examples

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.