[Chapter 5] 5.9 PLVdyn: Dynamic SQL Operations

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, PLVdyn and PLVfk: Dynamic SQL and PL/SQL for details.

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 .

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.

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 turn off execution without modifying your code.
PROCEDURE execute_and_close (cur_inout IN OUT INTEGER);: Combines the execute and close operations into a single call.
PROCEDURE parse_delete
(table_in IN VARCHAR2, where_in IN VARCHAR2,
cur_out OUT INTEGER);: Performs the parse step of DBMS_SQL for a DELETE string constructed from the arguments in the parameter list.

PROCEDURE ddl (string_in IN VARCHAR2);: Executes any DDL statement by performing an OPEN, then a PARSE. This program forces a commit in your session, as when any DDL command is given.
PROCEDURE drop_object
(type_in IN VARCHAR2, name_in IN VARCHAR2,
schema_in IN VARCHAR2 := USER);: Provides a generic, powerful interface to the DDL DROP command. You can drop individual or multiple objects.
PROCEDURE truncate
(type_in IN VARCHAR2, name_in IN VARCHAR2,
schema_in IN VARCHAR2 := USER);: Truncates either a table or a cluster as specified.
PROCEDURE compile
(stg_in IN VARCHAR2, show_err_in IN VARCHAR2 := PLV.noshow);: Executes a CREATE OR REPLACE of the program contained in the first argument, stg_in . You can also request that errors from this compile be immediately displayed with a call to the PLVvu.err procedure.
PROCEDURE compile
(table_in IN PLVtab.vc2000_table,
lines_in IN INTEGER,
show_err_in IN VARCHAR2 := PLV.noshow);: Another version of dynamic CREATE OR REPLACE that reads the source code for the program from the PL/SQL table.
FUNCTION nextseq (seq_in IN VARCHAR2, increment_in IN INTEGER := 1)
RETURN INTEGER;: Returns the next value from the specified sequence. Can retrieve the immediate next value or the n th next value. Use of this function avoids direct reference to the DUAL table.

PROCEDURE dml_insert_select
(table_in IN VARCHAR2, select_in IN VARCHAR2);: Issues an INSERT-SELECT statement based on the arguments provided.
PROCEDURE dml_delete
(table_in IN VARCHAR2, where_in IN VARCHAR2 := NULL);: Deletes all rows specified by the WHERE clause from the table argument.
PROCEDURE dml_update
(table_in IN VARCHAR2,
column_in IN VARCHAR2,
value_in IN VARCHAR2|NUMBER|DATE,
where_in IN VARCHAR2 := NULL);: Overloaded to support string, numeric, and date values, dml_update performs a single-column UPDATE as specified by the arguments.

PROCEDURE plsql (string_in IN VARCHAR2);: Executes any PL/SQL code. This procedure automatically packages your string inside a BEGIN-END block and terminates it with a semicolon.

PROCEDURE disptab
(table_in IN VARCHAR2,
where_in IN VARCHAR2 := NULL,
string_length_in IN INTEGER := 20,
date_format_in IN VARCHAR2 := PLV.datemask,
num_length_in IN INTEGER := 10);: Displays the requested contents of any database table. Good example of the kind of code required to perform Method 4 dynamic SQL .
FUNCTION plsql_block (string_in IN VARCHAR2) RETURN VARCHAR2;: Returns a string that is a valid PL/SQL block for dynamic PL/SQL execution.
FUNCTION placeholder
(string_in IN VARCHAR2, start_in IN INTEGER := 1)
RETURN VARCHAR2;: Locates and returns the n th placeholder for bind variables in strings.
FUNCTION tabexists (table_in IN VARCHAR2) RETURN BOOLEAN;: Returns TRUE if the specified table exists.
PROCEDURE time_plsql
(stg_in IN VARCHAR2, repetitions_in IN INTEGER := 1);: Calculates the overhead required to execute a dynamically constructed anonymous PL/SQL block.