O'Reilly logo

Advanced Oracle PL/SQL Programming with Packages by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required