Advanced Oracle PL/SQL Programming with Packages

5.9. PLVdyn: Dynamic SQL Operations

The PLVdyn (PL/Vision DYNamic SQL) package provides a high-level interface to Oracle's builtin DBMS_SQL package. See Chapter 19 for details.

5.9.1. Tracing PLVdyn activity

PROCEDURE showsql (start_with_in IN VARCHAR2 := NULL);

Requests that the string being parsed dynamically be displayed. You can specify the string that should start the displayed text.

PROCEDURE noshowsql;

Turns off the display of the dynamic SQL string.

FUNCTION showing RETURN BOOLEAN;

Returns TRUE if currently showing the dynamic SQL.

5.9.2. Controlling execution of dynamic SQL

PROCEDURE execsql;

Requests that calls to PLVdyn.execute call the underlying DBMS_SQL.EXECUTE builtin.

PROCEDURE noexecsql;

Requests that PLVdyn.execute not actually execute the specified cursor.

FUNCTION executing RETURN BOOLEAN;

Returns TRUE if currently executing the dynamic SQL.

5.9.3. Bundled, low-level operations

FUNCTION open_and_parse 
   (string_in IN VARCHAR2,
    mode_in IN INTEGER := DBMS_SQL.NATIVE) RETURN INTEGER;

Combines the open and parse operations into a single function call.

PROCEDURE execute (cur_inout IN INTEGER);

A passthrough to the DBMS_SQL.EXECUTE function. By using PLVdyn.execute, you give yourself the flexibility to turn off execution without modifying your code.

PROCEDURE execute_and_fetch
    (cur_inout IN INTEGER, match_in IN BOOLEAN := FALSE);

A passthrough to the DBMS_SQL.EXECUTE_AND_FETCH function. By using this procedure, you give yourself the flexibility to ...