home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

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.)


Previous: 21.5 OCI Service Routines Oracle PL/SQL Programming, 2nd Edition Next: 21.7 Examples
21.5 OCI Service Routines Book Index 21.7 Examples

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference










??????????????@Mail.ru