9.2 Referencing Oracle Packages

[ Team LiB ]

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

[ Team LiB ]