You can use SQL*Plus to manage stored objects like database triggers, procedures, functions, and packages. When Oracle7 was first released, SQL*Plus was the only way to create stored procedures. With the advent of CDE and Oracle Procedure Builder, however, you now have a number of choices for creating and updating your stored objects. Still, many of you will rely on SQL*Plus, especially if your DBA activities are performed on a UNIX (or other non-Windows) server.
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:
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:
Here are some tips for storing code in files:
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.
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.