Well Tom, Query = 10 and Current = 40 here why?
Current=40 and Query = 0 why not like this?

Hi Tom,
Thanks for great service.
I have a following question.
in my company my peers ask me to use dynamic sql instead of static sql as it results in less number of lines and the sqls are not that complex and are executed once in a day.
is it a good approach?

Thanks Tom for the quick reply.
what I meant by number of lines is, number of lines of code that gets reduced when we use dynamic sql in PL/SQL.

Tom,
I'll add more details here.
instead of having an insert sql, or update sql for each table. have a sql string which has the sql and pass it to procedure which uses dynamic sql to execute and return any exceptions. as we have exception handling only once in procedure and not for every sql so it reduces the number of lines.

this is how the pseudo code looks like

--using STATIC SQL
PROCEDURE A
BEGIN
BEGIN
Insert stmt ..
EXCEPTION
END ;
END ;

PROCEDURE B
BEGIN
BEGIN
Insert stmt ..
EXCEPTION
END ;
END ;

--using DYNAMIC SQL
PROCEDURE A
sqlString varchar2(400):= Insert Stmt ..
BEGIN
dynamic_insert(sqlString,errorCode);
END ;

PROCEDURE B
sqlString varchar2(400):= Insert Stmt ..
BEGIN
dynamic_insert(sqlString,errorCode);
END ;

PROCEDURE dynamic_insert(
sqlString IN VARCHAR2,
errorCode OUT NUMBER
)

BEGIN
execute immediate sqlString ..
EXCEPTION
WHEN ..
raise exception ..
END ;

Hi tom,

I want to know if we can store unprintable characters,for example "/0",into oracle and we can execute SQL on unprintable characters successfully?

Thanks,

hlh_yuanxy

Hi Tom.

But in case of this situations:

PROCEDURE LOG_D(msg IN VARCHAR2) IS
PRAGMA autonomous_transaction;
BEGIN
INSERT INTO POST_LOG_D(sqltxt,dat) VALUES(msg,SYSDATE);
COMMIT;
END;

and

PROCEDURE LOG_D(msg IN VARCHAR2) IS
PRAGMA autonomous_transaction;
BEGIN
execute immediate 'INSERT INTO POST_LOG_D(sqltxt,dat) VALUES(:1,:2)' USING msg,SYSDATE;
COMMIT;
END;

You thinking the 1st variant is better?

THANKS

Re: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:864237961410#tom77001927410459

Hi Tom,

My response is to your last follow up on the "Dynamic SQL vs Static SQL" topic on November 19, 2006 - 3pm.

Does your responses regarding static SQL still apply to Oracle 9i?

Oracle documentations at http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg09dyn.htm stated that "you should use dynamic SQL in cases where static SQL does not support the operation you want to perform, or in cases where you do not know the exact SQL statements that must be executed by a PL/SQL procedure. These SQL statements may depend on user input, or they may depend on processing work done by the program. The following sections describe typical situations where you should use dynamic SQL and typical problems that can be solved by using dynamic SQL."

"These SQL statements may depend on user input" statement confuses me. It's making me think that since Jony from UKR's case includes some user input, it is recommended to use the 2nd approach because of its dynamic nature.

In Oracle documentation, it also provides numerous examples of very simple dynamic SQL usage that could have written using static SQL.

Below is the given example that was given in Oracle documentation:

-----------------
For example, the following native dynamic SQL code does not use bind variables:

CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char (my_deptno);
END;
/
SHOW ERRORS;

For each distinct my_deptno variable, a new cursor is created, causing resource contention and poor performance. Instead, bind my_deptno as a bind variable:

CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING my_deptno;
END;


Here, the same cursor is reused for different values of the bind my_deptno, improving performance and scalabilty.

----------------
How would the example above compare to static SQL below?

CREATE OR REPLACE PROCEDURE del_dept (
my_deptno dept.deptno%TYPE) IS
BEGIN
DELETE FROM dept WHERE deptno = my_deptno;
END;


Please help clarify the statement(s) stated in Oracle documentation.

Thank you.

Hi Tom,

I am writing this, as the last review was in 2011. I would like to know if there are any changes/modifications/enhancements happened , till date, to improve the performance of an SQL written through Dynamic sql, which can be very well written as static SQL. Or its the same fundamental point that performance of an say, Insert statement, is faster when written as static sql than execute immediate.

Database is on 19.11.

This is dynamic SQL construct inside the stored procedure.
At the time of the stored procedure call, noticing the binds are not bound and Oracle generates lot of unique statement.

Can you please show how to write this dynamic SQL query with correct placement of binds and values?

Thanks!

/*
* Execute an insert [regular] query.
*/
EXECUTE IMMEDIATE '
DECLARE
sql_qry_nest VARCHAR2(1000);
BEGIN
sql_qry_nest := ''
INSERT INTO '
|| tableName_in
|| ' ( '
|| l_keys || ',' || l_cols
|| ',RECORDED_DT_START, EFFECTIVE_DATE_START, RECORDED_DATE_END, EFFECTIVE_DATE_END )'
|| ' VALUES '
|| '(' || l_all_vals_indx || ')'';
EXECUTE IMMEDIATE sql_qry_nest
USING ' || l_all_vals || ',' || 'sys_extract_utc(SYSTIMESTAMP),TO_TIMESTAMP(''01-JAN-0001 00:00:00:000000'', ''DD-Mon-YYYY HH24:MI:SS:FF''),'
|| 'TO_TIMESTAMP(''31-DEC-9999 00:00:00:000000'', ''DD-Mon-YYYY HH24:MI:SS:FF'') + 0.99999,TO_TIMESTAMP(''31-DEC-9999 00:00:00:000000'', ''DD-Mon-YYYY HH24:MI:SS:FF'') + 0.99999 ;
END;
'
;
dbms_output.put_line('Last insert done');

EXCEPTION WHEN OTHERS THEN
v_error_msg := v_error_msg || ' Errors happened when updating BTG.CALL_SCHED_SM. Please contact DW team.:: Error Code= ' || SQLCODE || ' Error Message= ' || SQLERRM ||chr(13)||chr(10);
END;
/

|| '(' || l_all_vals_indx || ')'';

begin
  execute immediate q'!
  declare 
    stmt clob;
  begin
    stmt := 'insert into ' || :tab || ' values ( ' || :v1 || ' )';
    execute immediate stmt;
  end;!'
  using 'T', 1;
end;
/

declare
  trec t%rowtype;
begin
  trec := init_t ( ... );
  insert into t values trec;
end;
/

Team,

Just would like to point out that, things got changes much better in the recent version of Oracle database.

unlike the earlier version - initial demo - shown by Tom Kyte dont have much parsing for dynamic sql's from pl/sql.

demo@XEPDB1> declare
  2     x number ;
  3  begin
  4     for i in 1..10
  5     loop
  6             execute immediate ' select count(*) from dual d1' into x;
  7             select count(*) into x from dual d2;
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

and Tkprof shows this

select count(*) 
from
 dual d1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0          0          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.03          0          0          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=15 us starts=1)
         1          1          1   FAST DUAL  (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)

********************************************************************************

SQL ID: 1u3xcupjn9tkg Plan Hash: 3910148636

SELECT COUNT(*) 
FROM
 DUAL D2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.04          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0          0          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21      0.00       0.04          0          0          0          10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=11 us starts=1)
         1          1          1   FAST DUAL  (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)