Showing posts with label dbms_sql. Show all posts
Showing posts with label dbms_sql. Show all posts

Native Dynamic SQL x DBMS_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

Dynamic SQL: DBMS_SQL package

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:
  1. OPEN_CURSOR
  2. PARSE
  3. BIND_VARIABLE or BIND_ARRAY
  4. DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY
  5. EXECUTE
  6. FETCH_ROWS or EXECUTE_AND_FETCH
  7. VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG
  8. 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