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 .
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 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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.