Oracle Database 11g PL/SQL Programming P2

These enhancements are briefly reviewed in the following subsections. Chapter 3 covers theSIMPLE_INTEGER datatype. Chapter 4 covers the continue statement. Chapter 6 demonstratesthe cross-session PL/SQL function result cache, and both mixed, named, and positional notationcalls. Automatic subprogram inlining and the PL/SQL Native Compiler are covered in Chapter 9.Chapter 16 covers web application development and the multiprocess connection pool.
Nội dung trích xuất từ tài liệu:
Oracle Database 11g PL/SQL Programming P2 Chapter 1: Oracle PL/SQL Overview 15can also call the FORMAT_CALL_STACK or FORMAT_ERROR_STACK from the same packageto work with thrown exceptions. The following is a simple example:DECLARE local_exception EXCEPTION; FUNCTION nested_local_function RETURN BOOLEAN IS retval BOOLEAN := FALSE; BEGIN RAISE local_exception; RETURN retval; END;BEGIN IF nested_local_function THEN dbms_output.put_line(No raised exception); END IF;EXCEPTION WHEN others THEN dbms_output.put_line(DBMS_UTILITY.FORMAT_CALL_STACK); dbms_output.put_line(------------------------------); dbms_output.put_line(dbms_utility.format_call_stack); dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); dbms_output.put_line(-----------------------------------); dbms_output.put_line(dbms_utility.format_error_backtrace); dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK); dbms_output.put_line(-------------------------------); dbms_output.put_line(dbms_utility.format_error_stack);END;/ This script produces the following output:DBMS_UTILITY.FORMAT_CALL_STACK----------------------------------- PL/SQL Call Stack ----- object line object handle numbername20909240 18 anonymous blockDBMS_UTILITY.FORMAT_ERROR_BACKTRACE-----------------------------------ORA-06512: at line 7ORA-06512: at line 11DBMS_UTILITY.FORMAT_ERROR_STACK-------------------------------ORA-06510: PL/SQL: unhandled user-defined exception You will likely find the FORMAT_ERROR_BACKTRACE the most helpful. It captures the linewhere the first error occurs at the top, and then moves backward through calls until it arrives at16 Oracle Database 11g PL/SQL Programming the initial call. Line numbers and program names are displayed together when named blocks are involved in an event stack. Chapter 5 contains more on error management. Wrapping PL/SQL Stored Programs Beginning with Oracle 10g Release 2, the database now supports the ability to wrap, or obfuscate, your PL/SQL stored programs. This is done by using the DBMS_DDL package CREATE_WRAPPED procedure. You use it as follows: BEGIN dbms_ddl.create_wrapped( CREATE OR REPLACE FUNCTION hello_world RETURN STRING AS ||BEGIN || RETURN Hello World!; ||END;); END; / After creating the function, you can query it by using the following SQL*Plus column formatting and query: SQL> COLUMN message FORMAT A20 HEADING Message SQL> SELECT hello_world AS message FROM dual; Message -------------------- Hello World! You can describe the function to inspect its signature and return type: SQL> DESCRIBE hello_world FUNCTION hello_world RETURNS VARCHAR2 Any attempt to inspect its detailed operations will yield an obfuscated result. You can test this by querying stored function implementation in the TEXT column of the USER_SOURCE table, like the following: SQL> COLUMN text FORMAT A80 HEADING Source Text SQL> SET PAGESIZE 49999 SQL> SELECT text FROM user_source WHERE name = HELLO_WORLD; The following output is returned: FUNCTION hello_world wrapped a000000 369 abcd . . . et cetera . . . This is a very useful utility to hide the implementation details from prying eyes. We will revisit this in Appendix F. Chapter 1: Oracle PL/SQL Overview 17Oracle 11g New FeaturesNew PL/SQL features introduced in Oracle 11g include ■ Automatic subprogram inlining ■ A continue statement ■ A cross-session PL/SQL function result cache ■ Dynamic SQL enhancements ■ Mixed, named, and positional notation SQL calls ■ A multiprocess connection pool ■ A PL/SQL Hierarchical Profiler ■ That the PL/SQL Native Compiler now generates native code ■ PL/Scope ■ Regular expression enhancements ■ A SIMPLE_INTEGER datatype ■ Direct sequence calls in SQL statements These enhancements are briefly reviewed in the following subsections. Chapter 3 covers theSIMPLE_INTEGER datatype. Chapter 4 covers the continue statement. Chapter 6 demonstratesthe cross-session PL/SQL function result cache, and both mixed, named, and positional notationcalls. Automatic subprogram inlining and the PL/SQL Native Compiler are covered in Chapter 9.Chapter 16 covers web application development and the multiprocess connection pool. You willalso find more information about the Regular Expression, PL/SQL Hierarchical Profiler, and PL/Scopein Appendixes E, G, and H, respectively.Automatic Subprogram InliningInlining a subprogram ...