Page 299
Here's a sample run:
Enter Username: scott Turn Trace ON/OFF: on old 8: OWNER CONSTANT VARCHAR2(30) := UPPER(`&&owner'); new 8: OWNER CONSTANT VARCHAR2(30) := UPPER(`scott'); old 9: SETTRACE CONSTANT VARCHAR2(3) := RTRIM(UPPER(`&&trace')); new 9: SETTRACE CONSTANT VARCHAR2(3) := RTRIM(UPPER(`on')); TRACE FOR USER SCOTT is now ON PL/SQL procedure successfully completed.
Once turned on, the trace remains active until you or the user turns trace back off, or the user disconnects.
This package contains a bunch of useful routines, some of which I'll illustrate. A brief description of them is given in Table 12.3.
Table 12.3 Miscellaneous Utility Routines Found in DBMS_UTILITY
Subprogram | Description |
PROCEDURE compile_schema( Powner VARCHAR2); | Recompiles all stored subprograms and packages in a given schema. |
PROCEDURE analyze_schema( Powner VARCHAR2, Pmethod VARCHAR2, Pest_rows NUMBER DEFAULT NULL, Pest_pct NUMBER DEFAULT NULL); | Performs ANALYZE on all tables, indexes, and clusters for the given schema. |
FUNCTION format_error_stack RETURN VARCHAR2; | Returns a string containing the first 2,000 bytes of the error stack. |
FUNCTION format_call_stack RETURN VARCHAR2; | Returns a string containing the first 2,000 bytes of the program call stack. |
FUNCTION is_parallel_server RETURN BOOLEAN; | Returns TRUE/FALSE indicating whether parallel option for this server is turned on. |
FUNCTION get_time RETURN NUMBER; | Returns the time in hundredths of a second since the epoch (good for timing applications). |
continues
Page 300
Table 12.3 Continued
Subprogram | Description |
PROCEDURE name_resolve( Pname IN VARCHAR2, Pcontext IN NUMBER, Powner OUT VARCHAR2, Ppart1 OUT VARCHAR2, Ppart2 OUT VARCHAR2, Plink OUT VARCHAR2, Ppart1_type OUT NUMBER, Pobj_number OUT NUMBER); | Resolves a given object name, expanding synonyms and following remote database links as necessary; also does authorization checking. |
PROCEDURE name_tokenize( Pname IN VARCHAR2, Powner OUT VARCHAR2, Ptable OUT VARCHAR2, Pcol OUT VARCHAR2, Plink OUT VARCHAR2, Pnext OUT BINARY_INTEGER); | Given a string containing an object reference in dot notation, breaks it up into its component parts: owner, table, column, database link. |
PROCEDURE comma_to_table( Plist IN VARCHAR2, Ptabn OUT BINARY_INTEGER, Ptab OUT uncl_array); | Given a string containing tokens separated by commas, loads the tokens into a PL/SQL table, starting with table element 1. |
PROCEDURE table_to_comma( Ptab IN uncl_array, Ptabn OUT BINARY_INTEGER, Plist OUT VARCHAR2); | Given a PL/SQL table, builds a comma-delimited string consisting of the values from the table. The first table element must be 1 and the last table entry must be NULL. |
FUNCTION port_string RETURN VARCHAR2; | Returns a string containing the version of Oracle and the operating system. |
FUNCTION make_data_block_address( Pfile NUMBER, Pblock NUMBER) RETURN NUMBER; | Given a file and block number, returns the data block address; used for accessing fixed tables containing data block addresses. |
FUNCTION data_block_address_file( Pdba NUMBER) RETURN NUMBER; | Given a data block address, returns the file portion of it. |
Page 301
Subprogram | Description |
FUNCTION data_block_address_block( Pdba NUMBER) RETURN NUMBER; | Given a data block address, returns the data block portion of it. |
You can use format_error_stack in exception handlers to display the error stack right at the point where it's most useful, perhaps storing the string in an error log table, or sending it to another session via a pipe. Here's a very simple example:
DECLARE x VARCHAR2(1); BEGIN x := `bust!'; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); END; /
which returns:
ORA-06502: PL/SQL: numeric or value error PL/SQL procedure successfully completed.
In a similar vein, you can use format_call_stack to view the calling stack. This is useful when you're nested down several levels through various subprograms. Here is a modest example (see Listing 12.13).
Listing 12.13 callstk.sqlWhat's on the Call Stack?
CREATE TABLE call_log ( log_date DATE, log_level NUMBER, log_msg VARCHAR2(2000)) / DECLARE x NUMBER; PROCEDURE nest(Plevel IN OUT NUMBER, Pstopat IN NUMBER) IS msg VARCHAR2(2000); BEGIN IF (Plevel < Pstopat) THEN Plevel := Plevel + 1; -- increment nesting depth nest(Plevel, Pstopat); ELSE msg := DBMS_UTILITY.format_call_stack; INSERT INTO call_log VALUES (SYSDATE, Plevel, msg); END IF; END nest;
continues
Page 302
Listing 12.13 Continued
BEGIN x := 1; -- always start with 1 nest(x, 4); END; / SELECT * FROM call_log ORDER BY log_date / DROP TABLE call_log /
I get:
Table created. PL/SQL procedure successfully completed. LOG_DATE LOG_LEVEL -------- -------- LOG_MSG ---------------------------------------- 27-NOV-97 4 ---- PL/SQL Call Stack ---- object line object handle number name 12770c8 10 anonymous block 12770c8 8 anonymous block 12770c8 8 anonymous block 12770c8 8 anonymous block 12770c8 17 anonymous block Table dropped. ?
Okay, so this isn't the most useful thing in an anonymous block. If you used it in a stored procedure with some sort of trace mechanism (like the one we built earlier), you could unwind the stack during a debugging session to see where your program is going.
Moving right along, let's see whether our server is running Parallel Query Mode by using Listing 12.14.
Listing 12.14 pqm.sqlCheck to See if We're Running Parallel Query Mode
BEGIN DBMS_OUTPUT.enable; IF (DBMS_UTILITY.is_parallel_server) THEN DBMS_OUTPUT.put_line(`database is running in parallel server mode'); ELSE DBMS_OUTPUT.put_line(`database is NOT running in parallel server mode'); END IF; END; /
Page 303
I think I wrote this in 30 seconds, a new world record! Running it I get:
database is NOT running in parallel server mode PL/SQL procedure successfully completed.
We're on a roll. Now let's do a timing example, as shown below in Listing 12.15.
Listing 12.15 timetest.sqlLoop Index Timing Test: NUMBER versus PLS_INTEGER, Which Is Faster?
-- Loop Indexing Test: NUMBER vs. PLS_INTEGER DECLARE maxnum CONSTANT NUMBER := 100000; -- number of loops to do maxbin CONSTANT PLS_INTEGER := 100000; -- number of loops to do time_start NUMBER; -- time we started (in 10ms) time_stop NUMBER; -- time we stopped (in 10ms) numloops NUMBER := 0; -- loop index binloops PLS_INTEGER := 0; -- loop index BEGIN DBMS_OUTPUT.enable; time_start := DBMS_UTILITY.get_time; WHILE (numloops < maxnum) LOOP -- spin our wheels numloops := numloops + 1; END LOOP; -- spinning time_stop := DBMS_UTILITY.get_time; DBMS_OUTPUT.put_line(`Looping with NUMBER index ` || TO_CHAR(maxnum) || `x takes ` || TO_CHAR(time_stop - time_start) || ` hundredths of a sec'); time_start := DBMS_UTILITY.get_time; WHILE (binloops < maxbin) LOOP -- spin our wheels binloops := binloops + 1; END LOOP; -- spinning time_stop := DBMS_UTILITY.get_time; DBMS_OUTPUT.put_line(`Looping with PLS_BINARY index ` || TO_CHAR(maxbin) || `x takes ` || TO_CHAR(time_stop - time_start) || ` hundredths of a sec'); END; /
And I get:
Looping with NUMBER index 100000x takes 140 hundredths of a sec Looping with PLS_BINARY index 100000x takes 54 hundredths of a sec PL/SQL procedure successfully completed.
Wow! PLS_INTEGER math is a lot quicker.