How to convert between DBMS_SQL and Native Dynamic SQL in Oracle 11g
By AyubKhanz
DBMS_SQL and Native Dynamic SQL
This Article is intended for plsql programmer, who may not be aware of the new features of Oracle 11g.One of those new features is to switch between dynamic SQL and DBMS_SQL. You may asking why to switch to DBMS_SQL while native dynamic SQL(NDS) is more complete and easy to code and understand?. Well, there are certain advantages of using DBMS_SQL as opposed to native dynamic SQL.
The DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER API's gives you the freedom to execute native dynamic SQL using the DBMS_SQL package and then covert DBMS_SQL to REF CURSOR. The DBMS_SQL.TO_CURSOR_NUMBER API of Oracle 11g database provides you the ability to execute dynamic sql via ref cursor amd then convert it to DBMS_SQL for data retrieval
The following script demonstrates the usage of DBMS_SQL.TO_REFCURSOR. In this example, a simple dynamic query is being executed using DBMS_SQL, and the cursor is then being converted to a REF CURSOR.
DBMS_SQL.TO_REFCURSOR
DECLARE
sql_str CLOB;
cur_var BINARY_INTEGER := dbms_sql.open_cursor;
ref_cur SYS_REFCURSOR;
get_value BINARY_INTEGER;
cur_rec hr.employees%ROWTYPE;
hireyear CHAR(4) := &hireyear;
BEGIN
-- Formulate query
sql_str := 'SELECT *
FROM hr.employees e
WHERE to_char(e.hire_date,''yyyy'') = :yr
ORDER BY e.hire_date';
-- Parsing SQL query
dbms_sql.parse(cur_var, sql_str, dbms_sql.native);
-- Binding yr variable
dbms_sql.bind_variable(cur_var, 'yr', hireyear);
-- Executing query and converting to REF CURSOR
get_value := dbms_sql.execute(cur_var);
ref_cur := dbms_sql.to_refcursor(cur_var);
dbms_output.put_line('Employees that have been Hired in ' || hireyear);
LOOP
FETCH ref_cur
INTO cur_rec;
dbms_output.put_line(cur_rec.employee_id || ' - ' ||
cur_rec.first_name || ' - ' || cur_rec.hire_date);
EXIT WHEN ref_cur%NOTFOUND;
END LOOP;
CLOSE ref_cur;
END;
/*Here is the Result /*
Employees that have been Hired in 2007
127 - James - 14-JAN-07
187 - Anthony - 07-FEB-07
107 - Diana - 07-FEB-07
171 - William - 23-FEB-07
195 - Vance - 17-MAR-07
163 - Danielle - 19-MAR-07
172 - Elizabeth - 24-MAR-07
132 - TJ - 10-APR-07
104 - Bruce - 21-MAY-07
178 - Kimberely - 24-MAY-07The above script prompts for the Hired Year via the :hr bind variable and the SQL*Plus &hireyear substitution variable. The DBMS_SQL API is used to parse,bind and execute the specific year that was entered to the dynamic SQL string and executes the query to find all the employess that have been hired in the specfied year. Once the query is executed, the cursor is converted to a REF CURSOR using the DBMS_SQL.TO_REFCURSOR API. Native dynamic SQL is then used to process the results of the query.
The script shows that the native dynamic SQL is much easier to read and process. The advantage of converting to a REF CURSOR is to have the ability to easily process code using native dynamic SQL but still have some of the advantages of using DBMS_SQL for querying the data. For instance, if the number of bind variables was unknown until runtime, then DBMS_SQL would be required.
A similar technique can be used if DBMS_SQL is required to process the results of a query. The DBMS_SQL.TO_CURSOR_NUMBER API provides the ability to convert a cursor from a REF CURSOR to DBMS_SQL.
The following example shows the same query on the employees table, but this time native NDS is used to set up the query and execute it, and DBMS_SQL is used to describe the table structure. One of the cool features of the DBMS_SQL API is that it is possible to describe the columns of a query that will be returned.
DBMS_SQL.TO_CURSOR_NUMBER
DECLARE
sql_str clob;
ref_cur SYS_REFCURSOR;
cursor_var binary_INTEGER;
columns_var binary_INTEGER;
desc_var dbms_sql.desc_tab;
hireyear char(4) := &hireyear;
get_value NUMBER;
BEGIN
-- Formulate query
sql_str := 'SELECT * FROM hr.employees e WHERE
to_char(e.hire_date,''yyyy'') = :yr ORDER BY e.hire_date';
-- Open the REF CURSOR
OPEN ref_cur FOR sql_str
USING hireyear;
--Converting ref cursor to dbms_sql
cursor_var := dbms_sql.to_cursor_number(ref_cur);
dbms_sql.describe_columns(cursor_var, columns_var, desc_var);
dbms_sql.close_cursor(cursor_var);
FOR i IN 1 .. columns_var LOOP
dbms_output.put_line(desc_var(i)
.col_name || ' -
' || CASE desc_var(i).col_type
WHEN 1 THEN
' VARCHAR2 '
WHEN 2 THEN
' NUMBER '
ELSE
' other '
END);
END LOOP;
END;Oracle Database Answers
- LEAD and LAG examples in Oracle
var hl_oldOnLoad = window. - 4 days ago
- Optimizing SQL with Deterministic Functions
- 7 weeks ago
- ora-06502: PL/SQL: numeric or value error : character string buffer too small
ora-06502: PL/SQL: numeric or value error : character string buffer too small. - 8 weeks ago
- ORA-03114:not connected to ORACLE
While compilimg anyb Procedure, Fuction. - 2 months ago
- Oracle Error ora-01000:maximum open cursors exceeded
An error occured: java. - 2 months ago
Comments
No comments yet.
