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.
The following is a selected list of available Oracle packages and a description of their purpose. 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.
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;
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.
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.
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; /
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.
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.
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.
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.
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; /
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.
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.
select utl_url.escape('http://www.oracle.com/url with space.html') from dual;
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.