0276-0279
Page 276
In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling is not established. This exception is illustrated in the following example:
when OTHERS then out_status_code := `FAIL'; out_msg := g_out_msg ` ` SUBSTR(SQLERRM, 1, 60);
This technique is used in the order_total sample procedure to trap all procedure errors other than NO_DATA_FOUND. The information passed back to the caller in out_msg is the subprogram name contained in g_out_msg concatenated with the first 60 characters returned from the SQLERRM function by the SUBSTR function.
| NOTE |
Both SQLERRM and SUBSTR are internal PL/SQL functions. You can find a complete list of internal functions later in this chapter. |
SQLERRM returns a valid message only when called inside an exception handler unless an argument that is a valid SQL error number is passed to the function. The Oracle error code is the first part of the message returned from SQLERRM. Next is the text associated with that Oracle error code.
In this manner, all errors encountered during procedure execution are trapped and passed back to the application for debug purposes. The following is a sample return error from the order_total procedure:
FAIL: init_line_items ORA-01001: invalid cursor
This error message (formatted by the application) reveals an illegal cursor operation in the subprogram init_line_items. The portion of the message returned from SQLERRM begins with the ORA-01001 SQL error code. Another error message is illustrated in the following example:
FAIL: calc_ship_charges
In this case, the subprogram calc_ship_charges had a NO_DATA_FOUND error. This is determined by the fact that no SQL error messages are concatenated with the message text.
Comments
Although some people think commenting code is unnecessary, you can use two methods to place comments within your PL/SQL procedures. The first is for commenting single lines; the syntax is shown in the following example:
--*************** CREATE PACKAGE ORDER_TOTALING ***************
Page 277
A double dash at the start of the line marks the line as a comment. The second method is used to place a sequence of comment statements in a PL/SQL package:
/* The following code generates a list of unique recipient numbers from all recipient numbers for a particular order */
A comment block such as this begins with /* and ends with */. You can place single-line and multiple-line comments in any portion of PL/SQL code.
| CAUTION |
PL/SQL blocks that are dynamically compiled in Oracle precompiler applications do not support the use of single-line comments. |
Stored Procedures
You can store PL/SQL code in the Oracle database with the RDBMS Procedural Database Extension. Advantages of using stored procedures include easier maintenance, decreased application size , increased execution speed, and greater memory savings, to name a few. With this in mind, explore the various techniques for accessing stored procedures described in the following sections.
Referencing Stored Procedures
Another big advantage to using stored procedures is the capability to reference the procedure from many different Oracle applications. You can make reference to stored procedures with other stored procedures, database triggers, applications built with Oracle precompilers, or Oracle tools such as SQL*Forms. The following example calls the order_total procedure from another procedure:
order_total.get_order_total (order_num, status_code, message, merch_gross, shipping, taxes, grand_total);
The following example shows the same order_total procedure referenced from Pro*C, an Oracle precompiler application:
EXEC SQL BEGIN order_total.get_order_total ( :order_num, :status_code, :message, :merch_gross, :shipping, :taxes,
Page 278
:grand_total); END; END-EXEC;
All parameters to the order_total procedure are Oracle bind variables that you must declare before the reference to the package. The final example illustrates a call to the order_total package from a SQL*Forms application:
BEGIN ... order_total.get_order_total ( order_num, status_code, message, merch_gross, shipping, taxes, grand_total); ... END;
Once again, you must declare all variables passed as parameters before calling the procedure.
| CAUTION |
Calling stored procedures with COMMIT, ROLLBACK, or SAVEPOINT statements from SQL*Forms is prohibited , as discussed later in this chapter. |
Stored Procedure States
After compilation, a stored procedure exists in either a valid or invalid state. If you haven't made any changes to the procedure, it is considered valid and can be referenced. If any subprogram or object referenced within a procedure changes, its state becomes invalid. Only procedures in a valid state are available for reference.
Referencing a procedure that is invalid causes Oracle to recompile any and all objects called by the referenced procedure. If the recompilation does not succeed, Oracle returns a runtime error to the caller, and the procedure remains in an invalid state. Otherwise, Oracle recompiles the referenced procedure, and if the recompilation is successful, execution continues.
| CAUTION |
Stored procedures are located in the Oracle SGA after compilation. If the SGA is too small for the user base, the procedure might be swapped out and become invalid with no indication to the caller. The first reference to the procedure after it is swapped out causes a recompilation, returning it to a valid state. |
Page 279
Overloading
The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call; the count and datatypes of formal parameters are also considered.
PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation as illustrated by previous examples on application references to stored procedures. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.
Commits
The COMMIT statement is available to PL/SQL procedures unless you call the procedure from a SQL*Forms application. To enable COMMITs within a procedure called by a SQL*Forms application, you must issue the DDL statement ALTER SESSION ENABLE COMMIT IN PROCEDURE before you invoke the PL/SQL object. Because you cannot issue this command from SQL*Forms, you must create a user exit from which you can issue the ALTER SESSION statement and subsequently call the procedure. The following is an example of calling the order_total procedure from SQL*Forms through a user exit:
user_exit(`order_totl');
In this case, the order_totl routine of the SQL*Forms user exit references the order_total packaged procedure.
| CAUTION |
Issuing a COMMIT from a PL/SQL procedure that is called from SQL*Forms attempts to commit any changes from the Forms application as well. |
The STANDARD Package
PL/SQL provides various tools for developers in a package named STANDARD. These tools include internal functions and internal exceptions. I previously discussed exception handling and two internal functions, SQLCODE and SQLERRM, that provide information for exception reporting and are valid in exception handlers only.
EAN: 2147483647
Pages: 391