Oracle7 Server Application Developer's Guide

Overview

You can write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime.

This permits you to create procedures that are more general purpose. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.

Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the DBMS_SQL package. This helps solve the problem of not being able to parse data definition language statements directly using PL/SQL. For example, you might now choose to issue a DROP TABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package.

Creating the DBMS_SQL Package

To create the DBMS_SQL package, submit the DBMSSQL.SQL and PRVTSQL.PLB scripts when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script. See

for information on granting the necessary privileges to users who will be executing this package.