Showing posts with label Conditiional Compilation. Show all posts
Showing posts with label Conditiional Compilation. Show all posts

(19-20) PL/SQL Compiler


PL/SQL Compiler
  • Several initialization parameters (compiler parameters) affect the compilation of PL/SQL units.
  • The values at the time of compilation of the PLSQL_CCFLAGS, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS, and NLS_LENGTH_SEMANTICS initialization parameters are stored with the unit's metadata.
  • ALL_PLSQL_OBJECT_SETTINGS view: Keeps information about the settings of these parameters.


PLSCOPE_SETTINGS Controls compile-time collection, cross-reference, and storage of PL/SQL source text identifier data.
PLSCOPE_SETTINGS = IDENTIFIERS:{ NONE | ALL }
PLSQL_CCFLAGS Enables you to control conditional compilation of each PL/SQL unit independently.
PLQL_CODE_TYPE Defines compilation mode for PL/SQL libary units.
PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
PLSQL_OPTIMIZE_LEVEL The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Range: {0,3}
PLSQL_DEBUG Specifies whether to compile PL/SQL units for debugging.
Deprecated on 11g.
To compile PL/SQL units for debugging, specify PLSQL_OPTIMIZE_LEVEL=1.
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
Related to Native Compilation.Deprecated on 11g.
PLSQL_WARNINGS Enables/disables the reporting of warning messages by the PL/SQL compiler.
Compile-time warning categories: [ SEVERE | PERFORMANCE | INFORMATIONAL ]
NLS_LENGTH_SEMANTICS




PL/SQL Optimizer
Prior to Oracle Database 10g Release 1 (10.1), the PL/SQL compiler translated your source text to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizer that can rearrange code for better performance.

The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by setting the compilation parameter PLSQL_OPTIMIZE_LEVEL=1 instead of its default value 2. In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all. Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.



On 10g R1

PLSQL_OPTIMIZE_LEVEL init parameter: Controls Global optimization of Pl/SQL Code. Default value is often good enough.

Native compilation: fewer initialization parameters to set and less compiler configuration. Object code is stored in the database.
  • PLSQL_NATIVE_LIBRARY_DIR: Only parameter required.(Deprecated on 11g)
  • PLSQL_CODE_TYPE: turn native compilation ON|OFF.
  • PLSQL_COMPILER_FLAGS: Deprecated.
  • $ORACLE_HOME/plsql/spnc_commands file contains the commands and options for compiling and linking
  • dbmsupgnv.sql: recompiles all the PL/SQL modules in a database as NATIVE.
  • dbmsupgin.sql: recompiles all the PL/SQL modules in a database as INTERPRETED.

Compile-Time Warnings
  • May be issued when you compile subprograms that produce ambiguous results or use inefficient constructs.
  • Enable | Disable warnigns: Use PLSQL_WARNINGS init parameter or DBMS_WARNING package.


On 10g R2
  • Conditional Compilation
    • Enables selective inclusion of code depending on the values of the conditions evaluated during compilation. For example:
    • You can determine which PL/SQL features in a PL/SQL application are used for specific database releases.
    • Also useful when you want to execute debugging procedures in a development environment, but want to turn off the debugging routines in a production environment.
  • Dynamic Wrap
    • DBMS_DDL wrap subprograms obfuscate (hide) dynamically generated PL/SQL code units.

--------------------------------------


Pragmas
A pragma is an instruction to the compiler that it processes at compile time


Pragma Autonomous_transaction
  • Marks a routine [(not-nested) anonymous block, subprogram, method (of ADT), Trigger] as autonomous; that is, independent of the main transaction
  • Autonomous transactions do SQL operations and commit or rollback, without committing or rolling back the main transaction.
  • When you enter the executable section of an autonomous routine, the main transaction suspends. When you exit the routine, the main transaction resumes.
  • If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception.
  • To exit normally, you must explicitly commit or roll back all autonomous transactions.

(1) Autonomous PL/SQL Block
-- create table emp as bellow:
SQL> create table emp
 (name varchar2(20), jobid varchar2(10));

-- insert four initial rows and commit inserts.
SQL> insert into emp values('John', 'Accounting');
SQL> insert into emp values('Paul', 'Accounting');
SQL> insert into emp values('George', 'Ads');
SQL> insert into emp values('Ringo', 'Ads');
SQL> commit;

-- check the inserted rows.
SQL> select * from emp;

NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads

-- insert a 5th row. and do not commit.
-- Check the inserted row.
SQL> insert into emp values('Ghost', 'Ads');
1 row created.

SQL> select * from emp;
NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads
Ghost                Ads

-- Now add the autonomous block.

SQL> set serveroutput on
SQL> declare
  2     pragma autonomous_transaction;
  3    numemp number;
  4  begin
  5    select count(*) into numemp from emp;
  6    dbms_output.put_line('num emps before autonomous insert: ' || numemp);
  7    insert into emp values('Autonomous', 'New');
  8    dbms_output.put_line('Inserted: Autonomous');
  9    select count(*) into numemp from emp;
 10    dbms_output.put_line('num emps after autonomous insert: ' || numemp);
 11    commit;
 12  end;
 13  /
num emps before autonomous insert: 4
Inserted: Autonomous
num emps after autonomous insert: 5

PL/SQL procedure successfully completed.

-- Now outside the autonomous block, check the existing 
-- rows and rollback the insertion of "Ghost"
SQL> select * from emp;
NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads
Ghost                Ads
Autonomous           New

SQL> rollback;

Rollback complete.

-- Check that the row for "Ghost" was rolled back, but the 
-- value inserted in the autonomous transaction remained.
SQL> select * from emp;

NAME                 JOBID
-------------------- ----------
John                 Accounting
Paul                 Accounting
George               Ads
Ringo                Ads
Autonomous           New
SQL>

(2) Autonomous standalone procedure
In the example above, the anonymous block could be rewritten as an autonomous procedure
as below:
CREATE OR REPLACE PROCEDURE ins_emp
  AS
    pragma autonomous_transaction;
    numemp number;
BEGIN
    select count(*) into numemp from emp;
    dbms_output.put_line('num emps before autonomous insert: ' || numemp);
    insert into emp values('Autonomous', 'New');
    dbms_output.put_line('Inserted: Autonomous');
    select count(*) into numemp from emp;
    dbms_output.put_line('num emps after autonomous insert: ' || numemp);
    commit;
END ins_emp;

(3) Autonomous Triggers
A trigger must be autonomous to run TCL or DDL statements.
To run DDL statements, the trigger must use native dynamic SQL.






Associates a user-defined exception name with an error code.
Can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.
DECLARE
    past_due  EXCEPTION;                       -- declare exception
    PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
  BEGIN
    ...
Check herefor an example.

Pragma Inline (check here)
  • Specifies whether a subprogram invocation (or statement) is to be inlined.
  • Inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).
  • To allow subprogram inlining: PLSQL_OPTIMIZE_LEVEL compilation parameter := [2 (default) | 3 ]
  • If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations.

Pragma Restrict_References
  • Asserts that a user-defined subprogram does not read or write database tables or package variables.
  • Can appear only in a package specification or ADT specification. Typically, this pragma is specified for functions.
  • Subprograms that read or write database tables or package variables are difficult to optimize, because any invocation of the subprogram might produce different results or encounter errors.
  • .





Package...memory usage
...IS NOT
SERIALY_REUSABLE
Package state stored in the user global area (UGA) for each user.
The amount of UGA memory needed increases linearly with the number of users.
Package state can persist for the life of a session, locking UGA memory.
...IS
SERIALY_REUSABLE
Package state stored in a work area in a small pool in the system global area (SGA).
Package state persists only for the life of a server call, after which the work area returns to the pool. If a subsequent server call references the package, then Oracle Database reuses an instantiation from the pool. Reusing an instantiation re-initializes it; therefore, changes made to the package state in previous server calls are invisible.

CREATE OR REPLACE PACKAGE pkg IS
  PRAGMA SERIALLY_REUSABLE;
  n number:= 5;
END;
/
 
CREATE OR REPLACE PACKAGE BODY pkg IS
  PRAGMA SERIALLY_REUSABLE;
BEGIN
  n := 5;
END;
/

  • Allow you to better manage memory for scalability.
  • Specifies that the package state is needed for only one call to the server, after which the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
  • Appropriate for packages that declare large temporary work areas that are used once in the same session.

--------------------------

Conditional compilation
  • The Conditional compilation (CC) feature and related PL/SQL packages are available for the 10g Release 1 (10.1.0.4) and later.
  • CC allows constructs — with formally defined syntax and semantics — to be used to mark up text so that a preprocessor can deterministically derive the text that will be submitted to the compiler proper.
  • CC uses (a) selection directives($) (similar to IF stmt), (b) inquiry directives($$), and (c) error directives.
  • CC uses preprocessor control tokens($) to mark code that is processed before the PL/SQL unit is compiled: $IF, $THEN, $ELSE, $ELSIF, $ERROR
  • Oracle’s Applications Division provided the use case that motivated the introduction of PL/SQL conditional compilation. They wanted their code to be able to span different releases of Oracle Database, using the latest features in the latest release and using a fallback in earlier releases.(see more here)

Uses of Conditional Compilation (CC) {see detailed discussion in this white paper}
  • Allowing self-tracing code to be turned on during development and to be turned off when the code goes live.
  • Allowing alternative code fragments, each appropriate for the peculiarities of a particular operating system and inappropriate or illegal for other operating systems, to coexist in the same source text so the correct fragment can be selected for compilation according to the circumstances.
  • Newer Oracle releases introduce new features with new syntax and programs that take advantage of these are illegal in earlier releases. PL/SQL conditional compilation supports this use in an elegant and powerful way.
  • A developer often realizes that more than one approach to the design of a subprogram will result in its correct behavior; sometimes the alternative approaches result in source code versions which are textually largely the same but which differ critically in small areas distributed fairly evenly thought the source. PL/SQL conditional compilation allows all the approaches to be coded in a single source text — while they are being evaluated — and thereby eliminates the risk of carelessly introduced unintended differences.
  • Modular delivery of extra functionality can be implemented by optional PL/SQL compilation units which are installed according to what the customer has licensed. PL/SQL’s dependency model prevents the core part of the application referring statically to optional components that are not installed. However, the core part of the application should not need reinstallation in order to accommodate the installation of a new optional component. This has forced the use of dynamic invocation — which has some drawbacks. Conditional compilation allows a new approach.
Using selection directives ($IF, $ELSE) and error directives ($ERROR)
set serveroutput on
begin
  $IF dbms_db_version.ver_le_10_1 $THEN
     $ERROR  'unsupported database release' $END
  $ELSE
     dbms_output.put_line (
        'release ' || dbms_db_version.version || '.' || 
        dbms_db_version.release || ' is supported.'
     ); 
     -- since its a newer release, use a new commit syntax
    -- supported in 10.2
    commit write immediate nowait;
  $END
end;
/

anonymous block completed
release 11.2 is supported.

The package DBMS_DB_VERSION provide static constants:
  • dbms_db_version.version
  • dbms_db_version.release
  • dbms_db_version.ver_le_v
  • dbms_db_version.ver_le_v_r



Inquiry directives($$)
  • $$PLSQL_LINE: the number of the source line on which the directive appears in the current PL/SQL unit.
  • $$PLSQL_UNIT: the name of the current PL/SQL unit.

  • You can assign values to inquiry directives with the PLSQL_CCFLAGS compilation parameter
    • ALTER SESSION set PLSQL_CCFLAGS = 'flag:True, val:5'
  • $$plsql_compilation_parameter: a PL/SQL compilation parameter

Displaying Values of PL/SQL Compilation Parameters
BEGIN
  DBMS_OUTPUT.PUT_LINE('$$PLSCOPE_SETTINGS = '     || $$PLSCOPE_SETTINGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CCFLAGS = '        || $$PLSQL_CCFLAGS);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_CODE_TYPE = '      || $$PLSQL_CODE_TYPE);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = '       || $$PLSQL_WARNINGS);
  DBMS_OUTPUT.PUT_LINE('$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);
END;
/

anonymous block completed
$$PLSCOPE_SETTINGS = 
$$PLSQL_CCFLAGS = 
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 0
$$PLSQL_WARNINGS = DISABLE:ALL
$$NLS_LENGTH_SEMANTICS = BYTE

Using inquiry directives (example extracted from here)
-- USE PACKAGE SPEC and BODY to define and declare a USER-DEFINED subtype.

-- Package specification: Use selection directive ($IF) to test for the 
-- version of the database.
-- If version < 10g, define subtype as NUMBER.
-- If version >= 10g, define subtype as BINARY_DOUBLE
create or replace package my_pkg AS
  subtype my_real IS
    $IF dbms_db_version.version < 10 $THEN
      number;
    $ELSE
      binary_double;
    $END
  
  my_pi my_real;
  my_e  my_real;
end my_pkg;
/

-- Package body: test for db version. Assign values
-- for the subtype variable accordingly. 
create or replace package body my_pkg AS
begin
$IF dbms_db_version.version < 10 $THEN 
     my_pi := 3.14159265358979323846264338327950288420;
    my_e  := 2.71828182845904523536028747135266249775;
  $ELSE
    my_pi := 3.14159265358979323846264338327950288420d;
    my_e  := 2.71828182845904523536028747135266249775d;
  $END
END my_pkg;
/ 

-- Use the subtype defined in the package on a standalone procedure.

-- Uses inquiry directive($$my_debug) to decide whether or not 
-- to run some code. If $$my_debug = TRUE, run the debug code that 
-- check for the datatype of the subtype my_real.
create or replace procedure circle_area (radius my_pkg.my_real) IS
  my_area       my_pkg.my_real;
  my_data_type  varchar2(30);
begin
  my_area := my_pkg.my_pi * (radius**2);
  
  dbms_output.put_line
    ('Radius: '|| to_char(radius) || ' Area: '|| to_char(my_area));
  
  $IF $$my_debug $THEN
    select data_type into my_data_type
    from user_arguments
    where object_name = 'CIRCLE_AREA'
    and ARGUMENT_NAME = 'RADIUS';
    
    dbms_output.put_line
     ('Data type of the RADIUS argument is: '|| my_data_type);
  $END
end circle_area;
/

-- Run procedure circle_area 
(1) Set $$my_debug: FALSE
SQL> alter session set plsql_ccflags = 'my_debug:FALSE';
Session SET altered.

SQL> set serveroutput on 
SQL> exec circle_area(2);
Radius: 2.0E+000 Area: 1.2566370614359172E+001
PL/SQL procedure successfully completed.

(1) Set $$my_debug: TRUE
SQL> alter session set plsql_ccflags = 'my_debug:TRUE';
Session SET altered.

-- You need to recompile the procedure circle_area so it can 
-- see the change in the session 
SQL> alter procedure circle_area compile;
Procedure altered.

SQL> set serveroutput on 
SQL> exec circle_area(2);
Radius: 2.0E+000 Area: 1.2566370614359172E+001
Data type of the RADIUS argument is: BINARY_DOUBLE

PL/SQL procedure successfully completed.

Using DBMS_PREPROCESSOR to print source text
  • You can use the dbms_preprocessor package to print the source text executed after
    the conditional compilation directives were all processed.
  • For the procedure circle_area above, you can retrieve the source code processed when the
    debug directive ($$my_debug) is set to TRUE.
  • Subprograms
    • DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
    • DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (overloaded (3))


SQL> call dbms_preprocessor.print_post_processed_source
  2                                             ('PROCEDURE', 'DEV2', 'CIRCLE_AREA');
procedure circle_area (radius my_pkg.my_real) IS
my_area       my_pkg.my_real;
my_data_type  varchar2(30);
begin
my_area := my_pkg.my_pi * (radius**2);
dbms_output.put_line
('Radius: '|| to_char(radius) || ' Area: '|| to_char(my_area));
select data_type into my_data_type
from user_arguments
where object_name = 'CIRCLE_AREA'
and ARGUMENT_NAME = 'RADIUS';
dbms_output.put_line
('Data type of the RADIUS argument is: '|| my_data_type);
end circle_area;

Call completed.


CC Restrictions:
  • You cannot have a CC directive within a CREATE TYPE statement. (Why? Attributes of a TYPE will determine the physical structure of dependent tables.)
  • Package Spec, Package body, type body, procedure/function w/o parameters: first conditional compilation directive cannot appear before the keyword IS or AS.
  • subprogram with at least one formal parameter: the first conditional compilation directive cannot appear before the left parenthesis that follows the subprogram name.
  • Trigger or an anonymous block, the first conditional compilation directive cannot appear before the keyword DECLARE or BEGIN, whichever comes first.



On 11g R2: Compiling PL/SQL Units for Native Execution

  • PL/SQL units can be compiled into native code (processor-dependent system code), which is stored in the SYSTEM tablespace.
  • Any PL/SQL unit of any type can be natively compiled, including Oracle supplied ones.
  • Natively compiled program units work in all server environments.
  • Greatest performance gains: for computation-intensive procedural operations. (i.e. Data warehouse and applications with extensive server-side transformations of data for display).
  • Least performance gains: for PL/SQL subprograms that spend most of their time running SQL.
  • The PLSQL_CODE_TYPE compilation parameter determines whether PL/SQL code is natively compiled or interpreted.
    • ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
  • Run this query to determine how many objects are compiled NATIVE and INTERPRETED:
SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
FROM DBA_PLSQL_OBJECT_SETTINGS
WHERE PLSQL_CODE_TYPE IS NOT NULL
GROUP BY TYPE, PLSQL_CODE_TYPE
ORDER BY TYPE, PLSQL_CODE_TYPE;


Obfuscation and wrapping













































(15) Dynamic SQL

[ Oracle PL/SQL ]
"From all old seamy throats of elders, musty books,
I've salvaged not a word."
(Cormac McCarthy)

Dynamic SQL statements are not embedded in your source program and checked at compile time.
Instead, they are stored in character strings that are input to, or built by, the program at runtime.
(i.e. dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.)

Programming methodology for generating and running SQL statements at run time. Good for:
  • Writing general-purpose and flexible programs like ad hoc query systems
  • Writing programs that must run DDL statements
  • When number and datatypes of IN/OUT variables are unknown at compilation time
  • When the full text of a SQL stmt needs to be obtained at run-time only.
    • i.e. a SELECT stmt that includes an identifier (a table name) that is unknown at compile time.
    • i.e. a WHERE clause in which the number of subclauses is unknown at compile time.

How to write dynamic SQL ? (two ways)
  • (1) Native dynamic SQL: PL/SQL language feature for building and running dynamic SQL statements
  • (2) DBMS_SQL package: , an API for building, running, and describing dynamic SQL statements

Native dynamic SQL x DBMS_SQL package:
  • native dynamic SQL: Faster, and more readable (even more when optimized by compiler)
  • Native dynamic SQL: requires complete specification of IN/OUT vars (number and datatype) at compile time.
  • Switch between native dynamic SQL and DBMS_SQL package using
    DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.

Using EXECUTE IMMEDIATE (Advantages over DBMS_SQL)
  • DBMS_SQL: Oracle 8 and earlier
  • EXECUTE IMMEDIATE: Oracle 8i and later
  • Native dynamic SQL:
    • Integral part of the SQL language that is built into Pl/SQL, thus making it simpler to use and faster than the DBMS_SQL package.
    • Native dynamic SQL also supports the usage of object types and collections not supported by the DBMS_SQL package
    • Faster than DBMS_SQL because it parses, binds, and executes the statements immediately.
    • DBMS_SQL requires calls to procedures and functions.

Question: Can I use TCL or DDL within a PL/SQL Block?
A: No. You HAVE to use DYNAMIC SQL for that. (Native Dyn SQL or DBMS_SQL).
Static SQL is limited to DML (SELECT, INSERT, UPDATE, DELETE, MERGE), TCL (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION) and LOCK TABLE.











Native Dynamic SQL: EXECUTE IMMEDIATE
  • EXECUTE IMMEDIATE: Builds and runs a dynamic SQL statement in a single operation.
  • If dynamic_sql_stmt has a RETURNING INTO clause, using_clause can contain only IN bind arguments. The bind arguments in the RETURNING INTO clause are OUT bind arguments by definition.
  • When using dynamic SQL, beware of SQL injection, a security risk.
  • The value of bind_argument cannot be TRUE, FALSE, or NULL

Type of dynamic SQL statement USE of EXECUTE IMMEDIATE
SELECT returns at most one row OUT-bind arguments (defines): in the INTO clause.
IN-bind arguments: in the USING clause.
EXECUTE IMMEDIATE
              SELECT ... INTO <OUT-vars>
              USING <bind_arg>;
SELECT returns multiple rows OUT-bind arguments: in the BULK COLLECT INTO clause. IN-bind arguments: in the USING clause.
EXECUTE IMMEDIATE
              SELECT ... BULK COLLECT INTO <OUT-vars>
              USING <bind_arg>;
DML with RETURNING INTO clause IN-bind arguments: in the USING clause.
OUT-bind arguments: in the RETURNING INTO clause.
DMLw/o RETURNING INTO clause all bind arguments in the USING clause



Using dynamic SQL with EXECUTE IMMEDIATE:
-- create procedure to insert new record on departments table
create or replace procedure new_dept
 (deptid in out number,
  dname  in varchar2,
  mgrid  in varchar2,
  locid  in varchar2) as 
begin 
 deptid:= departments_seq.nextval;
 insert into departments (
    department_id,
    department_name,
    manager_id,
    location_id)
  values (deptid, dname, mgrid, locid);
end;

-- Use dynamic SQL to call the procedure new_dept
set serveroutput on
declare
 plsql_block varchar2(500);
 new_deptid  number(4);
 new_dname   varchar2(30)    := 'Advertising';
 new_mgrid   number(6)       := 200;
 new_locid   number(4)       := 1700;
 numdep      number;
begin
 select count(*) into numdep  from departments;
 dbms_output.put_line('# depts: ' || numdep);

 -- enter the dynamic SQL statement
 -- note that the statement is complete. Only dynamic binding of the 
 -- procedure parameters occur.
 -- note that the SQL stmt has placeholders for bind arguments.
 plsql_block := 'begin new_dept(:a, :b, :c, :d); end;';

 -- use EXECUTE IMMEDIATE to run the SQL stmt and specify the 
 -- binding variables.
 EXECUTE IMMEDIATE plsql_block
   USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
 select count(*) into numdep
 from departments;
 dbms_output.put_line('# depts: ' || numdep);
end;

anonymous block completed
# depts: 27
# depts: 28


Using SELECT, DDL and DML with Execute Immediate
Using Conditional Compilation


What the example below does (3 procedures):
(a) Check whether a table exists in the database (SELECT)
(b) DROP and Recreate or CREATE table test and GRANT access on the table to another user (DDL)
(c) Insert data on the created table (DML)


(1) Procedure check_object:
  • (SELECT returns one row only): Check whether a table named TESTE already exists in the database
  • procedure call: check_object('TESTE', 'TABLE');
CREATE OR REPLACE Procedure check_object (p_objname in varchar2, p_objtype in varchar2, 
                        p_created OUT boolean) IS
 query varchar2(200);
 vcount varchar2(40);
BEGIN
  -- using SELECT with EXECUTE IMMEDIATE
  query := 'select count(*) from user_objects '||
          'where object_type = :b '|| 
          'and object_name = :c ';
  -- If the dynamic SQL statement is a SELECT statement that can return 
  -- at most one row, put out-bind arguments (defines) in the INTO clause 
  -- and in-bind arguments in the USING clause.
  EXECUTE IMMEDIATE query into vcount  USING IN p_objtype, p_objname;
  IF vcount = 0 THEN
    $IF $$my_debug $THEN
       dbms_output.put_line(p_objtype || ' ' || p_objname || ' does not yet exist on the database.');
    $END
    p_created := FALSE;
  ELSE
    $IF $$my_debug $THEN
       dbms_output.put_line(p_objtype || ' ' || p_objname || ' already exist on the database.');
    $END
    p_created := TRUE;
  END IF; 
EXCEPTION
 When others then 
   dbms_output.put_line('Error Procedure check_object');
   dbms_output.put_line(sqlerrm);
   dbms_output.put_line(dbms_utility.format_error_backtrace());
END;


alter session set plsql_ccflags = 'my_debug:TRUE';
set serveroutput on
declare
 v_created boolean;
begin
check_object('TESTE', 'TABLE',v_created);
end;
/

Anonymous block completed
TABLE TESTE already exist on the database.

(2) Procedure create_table:
  • (DDL): If table exists, drop table and recreate it.
  • (DDL): Grant SELECT on table TESTE to userA.
  • procedure call: cr_tbl('TRUE', 'create table TESTE (name varchar2(20), salary number)';');
  • For the procedure to succeed, the procedure owner MUST have been granted the CREATE TABLE privilege directly, not via a role.
  • Role privileges are not applied in procedures

CREATE OR REPLACE procedure create_table (p_created in boolean, p_stmt in varchar2) IS
 e_leave_drop exception;
 e_leave_create exception;
 stmt varchar2(200);
 v_created boolean;
BEGIN
-- USING DDL with EXECUTE IMMEDIATE  
  IF p_created then
    -- table exist. Drop before creating.
    stmt := 'drop table teste';
    begin
      execute immediate stmt;
    exception 
      when others then 
        dbms_output.put_line('Error dropping table');
        dbms_output.put_line(sqlerrm);
        raise e_leave_drop;
    end;
    dbms_output.put_line('Table dropped.');
  END IF;
  stmt := p_stmt; 
  begin
    execute immediate stmt;
  exception 
    when others then 
      dbms_output.put_line('Error creating table');
      dbms_output.put_line(sqlerrm);
      raise e_leave_create;
  end;
  dbms_output.put_line('Table teste created.'); 
  v_created := TRUE;
  dbms_output.put_line('Calling check_object..'); 
  check_object('TESTE', 'TABLE', v_created);

  stmt :=  'grant select on teste to userb';
  execute immediate stmt;
  dbms_output.put_line('grant select completed');
EXCEPTION
 when e_leave_drop then 
  dbms_output.put_line('leaving procedure...');
 when e_leave_create then 
  dbms_output.put_line('leaving procedure...');
END;

alter procedure check_object compile;
alter session set PLSQL_CCFLAGS = 'my_debug:TRUE';
set serveroutput on
declare
stmt varchar2(200);
vcreated boolean default TRUE;
begin
 stmt := 'create table UserA.teste (name varchar2(20), salary number)';
 create_table(vcreated, stmt);
end;
/

Anonymous block completed
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed


(3) Procedure insert_tbl:
  • (DML): Insert values on table TESTE
  • If the dynamic SQL statement is a DML statement WITHOUT a RETURNING INTO clause, other than SELECT, put all bind arguments in the USING clause.

CREATE OR REPLACE procedure insert_tbl (p_name in varchar2, n in integer, 
                                        p_salinit number) as
 vsal number;
 stmt varchar2(200);
 vname varchar2(20);
BEGIN
  stmt := 'INSERT into TESTE values (:b, :c)';
  vsal := p_salinit;
  vname := p_name;
  for i in 1..n loop
    vname := vname || to_char(i);
    vsal := 10000 + i;
    execute immediate stmt USING vname, vsal;
    dbms_output.put_line('Inserted row '|| i);
  end loop;
EXCEPTION
 when others then 
   dbms_output.put_line('Insert error...');
   dbms_output.put_line(sqlerrm);
END;

set serveroutput on
begin
insert_tbl('Mary', 4, 5000);
end;
/

anonymous block completed
Inserted row 1
Inserted row 2
Inserted row 3
Inserted row 4

SQL> Select * from teste;

NAME                 SALARY                 
-------------------- ---------------------- 
Mary1                10001                  
Mary12               10002                  
Mary123              10003                  
Mary1234             10004                  


What the example below does:
(a) Check the columns of a given table in the data dictionary (Mulitple-row SELECT: BULK COLLECT INTO)
(b) Delete a row from the table. Returns data from the deleted row using RETURNIG clause (DML)

(1) Procedure list_tbl
  • (SELECT): Get column name list with BULK COLLECT INTO.
  • If the dynamic SQL statement is a SELECT statement that can return MULTIPLE rows,
    put out-bind arguments (defines) in the BULK COLLECT INTO clause and in-bind arguments in the USING clause.
  • You CANNOT use bind values to pass in the names of schema objects, such as table names or column names.
  • SO, names of schema objects have to be added to the dynamic sql statement, instead of passed as bind vars.

CREATE OR REPLACE procedure list_tbl (p_tblname in varchar2) as 
 type t_recdata is RECORD (nm varchar2(20), salary number);
 type t_aadata  is TABLE of t_recdata index by pls_integer;
 vdata t_aadata;
 type t_colnm is table of varchar2(20) index by pls_integer;
 vcolnm t_colnm;
 vb varchar2(30);
 stmt varchar2(200);
BEGIN
  stmt := 'SELECT column_name FROM user_tab_columns '||
          'WHERE table_name = :b';
  vb := p_tblname;
  EXECUTE IMMEDIATE stmt BULK COLLECT INTO vcolnm USING p_tblname;
   for i IN vcolnm.first..vcolnm.last loop
     if i = 1 then 
        vb := vcolnm(i);
     else
       vb := vb || ', '|| vcolnm(i); 
     end if;       
   end loop;
   dbms_output.put_line('columns are: '|| vb);  
    -- You CANNOT use bind values to pass in the names of schema objects, 
    -- such as table names or column names.
    -- SO, names of schema objects have to be added to the dynamic sql 
    -- statement, instead of passed as bind vars.
   stmt := 'select '|| vb || ' from teste'; 
   BEGIN
     EXECUTE IMMEDIATE stmt BULK COLLECT INTO vdata;
   EXCEPTION
     when others then
       dbms_output.put_line('Execute immediate: error SELECT');          
       dbms_output.put_line(sqlerrm);
  END;
  
  for i IN vcolnm.first..vcolnm.last loop
     dbms_output.put(vcolnm(i) || ' -- ');
  end loop;
  dbms_output.new_line();
  for i in vdata.first..vdata.last loop
     dbms_output.put_line(vdata(i).nm || ' '|| vdata(i).salary);  
  end loop;        
END list_tbl;

set serveroutput on
begin
list_tbl('TESTE');
end;
/

columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes12 10002
Ximenes123 10003



(2) Procedure delete_table:
  • procedure call: delete_table('Mary');
  • If the dynamic SQL statement is a DML statement with a RETURNING INTO clause,
    put in-bind arguments in the USING clause and out-bind arguments in the RETURNING INTO clause.

set serveroutput on
CREATE OR REPLACE procedure del_tbl(p_name in varchar2) as 
 type t_recdata is RECORD (nm varchar2(20), salary number);
 vrec t_recdata;
 stmt varchar2(200); 
BEGIN
  stmt := 'DELETE FROM teste '||
          'WHERE name = :b ' || 
          'RETURNING name, salary INTO :c, :d';
  execute immediate stmt USING IN p_name returning into vrec;
  dbms_output.put_line('Deleted rec: ' || vrec.nm || ' ' || vrec.salary);
  dbms_output.new_line;
  
  dbms_output.put_line('Listing table...');
  list_tbl('TESTE');
EXCEPTION
    when others then
       dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace());
END;

set serveroutput on
begin
  del_tbl('Mary123');
end;
/

anonymous block completed
Deleted rec: Mary123 10003
Listing table...
columns are: NAME, SALARY
NAME -- SALARY -- 
Mary1 10001
Mary12 10002
Mary1234 10004

Running all together:

set serveroutput on
declare
  vcreated boolean default TRUE;
begin
  check_object('TESTE', 'TABLE', vcreated);
  create_table(vcreated, 'create table teste (name varchar2(20), salary number)');
  insert_table('Ximenes', 3, 9700);
  list_tbl('TESTE');
  delete_table('Ximenes1');
end;

nonymous block completed
TABLE TESTE already exist on the database.
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed
Inserted row 1
Inserted row 2
Inserted row 3
columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes1 10001
Ximenes12 10002
Ximenes123 10003
Deleted rec: Ximenes1 10001

Listing table...
columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes12 10002
Ximenes123 10003


Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

DBMS_SQL Package

Other examples using DBMS_SQL

SQL Injection