Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
ISBN: 0596514468
EAN: 2147483647
EAN: 2147483647
Year: 2004
Pages: 234
Pages: 234
Authors: Steven Feuerstein, Bill Pribyl
| Chapter 23 Managing Code in the Database |
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.
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:
Source for all objects to which you have access, and therefore EXECUTE privilege on that object
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:
| File Type | Extension |
|---|---|
| General SQL*Plus script | <file>.sql |
| Testing script | <file>.tst |
| Stored procedure | <file>.sp |
| Stored function | <file>.sf |
| Stored package body | <file>spb |
| Stored package specification | <file>.sps |
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.
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.
An important point to remember with stored objects is that the command to CREATE OR REPLACE an object does not actually execute the stored object. It simply places the code for that object in the database and compiles it. To execute the program, you can then use the EXECUTE command at the SQL> prompt, or place a call to the program in a PL/SQL block. (This distinction is one reason I think that it is so important to use a different file extension (.sp) for the CREATE statements than for the normal SQL*Plus scripts (.sql ).)
Say, for example, you create a file named calctot.sp, as follows:
CREATE OR REPLACE PROCEDURE calc_totals (company_id_in IN NUMBER, totals_out OUT NUMBER) IS BEGIN ... body of procedure ... END; /
Then, when you execute this file with the command:
SQL> start calctot.sp
you receive the following message:
Procedure created.
No totals will have been calculated. The procedure was simply created. To calculate and display those totals you can now create a .sql file with a PL/SQL block, as follows:
DECLARE mytot NUMBER; BEGIN calc_totals (1005, mytot); DBMS_OUTPUT.PUT_LINE ('Totals for company 1005 is ' || TO_CHAR (mytot)); END; /and then run the script:
SQL> start calctots.sql
If you do receive a compile error when creating a stored object, you see a message like this:
Warning: Package Body created with compilation errors.
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.
| 23.4 Remote Procedure Calls | 23.6 Using SQL to Examine Stored Objects |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.