Previous | Table of Contents | Next

Page 295

From the first SELECT:

    BLUCK
--------
        1
        2

From the second block:

UPDATE BLICK: 2 rows ORA-0000: normal, successful completion
PL/SQL procedure successfully completed.
(from the second SELECT)
    BLUCK
--------
        0
        0

From the third block:

DROP BLICK: ORA-0000: normal, successful completion
PL/SQL procedure successfully completed.
(from the third SELECT)
ORA-00942: table or view does not exist

Yes, indeed, it works like a charm! Note that the number of rows returned is only meaningful for operations involving rows.

Now suppose we want to run a query. This is a bit more complex. The operations we must perform, in order, are as follows:

  1. Open a cursor.
  2. Parse the dynamic query string.
  3. Bind actual variables to the bind variables found in the WHERE clause.
  4. Define the columns listed in the SELECT clause, along with their data types.
  5. Execute the dynamic query.
  6. Fetch a row (this and the next operation are repeated until there are no more rows).
  7. Obtain the column values and store them in their associated variables.
  8. Close the cursor when finished.

This gives you an appreciation for what really goes on behind the scenes. A simple example involving the table we were playing with dynamically should makes things clearer (see Listing 12.10). Before running this sample, run the first two parts of testddl.sql to create the table and populate it with a couple of rows.

Listing 12.10 dynsql.sql—Dynamic SQL Requires a Sequence of Operations

-- Dynamic SQL Demonstration
-- BE SURE TO CREATE TABLE BLICK AND POPULATE WITH ROWS
DECLARE
  stmt VARCHAR2(50) := `SELECT bluck FROM blick WHERE bluck > :num';
                                                                   continues

Page 296

Listing 12.10 Continued

  stmt_c INTEGER;     -- the dynamic cursor handle
  rec blick%ROWTYPE;  -- the column value gets returned into here
  key NUMBER := 0;    -- the test value for WHERE clause
  dummy_int INTEGER;  -- don't care about execute return value (meaningless)
BEGIN
  DBMS_OUTPUT.enable;
  -- open a dynamic cursor
  stmt_c := DBMS_SQL.open_cursor;
  -- parse the dynamic query string
  DBMS_SQL.parse(stmt_c, stmt, DBMS_SQL.NATIVE);
  -- bind variables to input values for WHERE clause
  DBMS_SQL.bind_variable(stmt_c, `:num', key);
  -- define column listed in select clause by datatype
  DBMS_SQL.define_column(stmt_c, 1, rec.bluck);
  dummy_int := DBMS_SQL.execute(stmt_c);
  WHILE (DBMS_SQL.fetch_rows(stmt_c) > 0) LOOP     -- found a row
    DBMS_SQL.column_value(stmt_c, 1, rec.bluck);   -- extract column value
    DBMS_OUTPUT.put_line(`bluck = ` || TO_CHAR(rec.bluck)); -- print it
  END LOOP;  -- process rows
  DBMS_SQL.close_cursor(stmt_c);  -- close cursor, we're done
END;
/

After a moment, the server returns:

bluck = 1
bluck = 2
PL/SQL procedure successfully completed.

Note the use of the bind variable in the WHERE clause, denoted by the colon prefixed to it, as well as its matching bind variable in the bind_variable statement. The input variable is associated, or binded, to it. In similar fashion, each column listed in the SELECT clause must have a variable binded to it as well. This is done by the column's position within the SELECT clause.

As a final example, we can define and execute any PL/SQL block at runtime. First we'll create a generic stored procedure:

CREATE OR REPLACE PROCEDURE blook(Px OUT VARCHAR2) AS
BEGIN  -- build a generic stored procedure
  Px := `DAN';
END blook;
/

Then call it from a dynamic PL/SQL block (see Listing 12.11).

Listing 12.11 dynplsql.sql—Executing Dynamic PL/SQL

DECLARE
  blk VARCHAR2(100) := `DECLARE y VARCHAR2(5); BEGIN blook(y); :Z := y; END;';
  blk_cursor INTEGER;
  dummy_int INTEGER;
  xout VARCHAR2(5);

Page 297

BEGIN  -- call the generic stored procedure using dynamic PL/SQL
  DBMS_OUTPUT.enable;
  blk_cursor := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(blk_cursor, blk, DBMS_SQL.NATIVE);
  DBMS_SQL.bind_variable(blk_cursor, `:Z', xout, 5);
  dummy_int := DBMS_SQL.execute(blk_cursor);
  DBMS_SQL.variable_value(blk_cursor, `:Z', xout);  -- get output
  DBMS_OUTPUT.put_line(`Z: ` || xout);
  DBMS_SQL.close_cursor(blk_cursor);
END;
/

The dynamic PL/SQL statement stored in blk returns its value into xout. That's pretty straightforward. Note the use of the bind variable :Z in BIND_VARIABLE and VARIABLE_VALUE. Also, the semicolon terminating the block is required.

When you run this, you get:

Z: DAN
PL/SQL procedure successfully completed.
NOTE
If you want to put a comment into a dynamic PL/SQL block string, be sure to use a multiline comment! The single-line comment causes the compiler to ignore all text that follows it. You can't distinguish text "lines" with embedded newline characters in a string, either.

Dynamic SQL and PL/SQL is slick and extremely powerful. With this relatively simple tool, you can write completely dynamic programs to interact with any database object, determined at runtime.

Running a Trace with DBMS_SYSTEM

You can turn a trace on and off in someone else's session with set_sql_trace_in_session(). This sounds kind of sneaky, but it can help you find out if someone is hung, or in an infinite loop, or just waiting for a long query return. All you need is their user name to look up in v$session. Here's an example (see Listing 12.5).

CAUTION
By default, this packaged procedure is only available to SYS. If you want another user to have EXECUTE privilege on it, you'll have to explicitly grant it to another user from SYS.

Listing 12.12 trcsess.sql—Running a Trace in Someone Else's Session

ACCEPT owner PROMPT `Enter Username: `
ACCEPT trace PROMPT `Turn Trace ON/OFF: `
DECLARE
  -- cursors
                                                        continues
														

Page 298

Listing 12.12 Continued

  CURSOR sess_info(Powner VARCHAR2) IS
    SELECT sid, serial#
    FROM V$SESSION
    WHERE USERNAME = Powner;
  -- constants
  OWNER    CONSTANT VARCHAR2(30) := UPPER(`&&owner');
  SETTRACE CONSTANT VARCHAR2(3)  := RTRIM(UPPER(`&&trace'));
  -- variables
  status NUMERIC;
  sess_info_rec sess_info%ROWTYPE;
  lsid NUMBER;
  lserial NUMBER;
  sqltrace BOOLEAN;
BEGIN
  DBMS_OUTPUT.enable;
  OPEN sess_info(OWNER);
  FETCH sess_info INTO sess_info_rec;
  IF (sess_info%NOTFOUND) THEN
    RAISE NO_DATA_FOUND;
  END IF;
  IF (SETTRACE = `ON') THEN
    sqltrace := TRUE;
  ELSE  -- turn off
    sqltrace := FALSE;
  END IF;
  DBMS_SYSTEM.set_sql_trace_in_session(
    sess_info_rec.sid, sess_info_rec.serial#, sqltrace);
  DBMS_OUTPUT.put_line(`TRACE FOR USER ` || OWNER ||
                       ` is now ` || SETTRACE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.put_line(OWNER || ` is not logged on!');

WHEN OTHERS THEN
  status := SQLCODE;
  DBMS_OUTPUT.put_line(SQLERRM(status));
END;
/

If the same user name is connected multiple times, this script only finds the first one, as there is little to differentiate one session of the same user from another.

To my knowledge, there is no way to check whether trace is actually on or off, so this program assumes if the call succeeds then trace was set as desired.

Previous | Table of Contents | Next