dbms-notes: writing blocks to disk: dbms

[ Oracle PL/SQL ]

With Oracle 11g:

Native dynamic SQL and DBMS_SQL package now support statments larger than 32Kb.
EXECUTE IMMEDIATE, OPEN-FOR and DBMS_SQL.PARSE accept SQL statements in the form of CLOBs.

DBMS_SQL.TO_REFCURSOR converts a DBMS_SQL cursor ID into a REF CURSOR.
DBMS_SQL.to_cursor_number converts a REF CURSOR into a DBMS_SQL cursor ID.

DBMS_SQL.TO_REFCURSOR: From Cursor ID => to REF CURSOR.

set serveroutput on 
declare 
 lcursor number;                       -- for DBMS_SQL Cursor ID
 lreturn number;                       -- for DBMS_SQL Cursor ID
 
 lref_cursor sys_refcursor;            -- for REF CURSOR
 type        t_emptab is table of employees%rowtype;
 lemp_tab    t_emptab;

begin
  lcursor := dbms_sql.open_cursor;
  dbms_sql.parse(lcursor, 
                'Select * from employees',
                 DBMS_SQL.NATIVE);
  lreturn := dbms_sql.execute(lcursor);
  
                                        -- convert from dbms_sql Cursor ID to a REF CURSOR
  lref_cursor := dbms_sql.to_refcursor(lcursor);
  fetch lref_cursor bulk collect into lemp_tab;
  dbms_output.put_line('Employee count: '||lemp_tab.count);
  close lref_cursor;
end;
/
anonymous block completed
Elapsed: 00:00:00.050
Employee count: 107

DBMS_SQL.to_cursor_number: from REF CURSOR => to DBMS_SQL cursor ID.

declare 
  lref_cursor sys_refcursor;
  lcursor number;
  lcount  number := 0;
begin 
  OPEN lref_cursor FOR 'select * from employees';
                                          -- convert from REF CURSOR to a dbms_sql Cursor ID
  lcursor := dbms_sql.to_cursor_number(lref_cursor);

  while dbms_sql.fetch_rows(lcursor) > 0 LOOP
    lcount := lcount + 1;
  end loop;
  dbms_output.put_line('Employee count: '|| lcount);
  dbms_sql.close_cursor(lcursor);
end;
/
anonymous block completed
Elapsed: 00:00:00.024
Employee count: 107

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
BIND_ARRAY (Ps) BIND_VARIABLE (Ps) CLOSE_CURSOR COLUMN_VALUE DEFINE_ARRAY DEFINE_COLUMN DESCRIBE_COLUMN (P) EXECUTE (F) EXECUTE_AND_FETCH (F) FETCH_ROWS (F) IS_OPEN (F) LAST_ERROR_POSITION (F) LAST_ROW_COUNT (F) LAST_ROW_ID LAST_SQL_FUNCTION_CODE OPEN_CURSOR (F) PARSE (Ps) TO_CURSOR_NUMBER (F) TO_REFCURSOR (F) VARIABLE_VALUE (Ps)

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

Pages

Native Dynamic SQL x DBMS_SQL

Dynamic SQL: DBMS_SQL package