Extremely helpful.
Wonderfuly helpful.
Amazingly helpful.
An overview, 5 coded examples, with runtimes, ...
Concise, brief but not too brief.
Well-formated i.e. readable.

As usual ... Tom (this must be Tom's crew, not just 1 person) .. hits a bulls-eye.

Hi Tom,
This is really an excellent piece to understand and use
bulk variables and arrays. A very educative one.

This example really helped us. Thank you very much

Tom,

You are "THE" person.

Thanks alot.

I was looking for an example to properly use String data in my scripts for a data migration script. The particular problem I was confronted with was using a VARCHAR column in the where clause of my query and the data for this column could contain single and double apostrophes, in which case the script was failing. The information on bind_variable showed here pointed me in the right direction and the examples made the usage cystal clear. Following this, I was able to successfully run my script in the first attempt after the change to using bind_variable.

Brilliant example ... and ... explained

I Have run p4 and, seen tkprof ..here is the info

1)fist time i used sqlplus to execute this p4..it did following...
a)create or replace procedure p4
as
begin
for i in 1 .. 10
loop
execute immediate
'insert into test (eno,ename) values (:x,:y)'
using i,'name ' || i;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.14 0.16 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.14 0.16 0 0 2 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 32
b)BEGIN p4; END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.01 0.03 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.02 0.04 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 32

c)insert into test (eno,ename)
values
(:x,:y)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.01 0.01 0 0 0 0
Execute 10 0.01 0.01 0 10 12 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.02 0.02 0 10 12 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 32 (recursive depth: 1)

Frankly speaking i would like to see part C that too with one parse and 10 execute.Can You tell me what's happening here ???
Regards
Ak

Hi,
I even tried calling this proc p4 from plsql,and it was same
result,please clarify(I even don't want soft parse,because soft parse is less expansive hard parse).I am in a process to change my complete application in order to Use bind variable,we are planning to put all sql statement in a store proc and call store proc from front end...because vb doesn't seems to doing
what we want ,and our vb programmer have given Up.Please guide me.
Thanks
Ak




Thanks,
Ak

Hi,
IN addition to the previous two comments...Please tell me
what is best way to insert,select,delete a record thro
stored proc(we will be passing the parameter at run time).
And this proc will be called again and again simultanuosly
from diff session(diff workstation).Please give me One complete example.

Thanks,
Ak

Isn't that the part that pl/sql optimiser supposed to do? For example, moving expensive expression outside the loop, transforming P1 into P2 and P4 into P5? That's what other procedural language optimizers routinely do (admittedly, for simpler, expressions, though). After all, if case P4 is easiest to write, why deprive user of doing so?

Hi tom,
I did a test
create or replace procedure p3(first number,sec char)is
begin
for i in 1..10 loop
insert into test values(first,sec);
commit;
end loop;
end;
sql>exec proc(1,'aa');

Tkprof was showing perfactly ok..but every time i execute p3 i see
begin p3(:b1,:b2) end;----this parse and execute 1 times each..why ?? first time ok...after that it shouldn't ??
Isn't ???

please clarify.

Thanks,
Ak

Hi tom,
In addition to previous question , I want to do following..
create or replace procedure try
as
t1 varchar2(10);
begin
t1:='Good';
execute immediate 'create table :x(eno number)' using t1;
end;
Meaning ,Table name i want to Generate at run time...can You give me One example.

Thanks,
regards
Ak

Which is "THE" best method among five of above?

TOM!!!! Is the best

Can you give a realistic example when insert performed in a loop can't be reduced to static sql? I mean, if sql is dynamic, the user probably wants to build the statement by concatenation, if it's static, then he uses binds. Why are we discussing "the middle ground" case; it doesn't exist!?

Hi Tom,

You could skip the "execute immediate"

create or replace procedure p5
as
begin
for i in 1 .. 1000
loop
state_pkg.g_x(i) := i;
state_pkg.g_y(i) := sysdate+i;
state_pkg.g_z(i) := 'this is row ' || i;
end loop;

-- 1..1000 or 1..state_pkg.g_x.COUNT or
forall i in state_pkg.g_x.FIRST..state_pkg.g_x.LAST
insert into t (x,
y,
z)
values (state_pkg.g_x(i),
state_pkg.g_y(i),
state_pkg.g_z(i));
end;
/

Your examples are just excellent and useful. Good work again...

I have often coded similar to p1. How can I adapt your example of p2 below. The 'from' clause has to be dyanmically built based on the cursor loop - I need to select data from different user's table depending on the actual user.

FOR rec IN curGeneralInvoice (ld_StartDate) LOOP
BEGIN
-- open cursor for processing
ln_CursorId := DBMS_SQL.OPEN_CURSOR;

-- Identify statement to process

ls_SelectStmt := 'SELECT dlcus_code2
,dlcus_area
,dlcus_code7
...
FROM '||rec.soh_company||'.dlcust@stx_york
WHERE dlcus_customer = :CustAcct';

-- parse the statement
DBMS_SQL.PARSE (ln_CursorId, ls_SelectStmt, DBMS_SQL.V7);

-- bind variables in the statement
DBMS_SQL.BIND_VARIABLE (ln_CursorId, ':CustAcct', rec.soh_account);

-- define the output variables
DBMS_SQL.DEFINE_COLUMN (ln_CursorId, 1,
... DBMS_SQL.DEFINE_COLUMN (ln_CursorId, 8, ls_code7 ,4);

-- execute the insert statement
ln_dummy := DBMS_SQL.EXECUTE (ln_CursorId);

-- determine number of rows fetched
ln_rows := DBMS_SQL.FETCH_ROWS (ln_CursorId);
.....

If i know table name to be inserted and the col name and values in the pl/sql procedure and if i want to insert 3000 rows in the loop which one is the best.
i.e

Insert into table High_yield values(var1,Var2) [STATIC SQL]


or DBMS_SQL.Parse
DBMS_SQL.Bind(Var1,Col1)
DBMS_SQL.Bind(Var2,Col2)





Thx
Ashok

Hi Tom,

I have a vary big table having 10 Mil rows

i have uri which contains uri and query in the uri field

so I wrote a script
as follows

/*************************************/
Table name : web_logs100

file_name varcahr2(2000),
uri varcahr2(2000),
query varcahr2(2000),
ip varcahr2(15)

There is no primiary key or any other indexes
on this table
/*************************************/

/*********************************************/
create or replace
procedure get_id_info_1 is

cursor uriCur is
select rowid,uri from web_logs100
where uri like '/view%/%';
check_name_id number;
check_name_id1 number;

Begin
FOR uriRec in uriCur
loop
exit when uriCur%NOTFOUND;
/********** seperation *************************/
check_name_id := instr(uriRec.uri,'/',-1);
Begin
update web_logs100
set uri = substr(:x,1,:z),
query = substr(:x,:z+1) WHERE rowid LIKE :y'

commit;
Exception
when others then
dbms_output.put_line('Error : '|| sqlerrm);
dbms_output.put_line('URI :'|| uriRec.uri);

End;
end loop;
end get_id_info_1;
/**********************************************/

It took two days already. will dynamic sql do it faster ?
can you tell me how to do that ?


thanks,
vivek

Hi Tom,
Sorry for above update statement !!

here is the correct one
so I wrote a script
as follows

/*************************************/
Table name : web_logs100

file_name varcahr2(2000),
uri varcahr2(2000),
query varcahr2(2000),
ip varcahr2(15)

There is no primiary key or any other indexes
on this table
/*************************************/

/*********************************************/
create or replace
procedure get_id_info_1 is

cursor uriCur is
select rowid,uri from web_logs100
where uri like '/view%/%';
check_name_id number;
check_name_id1 number;

Begin
FOR uriRec in uriCur
loop
exit when uriCur%NOTFOUND;
/********** seperation *************************/
check_name_id := instr(uriRec.uri,'/',-1);
Begin

update web_logs100
set uri = substr(uriRec.uri,1,check_name_id),
query = substr(uriRec.uri,check_name_id+1)
WHERE rowid = uriRec.rowid;
commit;

Exception
when others then
dbms_output.put_line('Error : '|| sqlerrm);
dbms_output.put_line('URI :'|| uriRec.uri);

End;
end loop;
end get_id_info_1;
/**********************************************/

It took two days already. will dynamic sql do it faster ?
can you tell me how to do that ?


thanks,
vivek

Thanks,
I was just trying to use dynamic sql here.
Why will the dynamic sql not help here ?

Can you please tell ?

will the statement get parsed only once here ?

Thanks,

Thats what I was looking! You are impressive

Thanks tom,

Now my table web_logs1000 is get loaded by a procedure and then authomatically it should populate all dimention tables and fact every night. so I have to have some thing I can call (like function or porcedure) this is not one time process.

on the other hand if I issue just update statement
and in the middle If I had to kill the session or something
I think I will loose all processed rows that is why commit is there ? what do you say ?

so how will I execute only update statement only ?

you are right !!

1.) I can eleminate those rows by adding one predicate to the update statement ie.
/*******************************************/
update web_logs
set uri = substr( uri, 1, instr(uri,'/',-1)),
query = substr(uri,instr(uri,'/',-1)+1)
where uri like '/view%/%'
and query is null;
/****************************************/

Also this table is tamporary table so as soon as I do
a little porcessing on it and move data to other tables
this table gets truncated and new sever logs is get loaded
(there are 4 servers -- I process 1 server logs at a time
and has 1 mil rows)

But still your suggestion is far batter and I have changed
it to simple update statement !!
one more thin here, so when do we use a static cursor and
when the dynamic cursor with the bind variable ?

because if I say
/*******************************************/
declare
cursor empcur is
select empid,empname,deptcd;

/***************************************/
--case 1
begin
for empRec in empCur
execute immidiate
'insert into emp1(:x,:y,:z)'
using emprec.empid,emprec.ename,emprec.deptcd;
end loop;

end;
/****************************************/
--case 2
begin
for empRec in empCur
insert into emp1
(emprec.empid,emprec.ename,emprec.deptcd);
end loop;
end;
/**************************************/

what is batter ?

Hi Tom,

I am tring to insert records into the same table as a part of SELECT. The table has 1.3 million records

SQL> select count(*) from scenarioadjustment;

  COUNT(*)
----------
   1384637

Here is the code.

DECLARE
  endnum                   NUMBER;
  c_quantity        CONSTANT INTEGER := 1000;
BEGIN
   
   -- Fill the scenarioadjustment table

   SELECT nvl((max(laneid)-1)/c_quantity,-1) 
     INTO endnum 
     FROM lane
    WHERE rfpid = 1148;

   FOR timethrough in 0..endnum

   LOOP

     INSERT INTO scenarioadjustment 
       ( laneequipmenttypeid, obcarriercodeid, scenarioid, packageid, rfpid, laneid,
     equipmenttypeid, servicetype, protectionlevel, incumbent,
     inboundfacilitycode, outboundfacilitycode, costperload, globaladjpct, facilityadjpct,
     laneadjpct, carrieradjpct, manualawardamount, autoawardamount,
     packageawardamount, historicalcost, needsautoaward, lanevolume,
     capacity, createddttm, numdays
       )
    SELECT * FROM
         (
        SELECT laneequipmenttypeid, obcarriercodeid, 8, packageid, rfpid, laneid,
           equipmenttypeid, servicetype, protectionlevel, incumbent,
           inboundfacilitycode, outboundfacilitycode, costperload, globaladjpct, facilityadjpct,
           laneadjpct, carrieradjpct, manualawardamount, autoawardamount,
           packageawardamount, historicalcost, needsautoaward, lanevolume,
           capacity, createddttm, numdays
          FROM scenarioadjustment
         WHERE rfpid = 1148
               AND scenarioid = 0
               AND laneid >= timethrough * c_quantity + 1 
               AND laneid <= timethrough * c_quantity + c_quantity
              
           ORDER BY RFPID, SCENARIOID, LANEID, LANEEQUIPMENTTYPEID,
                       OBCARRIERCODEID, PACKAGEID
     );
    
   END LOOP;

   COMMIT;

END;
/ 

Attached is the tkprof output 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2     28.30      56.93          0     231533     249943      225000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     28.30      56.93          0     231533     249943      225000

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 86     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
 225002  VIEW
 225002   TABLE ACCESS BY INDEX ROWID SCENARIOADJUSTMENT
 225002    INDEX RANGE SCAN (object id 77405)

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

I tried implementing the code with dynamic SQL as array insert and the code is much slower ??


set serveroutput on size 20000
-- Tring the insert as an array insert to improve the performance

DECLARE

v_batch_size             CONSTANT INTEGER :=1000;

v_LANEEQUIPMENTTYPEID    DBMS_SQL.NUMBER_TABLE;
v_OBCARRIERCODEID        DBMS_SQL.NUMBER_TABLE;
v_SCENARIOID             DBMS_SQL.NUMBER_TABLE;
v_RFPID                  DBMS_SQL.NUMBER_TABLE;
v_LANEID                 DBMS_SQL.NUMBER_TABLE;
v_PACKAGEID              DBMS_SQL.NUMBER_TABLE;
v_NEEDSAUTOAWARD         DBMS_SQL.NUMBER_TABLE;

v_cursor1                INTEGER;
v_cursor2                INTEGER;
v_return_code            INTEGER;
v_num_rows               INTEGER;
v_init                   INTEGER :=0;

v_insert_stmt            VARCHAR2(2000);
v_select_stmt            VARCHAR2(2000);

BEGIN

    v_cursor1 := DBMS_SQL.OPEN_CURSOR;
    v_cursor2 := DBMS_SQL.OPEN_CURSOR;

    v_select_stmt := 'SELECT laneequipmenttypeid, obcarriercodeid, 8, rfpid, laneid, packageid, needsautoaward ';
    v_select_stmt := v_select_stmt || ' FROM scenarioadjustment WHERE rfpid = 1148 AND scenarioid = 0 ';

    v_select_stmt := v_select_stmt || ' ORDER BY RFPID, SCENARIOID, LANEID, LANEEQUIPMENTTYPEID, OBCARRIERCODEID, PACKAGEID';

    v_insert_stmt := 'INSERT INTO scenarioadjustment (laneequipmenttypeid, obcarriercodeid, scenarioid, rfpid, laneid, packageid, needsautoaward ) ';
    v_insert_stmt := v_insert_stmt ||  ' values ( :laneequipmenttypeid, :obcarriercodeid, :scenarioid,  :rfpid, :laneid, :packageid, :needsautoaward ) ';

    DBMS_SQL.PARSE(v_cursor1,  v_select_stmt, dbms_sql.native);
    DBMS_SQL.PARSE(v_cursor2,  v_insert_stmt, dbms_sql.native);

   -- Use DEFINE_ARRAY to specify the output variables for the Select

   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 1, v_LANEEQUIPMENTTYPEID, v_batch_size, 1);
   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 2, v_OBCARRIERCODEID, v_batch_size, 1);
   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 3, v_SCENARIOID, v_batch_size, 1);
   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 4, v_RFPID, v_batch_size, 1);
   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 5, v_LANEID, v_batch_size, 1);
   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 6, v_PACKAGEID, v_batch_size, 1);
   DBMS_SQL.DEFINE_ARRAY (v_cursor1, 7, v_NEEDSAUTOAWARD, v_batch_size, 1);

   -- Execute the select statement

   v_return_code := DBMS_SQL.EXECUTE(v_cursor1);

   -- DBMS_OUTPUT.PUT_LINE(' Return Code '  || TO_NUMBER(v_return_code));

   
   LOOP

      v_num_rows  := DBMS_SQL.FETCH_ROWS(v_cursor1);

     -- DBMS_OUTPUT.PUT_LINE('No. of rows Fetched '  || TO_NUMBER(v_num_rows));

     DBMS_SQL.COLUMN_VALUE(v_cursor1, 1, V_LANEEQUIPMENTTYPEID);
     DBMS_SQL.COLUMN_VALUE(v_cursor1, 2, v_OBCARRIERCODEID);
     DBMS_SQL.COLUMN_VALUE(v_cursor1, 3, v_SCENARIOID);
     DBMS_SQL.COLUMN_VALUE(v_cursor1, 4, v_RFPID);
     DBMS_SQL.COLUMN_VALUE(v_cursor1, 5, v_LANEID);
     DBMS_SQL.COLUMN_VALUE(v_cursor1, 6, v_PACKAGEID);
     DBMS_SQL.COLUMN_VALUE(v_cursor1, 7, v_NEEDSAUTOAWARD);

    IF v_num_rows = 0 THEN
       EXIT;
    END IF;

    -- Use Bind Array to specify the input variables for insert

    DBMS_SQL.BIND_ARRAY(v_cursor2, ':laneequipmenttypeid', v_laneequipmenttypeid, v_init + 1, v_init + v_Num_Rows );
    DBMS_SQL.BIND_ARRAY(v_cursor2, ':obcarriercodeid', v_obcarriercodeid, v_init + 1, v_init + v_Num_Rows );
    DBMS_SQL.BIND_ARRAY(v_cursor2, ':scenarioid', v_scenarioid, v_init + 1, v_init + v_Num_Rows );
    DBMS_SQL.BIND_ARRAY(v_cursor2, ':rfpid', v_rfpid, v_init + 1, v_init + v_Num_Rows );
    DBMS_SQL.BIND_ARRAY(v_cursor2, ':laneid', v_laneid, v_init + 1, v_init + v_Num_Rows );
    DBMS_SQL.BIND_ARRAY(v_cursor2, ':packageid', v_packageid, v_init + 1, v_init + v_Num_Rows );
    DBMS_SQL.BIND_ARRAY(v_cursor2, ':needsautoaward', v_needsautoaward, v_init + 1, v_init + v_Num_Rows );

    v_init := v_init + v_Num_Rows;

    -- Execute the insert statement

    v_return_code := DBMS_SQL.EXECUTE(v_cursor2);

    EXIT WHEN v_num_rows < v_batch_size;

  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(v_cursor1);
  DBMS_SQL.CLOSE_CURSOR(v_cursor2);

END;
/

Please suggest 

Tom,
This is excellent. I am using Oracle 9i(9.0.1.1.1), I can use the same procedures. But, is there any way that I could enhance with 9i PL/SQL new features.

I attended your seminar at AOTC it was really good.

Thanks,

Geet

Yes, as a matter of fact, there is a new way to use native dynamic sql with bulk operations:

ops$tkyte@ORA9I.WORLD> create table t ( x int );

Table created.

ops$tkyte@ORA9I.WORLD> 
ops$tkyte@ORA9I.WORLD> declare
  2          type array is table of number index by binary_integer;
  3  
  4          l_data array;
  5  begin
  6     for i in 1 .. 100
  7     loop
  8        l_data(i) := i;
  9     end loop;
 10  
 11     forall i in 1..l_data.count
 12        execute immediate 'insert into t (x) values( :x)' using l_data(i);
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9I.WORLD> select count(*) from t;

  COUNT(*)
----------
       100

ops$tkyte@ORA9I.WORLD> 


thanks for the feedback -- it was fun to be able to do a non "sales" presentation for once ;)

 

Thanks Tom,

I tried inserting as a part of select and it took 51 seconds as compared to 56 before.

Explain Plan looks like:-

INSERT INTO scenarioadjustment
( laneequipmenttypeid, obcarriercodeid, scenarioid, packageid, rfpid, laneid,
equipmenttypeid, servicetype, protectionlevel, incumbent,
inboundfacilitycode, outboundfacilitycode, costperload, globaladjpct, facilityadjpct,
laneadjpct, carrieradjpct, manualawardamount, autoawardamount,
packageawardamount, historicalcost, needsautoaward, lanevolume,
capacity, createddttm, numdays
)
SELECT * FROM
(
SELECT laneequipmenttypeid, obcarriercodeid, 8, packageid, rfpid, laneid,
equipmenttypeid, servicetype, protectionlevel, incumbent,
inboundfacilitycode, outboundfacilitycode, costperload, globaladjpct, facilityadjpct,
laneadjpct, carrieradjpct, manualawardamount, autoawardamount,
packageawardamount, historicalcost, needsautoaward, lanevolume,
capacity, createddttm, numdays
FROM scenarioadjustment
WHERE rfpid = 1148
AND scenarioid = 0
ORDER BY RFPID, SCENARIOID, LANEID, LANEEQUIPMENTTYPEID, OBCARRIERCODEID, PACKAGEID
)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 24.24 51.94 0 231523 249992 225000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 24.24 51.94 0 231523 249992 225000

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 86

Rows Row Source Operation
------- ---------------------------------------------------
225001 VIEW
225001 TABLE ACCESS BY INDEX ROWID SCENARIOADJUSTMENT
225001 INDEX RANGE SCAN (object id 77405)

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


Is there any other way to make it run much faster?? It is large table with more than million records.

Seg Info:-

SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------------- ---------- ---------- ---------- -------------- ----------
MIN_EXTENTS MAX_EXTENTS PCT_INCREASE BUFFER_
----------- ----------- ------------ -------
SCENARIOADJUSTMENT OBLBNT 197656576 24128 377 80216064 524288
1 2147483645 0 DEFAULT

PCT_FREE: 10
PCT_USED: 40
EXTENT_MANAGEMENT: LOCAL

Please suggest!

I have to populate a cursor (afield_c) with three variables which is giving me headaches. As you can see from my code below I have tried to call afield_c and failed and also tried to create a select statement with execute immediate but it failed due to the INTO phrase.



DECLARE

v_field VARCHAR2(254);

FUNCTION update_fields (p_keyid crm.keyid%TYPE,
p_source_name crm.source_name%TYPE,
p_field VARCHAR2 )
RETURN VARCHAR2 IS

CURSOR afield_c
IS
SELECT RTRIM(LTRIM(CHR(39) || p_field || CHR(39))) as q_field
FROM crm
WHERE RTRIM(LTRIM(keyid)) = p_keyid
AND source_name = p_source_name
AND p_field IS NOT NULL;

v_return VARCHAR2(254);
v_vfield VARCHAR2(254);
v_phrase VARCHAR2(254);
BEGIN

-- v_phrase := ' SELECT RTRIM(LTRIM(' || p_field ||
-- ')) INTO v_vfield FROM crm WHERE keyid = ' || p_keyid ||
-- ' AND source_name = ' || CHR(39) || p_source_name || CHR(39) || ' AND ' || p_field || ' IS NOT NULL' ;


v_phrase := ' SELECT RTRIM(LTRIM(' || p_field ||
')) FROM crm WHERE keyid = ' || p_keyid ||
' AND source_name = ' || CHR(39) || p_source_name || CHR(39) || ' AND ' || p_field || ' IS NOT NULL' ;
dbms_output.put_line(v_phrase);

EXECUTE IMMEDIATE (v_phrase);

dbms_output.put_line('done');
/*
v_field := NULL;

FOR recfield in afield_c LOOP
dbms_output.put_line(recfield.q_field);
v_return := recfield.q_field;
END LOOP;
*/
dbms_output.put_line(v_vfield);
RETURN v_vfield;
END update_fields;

FUNCTION call_update_fields (p_keyid crm.keyid%TYPE)
RETURN VARCHAR2 IS
v_return2 crm.aname%TYPE;
BEGIN

IF update_fields(p_keyid,'PMD','ANAME') IS NULL THEN
IF update_fields(p_keyid,'Segmentation','ANAME') IS NULL THEN
IF update_fields(p_keyid,'ETI','ANAME') IS NULL THEN
IF update_fields(p_keyid,'QQ','ANAME') IS NULL THEN
IF update_fields(p_keyid,'ESP','ANAME') IS NULL THEN
IF update_fields(p_keyid,'Asia','ANAME') IS NULL THEN
NULL;
ELSE v_return2 := update_fields(p_keyid,'Asia','ANAME');
END IF;
ELSE v_return2 := update_fields(p_keyid,'ESP','ANAME');
END IF;
ELSE v_return2 := update_fields(p_keyid,'QQ','ANAME');
END IF;
ELSE v_return2 := update_fields(p_keyid,'ETI','ANAME');
END IF;
ELSE v_return2 := update_fields(p_keyid,'Segmentation','ANAME');
END IF;
ELSE v_return2 := update_fields(p_keyid,'PMD','ANAME');
END IF;

RETURN v_return2;
END call_update_fields;

BEGIN

v_field := call_update_fields(381);
dbms_output.put_line(v_field);

END;
/


Thanks as always,

Ivan

Umm, since it just appears you want the first ANAME for a key ordered by source_name in a special way -- why do you not just code:

is
   l_return varchar2(255);
begin
   for x in ( select *
                from crm
               WHERE keyid = p_keyid
                 and source_name in 
                 ( 'Asia', 'ESP', 'QQ', 'ETI', 'Segmentation', 'PMD' )
               order by decode( sournce_name, 'PMD', 1, 'Segmentation', 2
                                'ETI', 3', 'QQ', 4, 'ESP' 5, 'Asia', 6 )
             )
   loop
       l_return := x.aname;
       exit;
   end loop;
   return l_return;
end;
    
   
but the execute immediate would be something like:

   execute immediate l_query INTO l_field using bind var, bind var;

USING BIND VARIABLES!!!  something like this:

ops$tkyte@ORA920> declare
  2          l_query long := 'select * from dual where 1 = :key and 2 = :source';
  3          l_key   number default 1;
  4          l_source number default 2;
  5          l_val    varchar2(25);
  6  begin
  7          execute immediate l_query
  8            INTO l_val
  9            USING l_key, l_source ;
 10
 11          dbms_output.put_line( 'code for rows, val = ' || l_val );
 12  exception
 13          when no_data_found then
 14                  dbms_output.put_line( 'code for no rows' );
 15  end;
 16  /
code for rows, val = X

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> declare
  2          l_query long := 'select * from dual where 1 = :key and 2 = :source';
  3          l_key   number default 1;
  4          l_source number default 1;
  5          l_val    varchar2(25);
  6  begin
  7          execute immediate l_query
  8            INTO l_val
  9            USING l_key, l_source ;
 10
 11          dbms_output.put_line( 'code for rows, val = ' || l_val );
 12  exception
 13          when no_data_found then
 14                  dbms_output.put_line( 'code for no rows' );
 15  end;
 16  /
code for no rows

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>

 

In my example above the aname was just one of several fields that I needed to update. My question should have been more explicit in asking how I could code one function to take the arguments and ordering of several fields not just aname. I do not want to create 12 of the same sort of functions that differ by just field name and different order that I need the data sorted.

I like the decode method (much easier code then mine). So would I just send three bind variables for the field name, in clause and the decode clause?

I have been wondering if you know of any programming courses that teach the "Advanced best practices of programming"? My company allows me to buy books (I have yours) but the programming classes seem to be to basic. Maybe you should run a boot camp for programmers (just a thought).

Thanks as always,

Ivan

Hi Tom,

I have a requirement of selecting some rows from a table in insert and update another table.

I could have done insert by using following dynamically created string.

INSERT /*+ APPEND */ INTO ACCOUNT_TYPES(ACCOUNT_TYPE,ATYP_NAME) SELECT NAME,DATA_TYPE_ID FROM CONV_DATA_TYPES

This string is dynamically created I don’t know the number of columns also in the string. So I have to use dbms_sql for executing this.

But I want to perform it procedurally with pl/sql by opening a cursor from the source table ie : SELECT NAME,DATA_TYPE_ID FROM CONV_DATA_TYPES
And then dynamically execute
INSERT INTO ACCOUNT_TYPES(ACCOUNT_TYPE,ATYP_NAME)
VALUES (:bv,bv1);

as the columns are not known but the number of col will be always equal to number of col in select.

This way if my insert statement fail for one row I can log the error into another table like rowid , reason..for failing and then continuing the process further rows. Because once I start this process of insert I want to complete it for all the selected rows.

Same way update also like I don’t want to execute the

UPDATE ACCOUNT_TYPES SET (ACCOUNT_TYPE,ATYP_NAME) = (SELECT DATA_TYPE_ID,NAME FROM CONV_DATA_TYPES,ACCOUNT_TYPES WHERE = DATA_TYPE_ID) WHERE EXISTS (SELECT DATA_TYPE_ID,NAME FROM CONV_DATA_TYPES,ACCOUNT_TYPES WHERE = DATA_TYPE_ID)

I want to process every row and log the rowid into a log table with reason for fail.

I have to use dbms_sql for both operations. As my select col list in not known.

I am giving you the psudo code

Declare
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_array dbms_sql.Varchar2_Table;
v_values varchar2(3000);
cur integer default dbms_sql.open_cursor;
BEGIN
dbms_sql.parse(l_theCursor,dynamic_select_string,dbms_sql.native);
dbms_sql.describe_columns (l_theCursor,l_colCnt,l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i,l_columnValue, 4000 ); -- here I want to fetch the rows in bulk say
--(1000) at a time how to do it by using define_array?
dbms_sql.define_array(l_theCursor, i,l_array,1000, 1);
dbms_sql.column_value(l_theCursor, i,l_array );
end loop;
l_status := dbms_sql.execute_and_fetch(l_theCursor);
--** now open the insert cursor for inserting into target table how to move further? **** i am stuck here.

But I am doing like:

create or replace procedure prc_temp(p_insert_statment varchar2) IS
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_array dbms_sql.Varchar2_Table;
v_values varchar2(3000);
l_cur INTEGER;
ins_cur integer default dbms_sql.open_cursor;
v_insert_statment VARCHAR(32000);
BEGIN
dbms_sql.parse(l_theCursor,'SELECt v1,n1 from temp_b',dbms_sql.native);
dbms_sql.describe_columns (l_theCursor,l_colCnt,l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i,l_columnValue, 4000 );
end loop;
l_status := dbms_sql.execute(l_theCursor);
WHILE ( dbms_sql.fetch_rows(l_theCursor) > 0 ) LOOP
FOR i IN 1 .. l_colCnt LOOP
dbms_sql.column_value(l_theCursor, i, l_columnValue );
v_values := v_values ||l_columnValue||',';
END LOOP;
v_values := substr(v_values,0,instr(v_values,',',-1)-1);
dbms_output.put_line(v_Values);
-- Here i can open the insert cursor after creating a insert string like
v_insert_statment:= p_insert_statment||'values('||v_values||')';
v_insert_statment:= 'insert into temp_a values('||v_values||')';
begin
dbms_sql.parse(ins_cur,v_insert_statment,dbms_sql.native);
l_cur := dbms_sql.execute(ins_cur);
EXCEPTION
when others then
-- insert into log table.....
null;
END;
v_Values := null;
end loop;
dbms_output.put_line(v_insert_statment);
DBMS_SQL.CLOSE_CURSOR(ins_cur);
DBMS_SQL.CLOSE_CURSOR(l_theCursor);
END;

Assume the insert and the select string are dynamically created.
Pl suggest if there is any better way of doing it.

Regards,

Vikas Sharma

Hi Tom,

Here is my problem:
I have loaders running against a database changing only certain fields. In order to use streams (thanks for the hint) I need to update only those fields that have changes. This means that my update statement will change per company depending on if the data already exists in the table.

How do I do an dynamic SQL statement that changes the fields that need to be updated and still pass in the values?
Here is an example of just two of the 14 fields that need to be used to construct the statement and the using clause.

-- address_1 data
IF v_address_1_check THEN
v_update_company_record := TRUE;
IF RecExist.address_1 <> v_address_1 THEN
v_sql_statement := v_sql_statement || ' co.address_1 = :a ,co.address_1_lupd = :aa,co.address_1_upd = :aaa ';
v_using_statement := v_using_statement || v_address_1 || ',' || SYSDATE || ',' || USER || ',' ;
ELSE
v_sql_statement := v_sql_statement || ' co.address_1_lupd =:aa, co.address_1_upd = :aaa ';
v_using_statement := v_using_statement || ',' || SYSDATE || ',' || USER || ',' ;
END IF;
END IF;

-- address_2 data
IF v_address_2_check THEN
v_update_company_record := TRUE;
IF RecExist.address_2 <> v_address_2 THEN
v_sql_statement := v_sql_statement || ' co.address_2 = :b ,co.address_2_lupd = :bb, co.address_2_upd = :bbb ';
v_using_statement := v_using_statement || v_address_2 || ',' || SYSDATE || ',' || USER || ',' ;
ELSE
v_sql_statement := v_sql_statement || ' co.address_2_lupd =:bb, co.address_2_upd = :bbb ';
v_using_statement := v_using_statement || ',' || SYSDATE || ',' || USER || ',' ;
END IF;
END IF;

Thanks as always,
Ivan

How could I use static sql when I have to build a update statement for each record and the update statement can change per record.

Example:
If the address_1 exists then I only need to update the date and user while if it doesnt exist then I have to update the actual address_1 field, the date and the user. Times this by 14 fields and the update statement is going to be different for each record. For just two of the 14 fields here would be the possible statements:

-- if address_1 and address_2 are new
update table
set address_1 = v_address_1,
address_1_date = v_address_date,
address_1_user = v_address_1_user,
address_2 = v_address_2,
address_2_date = v_address_2_date,
address_2_user = v_address_2_user
where rowid = 'ABC';

or it could be

-- if address_1 and address_2 already exist
update table
set address_1_date = v_address_date,
address_1_user = v_address_1_user,
address_2_date = v_address_2_date,
address_2_user = v_address_2_user
where rowid = 'ABC';

or

-- address_1 is new and address_2 is new
update table
set address_1 = v_address_1,
address_1_date = v_address_date,
address_1_user = v_address_1_user,
address_2_date = v_address_2_date,
address_2_user = v_address_2_user
where rowid = 'ABC';

etc....

The end result will be one update statement for each record instead of 14 updates to one record.

Thanks,
Ivan

Very helpful. Just what i was looking for!!

But I am facing a dilemma. Query performance is my one and only criteria (never mind the complexity of the writing of code). So how do I decide between using DBMS_SQL or EXECUTE IMMEDIATE? I am doing an insert into...select. The query is built dynamically. The data volume is quite large and indefinite. The result may procedure anywhere from hundreds or thousands to millions of rows. So from a purely performance point of view which would be a better option?

I am also in some places doing a bulk collect into index by tables followed by a bulk insert/update. Is it wise to store so much data in the arrays given the volume of data I described above? Should a LIMIT be placed on the bulk collect? If yes, then how do we decide on the optimal limit to place on the collects?

Also as all my sql's are dynamically built (the table names and column names are variable), while doing a bulk collect I have had to use static packages with EXECUTE IMMEDIATE (workaround you described in another discussion - I am using oracle 8.1.7). Will this incur any overhead? Or will it be better to use DBMS_SQL in such cases. Again performance is the only issue and data volume is high.

The application performance is giving me headaches. Please help!!!

Tom,

I posted here because my question seems closer to op's question.

we have an performance issue running this query...

declare
l_inserted number;
l_ins_stmt varchar2(4000);
l_sql_stmt varchar2(4000);
l_date_string varchar2(10) := null;
begin
l_ins_stmt := '
INSERT /*+ APPEND */ INTO d14248_42_y1
SELECT * FROM stg_42_29 stg
WHERE NOT EXISTS (SELECT 1
FROM d14248_42_y1 prd
WHERE prd.Z_DOC_ID = stg.Z_DOC_ID)
AND rownum <= 5120
;';

l_sql_stmt := '
declare
l_counter number := 1;
l_row_count number := 0;
begin
while (l_counter > 0)
loop
' || l_ins_stmt || '
l_counter := sql%rowcount;
l_row_count := l_row_count + l_counter;
commit;
end loop;
:row_processed := l_row_count;
end;
';
EXECUTE IMMEDIATE l_sql_stmt using out l_inserted;
dbms_output.put_line(l_inserted);
end;
/

To give you a little bit information, using 8.1.7. Staging and production table has index on Z_DOC_ID columns.

it takes approximately 6 minutes to load 15000 rows to move data from staging table to production. when i run the same insert statement alone, it takes only 2-3 seconds. That seems to be a huge difference. Is there a way to tune this query or any workaround would be appreciated.

thanks,
senthil

you got me there Tom, i should've mentioned that in the first place. This begin..end block is an excerpt from one of our stored procedures, got generated on the fly(dont ask me why we even do that). we know the staging and production table name and the structure when we call this procedure. so, we are forced to use 'execute immediate', unless we have missed anything. fortunately we found this in our regression test.

thanks,
senthil

Tom, you are a machine!

My requirement ins like below:

FOR DAY IN (sun, mon, tue, wed, thu, fri, sat )
RUN THE FOLLOWING DYNAMIC SQL (replacing DAY WITH sun, mon, tue, etc) TO populate THE activities TABLE.

INSERT INTO activities (ein, data_source, tsdate,activity, HOURS)
SELECT SUBSTR(trim(raw_ein),-9,9), data_source,
TO_DATE(SUBSTR(day_date,-8,8),'dd/mm/yy') tsdate,
day_activity activity,
SUM(MOD(SUBSTR(day_end,1,2)-SUBSTR(day_start,1,2)+24,24)*3600
+(SUBSTR(day_end,4,2)-SUBSTR(day_start,4,2))*60) HOURS
FROM DATA_A
GROUP BY raw_ein, data_source, day_date, day_activity

issue a COMMIT

Now this insert will take plaace only once for a day i.e. once for sun then once for sat..etc..
I'm thinking of using Execute Immediate for this, is it Ok?

I've tried this...Please give me your comments..
----------------------------------------------------
DECLARE
TYPE day_table IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
days day_table;
x VARCHAR2(3);
stmt VARCHAR2(4000);
BEGIN
FOR i IN 1..7
LOOP
days(i):=TO_CHAR(SYSDATE-i,'dy');
END LOOP;
FOR i IN 1..DAYs.COUNT
LOOP
stmt:= 'INSERT INTO activities (ein, data_source, tsdate,activity, HOURS)
SELECT SUBSTR(trim(raw_ein),-9,9), NVL(data_source,''NULL''), TO_DATE(SUBSTR('||days(i)||'_date,-8,8),''dd/mm/yy'') tsdate, '||days(i)||'_activity activity,
SUM(MOD(SUBSTR('||days(i)||'_end,1,2)-SUBSTR('||days(i)||'_start,1,2)+24,24)*3600 +(SUBSTR('||days(i)||'_end,4,2)-SUBSTR('||days(i)||'_start,4,2))*60) HOURS
FROM data_A p WHERE '||days(i)||'_start IS NOT NULL AND '||days(i)||'_end IS NOT NULL GROUP BY raw_ein, data_source, '||days(i)||'_date, '||days(i)||'_activity';
EXECUTE IMMEDIATE stmt ;
END LOOP;
END;

"P4 -- so easy to code, uses bind variables -- pretty efficient.
Unfortunately, internally its a little like P1. The open, parse, close calls are "

Why does it parse every time, if the variables are bound and the statement doesnt change(like the insert in your example) then the execution plan is already available, can't the engine do the trivial check (shared pool)

Hi tom,

I am trying to implement the following using your p5. Any ideas I have tried many times. Please help.

FUNCTION ADD_NEW_SAMPLE_COMPONENT(P_COMPONENT IN TP_COMPONENT_ARR,
P_NUMBER_OF_PREPS IN VARCHAR2)
RETURN VARCHAR2
IS
V_COUNTER VARCHAR2(10);
STEP_NA CONSTANT BOOLEAN DEFAULT P_COMPONENT(1).na_option='Y';

COMPONENT_fk_id varchar_arr:=varchar_arr();
id varchar_arr:=varchar_arr();
date_created varchar_arr:=varchar_arr();
created_by varchar_arr:=varchar_arr();
record_status varchar_arr:=varchar_arr();
comments varchar_arr:=varchar_arr();
template_prep_fk_id varchar_arr:=varchar_arr();
template_prep_process_fk_id varchar_arr:=varchar_arr();
counter varchar_arr:=varchar_arr();
COMPONENT_type varchar_arr:=varchar_arr();
EXPIRATION_DATE varchar_arr:=varchar_arr();
na_option varchar_arr:=varchar_arr();
----------------------------------------------------
template_step_# varchar_arr:=varchar_arr();

BEGIN
------set up new -----------------------------------
TP_PROCESS_REC.id:=TEMPLATE_PREP_PROCESS_PKG.GET_NEXT_PROCESS;
TP_PROCESS_REC.date_created:=SYSDATE;
TP_PROCESS_REC.created_by:=USER;
TP_PROCESS_REC.record_status:='CURRENT';
TP_PROCESS_REC.comments:=P_COMPONENT(1).COMMENTS;
TP_PROCESS_REC.reason_for_change:=P_COMPONENT(1).reason;
TP_PROCESS_REC.updated:='N';
TP_PROCESS_REC.template_prep_fk_id:=P_COMPONENT(1).TEMPLATE_PREP_FK_ID;
TP_PROCESS_REC.template_step_#:=P_COMPONENT(1).template_step_#;

TP_PROCESS_REC.progress_flag:='
<font color="#006600" SIZE="1">Entered by '||V_USER||' on '||TO_CHAR(SYSDATE,'MM/DD/YY HH:MI A.M.')||'</font>
<BR> You have generated the following ID''S';

INSERT INTO TEMPLATE_PREP_PROCESS
VALUES TP_PROCESS_REC;
--------GATHER EXISTING INFORAMTION-----------------------------------------------------------

SELECT ID INTO TP_PROCESS_REC.ID
FROM TEMPLATE_PREP_PROCESS TP1 WHERE DATE_CREATED=(
SELECT MAX(DATE_CREATED) FROM TEMPLATE_PREP_PROCESS TP
WHERE TEMPLATE_PREP_FK_ID=TP_PROCESS_REC.template_prep_fk_id
AND TEMPLATE_STEP_#=TP_PROCESS_REC.template_step_#
AND RECORD_STATUS='CURRENT');

SELECT PROGRESS_FLAG INTO TP_PROCESS_REC.PROGRESS_FLAG
FROM TEMPLATE_PREP_PROCESS
WHERE ID=TP_PROCESS_REC.ID;



FOR i in 1..P_COMPONENT.COUNT LOOP
FOR a in 1..P_NUMBER_OF_PREPS loop
COMPONENT_FK_ID.EXTEND;
id.EXTEND;
date_created.EXTEND;
created_by.EXTEND;
record_status.EXTEND;
comments.EXTEND;
template_prep_fk_id.EXTEND;
template_prep_process_fk_id.EXTEND;
counter.EXTEND;
COMPONENT_type.EXTEND;
EXPIRATION_DATE.EXTEND;
na_option.EXTEND;
--------------------------------------------
template_step_#.EXTEND;

COMPONENT_fk_id(a):=TP_COMPONENTS_PKG.GENERATE_COMPONENT_ID(P_COMPONENT(i),V_COUNTER);

IF a=1 then
counter(a):=V_COUNTER;
else
if v_counter=1 then
counter(a):=a;
else
counter(a):=V_COUNTER+a-1;
end if;
end if;

COMPONENT_fk_id(a):=COMPONENT_fk_id(a)||'_'||counter(a);
TP_PROCESS_REC.progress_flag:=TP_PROCESS_REC.progress_flag||'<BR>'||COMPONENT_fk_id(a);

id(a):=P_COMPONENT(i).id;
date_created(a):=SYSDATE;
created_by(a):=USER;
record_status(a):='CURRENT';
comments(a):=P_COMPONENT(1).COMMENTS;
template_prep_fk_id(a):=P_COMPONENT(1).TEMPLATE_PREP_FK_ID;
template_prep_process_fk_id(a):=TP_PROCESS_REC.id;

COMPONENT_type(a):=P_COMPONENT(1).COMPONENT_type;
EXPIRATION_DATE(a):=P_COMPONENT(1).expiration_date;
na_option(a):=P_COMPONENT(1).na_option;
-----KEEP TRACK OF COMPONENTS PROGRESS------------------------------------------------------------------------------
date_created(a):=SYSDATE;
created_by(a):=USER;
record_status(a):='CURRENT';
template_prep_fk_id(a):=TP_COMPONENT_REC.template_prep_fk_id;
template_step_#(a):=TP_PROCESS_REC.template_step_#;
--component_fk_id(a):=TP_COMPONENT_REC.COMPONENT_fk_id;


end loop;
END LOOP;



begin
forall i in 1 .. COMPONENT_FK_ID.COUNT
INSERT INTO TP_COMPONENTS ( COMPONENT_fk_id ,
id ,
date_created ,
created_by ,
record_status ,
comments ,
template_prep_fk_id ,
template_prep_process_fk_id ,
counter ,
COMPONENT_type ,
EXPIRATION_DATE ,
NA_OPTION )
VALUES
( COMPONENT_fk_id(i) ,
id(i) ,
date_created(i) ,
created_by(i) ,
record_status(i) ,
P_COMPONENT(1).comments ,
P_COMPONENT(1).TEMPLATE_PREP_FK_ID,
template_prep_process_fk_id(i) ,
counter(i) ,
COMPONENT_type(i) ,
EXPIRATION_DATE(i) ,
NA_OPTION(i) );
end;

/* execute immediate
'begin
forall i in 1 .. :N
INSERT INTO TEMP_PROCESS_COMPS (template_step_# ,
date_created ,
created_by ,
record_status ,
template_prep_fk_id ,
component_fk_id )
VALUES (template_step_#(i) ,
date_created(i) ,
created_by(i) ,
record_status(i) ,
template_prep_fk_id(i) ,
component_fk_id(i) );
end;'
USING P_COMPONENT.COUNT;*/


--write expiration ---------------------------------------
TP_PROCESS_REC.progress_flag:=TP_PROCESS_REC.progress_flag||'<BR>Expiration Date :'||p_component(1).expiration_date;
--------------------------------------------------------------

UPDATE TEMPLATE_PREP_PROCESS
SET progress_flag=TP_PROCESS_REC.progress_flag
WHERE ID=TP_PROCESS_REC.ID;


--COMMIT;
RETURN '1';

END ADD_NEW_SAMPLE_COMPONENT ;

Tom,

Is there any difference between using the following two formats other than the second one using less lines of code?

V_DOW VARCHAR2(3) := TO_CHAR(SYSDATE,'DY');

1) IF V_DOW = 'SAT' THEN
V_DATE_D := SYSDATE - 1;
ELSIF V_DOW = 'SUN' THEN
V_DATE_D := SYSDATE - 2;
ELSIF V_DOW = 'MON' THEN
V_DATE_D := SYSDATE - 3;
ELSIF V_DOW = 'TUE' THEN
V_DATE_D := SYSDATE - 4;
ELSIF V_DOW = 'WED' THEN
V_DATE_D := SYSDATE - 5;
ELSIF V_DOW = 'THU' THEN
V_DATE_D := SYSDATE - 6;
ELSE
V_DATE_D := SYSDATE - 7;
END IF ;

2) SELECT DECODE(V_DOW, 'SAT', sysdate-1,
'SUN', sysdate-2,
'MON', sysdate-3,
'TUE', sysdate-4,
'WED', sysdate-5,
'THU', sysdate-6,
'FRI', sysdate-7)
INTO V_DATE_D
FROM DUAL;

Hi
What happens if I use a variable for table name? Following example:
EXECUTE IMMEDIATE 'insert into :t values(1,2,3)' using table_name;
As I know it dosen't work, as I can only bind "variables". But what happens really if I use a variable for table name.
EXECUTE IMMEDIATE 'insert into '||table_name||' values(1,2,3)';
I afraid it will be hardparsed everytime. Is it true?

Shahrouz DARVISHZADEH

Hi Tom,

this is an extract of a procedure I have inherited. Because of the SQLWhere in the execute imediate I end up doing an awful lot of hard parses.

an example of where_clause is .. AMOUNT >=(-499970.6) AND AMOUNT < (499900.73) ....

can you help in any way .. can I get this into 1 sql statement , or some smart dynamic sql that binds using the above techniques??



create or replace Procedure updatetransactionalerts (
naccount_fk IN NUMBER,
nmessage_pk IN NUMBER,
nlogicaltransaction_fk IN pls_integer,
nStatus IN NUMBER,
nType IN NUMBER,
nAmount IN NUMBER)
IS

SQLText varchar2(2000);
SQLWhere varchar2(2000);
colName varchar2(30);
AlertBatchNo number(15);
newpk number(15);
Seq number(15);
sAlertType varchar(50);
rowcount PLS_INTEGER :=0;

BEGIN
DECLARE CURSOR AlertListC IS

select a.pk,a.rtnuser_fk,a.alerttype_fk,
(case when a.where_clause is not null then 'WHERE ' || a.where_clause end) where_clause ,
a.description, c.name
from alert a, alert_account_xref b ,alerttype c
where a.pk = b.alert_fk
and a.alerttype_fk = c.pk
and a.alerttype_fk = 1
and a.active=1
and (a.valid_From is Null or a.valid_From<=SYSDATE)
and (a.valid_To is Null or a.valid_To>=SYSDATE)
and (a.Last_Trigger_Time is null or a.Last_Trigger_Time <=(SYSDATE-(10/(24*60))) )
and b.account_fk = naccount_fk ;

BEGIN

-- get the next alertbatch number
SELECT SEQ_ALERT_BATCH.NextVal INTO alertbatchno FROM Dual;

FOR Alert IN AlertListC LOOP


SQLWhere := replace(replace(replace(Alert.where_clause,'STATUS',nStatus),'AMOUNT',nAmount),'TYPE',nType);
-- insert into the log
EXECUTE IMMEDIATE 'INSERT INTO AlertLog(PK, alerttype_fk, Alert_FK, Trigger_Time, Account_FK,
Message_Instance_FK, Alert_Batch_no,rtnUser_fk)
SELECT SEQ_ALERTLOG.NextVal,:2,:3,:4,:5,:6,:7,:8 FROM DUAL '|| SQLWhere
using alert.alerttype_fk, alert.PK, sysdate, naccount_fk, nmessage_pk, AlertBatchNo,
alert.rtnuser_fk;

END LOOP;

END;

END UPDATETRANSACTIONALERTS;

as always
many many thanks in advance

You'll either

a) have to change your data model and store "where clauses" and "inputs into where clauses" -- eg: store:

AMOUNT >=sys_context('myctx','amt1') AND AMOUNT < sys_context('myctx','amt2')

and storing in a normalized table the string amt1 and the value for amt1.  Then, use dbms_session.set_context to set these values

b) blow off binds all together and rely on cursor sharing -- like this:



ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  execute immediate '
  5                  insert /* TAG */ into t ( x )
  6                  select :1
  7                    from dual
  8                   where ' || i || ' = ' || i
  9                  using i;
 10          end loop;
 11  end;
 12  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text from v$sql where sql_text like '% /* TAG */ %';
 
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
   insert /* TAG */ into t ( x )   select :1     from dual    where 4 = 4
   insert /* TAG */ into t ( x )   select :1     from dual    where 7 = 7
   insert /* TAG */ into t ( x )   select :1     from dual    where 2 = 2
select sql_text from v$sql where sql_text like '% /* TAG */ %'
begin  for i in 1 .. 10  loop   execute immediate '   insert /* TAG */ into t ( x )   select :1     from dual    where ' || i || '
= ' || i   using i;  end loop; end;
 
   insert /* TAG */ into t ( x )   select :1     from dual    where 8 = 8
   insert /* TAG */ into t ( x )   select :1     from dual    where 5 = 5
   insert /* TAG */ into t ( x )   select :1     from dual    where 3 = 3
   insert /* TAG */ into t ( x )   select :1     from dual    where 10 = 10
   insert /* TAG */ into t ( x )   select :1     from dual    where 1 = 1
   insert /* TAG */ into t ( x )   select :1     from dual    where 9 = 9
   insert /* TAG */ into t ( x )   select :1     from dual    where 6 = 6
 
12 rows selected.

<b>that shows your problem and this is the alternative:</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2          execute immediate 'alter session set cursor_sharing=force';
  3          for i in 1 .. 10
  4          loop
  5                  execute immediate '
  6                  insert /* TAG2 */ into t ( x )
  7                  select ' || i || '
  8                    from dual
  9                   where ' || i || ' = ' || i;
 10          end loop;
 11          execute immediate 'alter session set cursor_sharing=exact';
 12  end;
 13  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text from v$sql where sql_text like '% /* TAG2 */ %';
 
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
select sql_text from v$sql where sql_text like '% /* TAG2 */ %'
   insert /* TAG2 */ into t ( x )   select :"SYS_B_0"     from dual    where :"SYS_B_1" = :"SYS_B_2"
begin  execute immediate 'alter session set cursor_sharing=force';  for i in 1 .. 10  loop   execute immediate '   insert /* TAG2 *
/ into t ( x )   select ' || i || '     from dual    where ' || i || ' = ' || i;  end loop;  execute immediate 'alter session set c
ursor_sharing=exact'; end;


<b>just using cursor sharing won't fix you for if you bind a single value into the statement (as you are) cursor sharing won't touch the other literals.  You would have to stop binding all together.</b>
 

sorry, could you expand option 1 with a simple example.

thanks

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table wc ( id int primary key, where_clause varchar2(2000) );
 
Table created.
 
ops$tkyte@ORA9IR2> create table wc_dtl ( id references wc, bv_name varchar2(30), bv_val varchar2(2000), primary
  2  key(id,bv_name) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into wc values
  2  ( 1, ' sal between to_number(sys_context( ''myctx'', ''lo_sal'' )) and to_number(sys_context( ''myctx'', ''hi_sal'' )) ' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into wc_dtl values ( 1, 'lo_sal', 100 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into wc_dtl values ( 1, 'hi_sal', 1000 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace context myctx using myproc
  2  /
 
Context created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure myproc( p_name in varchar2, p_val in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_context( 'myctx', p_name, p_val );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_stmt long;
  3  begin
  4          l_stmt := 'insert into t select empno, sal from emp where hiredate >= :x ';
  5
  6          for x in ( select * from wc where id = 1 )
  7          loop
  8                  l_stmt := l_stmt || ' and ' || x.where_clause;
  9          end loop;
 10
 11          for x in ( select * from wc_dtl where id = 1 )
 12          loop
 13                  myProc( x.bv_name, x.bv_val );
 14          end loop;
 15
 16          execute immediate l_stmt using to_date( '01-jan-1980' );
 17  end;
 18  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X          Y
---------- ----------
      7369        800
      7900        950
 

Thank you ....

out of interest what if my where clause that is stored was a an sal IN( 1,2,3) rather than the between can I use a similar technique ?

of course.


ops$tkyte@ORA9IR2> insert into wc values
  2  ( 1, ' sal in ( :x, :y, :z )' );
1 row created.
 
ops$tkyte@ORA9IR2> insert into wc_dtl values ( 1, 'x', 1 );

ops$tkyte@ORA9IR2> insert into wc_dtl values ( 1, 'y', 2 );

ops$tkyte@ORA9IR2> insert into wc_dtl values ( 1, 'z', 3 );
  

using the technique you mentioned above for an IN, and using the sample code you provided previous ... I get an error. The only difference the hiredate bind

any ideas ?

n_sys@N> declare
2 l_stmt long;
3 begin
4 l_stmt := 'insert into t select empno, sal from emp where hired
ate >= :a ';
5
6 for x in ( select * from wc where id = 1 )
7 loop
8 l_stmt := l_stmt || ' and ' || x.where_clause;
9 end loop;
10
11 for x in ( select * from wc_dtl where id = 1)
12 loop
13 myProc( x.bv_name, x.bv_val );
14 end loop;
15
16 execute immediate l_stmt using to_date( '01-jan-1980' );
17 end;
18 /
declare
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 16

thanks

Can I use the same technique as setting the context here would not work ?

n_sys@N> select * from wc_dtl;

ID BV_NAME
---------- ------------------------------
BV_VAL
-----------------------------------------------------
--------------------
1 x
1

1 y
2

1 z
3

2 lo_sal
10

2 hi_sal
1000

n_sys@NOSTRODB> select * from wc;

ID
----------
WHERE_CLAUSE
--------------------------------------------------------------------------------
---------------------------------------------------
1
sal in ( :x , :y, :z )

2
sal between to_number(sys_context( 'myctx', 'lo_sal' )) and to_number(sys_conte
xt( 'myctx', 'hi_sal' ))


I am trying so hard to bind this particular inheritance but having trouble. I am probably missing the obvious and feel quite dumb

thanks

we are facing problem, while using dbms_sql.parse statement in my program. our database Version is 9.0.2. Can you help me please.

Tom,

I am using the package dbms_sql in order to bind, parse and execute queries, and after tracing the session and running the tkprof I found out that the same query was parsed every single time I executed. 
Do you have any idea of why this is happening.

I will show you an example using the print_table code that you posted in this forum.

I executed the print_table 4 times using the exact same query. At the end you can see the tkprof results where it shows that the select statments was reparse 4 times.

SQL> exec print_table('select * from dual');
DUMMY                         : X
-----------------

PL/SQL procedure successfully completed.

SQL> exec print_table('select * from dual');
DUMMY                         : X
-----------------

PL/SQL procedure successfully completed.

SQL> exec print_table('select * from dual');
DUMMY                         : X
-----------------

PL/SQL procedure successfully completed.

SQL> exec print_table('select * from dual');
DUMMY                         : X
-----------------

PL/SQL procedure successfully completed.

Here you have the tkprof results where you will see that the select * from dual statement was parse the four time I executed the print_table procedure.
********************************************************************************

select * 
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0          4         16           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.00          0          4         16           4

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67     (recursive depth: 1)

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

I am having the same problem every time I use the package dbms_sql in any program in order to bind, parse and execute any sql statement.

I hope you can give me an idea of why this is happining and what I can do in order to be able to have only one parse when I use dbms_sql to bind and parse sql statements.

Thanks 

One thing I forgot to mention, I am using Oracle 8.1.7.4.0.

Just in case you don't have handy the code for the print_table procedure, here you have what I used for my test.

create or replace procedure print_table(p_query in varchar2) is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( substr(rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue,1,255) );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end print_table;
/

Thanks for any help you can give me on this.

Tom,

Thanks a lot for you help. I implemented your suggestions in the package that we use to parse, bind and execute dynamic sql and it is working great. Now every time the package is called, it does not close the cursor and keeps all the sql statements parsed before so it can reuse them.
Now I have one more question regarding this. This package is being called constantly by many different programs every time they need to parse and execute dynamic sql, and now that the package is not closing the cursor after executing the statement and keeping all the statements that it has parsed before, is there any posibility of having memory problems because of that and having this package or any of the programs fail?

Thanks for your help on this.

Thanks for your help on this Tom.
Please let me know if what I think is correct: If a session can have up to 300 cursors open, and let say that we have three different sessions: session1 running program1, session2 running program2 and session3 running program3, and all these three programs can call the package that uses dbms_sql to parse the statements. In this scenario the package will be able to open up to 300 cursors in each session (up to 300 cursors for session1, up to 300 cursors for session2 and up to 300 cursors for session3), and the cursors that the package will open in session1 will be independently from the other cursors that the package will open in session2 or session3. So let say that session1 reaches 300 cursors so when the package tries to open cursor 301 for session1 it will get an error, but the other two sessions won't be affected by this error on session1 and they will continue working and using the package without problems as long as they do not open more than 300 cursors on their own sessions.
Am I correct? Is there anything in my thought that is not exactly correct?

Thanks for your help on this.

I was looking for a way to change the date format and found code in this thread to do that ( execute immediate alter session set nls_date_format= ... ) and this works for output, but when I save changes from an input statement on a webpage then it doesn't work. I'll keep trying to find the answer, so thanks for the help on the partial success.

Hi Tom,

There is a program that needs to execute select statements using case on 8i. As case is not supported in pl/sql 8i we are running the sql statement using execute immediate. In order to parse and bind these kind of statments I want to create a procedure that will receive the sql stament as a paramenter with all the variables and values I need to bind and then let that procedure to parse, bind, execute and return the results to the main program. I was reading your book (expert one to one) on page 723 where you show an example of how to use dbms_sql but the results are sending to a flat file. I would like to send the result to a collection that then will allow the main program to manipulate the result data. How can I modify that example on page 723 in order to send the result to a collection instead of a flat file?

I hope you can help me with this.

Thanks.

What is the equivalent of

execute immediate s1 into v1 using var1; in dbms_sql?

s1 = select count(*) from t1 where col1 = :var1.

What should be the second argument?

dbms_sql.VARIABLE_VALUE ( x_cursor, '', l_return_value );

I'm facing the problem with following code

CREATE OR REPLACE PROCEDURE test
AS
x_cursor INT DEFAULT dbms_sql.open_cursor;
y_cursor INT DEFAULT dbms_sql.open_cursor;
x_status INT;
y_status INT;
l_return_value NUMBER DEFAULT NULL;


h_filehandle utl_file.file_type;
filedata VARCHAR2(2000);
h_crn NUMBER(11);

BEGIN


dbms_sql.parse( x_cursor,
'select count(*) FROM table1 c, table2 a WHERE c.cno = :x
AND c.cno = a.cno AND a.tdate BETWEEN ''01-jan-99'' AND SYSDATE
AND a.ono NOT IN (SELECT ono FROM test3 WHERE (obcode = ''XYZ''
OR ano IN (''12'', ''23'', ''56''))) AND a.tcode IN (''X'',''Y'')
AND a.tvl >= 0', dbms_sql.native );

dbms_sql.parse( y_cursor,'INSERT INTO test_log VALUES (:y)',dbms_sql.native );
dbms_sql.define_column( x_cursor, 1, l_return_value);
h_filehandle := utl_file.fopen('/dump','text.txt' , 'r');

LOOP
BEGIN
utl_file.get_line(h_filehandle,filedata);
h_crn := TO_NUMBER(SUBSTR(filedata,1,11));
dbms_sql.bind_variable( x_cursor, ':x', h_crn );
x_status:=dbms_sql.EXECUTE ( x_cursor);

IF ( dbms_sql.fetch_rows(x_cursor) > 0 ) THEN
dbms_sql.column_VALUE ( x_cursor, 1, l_return_value );
END IF;

IF l_return_value> 0 THEN
dbms_sql.bind_variable( y_cursor, ':y', h_crn );
y_status := dbms_sql.EXECUTE( y_cursor );
END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN EXIT;

END;

END LOOP;

utl_file.fclose(h_filehandle);
dbms_sql.close_cursor(x_cursor);
dbms_sql.close_cursor(y_cursor);

END;
/

I always get the value of l_return_value as 0. Can't figure out why?

If I execute the select count(*) statement separately, counts are > 0.

Sorry for insufficient information. My problem is if I use the query with same value (which is coming from flat file), I'm getting count which is non-zero. But if I execute the query from procedure it returns 0 rows.

I even verified the query which is been generated. I can't understand what is happening. Please tell me how should I debug the problem.

a) printing out that which you are binding to see if it even remotely looks like what you THINK you are binding.

-- I replaced the complicated query with a simple query, to check if everything is working fine. Resulted in correct rowcount.

b) littering the code with dbms_output calls to see what the heck it is doing.

-- I use TOAD for the development. I assigned the query to one variable and evaluated the variable runtime; it is forming the proper query.

c) getting a debugger like jdeveloper to walk through the code
-- I'm using TOAD.

d) enabling a 10046 level 12 trace to see the dump of the query AND the binds to the query

-- I'll try this option

Yes, insert as select and sql*loader is very good option.

"(the art of debugging our own code seems to be getting lost over time.....)"

=o) ouch!

For what it's worth: One shop I worked had a "60-minute" rule for resolving sticky problems:

1) Give it 60 minutes to try and figure it out. Research - is it a programming, design, or an approach issue? Put together a simplified, testable case and see what happens.

2) If I haven't solved it, set aside my precious ego (or what's left of it) and go ask a fellow programmer (we actually had a charge number for "programming assistance").

3) If the two of us haven't figured out the issue within 60 minutes, and we have paid for support, use it.

4) If that hasn't worked, I take the test case and post to the Oracle community. This is where Tom (or a worthy counterpart) usually says I'm overlooking something obvious and go read the fine manual. Sigh.

I always scan this site in the research phase for something similar to what the issue is (and usually find it). Far and away the BEST site!

I ran the benchmark on my 10g instance. Performance of -3 has been the best (even after setting plsql_optimize_level to zero).

Here're the results -
run 1 - 1000 rows
run 2 - 10000 rows

exec p1
run 1 - 0.64 sec
run 2 - 5.88 sec

p2
run 1 - 0.20 sec
run 2 - 1.99 sec

p3
run 1 - 0.01 sec
run 2 - 0.12 sec

p4
run 1 - 0.15 sec
run 2 - 1.33 sec

p5
run 1 - 0.13 sec
run 2 - 0.13 sec

However, I have found at least two comments in the documentation that seem to conflict with your opinion.

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg09dyn.htm#26691 <code>

#1 - "Native Dynamic SQL is Faster than DBMS_SQL

Native dynamic SQL in PL/SQL performs comparably to the performance of static SQL, because the PL/SQL interpreter has built-in support for it. Programs that use native dynamic SQL are much faster than programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls. (Your performance gains may vary depending on your application.)

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"


-- You've always maintained that Native Dynamic SQL (Execute Immediate) is usually slower than DBMS_SQL for repeating use (when bound).

#2 - "Performance of static SQL is generally better than dynamic SQL."

-- Why "generally"? Shouldn't it be just "is better".

you are the best!

Thanks for providing examples and statistics.

I'm trying to create dynamic insert statements but running into an issue with dates. What we are trying to do is something like the following, but without any luck:

CREATE TABLE my_table (my_date DATE);

DECLARE
text_date VARCHAR2(100) := 'TO_DATE(''20050323'',''RRRRMMDD'')';
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO my_table (my_date) VALUES (:1)'
USING text_date;
END;
/

Am I missing something easy, or are we going to have to convert the value to a date and then bind it?

We get the string 'TO_DATE(''20050323'',''RRRRMMDD'')' from the transformation process. I can get around the problem by not using binds, but that opens a new and bigger can of worms. Maybe some sort of string manipulation can pluck out the parts of the to_date function, but the problem there is that the date format changes for different fields, so pattern matching becomes difficult.

I have found out that that my problem is a little bigger than I first thought. I will be recieving an array with two attributes (column_name and column_value). The column_name is straight forward, it is the name of the column in the destination table. The column_value is a little more involved. It will contain data values as well as small transformations on the values. For example, I will recieve an element with a column_name equal to 'first_name' and the column value will be 'SUBSTR('Smith,John',INSTR('Smith,John',',',1,1)+1,LENGTH('Smith,John'))'. So I will need to create an insert statement like 'INSERT INTO emp (first_name) VALUES ('John')'. The problem is that if I don't use binds, I will throws thousands of SQL statements every few minutes into my shared pool that have very little hope of being used again. I have tried several methods to convert the column_value attribute without injecting a new SQL statement. Here's a sample:
EXECUTE IMMEDIATE 'BEGIN
:1 := :2;
END;'
USING OUT vInsVal,transforms(i).transformation;
All that happens is that vInsVal gets assigned 'SUBSTR('Smith,John',INSTR('Smith,John',',',1,1)+1,LENGTH('Smith,John'))'.
Any suggestions?

oh my, thats not very nice.

I wish they would not do that.  Cursor sharing = force|similar is something you might have to do in your program.  At least all inputs of "that type" will be converted into binds:


ops$tkyte@ORA10GR1> create table t ( x varchar2(50) );
 
Table created.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> alter session set cursor_sharing=force;
 
Session altered.
 
ops$tkyte@ORA10GR1> begin
  2          execute immediate 'insert into t (x) values ( ' ||
  3          'SUBSTR(''Smith,John'',INSTR(''Smith,John'','','',1,1)+1,LENGTH(''Smith,John''))' ||
  4          ')';
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1> select * from t;
 
X
--------------------------------------------------
John
 
ops$tkyte@ORA10GR1> select sql_text from v$sql where sql_text like 'insert into t (x)%';
 
SQL_TEXT
-------------------------------------------------------------------------------
insert into t (x) values ( SUBSTR(:"SYS_B_0",INSTR(:"SYS_B_1",:"SYS_B_2",:"SYS_
B_3",:"SYS_B_4")+:"SYS_B_5",LENGTH(:"SYS_B_6")))
 
 

Hi Tom,

In my procedure, for quries I am using lot of constants.
These set of queries I have in procedure vary with input parameter.

Should I be using bind variables instead of constants?

And which approach would you suggest?
1.Execute Immediate
or
2.DBMS_SQL

Thanks for your help.

procedure (oid number)
is
v_cnt number;
begin

select count(*) into v_cnt
from table1 where col1='y'
and delete_ind='n'
and key=oid;

end p1;

will it be better if i use
bind variables instead of
'y'/'n'?

if yes, what would you suggest
dbms_sql or execute_immediate

Excellent! Thanks for your response.

In case of varying values, which approach do you suggest:

1.Execute_Immediate
2.Dbms_SQL

Thanks.

Thanks for the detailed explanation.

For eg:

For a scenario like this:

SELECT COUNT(*) INTO V_CNT FROM TABLE_1
WHERE KEY =<USER INPUT>

Is it good enough to say:

V_SQL:='SELECT COUNT(*) INTO V_CNT FROM TABLE_1
WHERE KEY =:1'

EXECUTE IMMEDIATE V_SQL INTO V_CNT USING <USER INPUT>

Is it not the right approach even when I am doing same thing for different user inputs for 'key' column?

Thank you

Tom,
I trying to perform the below but failing to do so,
can you please have advise

declare
v_id1 number;
v_tab varchar2(100);
l_sqlstmt varchar2(2000);
v_id number;
v_status varchar2(10);
begin
delete from c;
for crec in (select id1 from b)loop
begin
v_id := crec.id1;
v_tab := 'a';
select id1 into v_id1 from a where id1 = crec.id1;
exception
when no_data_found then
v_id := crec.id1;
v_tab := 'b';
select id1 into v_id1 from b where id1 = crec.id1;
end;
dbms_output.put_line ('v_tab: '||v_tab);
l_sqlstmt := 'insert into c select id1, v_tab from '|| v_tab ||' where id1 = '|| v_id ||';' ;
execute Immediate l_sqlstmt ;
end loop;
end;

Its resolved, my query formation was incorrect

Thankyou.

Tom,
Sorry about not giving the details and Thankyou about reading it eventough I said below that it is resolved.

Requirement:
I have a for loop which should have different values in v_tab and v_id variables at every iteration.
hence, earlier pasted was my pl/sql block.

Issue:
I was having issue with the execute immediate stmt.

Resolution:
my query was incorrect
l_sqlstmt := 'insert into c select id1, v_tab from '|| v_tab ||' where id1 = '|| v_id ||';' ;

correct query formation
l_sqlstmt := 'insert into c select id1, '|| v_tab ||' from '|| v_tab ||' where id1 = '|| v_id ||';' ;


Thankyou for your assistance.

Thankyou TOM,

I am setting the values for v_id & v_tab above in the pl/sql

vi $ORACLE_HOME/rdbms/admin/dbmssql.sql

type Varchar2_Table is table of varchar2(2000) index by binary_integer;

Hmmmmmmmmmm

And Oracle doesn't seem to want to do anything about it.

The example below (run using Oracle 10.1.0.4) shows how it's broken.  Of course, I wouldn't use this code with small tables, but with tables that have billions of rows, I need to array fetch in batches to copy data from one table to another.

SQL> drop table vcharsrc;

Table dropped.

SQL> drop table vchardst;

Table dropped.

SQL> create table vcharsrc (vtext varchar2(4000));

Table created.

SQL> create table vchardst (vtext varchar2(4000));

Table created.

SQL>
SQL> insert into vcharsrc values (rpad('a',1997,'b'));

1 row created.

SQL> insert into vcharsrc values (rpad('a',1997,'b'));

1 row created.

SQL> insert into vcharsrc values (rpad('a',1997,'b'));

1 row created.

SQL> insert into vcharsrc values (rpad('a',1997,'b'));

1 row created.

SQL> insert into vcharsrc values (rpad('a',1997,'b'));

1 row created.

SQL> insert into vcharsrc values (rpad('a',2001,'b'));

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> declare
  2      scid integer:=-99; -- source cursor
  3      dcid integer:=-99; -- destination cursor
  4      vchar0 DBMS_SQL.VARCHAR2_TABLE;
  5      --type Varchar4_Table is table of varchar2(4000) index by binary_integer;
  6      --vchar0 VARCHAR4_TABLE;
  7      theQuery varchar2(100) := 'Select * from vcharsrc';
  8      theInsert varchar2(100) := 'insert into vchardst (vtext) values (:hvvtext)';
  9      bufferRows integer := 10;
 10      rowCount integer:=0;
 11      iresult integer:=0;
 12
 13  Begin
 14
 15      scid := dbms_sql.open_cursor;
 16      dcid := dbms_sql.open_cursor;
 17
 18
 19      -- Source table
 20      DBMS_SQL.PARSE(scid, theQuery, DBMS_SQL.native);
 21      dbms_sql.define_array(scid, 1, vchar0, bufferRows, 1);
 22      rowCount := dbms_sql.execute_and_fetch(scid);
 23
 24      -- Destination table
 25      DBMS_SQL.PARSE(dcid, theInsert, DBMS_SQL.native);
 26
 27      dbms_sql.column_value(scid, 1, vchar0);
 28      dbms_sql.bind_array(dcid, ':hvvtext', vchar0, 1,rowCount);
 29      dbms_sql.define_array(scid, 1, vchar0, bufferRows,1);
 30
 31      iresult := dbms_sql.execute(dcid);
 32      commit;
 33
 34  End;
 35  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1216
ORA-06512: at "SYS.DBMS_SQL", line 334
ORA-06512: at line 22


SQL>

My full code to copy data from source table to destination table dynamically creates the select and insert statements based on the schema definition, and uses dbms_sql.define_array to bulk fetch the data, and column_value and bind_array to insert the data in batches of 100

 

Just bind those columns as a DBMS_SQL.CLOB_TABLE instead of a DBMS_SQL.VARCHAR2_TABLE. Is there any performance hit for doing this?

Yes, they don't think it's a bug, and they would file an enhancement request, but they say it probably will not get done. Here is what they said:

<<<Although development has stated that an enhancement request could be filed, my experience is that it will never get addressed, especially since dbms_sql is a very old package that has since been superceded by "execute immediate". >>>

As far as the "insert append" solution goes, I don't think I can use it because the unique constraints have to be checked. In any case, I found a workaround using "bind as CLOB".

I wrote some code to move 10000 rows of Varchar data (100 rows per fetch), and it took 3 seconds using DBMS_SQL.VARCHAR2_TABLE binding vs. 24 seconds using DBMS_SQL.CLOB_TABLE binding. Of course, as we have seen, if the varchars are longer than 2000 characters, the DBMS_SQL.VARCHAR2_TABLE binding doesn't work.

Why do I get a NO_DATA_FOUND error when trying to use CHAR bind variables ?

CREATE TABLE t
(a   NUMBER,
 b   CHAR(10));

INSERT INTO t
     VALUES (1, 'A');



CREATE OR REPLACE PROCEDURE p1 (in_column VARCHAR2, in_value VARCHAR2, out_value OUT VARCHAR2) AS
BEGIN
   EXECUTE IMMEDIATE 'SELECT ' || in_column || ' FROM t WHERE ' || in_column || ' = :value'
   INTO              out_value
   USING             in_value;
END;
/

SQL> DECLARE
  2     out_value   VARCHAR2 (4000);
  3  BEGIN
  4  -- Now call the stored program
  5     p1 ('A', '1', out_value);
  6  -- Output the results
  7     DBMS_OUTPUT.put_line (SUBSTR ('out_value = ' || out_value, 1, 255));
  8  EXCEPTION
  9     WHEN OTHERS THEN
 10        DBMS_OUTPUT.put_line (SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' |
| SQLERRM, 1, 255));
 11        RAISE;
 12  END;
 13  /
out_value = 1

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2     out_value   VARCHAR2 (4000);
  3  BEGIN
  4  -- Now call the stored program
  5     p1 ('A', 1, out_value);
  6  -- Output the results
  7     DBMS_OUTPUT.put_line (SUBSTR ('out_value = ' || out_value, 1, 255));
  8  EXCEPTION
  9     WHEN OTHERS THEN
 10        DBMS_OUTPUT.put_line (SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' |
| SQLERRM, 1, 255));
 11        RAISE;
 12  END;
 13  /
out_value = 1

PL/SQL procedure successfully completed.

SQL> DECLARE
  2     out_value   VARCHAR2 (4000);
  3  BEGIN
  4  -- Now call the stored program
  5     p1 ('B', 'A', out_value);
  6  -- Output the results
  7     DBMS_OUTPUT.put_line (SUBSTR ('out_value = ' || out_value, 1, 255));
  8  EXCEPTION
  9     WHEN OTHERS THEN
 10        DBMS_OUTPUT.put_line (SUBSTR ('Error ' || TO_CHAR (SQLCODE) || ': ' |
| SQLERRM, 1, 255));
 11        RAISE;
 12  END;
 13  /
Error 100: ORA-01403: no data found
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 11 

<quote src=Expert Oracle Database Architecture>

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. Let’s use the 'Hello World' string in a simple table to demonstrate:

ops$tkyte@ORA10G> create table t
  2  ( char_column      char(20),
  3    varchar2_column  varchar2(20)
  4  )
  5  /
Table created.

ops$tkyte@ORA10G> insert into t values ( 'Hello World', 'Hello World' );
1 row created.

ops$tkyte@ORA10G> select * from t;
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World
ops$tkyte@ORA10G> select * from t where char_column = 'Hello World';
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

ops$tkyte@ORA10G> select * from t where varchar2_column = 'Hello World';
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

So far, the columns look identical but, in fact, some implicit conversion has taken place and the CHAR(11) literal has been promoted to a CHAR(20) and blank padded when compared to the CHAR column. This must have happened since Hello World……… is not the same as Hello World without the trailing spaces. We can confirm that these two strings are materially different:

ops$tkyte@ORA10G> select * from t where char_column = varchar2_column;
no rows selected

They are not equal to each other. We would have to either blank pad out the VARCHAR2_COLUMN to be 20 bytes in length or trim the trailing blanks from the CHAR_COLUMN, as follows:

ops$tkyte@ORA10G> select * from t where trim(char_column) = varchar2_column;
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World
ops$tkyte@ORA10G> select * from t where char_column = rpad( varchar2_column, 20 );
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

Note    There are many ways to blank pad the VARCHAR2_COLUMN, such as using the CAST() function.

The problem arises with applications that use variable length strings when they bind inputs, with the resulting “no data found” that is sure to follow:

ops$tkyte@ORA10G> variable varchar2_bv varchar2(20)
ops$tkyte@ORA10G> exec :varchar2_bv := 'Hello World';
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> select * from t where char_column = :varchar2_bv;
no rows selected

ops$tkyte@ORA10G> select * from t where varchar2_column = :varchar2_bv;
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

So here, the search for the VARCHAR2 string worked, but the CHAR column did not. The VARCHAR2 bind variable will not be promoted to a CHAR(20) in the same way as a character string literal. At this point, many programmers form the opinion that “bind variables don’t work; we have to use literals.” That would be a very bad decision indeed. The solution is to bind using a CHAR type:

ops$tkyte@ORA10G> variable char_bv char(20)
ops$tkyte@ORA10G> exec :char_bv := 'Hello World';
 
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from t where char_column = :char_bv;
CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World
ops$tkyte@ORA10G> select * from t where varchar2_column = :char_bv;
no rows selected

However, if you mix and match VARCHAR2 and CHAR, you’ll be running into this issue constantly. Not only that, but the developer is now having to consider the field width in her applications. If the developer opts for the RPAD() trick to convert the bind variable into something that will be comparable to the CHAR field (it is preferable, of course, to pad out the bind variable, rather than TRIM the database column, as applying the function TRIM to the column could easily make it impossible to use existing indexes on that column), she would have to be concerned with column length changes over time. If the size of the field changes, then the application is impacted, as it must change its field width.

It is for these reasons—the fixed-width storage, which tends to make the tables and related indexes much larger than normal, coupled with the bind variable issue—that I avoid the CHAR type in all circumstances. I cannot even make an argument for it in the case of the one-character field, because in that case it is really of no material difference. The VARCHAR2(1) and CHAR(1) are identical in all aspects. There is no compelling reason to use the CHAR type in that case, and to avoid any confusion, I “just say no,” even for the CHAR(1) field.
</quote> 

Hi Tom,

Is there a way where I can bind the variables conditionally.

For example,

procedure xxx(in variables)
is
v_sql1 VARCHAR2 (100);
v_sql2 VARCHAR2 (100);

Begin
OPEN v_curr -- refcursor
FOR
SELECT statement using in variables
LOOP
FETCH v_curr into (some variables);
EXIT WHEN v_curr%NOTFOUND;

v_sql1:='SELECT table_alias.column_name from table_name table_name table_alias where table_alias.column_name2=:x';

if some condition is true
then
v_sql2:='AND table_alias.column_name2 <>:y'
end if;

EXECUTE IMMEDIATE (v_sql1 || v_sql2) Using x, (how do i bind y here considering that it is inside the If condition?)

END LOOP;

END;

There are quite a few conditions inside the procedure which ulimately form the query. If I could have used static sql, i would have. Is there a way i can acheive this other than putting the execute immediate itself under the IF-ELSE Condition ? Or do i need to

if some condition is true
EXECUTE IMMEDIATE (v_sql1 || v_sql2) Using x,y;
else
EXECUTE IMMEDIATE (v_sql1 ) using x;
end if;

Your help would be highly appreciated.

Thanks in advance!

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 82

Thanks for your quick response Tom.

Even though I seem to have role permissions for DBMS_SESSION, it looks like i do not have the execute grant on it. I will never be able to get those privileges from the client! Maybe you can advise some other way to acheive this. If not, I will have to go ahead with putting the execute immediate itself under the if-else condition, I guess.

But on the brighter side, i learnt about something which i never knew could be used in such a fashion. As always, you're the best.

Regards

no, that is not what it means at all.

you have the ability to use dbms_session
but whatever you are asking dbms_session to do is not permitted

but you don't give us ANY clue as to what you are doing.

I will guess, you are trying to use an application context:



ops$tkyte%ORA9IR2> create or replace context my_ctx using p
  2  /

Context created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure p
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx', 'x', 1 );
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA9IR2> exec dbms_session.set_context( 'my_ctx', 'x', 1 );
BEGIN dbms_session.set_context( 'my_ctx', 'x', 1 ); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at line 1


ops$tkyte%ORA9IR2> exec p

PL/SQL procedure successfully completed.


<b>You can only set the context from the procedure/package/function you bound the context to upon creating the context</b>

 

Hi Tom,

I have created a small test case to demonstrate the problem that I am facing. I have heavily borrowed from the example that you have given in the following link:

http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279

I hope the indentation comes out correctly here!

SQL> create or replace context MY_CTX using test_cntxt.MY_PROCEDURE;

Context created

SQL> 
SQL> CREATE OR REPLACE PACKAGE test_cntxt AS
  2    TYPE resultset IS REF CURSOR;
  3  
  4   procedure my_procedure( p_ename     in    varchar2 default NULL,
  5                           p_hiredate  in    date default NULL,
  6                           p_sal       in    number default NULL,
  7               p_rs        OUT   resultset);
  8  END;
  9  /

Package created

SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY test_cntxt
  2  AS
  3  procedure my_procedure( p_ename     in    varchar2 default NULL,
  4                          p_hiredate  in    date default NULL,
  5                          p_sal       in    number default NULL,
  6              p_rs        OUT   resultset)
  7  
  8  AS
  9  l_query  varchar2(512) default 'select * from emp where 1 = 1 ';
 10  
 11   BEGIN
 12  
 13      if ( p_ename is NOT NULL ) then
 14             dbms_session.set_context( 'MY_CTX', 'ENAME',
 15                                       '%'||upper(p_ename)||'%');
 16             l_query := l_query ||
 17                  ' and ename like
 18                    sys_context( ''MY_CTX'', ''ENAME'' ) ';
 19         end if;
 20  
 21      OPEN p_rs FOR l_query;
 22   END;
 23  
 24  END;
 25  /

Package body created

SQL> ALTER PACKAGE test_cntxt COMPILE;

Package altered

SQL> ALTER PACKAGE test_cntxt COMPILE BODY;

Package body altered

SQL> SET SERVEROUTPUT ON;
SQL> SET AUTOPRINT ON;
SQL> VARIABLE A REFCURSOR;
BEGIN
test_cntxt.my_procedure('JONES','','',:A);
END;SQL>   2    3  
  4  /
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 82
ORA-06512: at "SCOTT.TEST_CNTXT", line 14
ORA-06512: at line 2

I even tried this, call me stupid, but

SQL> create or replace context MY_CTX using MY_PROCEDURE;

Context created

SQL> VARIABLE A REFCURSOR;
BEGIN
test_cntxt.my_procedure('JONES','','',:A);
END;SQL>   2    3  
  4  /
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 82
ORA-06512: at "SCOTT.TEST_CNTXT", line 14
ORA-06512: at line 2

And if I leave p_ename as null, it works fine

SQL> set linesize 4000
SQL> VARIABLE A REFCURSOR;
BEGIN
test_cntxt.my_procedure('','','',:A);
END;SQL>   2    3  
  4  /

PL/SQL procedure successfully completed.


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-FEB-81       4000        625         30
      7566 JONES      MANAGER         7839 02-APR-81       2975        300         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1750         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850        300         30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450        300         10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000        300         20
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500        250         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100        300         20
      7902 FORD       ANALYST         7566 03-DEC-81       3000        125         20
      7934 MILLER     CLERK           7782 23-JAN-82       1300        300         10

10 rows selected.


SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0      Production
TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production


Thank you for your patience!
 

Thanks for the ultra quick response!!!!

Sorry for bothering you on such a small thing...

note to self :: *must read more carefully*!!

Hi Tom ...

I was trying to execute a select query dynamically and put the data returned in a flat file. I achieved it using DBMS_SQL and UTL_FILE packages. But now, i want to use an existing procedure instead of select query; execute it dynamically using DBMS_SQL; and write the output in a flat file. How can i do that? Need your help !!!

(The reason i used DBMS_SQL instead of "execute immediate" is, i need column details for formatting the flat file. can i do the same using "execute immediate")

Procedure to be called ---
procedure sp_get_bdg_clnt_ovr_det
(
p_bdg_seq_id in client_bdg_scorecard.bdg_seq_id%type,
p_ovr_det out pkg_global.ref_cursor
)

I want to use the output cursor for writing a flat file.

Thanks in advance ...

Hello Tom,
Good day to you!

I have a query that takes in one date parameter and gives me a single row output.
The parameter is a date. I need to run the same sql for all weekends for two years..
eg:

   Grouping week ending  passed  failed  no result
1 Alpha 01/13/2006 4791 1131 3039

now, I need to run the same query for next weekend and so on for the full two years.

I think I need to create a dummy table and run a procedure to insert the table with the output? Do you have a simple example for the same Tom ?

Can you please help? Thank you so much!

Tom,
I wrote a query to give me all the weekends. These are the dates that I need to enter as a parameter for my query. So, basically, I need to execute the query 152 times and get the output :( I only got this far and am wondering how to go about this! Can you please help?

select week_end
  from
(
select start_date as week_start,
       start_date + 6 as week_end,
       trunc(start_date,'d'),
       row_number() over (partition by trunc(start_date,'d') 
                              order by start_date) as rownumber
  from
  (select  to_date('10/31/2004','mm/dd/yyyy') + (level-1) as start_date        
           from dual
         connect by level <= trunc(sysdate)- to_date('10/31/2004','mm/dd/yyyy')+1
  
))
where rownumber  = 1

Thanks!

ops$tkyte%ORA10GR2> alter session set nls_date_format = 'Dy Mon DDth, YYYY';

Session altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select next_day(sysdate+(level-1)*7,'sat'), next_day( sysdate+(level-1)*7, 'sun')
  2    from dual
  3  connect by level <= 52*2
  4  /

NEXT_DAY(SYSDATE+( NEXT_DAY(SYSDATE+(
------------------ ------------------
Sat Sep 29TH, 2007 Sun Sep 30TH, 2007
Sat Oct 06TH, 2007 Sun Oct 07TH, 2007
...
Sat Sep 19TH, 2009 Sun Sep 20TH, 2009

104 rows selected.

ops$tkyte%ORA10GR2> spool off

Tom, How can I find what a col_type represent in DBMS_SQL.DESC_TAB Table Type?

It has got numbers like 1,2,12,180 etc..I could find for some using trial and err methods..but is there documentation somewhere showing what does 1,2,3 etc mean for col_type?
Thanks,

       decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                       2, decode(c.scale, null,
                                 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG',
                       9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                       12, 'DATE',
                       23, 'RAW', 24, 'LONG RAW',
                       58, nvl2(ac.synobj#, (select o.name from obj$ o
                                where o.obj#=ac.synobj#), ot.name),
                       69, 'ROWID',
                       96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       100, 'BINARY_FLOAT',
                       101, 'BINARY_DOUBLE',
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       122, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       123, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       178, 'TIME(' ||c.scale|| ')',
                       179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       180, 'TIMESTAMP(' ||c.scale|| ')',
                       181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
                       182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
                       183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
                             c.scale || ')',
                       208, 'UROWID',
                       'UNDEFINED'),

Sorry, Tom, Did not realy get from your response regarding col_type question above.

1) I did not see column type# in dba_tab_cols [??]
2) If I describe colums using dbms_sql, column information is represented by PL/SQL table desc_tab with columns like col_type,col_name,col_length etc..
But by getting col_type value, how can I determine if it's a varchar2 or Date or Number etc?

You might be explaining the same thing for me, but I am not getting it. can you explain it more clearly? The way you wrote I am understanding one thing. It's not mentioned in any dictionary table, what is 1,2,12 etc.. we just have to find out doing trial and err and can use DECODE to use it in our functions. or am I geeting it completely wrong?

Sorry..it's not my day today..

Thanks Tom. Now i got it. When you said "get decode from dba_tab_cols", I did not realise you were talking about View Script. I just checked it and could find it. Thanks a bunch for all your help.
I can use this code directly in my functions..

Hi Tom,

I have an alternative solution to the ones specified, though a variation of p5, but better results. I had tried this method once before, and when I read your answer I expected to find this too as a solution. Could you confirm my findings and whether it is indeed the best.

The enhanced procedure is p6. Below are the testing results. The Oracle version used is 10g. With 1000 rows the results were quite close and difficult to distinguish, but with 10000 rows the difference is quite clear.

-----------------------------------------------------------
TESTING:
-----------------------------------------------------------create or replace procedure p6
as
begin
    for i in 1 .. 10000
    loop
        state_pkg.g_x(i) := i;
        state_pkg.g_y(i) := sysdate+i;
        state_pkg.g_z(i) := 'this is row ' || i;
    end loop;
    forall i in 1 .. 10000
     execute immediate 'insert into t (x,y,z) values (:1, :2, :3)' using state_pkg.g_x(i), state_pkg.g_y(i), state_pkg.g_z(i);
end;
/

Testing Results--------------------------------------------

SQL> exec p1

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.47
SQL>  truncate table t;

Table truncated.

Elapsed: 00:00:00.14
SQL> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.30
SQL>  truncate table t;

Table truncated.

Elapsed: 00:00:00.09
SQL> exec p3

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.21
SQL>  truncate table t;

Table truncated.

Elapsed: 00:00:00.09
SQL> exec p4

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.70
SQL>  truncate table t;

Table truncated.

Elapsed: 00:00:00.08
SQL> exec p5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
SQL>  truncate table t;

Table truncated.

Elapsed: 00:00:00.10
SQL> exec p6

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14

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

As per my testing, P6 is performing the best. Could you please confirm the same.

Thanks in advance!

Referring to your response on April 29, 2002 ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:455220177497#3596051689501 )

Why can you not do this?:

type array is table of t%rowtype index by binary_integer;

Instead of:

type array is table of number index by binary_integer;

If you try the row type declaration with version 10.2.0.3.0 you get an error:

PLS-00457: expressions have to be of SQL types

What I'm trying to do is dynamically bulk insert using a collection. Such as inserting into the hr.employees table like this:

DECLARE
   TYPE rec IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
   l_col rec;
   l_tbl VARCHAR(40) := 'employees';
BEGIN
   SELECT 207, NULL, 'Smith', 'smith@smith.ca', NULL, sysdate, 'AD_PRES', NULL, NULL, NULL, NULL 
   BULK COLLECT INTO l_col
   FROM DUAL;
   
   FORALL i IN l_col.first..l_col.last
      EXECUTE IMMEDIATE
        'INSERT INTO employees VALUES (:1)' USING l_col(i);
END;

Any ideas?

ops$tkyte%ORA10GR2> create table t ( x int, y int, z int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create type myScalarType as object ( x int, y int, z int )
  2  /

Type created.

ops$tkyte%ORA10GR2> create type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_col myTableType;
  3  begin
  4          select myScalarType( rownum, rownum, rownum ) bulk collect into l_col from all_users where rownum <= 5;
  5          execute immediate 'insert into t (x,y,z) select * from TABLE(:x)' using l_col;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from t;

         X          Y          Z
---------- ---------- ----------
         1          1          1
         2          2          2
         3          3          3
         4          4          4
         5          5          5

In your book expert oracle one on one on page 707 you have this code.

--- 8< ---
create or replace
function update_row( p_owner in varchar2,
p_newDname in varchar2,
p_newLoc in varchar2,
p_deptno in varchar2,
p_rowid out varchar2 )
return number
is
l_theCursor integer;
l_columnValue number default NULL;
l_status integer;
l_update long;
begin
l_update := 'update ' || p_owner || '.dept
set dname = :bv1, loc = :bv2
where deptno = to_number(:pk)
returning rowid into :out';

-- Step 1, open the cursor.
l_theCursor := dbms_sql.open_cursor;

begin
-- Step 2, parse the query.
dbms_sql.parse( c => l_theCursor,
statement => l_update,
language_flag => dbms_sql.native );

-- Step 3, bind all of the INPUTS and OUTPUTS.
dbms_sql.bind_variable( c => l_theCursor,
name => ':bv1',
value => p_newDname );
dbms_sql.bind_variable( c => l_theCursor,
name => ':bv2',
value => p_newLoc );
dbms_sql.bind_variable( c => l_theCursor,
name => ':pk',
value => p_deptno );
dbms_sql.bind_variable( c => l_theCursor,
name => ':out',
value => p_rowid,
out_value_size => 4000 );

-- Step 4, execute the statement. Since this is a DML
-- statement, L_STATUS is be the number of rows updated.
-- This is what we'll return.

l_status := dbms_sql.execute(l_theCursor);

-- Step 5, retrieve the OUT variables from the statement.
dbms_sql.variable_value( c => l_theCursor,
name => ':out',
value => p_rowid );

-- Step 6, close the cursor.
dbms_sql.close_cursor( c => l_theCursor );
return l_columnValue;
exception
when dup_val_on_index then
dbms_output.put_line( '===> ' || sqlerrm );
dbms_sql.close_cursor( c => l_theCursor );
RAISE;
end;
end;
/

--- 8< ---

My question is, after reading it several times is, Who populated the l_columnValue variable ?? I just couldn't find it.

  9  end;
 10  /
Updated  rows
its rowid was AAAGnuAAFAAAAESAAA