Introduction
This article series illustrates two ways of doing dynamic SQL, that is, SQL that is programmatically generated and submitted for execution. I have used one of the methods to solve parallel SQL performance issues. I will also show how to use one of the methods in conjunction with the recently-discussed collections.
This article will illustrate the DBMS_SQL package and Part 6 of this series will complete the conversation, showing EXECUTE_IMMEDIATE. Both methods of doing dynamic SQL have been nicely updated thru the years.
Dynamic SQL Overview
Dynamic SQL has been around since at least Oracle7. Dynamic SQL allows for precise control over the parsing and execution of SQL. This is important, especially when trying to utilize Cost-Based Optimizer (CBO) histograms. If a repeating SQL has bind variables with different values being supplied, the SQL is parsed (when the execution plan is created) for the first variables submitted. The execution plan that is generated might not be efficient for other data values subsequently submitted (very data dependent). Being able to reparse on demand solves this particular problem. Oracle11 has addressed this issue but the SQL still has to perform poorly once for this mechanism to kick in and re-hard parse the SQL.
Dynamic SQL can resolve bind variables back to their literal values, allowing for this SQL with literals to be hard-parsed with exact values. I have also used this technique to solve partitioned objects that do not want to ‘prune’ (i.e., will scan all of the partitions) when bind variables are present. Maybe content for another article?
Dynamic SQL allows for SQL (and DDL) to be dynamically created, created from input variables, and created differently depending on the business needs.
This same technique can be used to execute DDL but the DBMS_UTILITY.EXECUTE_DDL_STATEMENT procedure performs this task more efficientl
There are two flavors of Dynamic SQL:
- DBMS_SQL package
- Allows for total control over the parse, execute, and fetch of SQL and returned rows
- Contains well over 50 procedures/functions/types
- Supports bulk collect, returning, returning bulk collect, etc
- Native Dynamic SQL
- Uses Execute Immediate syntax
- Been around since Oracle7
- Typically, SQL is built first then passed to this feature of PL/SQL, a true built-in
- Oracle10 updated this to support bulk collect features
DBMS_SQL Package
The DBMS_SQL package allows for precise control over the fetch and processing of rows. The SQL can be completed programmatically and submitted for parsing. The key words here are ‘submitted for parsing’; you can force a hard parse upon any SQL using this package.
You can use this to share cursors as well. Maybe content for another article, but there is overhead in opening and closing cursors. Implicit cursors (where you just do a ‘select into’ perhaps) do the open/parse/fetch/close and if you are populating variables this way, you are causing a lot of CPU time for your apps.
Opening a cursor and using the DBMS_SQL.Parse command to process different SQL keeps these cursors open and avoids the costly overhead of quickly opening and closing dozens of cursors.
1: Declare
2: CurID integer;
3: v_rows number;
4: v_ename EMP.ENAME%TYPE;
5: Begin
6: CurID := DBMS_SQL.OPEN_CURSOR;
7: DBMS_SQL.PARSE(CurID,’SELECT ENAME FROM EMP’,1);
8: DBMS_SQL.DEFINE_COLUMN(CurID,1,v_ename,10);
9: v_rows := DBMS_SQL.EXECUTE(CurID);
10: WHILE DBMS_SQL.FETCH_ROWS(CurID) > 0
11: LOOP
12: DBMS_SQL.COLUMN_VALUE(CurID,1,v_ename);
13: DBMS_OUTPUT.PUT_LINE(‘Ename = ‘ || v_ename);
14: END LOOP;
15: DBMS_SQL.CLOSE_CURSOR(CurID);
This example is a simple ‘SELECT ENAME FROM EMP’; see line 7. The quoted text can be a character variable that contains SQL text. You can manipulate this SQL if you like prior to the DBMS_SQL_PARSE statement (again, line 7).
Notice that the programming is a bit like file processing, where you have a cursor id variable (see line 6) and you open and store the pointer to this cursor in this variable. You can have up to 50 cursors by default open in your PL/SQL module…the init.ora setting ‘MAX_CURSORS’ controls how many cursors your program can have open at one time.
This SQL works with one column so line 8 sets up the variable to hold the ENAME data. Notice at line 9 you can capture the row count. Lines 10 thru 14 will loop thru the cursor. This example is simply printing out the ENAME using DBMS_OUTPUT (see line 13).
You have to close your open cursors or your PL/SQL program will end in an error condition.
IF you want the total number of rows processed, you can use DBMS_SQL.LAST_ROW_COUNT. This is a function, so use it like this: v_rows := DBMS_SQL.Last_Row_Count; Put this statement after the loop and before the closing of the cursor. The information is lost once the cursor is closed.
1: Declare
2: CurID integer;
3: v_rows number;
4: v_ename EMP.ENAME%TYPE;
5: v_job EMP.JOB%TYPE;
6: v_sal EMP.SAL%TYPE;
7: Begin
8: CurID := DBMS_SQL.OPEN_CURSOR;
9: DBMS_SQL.PARSE(CurID,’select ename, job, sal from emp’,1);
10: DBMS_SQL.DEFINE_COLUMN(CurID,1,v_ename,10);
11: DBMS_SQL.DEFINE_COLUMN(CurID,2,v_job,9);
12: DBMS_SQL.DEFINE_COLUMN(CurID,3,v_sal);
13: v_rows := DBMS_SQL.EXECUTE(CurID);
14: WHILE DBMS_SQL.FETCH_ROWS(CurID) > 0
15: LOOP
16: DBMS_SQL.COLUMN_VALUE(CurID,1,v_ename);
17: DBMS_SQL.COLUMN_VALUE(CurID,2,v_job);
18: DBMS_SQL.COLUMN_VALUE(CurID,3,v_sal);
19: DBMS_OUTPUT.PUT_LINE(‘Ename = ‘ || v_ename || ‘ Job = ‘ || v_job || Sal = ‘ || v_sal);
20: END LOOP;
21: DBMS_OUTPUT.PUT_LINE(‘Total Rows processed = ‘ || DBMS_SQL.LAST_ROW_COUNT);
22: DBMS_SQL.CLOSE_CURSOR(CurID);
This example selects three columns from the EMP table. Notice that lines 10 through 12 are the column value clauses indicating the variable to store the selected contents and their relative position in the SELECT clause.
In this example, notice that the DBMS_SQL_FETCH_ROWS function is being used to run the LOOP; when no rows are returned, the loop will exit. Notice that this example prints the total rows processed using the above-mentioned DBMS_SQL.Last_Row_Count syntax. Also notice that I use the %TYPE syntax to get the exact column attributes at lines 4 thru 6.
1: In_percent number;
2: CurID integer;
3: v_rows number;
4: Begin
5: CurID := DBMS_SQL.OPEN_CURSOR;
6: DBMS_SQL.PARSE(CurID,UPDATE EMP SET SAL = SAL * :v_pct,1);
7: DBMS_SQL.BIND_VARIABLE(CurID,’:v_pct’,In_percent);
8: v_rows := DBMS_SQL.EXECUTE(CurID);
9: DBMS_OUTPUT.PUT_LINE(‘Rows Updated = ‘ || v_rows);
10: DBMS_SQL.CLOSE_CURSOR(CurID);
This is a DML example and it illustrates how to pass bind variables to a SQL statement. This example also illustrates the precise control over the PARSE and EXECUTE of a SQL statement.
Notice at line 6 that there is a bind variable in this SQL ‘:v_pct’. Before the EXECUTE at line 8, the bind variable will need to be provided using the DBMS_SQL.Bind_Variabe syntax illustrated at line 7.
Since this is a DML statement, v_rows at line 8 is capturing the number of rows affected by the DML. The standard exception processing applies here as well, we covered this in a prior article (click here to review the article).
New Features
Oracle continues its support of dynamic SQL with these newer features. The DBMS_SQL now supports CLOB data types. I believe this was added in Oracle11. Also added in Oracle11 was support for reference cursors (perhaps another article on sharing code and cursors in PL/SQL).
Summary
This article started the discussion of Dynamic SQL, or the control of parsing and executing most any SQL statement. You would want to do this for a variety of reasons including causing hard parsing of SQL with bind variables, SQL isn’t complete until there is some interaction with the user, etc.
The next article in this series, I’ll complete the conversation of Dynamic SQL with the very useful Execute_Immediate syntax along with examples on how I’ve used this syntax to solve performance issues with partitioned queries.
I hope you find these tips useful in your day-to-day use of the Oracle RDBMS.
Start the discussion at forums.toadworld.com