9.2 Referencing Oracle Packages


Each language has syntax for referencing other program units. The C programming language uses an INCLUDE directive to reference header files.

 
 #include <stdio.h> #include <stdlib.h> 

Perl scripts call procedures in the Windows32 library with a USE WIN32 directive.

 
 use Win32::Registry; use Win32::OLE; 

Java classes import whole packages and package classes.

 
 package project.students; import java.io.*; import java.util.*; import java.sql.*; import oracle.sql.*; import oracle.jdbc.*;  import project.util.*; 

With each of these environments, it is a task to locate the library code you need and make sure it is in your path . PL/SQL does not require a compiler directive to use other packages. If you want to write PL/SQL that uses the DBMS_OUTPUT package, or another Oracle PL/SQL package, you simply write the procedure call in your code.

9.2.1 Creation of the Environment

The database administrator compiles many PL/SQL packages into the Oracle SYS account. This is part of creating the database. The database creation process also creates public synonyms and public grants for these packages. Naturally, some packages are intended for Oracle internal use and have restricted access.

These packages provide a robust Application Programming Interface (API). You can use the Oracle API to develop PL/SQL procedures that use methods in Java classes, write to host files, send mail through an SMTP service, and many other functions.

As you write PL/SQL you display output using the DBMS_OUTPUT package. This is one component of the API, and for most programmers, the most frequently used package during development. The API for DBMS_OUTPUT is listed in Section 9.6.

The following illustrates the SYS statements executed, during database creation, that make DBMS_OUTPUT available for general use.

 
 1.GRANT EXECUTE ON DBMS_OUTPUT TO PUBLIC; 2.CREATE PUBLIC SYNONYM DBMS_OUTPUT FOR SYS.DBMS_OUTPUT; 

The first statement means that SCOTT and all future Oracle accounts can write PL/SQL that use the DBMS_OUTPUT package. The keyword PUBLIC gives the grant to all users. The second statement means that SCOTT can write PL/SQL with statements like:

 
 DBMS_OUTPUT.PUT_LINE('Hello'); 

Without the second statement, SCOTT would code the following:

 
 SYS.DBMS_OUTPUT.PUT_LINE('Hello'); 

Figure 9-1 illustrates the encapsulation of packages in the SYS account.

Figure 9-1. Referencing the Oracle PL/SQL Packages.

graphics/09fig01.jpg

When SCOTT compiles a procedure, Oracle identifies all referenced objects. If the SCOTT procedure references the DBMS_OUTPUT package, Oracle determines that DBMS_OUTPUT is a synonym for SYS.DBMS_OUTPUT and that SCOTT has EXECUTE privileges on that package.

9.2.2 The API

How extensive is the API? It includes hundreds of packages. Most packages begin with DBMS. Some utility packages begin with UTL such as UTL_SMTP ”a PL/SQL API interface to the SMTP service.

To preview all packages in the API that begin with DBMS or UTL, query the data dictionary view ALL_OBJECTS. The following SQL generates a spool file listing the DBMS and UTL packages. This script runs with TERM off, which turns terminal-output off. The script directs output strictly to the spool file, ALL_OBJECTS.LST.

 
 -- Filename ALL_OBJECTS.SQL set pagesize 0 set term off set feedback off spool all_objects SELECT  object_name  FROM   all_objects WHERE  owner='SYS' AND         object_type='PACKAGE' AND    (object_name like 'DBMS%' OR object_name like 'UTL%'); set feedback on set term on spool off 

The output file ALL_OBJECTS.LST will include hundreds of packages. The following illustrates the text of the list file generated:

 
  SQL>  @ALL_OBJECTS  This shows a few of the hundreds of packages.  DBMS_ALERT DBMS_APPLICATION_INFO  DBMS_AQ  DBMS_AQADM  DBMS_AQADM_SYS DBMS_AQADM_SYSCALLS DBMS_AQIN DBMS_AQJMS DBMS_AQ_EXP_HISTORY_TABLES 

As with any API, how do you use it? There are three approaches to learning the API for an Oracle package.

  • You can use the SQL*Plus Describe command. This is a definition of the interface only.

  • You can extract the package source from the data dictionary. This frequently includes examples and detailed descriptions on using the API.

  • You can visit the free Oracle Web site, technet.oracle.com , to review the package documentation.

You can describe a package specification with the SQL*Plus DESCRIBE command. Examples in the text use the DESCRIBE command for tables. This command also describes views procedures, functions, and packages. The DESCRIBE command shows the interface. This includes procedure and function names with each parameter type, mode, and default option. This is useful if you are already familiar with an API and need to review the specifics of the interface.

 
 SQL> DESC name-of-package-procedure-function 

For example, the following spools the interface specification for the DBMS_OUTPUT package to a file, DBMS_OUTPUT_SPEC.LST. The following session output shows a partial listing ”there are many other procedures to the DBMS_OUTPUT package.

 
  SQL>  spool dbms_output_spec  SQL>  desc dbms_output  PROCEDURE ENABLE   Argument Name      Type            In/Out Default?   ------------------ --------------- ------ --------   BUFFER_SIZE        NUMBER(38)      IN     DEFAULT   PROCEDURE PUT_LINE   Argument Name      Type            In/Out Default?   ------------------ --------------- ------ --------   A                  VARCHAR2        IN   SQL>  spool off 

The SQL*Plus DESCRIBE output of a package is a tabulated style view of the package specification. This may not be sufficient information if you are learning to use this package for the first time. It is merely a definition of the interface at a quick glance. The preceding DESCRIBE output shows procedures ENABLE and PUT_LINE, which have this interface definition.

 
 procedure enable(buffer_size IN NUMBER); procedure PUT_LINE(A VARCHAR2); 

You can describe functions and procedures as well. Refer to Section 9.1, "DBMS_OUTPUT," for additional information on using DBMS_OUTPUT.

A second option is to pull the package specification source from the data dictionary with a query against the view ALL_SOURCE. The result is the package interface specification that frequently includes comments on how to use the API. The SQL to perform this is included in Section 9.6, "USER_SOURCE."



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net