facebook twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: Introducing Subprograms

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

Up to this point, we have dealt primarily with anonymous blocks. These scripts are chunks of PL/SQL code that can be used to accomplish a wide variety of tasks. However, since PL/SQL is tightly integrated with SQL, it makes sense that code be available within the database itself. Subprograms provide all of the functionality possible in anonymous blocks but are stored in the database.

Lesson Goals

  • Learn about anonymous blocks
  • Learn to identify types of subprograms
  • Learn about the storage of subprograms within the database
  • Learn to identify data dictionary views related to subprograms
  • Learn to identify subprogram dependencies

Anonymous Blocks

Anonymous blocks consist of a (required) BEGIN and END keyword, an optional DECLARE section, an optional EXCEPTION section and required body content. Blocks can be arbitrarily nested, and can contain control structures that vary the execution path. This basic structure evident in anonymous blocks is used to construct various types of subprograms.

Types of Subprograms

A procedure is a subprogram that does not return a value (though there are ways to retrieve data through parameters, explained in detail later). It is generally called directly or by another PL/SQL subprogram.

A function is a PL/SQL object that does return a value directly. They can be called within SQL statements.

A trigger is a subprogram that is distinguished by the fact that it is fired when a given event occurs in the database (for example when an INSERT statement is executed on a specific table).

A package is a PL/SQL structure that is used to group related subprograms together.

Finding Subprograms through SQLDeveloper

If you are using a tool like SQL Developer, you can obtain a good deal of information about database objects, their contents and their status. In SQL Developer the connections pane contains a tree with each object type available to the user. In the image below, the procedures node is expanded and several stored procedures are visible. One procedure has a red circle with an X, indicating that it is invalid. An invalid object has a compilation error and cannot be run. SQL Developer view of Procedures

Right click on an object to view, edit, compile, run, change security access, drop, format or export the object. Error and diagnostic messages are displayed in separate areas along with text written using DBMS_OUTPUT. See the SQL Developer Help documentation for more information. Other third party tools contain similar options as well, but all of the items displayed are based upon the objects and functionality of the Oracle database itself.

Finding Subprograms in the Oracle Data Dictionary

Information about subprograms (including the actual source code) is stored in the Oracle Data Dictionary. If you would like general information about the Oracle Data Dictionary, the DICTIONARY view can provide information about each view, and the DICT_COLUMNS view has comments about individual columns in each view.

Code Sample:

select object_type, count(*) 
from all_objects 
group by object_type 
order by object_type;

Code Explanation

The ALL_OBJECTS view holds data on all objects available to the current user. The OBJECT_TYPE field indicates the type of object - in this course we are most interested in PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, or TRIGGER. This SQL query returns a count of each object type that the user can access.

Remember, the data dictionary contains views that have the prefix of ALL_ to indicate objects owned by and accessible to the user, USER_ to indicate objects that are owned by the current user, and DBA_ to indicate objects anywhere in the database. Unless you are connected as the SYS or SYSTEM user, the DBA_ views require permission to use.

Code Sample:

select * from user_procedures;

Code Explanation

This SQL query returns the contents of the USER_PROCEDURES view. Although stored procedures are also listed in the ALL_OBJECTS view, this view contains columns that are specific to stored procedures.

Code Sample:

select * from user_triggers;

Code Explanation

Information about triggers, including the events that cause them to fire and the actual trigger source code are returned in this query against the USER_TRIGGERS view.

Code Sample:

select * from user_source order by name, type, line;

Code Explanation

The USER_SOURCE view contains the actual PL/SQL source code of objects compiled within the Oracle Database. The trigger source code available in the USER_TRIGGERS view is also available here. Each row in the view corresponds to a line of source code. This view can be used to search all PL/SQL code that exists in a given Oracle Database.

The following query does a case insensitive search on all lines of PL/SQL code that have the string "add" in them.

select * from user_source where lower(text) like '%add%';

Object Dependencies

Code Sample:

desc user_objects

desc public_dependency

SELECT child_obj.object_name,  child_obj.object_type,
       parent_obj.object_name || ' ' ||
            parent_obj.object_type as refers_to
FROM public_dependency p
     join user_objects child_obj
          on child_obj.object_id = p.object_id
     join user_objects parent_obj
          on parent_obj.object_id = p.referenced_object_id
where child_obj.object_name = 'EMP_DETAILS_VIEW';

Code Explanation

A database object is described as dependent upon another database object if it relies upon the existence and validity of the object in order to function. In the query above, the various tables that are defined as dependencies for the EMP_DETAILS_VIEW are returned by accessing the PUBLIC_DEPENDENCY data dictionary view.

When a subprogram is created, it is compiled and stored in the Oracle database. If there are compilation errors, the object will be created, but will not be functional until the errors are addressed. Although compilation errors are immediately available when an object is compiled, information on them is also stored in the database until the errors are fixed. The status of an object that contains compilation errors is INVALID.

Code Sample:

select * from user_errors;

Code Explanation

Whenever you compile an Oracle object, you can issue the SHOW ERRORS command to list any compilation errors during the current connection session only. You can issue SHOW ERRORS PROCEDURE XXXX to list any compilation errors during the current connection session or at any time in the future. The USER_ERRORS view can also be queried to obtain this information.

Subprogram Information in the Oracle Data Dictionary

Duration: 10 to 15 minutes.
  1. Create the procedure by executing the script in the Demo below.
  2. Locate general information in the Oracle Data Dictionary about the procedure (as well as SQL Developer).
  3. Display the Source Code using the Data Dictionary (as well as SQL Developer).
  4. Remove the trailing semicolon following the NULL statement from the procedure and compile the procedure again.
  5. Look at the results in user source (and notice that the change is reflected here).
  6. Retrieve error information from the Oracle Data Dictionary using a query (and notice where the error shows up in SQL Developer). Determine the status of the object by looking at USER_OBJECTS.
  7. Add the trailing semicolon following the NULL statement from the procedure and compile the procedure again (it should be valid now). Check the status of the object in USER_OBJECTS.

Code Sample:

create or replace procedure a_simple_subprogram


-- You can create the A_SIMPLE_SUBPROGRAM by copying the code into 
-- the editor and choosing the Run Script button (and by a number of 
-- other means through the SQL Developer interface). 
-- To locate general information in the Oracle Data Dictionary about 
-- the procedure:

select * from user_procedures 
where object_name = 'A_SIMPLE_SUBPROGRAM';

select * from user_objects 
where object_name = 'A_SIMPLE_SUBPROGRAM';

-- You can also click the Procedures node in the Connections
-- Navigator in SQL Developer to view information about the
-- stored procedures.
-- To display the Source Code using the Data Dictionary: 

select * from user_source
where name = 'A_SIMPLE_SUBPROGRAM';

-- You can double click on the procedure in question  
-- (A_SIMPLE_SUBPROGRAM in this case) to view the source code.  
-- You can also right click on the procedure and choose the 
-- Edit option from the popup menu.
-- There are several ways to modify A_SIMPLE_SUBPROGRAM
-- and recompile.
-- One is:
-- * Right click on the A_SIMPLE_SUBPROGRAM procedure
--     and choose Edit.
-- * Delete the trailing semicolon
-- * Click the Compile Button
-- To display the source code using the Data Dictionary :

select * from user_source
where name = 'A_SIMPLE_SUBPROGRAM';

-- The following sections can be repeated as necessary as the object
-- starts in a valid state, is invalidated due to a compilation 
-- error and is subsequently fixed.
-- To Retrieve the error information from the Oracle Data Dictionary 
-- using a query:

select * from user_errors;

-- The error is visible in a number of panes in SQL Developer and 
-- the icon for the stored procedure also indicates that the object
-- is an invalid state due to a compilation error.
-- The status of the object can be viewed in the user_objects table 
-- as follows

select status from user_objects 
where object_name = 'A_SIMPLE_SUBPROGRAM';
-- Notice the same error message text in the Compiler-Log tab
-- at the bottom of SQL Developer.  
-- You can view the errors using SHOW ERROR command in SQL PLUS
-- as follows:

show err procedure a_simple_subprogram