• Loading
    • Dynamic SQL

      Learning Objectives

      After completing this session, you will be able to:
      • Explain dynamic SQL
      • Execute dynamic SQL
      • Describe bulk operations
      • Describe FORALL
      • Describe BULK COLLECT
      • Differentiate soft parse and hard parse


      Understand Dynamic SQL

      What is Dynamic SQL?
      Dynamic SQL is an advanced programming technique that adds flexibility and functionality to applications. Dynamic SQL allows you to write SQL that will then write and execute more SQL for you. This can be a great time saver because you can:
      • Automate repetitive tasks.
      • Write code that will work in any database or server.
      • Write code that dynamically adjusts itself to changing conditions


      Features of Dynamic SQL:
      • Dynamic SQL enables to write programs that reference SQL statements whose full text is not known until runtime
      • Before discussing dynamic SQL in detail, a clear definition of static SQL may provide a good starting point for understanding dynamic SQL
      • Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at Compilation time that is not there in case of Dynamic SQL


      Use of Dynamic SQL

      Following are the uses of Dynamic SQL:
      • Dynamic SQL lets execute SQL statements that are not supported in static SQL Programs, data definition language (DDL) statements such as CREATE, data control Statement such as GRANT and session control statement such as ALTER SESSION
      • Dynamic SQL should be used in cases where static SQL does not support the operation you want to perform or in cases you do not know the exact SQL statements that must be executed by a PL/SQL procedure i.e. at Runtime



      Execute Dynamic SQL

      The EXECUTE IMMEDIATE Statement: The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block
      Syntax:
      Code:
      EXECUTE IMMEDIATE dynamic_string 
      [INTO {define_variable[, define_variable]... | record}] 
      [USING [IN | OUT | IN OUT] bind_argument 
      [, [IN | OUT | IN OUT] bind_argument]...];



      • Where dynamic_string is a string expression that represents a SQL statement or PL/SQL block.
      • define_variable is a variable that stores a SELECTed column value, record is a user-defined or %ROWTYPE record that stores a SELECTed row.
      • bind_argument is an expression whose value is passed to the dynamic SQL statement or PL/SQL block.
      • The INTO clause, useful only for single-row queries, specifies the variables or Record into which column values are fetched. For each column value returned by the query, there must be a corresponding, type-compatible variable or field in the INTO clause
      • Every bind argument must be put in the USING clause. If no parameter mode is specified, it defaults to IN. At run time, any bind arguments in the USING clause replace corresponding placeholders in the SQL statement or PL/SQL block. So, every placeholder must be associated with a bind argument in the USING clause. Numeric, character, and string literals are allowed in the USING clause, but Boolean literals (TRUE, FALSE, NULL) are not. To pass nulls to the dynamic string, a workaround must be used. Dynamic SQL supports all the SQL data types.
      • So, for example, define variables and bind arguments can be collections, LOBs, instances of an object type, and REFs.


      Example using EXECUTE IMMEDIATE

      Code:
      DECLARE 
      sql_stmt VARCHAR2(100); 
      plsql_block VARCHAR2(200); 
      my_deptno NUMBER(2) := 50; 
      my_dname VARCHAR2(15) := ’PERSONNEL’; 
      my_loc VARCHAR2(15) := ’DALLAS’; 
      emp_rec emp%ROWTYPE; 
      BEGIN 
      sql_stmt := ’INSERT INTO dept VALUES (:1, :2, :3)’; 
      EXECUTE IMMEDIATE sql_stmt USING my_deptno, my_dname, 
      my_loc; 
      sql_stmt := ’SELECT * FROM emp WHERE empno = :id’; 
      EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING 7788;
      EXECUTE IMMEDIATE ’DELETE FROM dept
      WHERE deptno = :n’ USING my_deptno;
      plsql_block := ’BEGIN emp_stuff.raise_salary(:id, :amt); END;’;
      EXECUTE IMMEDIATE plsql_block USING 7788, 500;
      EXECUTE IMMEDIATE ’CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
      sql_stmt := ’ALTER SESSION SET SQL_TRACE TRUE’;
      EXECUTE IMMEDIATE sql_stmt;
      END;
      Example using the DBMS SQL package
      Code:
      CREATE PROCEDURE insert_into_table ( 
      Table_name VARCHAR2, 
      Deptnumber NUMBER, 
      Deptname VARCHAR2, 
      Location VARCHAR2) IS 
      Cur_hdl INTEGER; 
      Stmt_str VARCHAR2(200); 
      Rows_processed BINARY_INTEGER; 
      BEGIN 
      Stmt_str := 'INSERT INTO ' || Table_name || ' VALUES 
      (:deptno, :dname, :loc)'; 
      -----Open cursor 
      cur_hdl := dbms_sql.open_cursor; 
      ---- Parse cursor 
      dbms_sql.parse(cur_hdl, stmt_str,dbms_sql.native); 
      ----- Supply binds 
      dbms_sql.bind_variable (cur_hdl, ':deptno', deptnumber); 
      dbms_sql.bind_variable (cur_hdl, ':dname', deptname); 
      dbms_sql.bind_variable (cur_hdl, ':loc', location); 
      ---- Execute cursor 
      rows_processed := dbms_sql.execute(cur_hdl); 
      ---- Close cursor 
      dbms_sql.close_cursor(cur_hdl); 
      END;


      Understand Bulk Operations

      What is the meaning of Bulk Operations?
      • PL/SQL is very tightly integrated with the Oracle database SQL engine, but this tight integration does not mean that there isn’t any overhead associated with executing SQL statements from PL/SQL
      • When a PL/SQL program executes, the procedural portions are executed by the PL/SQL engine, but all SQL statement are passed to SQL layer for execution, then data is passed back to the procedural engine
      • The transfer of data back and forth from PL/SQL to SQL and back again is called context switching. The more switches occur the more performance degrades. Two new enhancements
      • BULK COLLECT and the FORALL statements allows to bulk together all of the context switches into a single switch and pass that to the SQL engine



      Understand FOR ALL

      What is the meaning of FOR ALL?
      • A variation on the classic FOR LOOP that bundles together multiple DML statements based on a collection
      • Bulk DML using the FORALL statement will also take advantage of turning multiple context switches into a single context switch.


      The FORALL statement must follow these simple rules:
      • The body of the FORALL statement must contain a single DML operation.
      • The DML must reference collection elements, indexed by the index_row variable in the FORALL statement. The scope of the index_row variable is the FORALL statement only; you may not reference it outside of that statement.
      • Do not declare an INTEGER variable for index_row. It is declared implicitly by the PL/SQL engine.
      • The lower and upper bounds must specify a valid range of consecutive index numbers for the collection(s) referenced in the SQL statement.


      Example of FOR ALL:
      Code:
      PROCEDURE proc_bulk_collect IS 
      CURSOR cur_emp IS 
      SELECT emono 
      FROM emp; 
      TYPE tt_empno IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; 
      tab_emono tt_empno; 
      BEGIN 
      OPEN cur_emp; 
      FETCH cur_emp INTO BULK COLLECT tab_emono; 
      close cur_emp; 
      FORALL i IN tab_emono.FIRST..tab_emono.LAST LOOP 
      DELETE FROM new_emp 
      WHERE empno = tab_emono(i); 
      END LOOP; 
      end;


      Understand BULK COLLECT
      BULK COLLECT is an enhancement to explicit and implicit cursor query operation that allows the transfer of multiple rows of data in one trip to the SQL engine. Following is an example of BULK COLLECT:

      Code:
      PROCEDURE proc_bulk_collect IS 
      CURSOR cur_emp IS 
      SELECT emono,ename 
      FROM emp; 
      TYPE tt_empno IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; 
      tab_emono tt_empno; 
      TYPE tt_ename IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; 
      tab_ename tt_ename; 
      BEGIN 
      OPEN cur_emp; 
      FETCH cur_emp INTO BULK COLLECT tab_emono,tab_ename; 
      close cur_emp; 
      END;


      Appreciate the difference between Soft Parse and Hard Parse


      Soft parse: It is a Statement, which is parsed and already in the shared pool, those statements for re-execution need not require parsing if it is found in the shared Pool. A shorter process to getting the query result

      Hard parse:
      It is a statement, which is parsed every time during the execution is called hard parse. If we have to Hard Parse a large percentage of our queries, our system will function slowly and in some cases. Hard parse can be avoided by using bind variable in subprogram.

      Summary

      • Dynamic SQL enables to write programs that reference SQL statement whose full text is not known until runtime.
      • Dynamic SQL lets execute SQL statements that are not supported in static SQL Programs.
      • The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block.
      • BULK COLLECT and the FORALL statements allows to bulk together all of the context switches into a single switch and pass that to the SQL engine.



      Test Your Understanding
      1. Which SQL statement PL/SQL support not supporting?
      2. What is a dynamic SQL?
      3. What is the use of EXECUTE IMMEDIATE?
      4. What is BULCK COLLECT?
      5. What is DBMS_SQL?
      6. The following are the two Dynamic SQL statements. What is the main difference between these two statements in the respect of execution?
      Code:
      Begin 
      EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)'; 
      End; 
      / 
      CREATE OR REPLACE PROCEDURE CREATE_TABLE2 AS 
      cur integer; 
      rc integer; 
      BEGIN cur := DBMS_SQL.OPEN_CURSOR; 
      DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE); 
      rc := DBMS_SQL.EXECUTE(cur); 
      DBMS_SQL.CLOSE_CURSOR(cur); 
      END;
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.