facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Using Oracle Supplied Packages

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

The Oracle database goes far beyond providing the base functionality expected in a standard RDBMS. Procedural programming is available through the use of PL/SQL. In addition, Oracle includes a large number of packages that are included with a standard database installation. Many other paid options available in Oracle are implemented (in full or in part) as PL/SQL packages.

Lesson Goals

  • Learn how Oracle packages extend the power of SQL and PL/SQL
  • Learn what Oracle supplied packages are available
  • Learn about packages related to input/output
  • Learn about packages related to networking

Oracle Supplied Packages

The following is a selected list of available Oracle packages and a description of their purpose. Image of list of selected Oracle packages This list does not include packages specifically related to database administration, security, moving data between databases, scheduling jobs within the database, queuing and other specialized optional packages.

Identifying Available Packages

Oracle supplied packages can be associated with various owner/schemas. The packages available vary by the database version, edition type (Standard, Enterprise) and options installed. To see the available Oracle installed packages owned by SYS in your installation, run the following query when logged in with a username (such as SYSTEM) that has permission to select the DBA_OBJECTS view.

SELECT DISTINCT owner, object_name FROM dba_objects WHERE owner = 'SYS' AND object_Type = 'PACKAGE' ORDER BY owner, object_name;

Identifying Package Subprograms

Oracle supplied package specifications can be referenced the same way as user defined packages. From the SQL*Plus command line, use the describe command and from SQL Developer click on the Connection Navigator tree on the left hand side of the screen.

Selected Oracle Supplied Package Demos

The following are a few examples of Oracle supplied packages that are commonly available and do not require special configuration or administrative prerequisites. The first of these will be of interest to PL/SQL developers interested in taking advantage of new language features but work in environments with a mixture of Oracle versions.

Code Sample:

Using-Oracle-Supplied-Packages/Demos/DBMS_DB_VERSION_example.sql
BEGIN

$IF DBMS_DB_VERSION.VER_LE_10 $THEN
   dbms_output.put_line('version 10 and earlier');
$ELSIF DBMS_DB_VERSION.VER_LE_11 $THEN
   dbms_output.put_line('version 11');   
$ELSE
   dbms_output.put_line('version 12 and later');
$END

END;
/

Code Explanation

For the most part, PL/SQL is backwards compatible with previous versions of Oracle. If you write code that you only want to compile in certain versions of the database, the DBMS_DB_VERSION package is used to retrieve Oracle version numbers and other information required.

Packages Related to Input/Output

There are a number of ways to get data into and out of the database. Using SQL, you can retrieve data using SELECT statements and add new data using INSERT statements. As seen throughout the course, the DBMS_OUTPUT provides a mechanism for returning buffers of data. These have been displayed to the screen using the PUT_LINE procedure. The DISABLE procedure can be used to disable DBMS_OUTPUT so that output is not displayed. The ENABLE procedure can be used to enable DBMS_OUTPUT and set the buffer size. You can control buffer retrieval using the GET_LINE and GET_LINES procedures.

BEGIN dbms_output.enable(1000000); dbms_output.put_line('You have seen this before...'); END;

Another package related to both input and output is the UTL_FILE package. This package allows you to read and write files on the database server. Note, this package does not allow you to read files where the database client resides (unless the client is the database server itself). The use of this package involves interaction with the underlying operating system. Therefore additional configuration is required including the creation of Oracle Directory objects and the granting of appropriate permissions.

Packages Related to Networking

There are a number of Oracle packages related to networking. The UTL_HTTP and UTL_TCP packages allow you to access the internet through SQL and PL/SQL. The UTL_MAIL and UTL_SMTP packages allow you to interact using email. Because of the security risks related to network access, a package called DBMS_NETWORK_ACL_ADMIN is available in 11g which is related to "Access Control Lists" which limit the network resources available to database users.

Numerous other packages have networking implications including UTL_URL which is used to manipulate URLs and various packages related to XML processing.

DBMS_SQL: Dynamic SQL

PL/SQL includes a number of options that allow for dynamic generation and processing of SQL. The DBMS_SQL package provides an even greater degree of flexibility in this area.

Code Sample:

Using-Oracle-Supplied-Packages/Demos/DBMS_SQL_example.sql
DECLARE
  v_SQL VARCHAR2(32767) := 'select * from employees where rownum=1';
  v_c 	NUMBER;
  v_execute 	  NUMBER;
  v_columnCount INTEGER;
  v_recTab 	    DBMS_SQL.DESC_TAB;
  v_varcharVal 	VARCHAR2(4000);
  v_numberVal 	NUMBER;
  v_dateVal 	  DATE;
  v_ret 	      NUMBER;  
BEGIN
  v_c := DBMS_SQL.OPEN_CURSOR;
  
  DBMS_SQL.PARSE(v_c, v_SQL, DBMS_SQL.NATIVE);
  
  v_execute := DBMS_SQL.EXECUTE(v_c);
  
  DBMS_SQL.DESCRIBE_COLUMNS(v_c, v_columnCount, v_recTab);
  
  FOR j in 1..v_columnCount
    
    LOOP
      CASE v_recTab(j).col_type
        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(v_c,j,v_varcharVal,2000);
        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(v_c,j,v_numberVal);
        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(v_c,j,v_dateVal);
        ELSE DBMS_SQL.DEFINE_COLUMN(v_c,j,v_varcharVal,2000);
      END CASE;
    END LOOP;
    
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(v_c);
    EXIT WHEN v_ret = 0;
    
    FOR j in 1..v_columnCount
      LOOP
        CASE v_recTab(j).col_type
          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(v_c, j, v_varcharVal);
            DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
                      v_recTab(j).col_name || ' ' || v_varcharVal);
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(v_c,j,v_numberVal);
            DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
                      v_recTab(j).col_name || ' ' || v_numberVal);
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(v_c,j,v_dateVal);
            DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
                      v_recTab(j).col_name || ' ' || v_dateVal);
          ELSE
            DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
                      v_recTab(j).col_name || ' ' || v_varcharVal);
        END CASE;
      END LOOP;
   END LOOP;
END;
/

Code Explanation

This example demonstrates the use of the DBMS_SQL package to parse and execute a SQL statement. The retrieved metadata can be used to dynamically retrieve the column data. This is useful when you don't know which columns will be retrieved at the time you are coding. Change the SQL query that is assigned to the V_SQL variable to see the flexibility of this approach.

DBMS_URL: Working with URLS

Data related to resources on the internet (or on local networks) are often stored in the database. This type of data needs to be handled differently than simple string data.

Code Sample:

Using-Oracle-Supplied-Packages/Demos/UTL_URL_example.sql
select utl_url.escape('http://www.oracle.com/url with space.html')
from dual;

Code Explanation

If you need to store and retrieve URLs (web addresses) you need to consider special encoding in your code. The UTL_URL package is used in this example to replace spaces with required character codes.

The exercise below will give you the opportunity to explore the DMBS_APPLICATION_INFO package. This package is used to provide visibility into long running application processes.