PLVdyn: Dynamic SQL Operations

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

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.

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.

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

Data Definition Language operations

• 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 nth next value. Use of this function avoids direct reference to the DUAL table.

Data Manipulation Language operations

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

Executing dynamic PL/SQL

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

Miscellaneous programs

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