Previous | Table of Contents | Next

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.

Using Miscellaneous Utilities in DBMS_UTILITY

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.sql—What'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.sql—Check 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.sql—Loop 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.

Previous | Table of Contents | Next