DBMS_SQL Package
What Is Dynamic SQL?
Dynamic SQL enables you to write programs those reference SQL statements whose full text is not known until runtime. Before discussing dynamic SQL in detail, a clear definition of static SQL may provide a good starting point for understanding dynamic SQL. Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at compilation, which provides the following benefits:
- Successful compilation verifies that the SQL statements reference valid database objects.
- Successful compilation verifies that the necessary privileges are in place to access the database objects.
- Performance of static SQL is generally better than dynamic SQL.
Because of these advantages, you should use dynamic SQL only if you cannot use static SQL to accomplish your goals, or if using static SQL is cumbersome compared to dynamic SQL. However, static SQL has limitations that can be overcome with dynamic SQL. You may not always know the full text of the SQL statements that must be executed in a PL/SQL procedure. Your program may accept user input that defines the SQL statements to execute, or your program may need to complete some processing work to determine the correct course of action. In such cases, you should use dynamic SQL.
For example, a reporting application in a data warehouse environment might not know the exact table name until runtime. These tables might be named according to the starting month and year of the quarter, for example , , , , , and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.INV_01_1997INV_04_1997INV_07_1997INV_10_1997INV_01_1998
You might also want to run a complex query with a user-selectable sort order. Instead of coding the query twice, with different clauses, you can construct the query dynamically to include a specified clause.ORDER BYORDER BY
Dynamic SQL programs can handle changes in data definitions, without the need to recompile. This makes dynamic SQL much more flexible than static SQL. Dynamic SQL lets you write reusable code because the SQL can be easily adapted for different environments.
Dynamic SQL also lets you execute data definition language (DDL) statements and other SQL statements that are not supported in purely static SQL programs.
Why Use Dynamic SQL?
You should use dynamic SQL in cases where static SQL does not support the operation you want to perform, or in cases where you do not know the exact SQL statements that must be executed by a PL/SQL procedure. These SQL statements may depend on user input, or they may depend on processing work done by the program. The following sections describe typical situations where you should use dynamic SQL and typical problems that can be solved by using dynamic SQL
Executing DDL and SCL Statements in PL/SQL
In PL/SQL, you can only execute the following types of statements using dynamic SQL, rather than static SQL:
Data definition language (DDL) statements, such as , , , and CREATEDROPGRANTREVOKE
Session control language (SCL) statements, such as and ALTERSESSIONSETROLE
Executing Dynamic Queries
You can use dynamic SQL to create applications that execute dynamic queries, whose full text is not known until runtime. Many types of applications need to use dynamic queries, including:
Applications that allow users to input or choose query search or sorting criteria at runtime
Applications that allow users to input or choose optimizer hints at run time
Applications that query a database where the data definitions of tables are constantly changing
Applications that query a database where new tables are created often
DBMS_SQL is used to write dynamic SQL in stored procedure and to parse DDL statements.
Some Procedures and Functions in DBMS_SQL package are –
Open_Cursor – Opens a new cursor and assigns a cursor ID number.
PARSE – Parses the DDL or DML statements, i.e. it checks the syntax and associates it with the opened cursor. In case of DDL statements they are executed immediately after parsing.
PARSE (ID number of the cursor, DDL or DML statement, language_flag)
language_flag
Determines how Oracle handles the SQL statement. The following options are recognized:
V6 (or ) specifies version 6 behavior. 0
NATIVE (or ) specifies normal behavior for the database to which the program is connected. 1
V7 (or ) specifies Oracle7 behavior. 2
EXECUTE – Executes the SQL statement and returns the number of rows processed.
Especially suitable for keeping track of count of rows affected by a DML statement.
create or replace procedure drop_table(p_tablename in varchar2)
is
C NUMBER;
begin
C := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(C,'DROP TABLE ' || p_tablename, DBMS_SQL.NATIVE);
exception
when others then
dbms_output.put_line('Table name to be dropped does not exist');
end;
create or replace procedure delete_rows(table_name varchar2)
is
c number;
rows_deleted number;
begin
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, 'Delete from ' || table_name, DBMS_SQL.NATIVE);
rows_deleted := DBMS_SQL.EXECUTE(c);
dbms_output.put_line('The total number of rows deleted are ' || rows_deleted);
Exception
when others then
dbms_output.put_line('No rows deleted');
end;
Execute Immediate – Parsing and execution is done in one statement.
Discussion about this article
| Author | Body |
admin
8/24/2009 2:34 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|