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:
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.sqlDynamic 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.sqlExecuting 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.
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.sqlRunning 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.