Using DBMS_SQL Package to Execute DDL Statements and Dynamic SQL from PL/SQL
(Doc ID 1008453.6)
Last updated on MARCH 28, 2019
Applies to:PL/SQL - Version 22.214.171.124 and later
Information in this document applies to any platform.
***Checked for relevance on 21-Oct-2014***
This article describes how to use the DBMS_SQL package provided with PL/SQL 2.1 to execute Data Definition Language (DDL) statements and dynamic SQL statements from PL/SQL.
Dynamic SQL allows you to use SQL statements where all or part of the statement is unknown untill runtime.
Compiling a PL/SQL program involves resolving references to Oracle objects by looking up their definitions in the data dictionary and then binding storage addresses to program variables that will hold Oracle data.
PL/SQL uses static or early binding. This means that binding is done at compile time which increases efficiency because the definitions of database objects are looked up at compile time, rather than at run time.
The limitation of this method is that the names of database objects must be known at compile time. In the past, this prevented data definition language (DDL) statements and dynamic SQL from being executed directly from PL/SQL.
The DBMS_SQL package allows you to parse any data manipulation language (DML) or DDL statement, and it allows you to use dynamic SQL statements in your code. Dynamic SQL statements are stored in character strings that are input to, or built by, the program at run time. This means that the names of database objects do not need to be known until run time. It also means that all or part of the SQL statement to be executed can be input at run time. This enables you to create more general purpose procedures.
The following bulletin describes the DBMS_SQL package and gives some examples of how to use it. For additional information, refer to the "Oracle� Database PL/SQL Packages and Types Reference ".
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document