Dynamic SQL

What is dynamic sql? Explain it.

Ans:

Dynamic SQL enables you to write programs those reference SQL statements whose full text is not known until runtime. Dynamic SQL also lets you execute data definition language (DDL) statements and other SQL statements that are not supported in purely static SQL programs.

We use dynamic sql for executing:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE
• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE

Inside Pl /sql block

   How we execute dynamic sql?

Ans:

Oracle provides two methods for using dynamic SQL within PL/SQL:

Native dynamic SQL (Execute immediate)

The DBMS_SQL package.

       What is the advantage of Native Dynamic SQL over DBMS_SQL package?

Ans:

Native Dynamic SQL is Easy to Use:

DBMS_SQL Package

Native Dynamic SQL

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;

CREATE PROCEDURE insert_into_table (       table_name  VARCHAR2,       deptnumber  NUMBER,       deptname    VARCHAR2,       location    VARCHAR2) IS    stmt_str    VARCHAR2(200); BEGIN    stmt_str := 'INSERT INTO ' || table_name || ' values(:deptno, :dname, :loc)';    EXECUTE IMMEDIATE stmt_str  USING  deptnumber, deptname, location;

Native Dynamic SQL is faster than DBMS_SQL:

          Because the PL/SQL interpreter has built-in support for it. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls.

Native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.

          The DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead. Each time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for use during execution. Each time you execute a fetch, the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead.

Native Dynamic SQL Supports User-Defined Types:

          Native dynamic SQL supports all of the types supported by static SQL in PL/SQL, including user-defined types such as user-defined objects, collections, and REFs. The DBMS_SQL package does not support these user-defined types.

Native Dynamic SQL Supports Fetching Into Records:

DECLARE

    TYPE EmpCurTyp IS REF CURSOR;

    c EmpCurTyp;

    emp_rec emp%ROWTYPE;

    stmt_str VARCHAR2(200);

    e_job emp.job%TYPE;

BEGIN

   stmt_str := 'SELECT * FROM emp WHERE job = :1';

    -- in a multi-row query

    OPEN c FOR stmt_str USING 'MANAGER';

    LOOP

        FETCH c INTO emp_rec;

        EXIT WHEN c%NOTFOUND;

    END LOOP;

    CLOSE c;

    -- in a single-row query

    EXECUTE IMMEDIATE stmt_str INTO emp_rec USING 'PRESIDENT';

END;

Note: A NOT NULL database column constraint does not apply to variables that are declared using %TYPE. Therefore, if you declare a variable using the %TYPE attribute that uses a database column defined as NOT NULL, you can assign the NULL value to the variable.

   What is the advantage of DBMS_SQL package over dynamic SQL?

Ans:

DBMS_SQL is supported in Client-Side Programs: The DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC); these calls occur when you need to bind a variable, define a variable, or execute a statement.

DBMS_SQL Supports DESCRIBE: The DESCRIBE_COLUMNS procedure in the DBMS_SQL package can be used to describe the columns for a cursor opened and parsed through DBMS_SQL. This feature is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.

DBMS_SQL Supports SQL Statements Larger than 32KB: The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.