 | DBMS_SQL Package |
SYS.DBMS_SQL is compiled with AUTHID CURRENT_USER (runs using the privileges of the current user)- Defines a SQL cursor number (PL/SQL Integer): Can be passed across call boundaries and stored.
- Provides an interface to use dynamic SQL to parse ANY DML or DDL statement using PL/SQL.
- When HAS to be used?
- - you don't have the complete SELECT list
- - you don't know what placeholds in a SELECT or DML must be bound
- When you CANNOT use DBMS_SQL?
- - Dynamic SQL stmt retrieves rows into records
- - If you need to use cursor attributes
(%FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT) - You can switch between native dynamic SQL AND DBMS_SQL package with:
- DBMS_SQL.TO_REFCURSOR function
- DBMS_SQL.TO_CURSOR_NUMBER function
Oracle Call Interface (OCI) x DBMS_SQL Package
- The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI).
- Addresses (also called pointers) are NOT user-visible in PL/SQL.
- The OCI uses bind by address. DBMS_SQL package uses bind by value.
- With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must call COLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program.
- The current release of the DBMS_SQL package does not provide CANCEL cursor procedures.
- Indicator variables are not required, because NULLs are fully supported as values of a PL/SQL variable.
| SYS.DBMS_SQL Subprograms |
|---|
|
New security regime: Oracle 11gR1
- Stricter than in previous versions
- Checks are made when binding and executing.
- Failure lead to
ORA-29470: Effective userid or roles are not the same as when cursor was parsed
Execution Flow:
- OPEN_CURSOR
- PARSE
- BIND_VARIABLE or BIND_ARRAY
- DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY
- EXECUTE
- FETCH_ROWS or EXECUTE_AND_FETCH
- VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG
- CLOSE_CURSOR
 | i.e. -- Procedure uses DBMS_SQL package to perform SELECT and DML statements. |
Task: Delete all employees of a given deparmtent who earn more than a given value.
(A) DEL_EMP procedure is composed of the three parts below:
(1) Perform a dynamic SELECT to show the number of records that will be deleted
(2) Perform the dynamic DML.
(3) Perform a dynamic SELECT to show that the records were indeed deleted.
(B) an anonymous block calls del_emp procedure
CREATE OR REPLACE PROCEDURE del_emp (dept in number, salary in number) as
cursor_dml integer;
cursor_select integer;
rows_processed integer;
v_numemp integer;
sel_str varchar2(500);
dml_str varchar2(500);
ignore integer;
BEGIN
-- Part 1: Select number BEFORE DML statement
-- (1) Open cursor for SELECT.
cursor_select := dbms_sql.open_cursor;
sel_str := 'Select count(*) nuemp from employees ' ||
' where department_id = :d';
-- (2) Parse SQL statement
dbms_sql.parse(cursor_select, sel_str, dbms_sql.native);
-- (3) Bind variables
dbms_sql.bind_variable(cursor_select, ':d', dept);
sel_str := 'Select count(*) nuemp from employees ' ||
' where department_id = '|| to_char(dept);
dbms_output.put_line('Select is: ' || sel_str);
-- (4) use define_column to specify the variables that
-- are to receive the SELECT values, much the way an
-- INTO clause does for a static query.
dbms_sql.define_column(cursor_select, 1, v_numemp);
-- (5) call the execute function to run STMT
ignore := dbms_sql.execute(cursor_select);
-- (6) Use fetch_rows to retrieve the query results.
-- Each successive fetch retrieves another set of rows,
-- until the fetch is unable to retrieve anymore rows.
-- If SELECT returns only ONE row, you may prefer to use
-- EXECTUVE_AND_FETCH instead.
If dbms_sql.fetch_rows(cursor_select) > 0 then
dbms_sql.column_value(cursor_select, 1, v_numemp);
end if;
dbms_output.put_line('Num emp in dept '|| to_char(dept) || ' before delete: '
|| to_char(v_numemp));
-- (7) Close the cursor.
dbms_sql.close_cursor(cursor_select);
-- Part 2: Now proceed with the DML Statement
dml_str := 'delete from employees where department_id = :d' ||
' and salary > :x';
cursor_dml := dbms_sql.open_cursor;
dbms_sql.parse(cursor_dml, dml_str, dbms_sql.native);
dbms_sql.bind_variable(cursor_dml, ':d', dept);
dbms_sql.bind_variable(cursor_dml, ':x', salary);
rows_processed := dbms_sql.execute(cursor_dml);
dbms_output.put_line('Num rows deleted: ' || rows_processed);
dbms_sql.close_cursor(cursor_dml);
-- Part 3: Select the number AFTER the DML statement
cursor_select := dbms_sql.open_cursor;
sel_str := 'Select count(*) nuemp from employees ' ||
' where department_id = :d';
dbms_sql.parse(cursor_select, sel_str, dbms_sql.native);
dbms_sql.bind_variable(cursor_select, ':d', dept);
dbms_sql.define_column(cursor_select, 1, v_numemp);
sel_str := 'Select count(*) nuemp from employees ' ||
' where department_id = '|| to_char(dept);
dbms_output.put_line('Select is: ' || sel_str);
dbms_sql.define_column(cursor_select, 1, v_numemp);
ignore := dbms_sql.execute(cursor_select);
If dbms_sql.fetch_rows(cursor_select) > 0 then
dbms_sql.column_value(cursor_select, 1, v_numemp);
end if;
dbms_output.put_line('Num emp in dept '|| to_char(dept) || ' after delete: '
|| to_char(v_numemp));
dbms_sql.close_cursor(cursor_select);
EXCEPTION
when others then
if dbms_sql.is_open(cursor_select) then
dbms_sql.close_cursor(cursor_select);
end if;
if dbms_sql.is_open(cursor_dml) then
dbms_sql.close_cursor(cursor_dml);
end if;
-- Use Function SQLCODE to return error number
dbms_output.put_line('Error code: ' || to_char(sqlcode));
-- Use Function SQLERRM to return error Message
dbms_output.put_line('Error Message: ' || to_char(sqlerrm));
-- Starting on 10gR2, Oracle recommends that you use
-- DBMS_UTILITY.FORMAT_ERROR_STACK() instead of SQLERRM.
-- While SQLERRM is limited to 512bytes, the other function is not.
dbms_output.put_line('Error stack: ' || dbms_utility.format_error_stack());
-- You can also use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
-- this function lists the complete error stack, including the line number
-- that generated the exception.
dbms_output.put_line('Error backtrace: ' || dbms_utility.format_error_backtrace());
END del_emp;
(B) Anonymous block calls DEL_EMP.
set serveroutput on declare nemp integer; begin del_emp(110, 6000); commit; end; / anonymous block completed Select is: Select count(*) nuemp from employees where department_id = 110 Num emp in dept 110 before delete: 2 Num rows deleted: 2 Select is: Select count(*) nuemp from employees where department_id = 110 Num emp in dept 110 after delete: 0
-- If the table employees is dropped or renamed, execution of del_emp will fail:
set serveroutput on declare nemp integer; begin del_emp(110, 6000); commit; end; / anonymous block completed Error code: -942 Error Message: ORA-00942: table or view does not exist Error stack: ORA-00942: table or view does not exist Error backtrace: ORA-06512: at "SYS.DBMS_SQL", line 1199 ORA-06512: at "DEVEL.DEL_EMP", line 17
No comments:
Post a Comment