#

| Chapter 5 PL/Vision Package Specifications |

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

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

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

### 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,`
`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.

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

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

5.8 PLVddd: DDL Syntax Dump |  | 5.10 PLVexc: Exception Handling

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