home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

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.

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.


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.


Returns TRUE if currently executing the dynamic SQL.

5.9.3 Bundled, low-level operations

FUNCTION open_and_parse
(string_in IN VARCHAR2,

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.

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

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

(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)

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.

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

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

5.9.7 Miscellaneous programs

(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)

Locates and returns the n th placeholder for bind variables in strings.


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.

Previous: 5.8 PLVddd: DDL Syntax Dump Advanced Oracle PL/SQL Programming with Packages Next: 5.10 PLVexc: Exception Handling
5.8 PLVddd: DDL Syntax Dump Book Index 5.10 PLVexc: Exception Handling

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference