23.5.1 Creating Stored Objects

The syntax to create stored objects is as follows:

CREATE [OR REPLACE] PROCEDURE proc_name (param_list) AS ... ;
CREATE [OR REPLACE] FUNCTION func_name (param_list) RETURN datatype AS ... ;
CREATE [OR REPLACE] PACKAGE pkg_name AS ... ;
CREATE [OR REPLACE] PACKAGE BODY pkg_name AS ... ;
CREATE [OR REPLACE] DATABASE TRIGGER trig_name AS ... ;

Everything from the object name onwards has the same syntax shown in the appropriate chapter on that object. The CREATE OR REPLACE syntax in front of the normal syntax indicates to SQL*Plus that you want to create the object in the database. This is a DDL (data definition language) statement in SQL, just like a CREATE TABLE statement.

You must create a package specification separately from the package body. In fact, you must create the specification first, or else you receive the following error:

PLS-00304: cannot compile body of 'pkg_name' without its specification

When an object is created, it is stored in a data dictionary table. The source for procedures, packages, functions, and package bodies is kept in the SOURCE$ table (owned by SYS). Views into the SOURCE$ table are:

ALL_SOURCE

Source for all objects to which you have access, and therefore EXECUTE privilege on that object

USER_SOURCE

Source for all objects you have created

The statements executed by a trigger are stored in the ACTION column of the TRIGGER$ table (owned by SYS). You can view all triggers you created by accessing USER_TRIGGERS, or all triggers to which you have access by viewing the contents of ALL_TRIGGERS.

Even though you store your modules in the database, you do not maintain your source code for these modules directly in their data dictionary tables. Instead, you must always keep the source code in text files. If you need to make a change to an object, make the changes in the source code and then replace the object in the database.

In order to distinguish regular SQL*Plus scripts from scripts that maintain stored objects, I employ a different file extension for them, as shown in the following table:

23.5.2 Tips for Storing Code in Files

Here are some tips for storing code in files:

• Always keep your CREATE OR REPLACE statements for package specifications separate from those of your package bodies. That way you can compile each separately. If you keep them together, when you change something in the package body, you will recompile both specification and body. When you recompile your specification, the status of any programs referencing an element in that package will be marked INVALID. They will then have to be recompiled.

• When you construct installation scripts for your package-based applications, first compile all specifications and then compile all bodies. When package bodies are compiled, references to other package elements are resolved through the specification. With all the specifications "in place" in the database, you are much less likely to run into seemingly circular dependencies. Note that you will have to be careful about the order in which you compile specifications to resolve any dependencies in those specifications themselves. On the other hand, you can compile your bodies in any order you like.

23.5.3 Changing Stored Objects

There are two ways to change an existing object in the database:

• Drop the object and recreate it

• Use the CREATE OR REPLACE syntax instead of CREATE

With the first approach, I would issue a DROP command followed by a CREATE command, as follows:

DROP PROCEDURE calc_totals;
CREATE PROCEDURE calc_totals
   (company_id_in IN NUMBER, totals_out OUT NUMBER)
IS
BEGIN
   ... body of procedure ...
END;
/

Alternatively, I can use a single command to indicate that if the object already exists, replace it in its entirety with the new version found in the statement:

CREATE OR REPLACE PROCEDURE calc_totals
   (company_id_in IN NUMBER, totals_out OUT NUMBER)
IS
BEGIN
   ... body of procedure ...
END;
/

If you are replacing a very large procedure, you might at times encounter an error like the following:

CREATE OR REPLACE PACKAGE BODY PSGlobal
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01562: failed to extend rollback segment (id = 2)
ORA-01547: failed to allocate extent of size 5 in tablespace 'ROLLBACK_DATA'

The RDBMS writes the rows being replaced (deleted) to the rollback segment; if the program is very large and your rollback segments are not commensurately massive, your CREATE OR REPLACE does not succeed. To get around this problem you can increase the size of your rollback segments or you can drop your object before you try to CREATE OR REPLACE. Because the DROP is a DDL statement, it will perform a commit upon completion. Then your rollback segments don't have to maintain the old, deleted rows while the INSERT is taking place.

23.5.4 Viewing Compilation Errors in SQL*Plus

When the PL/SQL engine encounters errors, it writes the generated error messages to the ERROR$ table (owned by SYS). To see these error messages, you can either write a query against the USER_ERRORS view, or you can type the following command at the SQL*Plus prompt:

SQL> show errors

The "show errors" command runs a script that formats the contents of USER_ERRORS so you can easily scan the errors. Here is an example of the output from show errors:

Errors for PACKAGE BODY PS_GLOBAL:

SQL> show errors
LINE/COL ERROR
-------- --------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/14     PLS-00304: cannot compile body of 'PSGLOBAL' without its
         specification

So you have a line number and an error message. Unfortunately, it is difficult to use these line numbers, since they do not correspond directly to the line numbers in your file. They are always relative to a specific program or PL/SQL block (remember that you can have more than one such program unit in a file). Even within that block, you will find it difficult to correlate the line number with a particular PL/SQL statement.

But, wait, there is hope for those line numbers! Even if they do not match your source code, they do match the line numbers saved with the text of that module in the data dictionary view of source code. The next section shows how to query this view to identify the line of code corresponding to a line number.