Skip to Main Content
  • Questions
  • DBMS_SQL : using bind variables in insert stmt

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Gunjeet.

Asked: July 19, 2000 - 9:31 pm UTC

Last updated: March 09, 2009 - 3:47 am UTC

Version: v8.1.6

Viewed 10K+ times! This question is

You Asked


Hi Thomas,

I understand that using bind variables in an insert statement
can speed up the performance.

Can it be done the same way using dbms_sql ? If yes, could you
please provide me with an example of inserting a bunch
of rows in a table (table-name is known) using dbms_sql and
bind variables ?

Also, will performance be better if the cursor is opened once
at the begining, then all inserts done and then closed only
once at the end ?

Thanks,

Gunjeet

and Tom said...

There are many ways to tackle this problem. I'll show you at least 5 of them that use dynamic sql to insert into a table and that all use bind variables. The performance to insert 1000 rows using these methods on my machine varied from over 2.5 seconds at the worst case to under 3/4 of a second in the best case.

The 5 methods I used were:

o dbms_sql where I opened, parsed, bound, executed and closed
the cursor in a loop. This is by far the worst performing
method. We do not need to open, parse and close in the loop
and doing so added about 1 (out of 2.6) seconds to our runtime

o dbms_sql where I opened, parsed and closed the cursor once
for 1000 rows. In a loop I bound and executed 1000 times.
This was much faster then above.

o dbms_sql where I filled an array with 1000 elements and then
open, parsed, bound arrays, executed and closed 1 time. This
was much faster then the second one above and over 2 times
faster then the first approach.

o EXECUTE IMMEDIATE (i see you are using 8.1.6, execute
immediate is an "easier dynamic sql". Execute immediate done
inside the cursor for loop was faster then #1 and #2 above
but since it does single row inserts, was marginally bested by
#3 above.

o EXECUTE IMMEDIATE to execute a plsql block that BULK inserted
1000 rows. This was by far the fastest.

If I had to rate the above on complexity of code I would say:

#1 - medium
#2 - medium
#3 - complex
#4 - trivial
#5 - complex

So, my 2 best performers were somewhat complex in their implementation. A trivial (easy to code) implementation faired very well though (numbers below for comparision).

Here is the example I used:

ops$tkyte@DEV8I.WORLD> create table t ( x int, y date, z varchar2(255) );
Table created.

thats my sample table. I will insert 1,000 rows into this table and time it.


ops$tkyte@DEV8I.WORLD> create or replace procedure p1
2 as
3 l_cursor int;
4 l_status int;
5 begin
6 for i in 1 .. 1000
7 loop
8 l_cursor := dbms_sql.open_cursor;
9 dbms_sql.parse( l_cursor,
10 'insert into t (x,y,z)
11 values (:x,:y,:z)',
12 dbms_sql.native );
13 dbms_sql.bind_variable( l_cursor, ':x', i );
14 dbms_sql.bind_variable( l_cursor, ':y', sysdate+i );
15 dbms_sql.bind_variable( l_cursor, ':z',
16 'this is row ' || i, 255 );
17 l_status := dbms_sql.execute( l_cursor );
18 dbms_sql.close_cursor(l_cursor);
19 end loop;
20 end;
21 /

Procedure created.

P1 is the most inefficient. At least it uses bind variables but it unneccessarily opens and parses and closes inside the loop. We will see below how much this affected us


ops$tkyte@DEV8I.WORLD> create or replace procedure p2
2 as
3 l_cursor int default dbms_sql.open_cursor;
4 l_status int;
5 begin
6 dbms_sql.parse( l_cursor,
7 'insert into t (x,y,z)
8 values (:x,:y,:z)',
9 dbms_sql.native );
10
11
12 for i in 1 .. 1000
13 loop
14 dbms_sql.bind_variable( l_cursor, ':x', i );
15 dbms_sql.bind_variable( l_cursor, ':y', sysdate+i );
16 dbms_sql.bind_variable( l_cursor, ':z',
17 'this is row ' || i, 255 );
18 l_status := dbms_sql.execute( l_cursor );
19 end loop;
20
21 dbms_sql.close_cursor(l_cursor);
22 end;
23 /

Procedure created.

P2 is a simple optimization of P1. moved the open and parse outside of the loop as well as the close


ops$tkyte@DEV8I.WORLD> create or replace procedure p3
2 as
3 l_cursor int;
4 l_status int;
5 l_x dbms_sql.number_table;
6 l_y dbms_sql.date_table;
7 l_z dbms_sql.varchar2_table;
8 begin
9 for i in 1 .. 1000
10 loop
11 l_x(i) := i;
12 l_y(i) := sysdate+i;
13 l_z(i) := 'this is row ' || i;
14 end loop;
15 l_cursor := dbms_sql.open_cursor;
16 dbms_sql.parse( l_cursor,
17 'insert into t (x,y,z)
18 values (:x,:y,:z)',
19 dbms_sql.native );
20 dbms_sql.bind_array( l_cursor, ':x', l_x );
21 dbms_sql.bind_array( l_cursor, ':y', l_y );
22 dbms_sql.bind_array( l_cursor, ':z', l_z );
23 l_status := dbms_sql.execute( l_cursor );
24 dbms_sql.close_cursor(l_cursor);
25 end;
26 /

Procedure created.

P3 is a complex optimization of P2. We will use array processing to send to the kernel 1000 rows to be inserted -- instead of calling into the kernel 1000 times to insert a row.


ops$tkyte@DEV8I.WORLD> create or replace procedure p4
2 as
3 begin
4 for i in 1 .. 1000
5 loop
6 execute immediate
7 'insert into t (x,y,z) values (:x,:y,:z)'
8 using i, sysdate+i, 'this is row ' || i;
9 end loop;
10 end;
11 /

Procedure created.

P4 -- so easy to code, uses bind variables -- pretty efficient. Unfortunately, internally its a little like P1. The open, parse, close calls are inside of the loop. Its faster then DBMS_SQL is (much) but still, not as fast as DBMS_SQL using array processing. Lets add that in the next example:


ops$tkyte@DEV8I.WORLD> create or replace package state_pkg
2 as
3 g_x dbms_sql.number_table;
4 g_y dbms_sql.date_table;
5 g_z dbms_sql.varchar2_table;
6 end;
7 /

Package created.

Here are the arrays we have. We cannot "bind" arrays using execute immediate so what we do is put them in a package spec so they are globally visible. We can reference a plsql package spec variable dynamically so we can still "bind" them indirectly


ops$tkyte@DEV8I.WORLD> create or replace procedure p5
2 as
3 begin
4 for i in 1 .. 1000
5 loop
6 state_pkg.g_x(i) := i;
7 state_pkg.g_y(i) := sysdate+i;
8 state_pkg.g_z(i) := 'this is row ' || i;
9 end loop;
10 execute immediate
11 'begin
12 forall i in 1 .. :N
13 insert into
14 t (x,y,z)
15 values (state_pkg.g_x(i),
16 state_pkg.g_y(i),
17 state_pkg.g_z(i));
18 end;'
19 USING 1000;
20 end;
21 /

Procedure created.

Here we combine 2 Oracle8i features -- FORALL (array processing for plsql inserts/updates and deletes) with EXECUTE IMMDIATE. While this is somewhat complex to code -- takes a little thought, it is by far the best performer.


ops$tkyte@DEV8I.WORLD> exec p1

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.58


ops$tkyte@DEV8I.WORLD> select count(*) from t;
COUNT(*)
----------
1000
Elapsed: 00:00:00.01

ops$tkyte@DEV8I.WORLD> truncate table t;
Table truncated.
Elapsed: 00:00:00.21

ops$tkyte@DEV8I.WORLD> exec p2

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.65


ops$tkyte@DEV8I.WORLD> select count(*) from t;
COUNT(*)
----------
1000
Elapsed: 00:00:00.00

ops$tkyte@DEV8I.WORLD> truncate table t;
Table truncated.
Elapsed: 00:00:00.24

ops$tkyte@DEV8I.WORLD> exec p3

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.07


ops$tkyte@DEV8I.WORLD> select count(*) from t;
COUNT(*)
----------
1000
Elapsed: 00:00:00.01

ops$tkyte@DEV8I.WORLD> truncate table t;
Table truncated.
Elapsed: 00:00:00.39

ops$tkyte@DEV8I.WORLD> exec p4

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.36


ops$tkyte@DEV8I.WORLD> select count(*) from t;
COUNT(*)
----------
1000
Elapsed: 00:00:00.01

ops$tkyte@DEV8I.WORLD> truncate table t;
Table truncated.
Elapsed: 00:00:00.45

ops$tkyte@DEV8I.WORLD> exec p5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.69


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

COUNT(*)
----------
1000

Elapsed: 00:00:00.01




Rating

  (98 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

"DBMS_SQL : using bind variables in insert stmt"

Chris Barr, March 12, 2001 - 3:17 pm UTC

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.



Wonderful example

Pichaimani Balasubramanian, May 14, 2001 - 7:25 pm UTC

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



Excellent work

Pradeep, July 12, 2001 - 9:20 am UTC

This example really helped us. Thank you very much

Excellent Explanation

Sivakumar Ponnuswamy, September 10, 2001 - 10:23 pm UTC

Tom,

You are "THE" person.

Thanks alot.

Just what I needed

Jaideep Shah, October 12, 2001 - 7:32 pm UTC

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.

Tania W., December 11, 2001 - 3:41 am UTC

Brilliant example ... and ... explained

result of tkprof Using this

Ak, January 11, 2002 - 10:57 pm UTC

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

Tom Kyte
January 12, 2002 - 9:58 am UTC

did you read my discussion?

I said:

<quote>
P4 -- so easy to code, uses bind variables -- pretty efficient.
Unfortunately, internally its a little like P1. The open, parse, close calls are
inside of the loop. Its faster then DBMS_SQL is (much) but still, not as fast
as DBMS_SQL using array processing. Lets add that in the next example:

</quote>

With ease of programming comes inefficiencies sometimes. This is one of them. You are using a very very simple construct "execute immediate". Everytime you call execute immediate -- you could be sending it a wholly different string -- it is a variable string that is passed into it. It cannot parse it once, it has to parse it each and every time.

execute immediate is good for ONE TIME execution of a dynamic sql statement.

dbms_sql is good for REPEATED executions.

If you are interested in yet more details, I have an expansive chapter on this in my book.

tkprof

Ak, January 11, 2002 - 11:07 pm UTC

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



Tom Kyte
January 12, 2002 - 10:00 am UTC

Well, first and foremost -- DO YOU NEED TO USE DYNAMIC SQL. I think the answer is "no, I do not need to use dynamic sql, static sql is what I will be using"

If that is the case, then don't worry -- plsql does all of the cursor caching for you. Just use static sql, code:


create or replace procedure p4
as
begin
for i in 1 .. 10
loop
insert into test (eno,ename) values ( i, 'name ' );
end loop;
end;


that'll parse the insert once per session, not once per iteration.

else, use DBMS_SQL and array binds.

tkprof

Ak, January 12, 2002 - 1:02 am UTC

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


Tom Kyte
January 12, 2002 - 10:02 am UTC

read the plsql manual. There is but one way to insert and delete a record (execute the insert/delete).

My prefered method for selection is a cursor for loop:


for x in ( select * from t where ... )
loop
....
end loop

if I expect 0, one or more records and select into if I expect 0 or 1 records:


begin
select * into l_record from t where ....;
exception
when no_data_found then
....
end;


pl/sql optimization

Mikito harakiri, January 12, 2002 - 3:10 pm UTC

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?

Tom Kyte
January 12, 2002 - 3:16 pm UTC

You missed the point.

it is in general:



loop
execute immediate some_variable;
end loop;


If you have code like:


loop
execute immediate 'insert into t values ( :x )' using y;
end loop;

you should NOT be using dynamic sql at all -- so its your fault. If you are using dyanmic sql, it typically involves code like:

loop
execute immediate some_variable_that_could_change_at_any_moment;
end loop


it is NOT POSSIBLE for us to 'optimize' that unless you want us to do the wrong thing.

If you can benefit from the optimization because you know that the statement is invariant in the loop -- you use DBMS_SQL, not execute immediate.


(i give up)....

tkprof

ak, January 13, 2002 - 9:08 pm UTC

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


Tom Kyte
January 14, 2002 - 7:29 am UTC

SQLPlus must parse each and every statment -- sqlplus is alot like using EXECUTE IMMEDIATE in plsql. It cannot cache the cursor since every time you enter a new statement -- it is potentially different from the prior statement.


In a WELL WRITTEN program in any language, you would see a parse of P3 once per session with many executes. In SQLPlus, a very very simple command line tool used to test ideas, you will not since everything is dynamic.

create inside execute immediate

ak, January 14, 2002 - 4:50 am UTC

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



Tom Kyte
January 14, 2002 - 7:49 am UTC

it would be

execute immediate 'create table ' || t1 || '(eno number)';


you cannot BIND DDL ever -- DDL does not do bind variables.

A reader, January 14, 2002 - 8:41 am UTC

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

Tom Kyte
January 14, 2002 - 10:05 am UTC

if there was THE best method -- there would be ONE way.


Use the one that is most appropriate (going to insert a single row into a table dyanically?? use execute immediate -- its trivial. going to insert 1,000 rows into it? dbms_sql might be the easiest to code and gets very good performance)....

A reader, January 14, 2002 - 9:50 am UTC

TOM!!!! Is the best

dynamic sql insert realistic example

Mikito Harakiri, January 14, 2002 - 1:10 pm UTC

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!?

Tom Kyte
January 14, 2002 - 1:42 pm UTC

You write a generic load utility in PLSQL. It is passed the name of a file, the name of a table.

This case happens MANY times -- sqlldr is perhaps the perfect example of it.

You write a generic "reformat" utility -- it is passed the owner.table_name to read from and the owner.table_name to write to.

There are two cases then where an insert performed in a loop can't be reduced to static SQL. You do not know the names of the table you are inserting into, you might not know the columns you are inserting into (use your imagination -- I'm sure there are others)

Never mind the fact that the user NEVER EVER in a BILLION YEARS wants to build the string by concatenation.

reason #1: the performance of that (as i've said many a time here) stinks. It kills performance, kills scalability, kills the shared pool, is 100% the wrong approach

reason#2: it is actually (get this) HARDER then using bind variables to successfully concatenate a string together. You have to worry about things like:

- does the character string literal exceed 4000 characters?? if so, you won't
be using concatenation in SQL.
- does the character string literal include quotes?? if so, be ready to double
them all up before concatenating
- what is the length of the result insert statement going to be? If I have
10 columns that are varchar2(4000) that could easily exceed 32k!! Now
I have to use dbms_sql.parse with a PLSQL table type instead of a string,
I have to make sure the sql statement does not exceed 32k. If I used binds
no such worries -- even in the extreme cases.


without "execute immediate"

Barry Ludwig, January 14, 2002 - 2:37 pm UTC

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;
/

Tom Kyte
January 14, 2002 - 4:10 pm UTC

sure, but it would not be a dynamic sql example anymore (the point of this was to compare/contrast the different approaches)...



Excellent solution and explanation

jiten padhiar, January 15, 2002 - 6:58 am UTC

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);
.....



Tom Kyte
January 15, 2002 - 9:39 am UTC

You could add "ORDER BY SOH_COMPANY" to the query so the data comes out sorted by company.

then,


is
l_last_company varchar2(255) default 'x';
...
l_cursorId number := dbms_sql.open_cursor;
begin
...
for rec in curGeneralInvoice( ld_startDate )
loop
if ( l_last_company != rec.soh_company )
then

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.native );

end if;
dbms_sql.bind_variable...
dbms_sql.define_column....
......


That way, you'll parse a query once per company and execute it over and over and over. Also, just get a dbms_sql cursor ONCE.




One More!

Ashok, January 15, 2002 - 9:01 am UTC

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

Tom Kyte
January 15, 2002 - 9:40 am UTC

STATIC sql is always preferable to dynamic sql in PLSQL -- without question. If you can do it statically -- do it.

what is the best way ?

vivek, February 25, 2002 - 4:15 pm UTC

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

Tom Kyte
February 25, 2002 - 4:19 pm UTC

your update statement is not even close to being valid.

No, dynamic sql will not do it faster.

Why would you have "where rowid like" when you have the exact rowid???

what is the best way ?

vivek, February 25, 2002 - 4:20 pm UTC

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

Tom Kyte
February 25, 2002 - 7:35 pm UTC

Well -- your logic is:

loop over 10,000,000 records
update a single row
commit
end loop

that has got to be the slowest approach ever. It'll generate the MOST redo, it'll take the longest to run (pretty much each commit must wait for a log file sync).

You should just issue:

update web_logs100
set uri = substr( uri, 1, instr(uri,'/',-1),
query = substr(uri, 1, instr(uri,'/',-1)
where uri like '/view%/%';


The problem with your approach (in addition to being slllloooowwwww) is that it is not RESTARTABLE. What if halfway through you "fail". If you just re-run it, you'll corrupt already updated rows (eg: the url /view/something/else/was/here/x would be updated to /view/something/else/was/here/ on the first go around and the second time it would be /view/something/else/was/ the next time.

Just a single update, its all you need. You are wasting resources by committing when you should not be. If you are worried about rollback -- add more. Don't do row at a time processing when a single update would do it.

dynamic sql CANNOT be faster - the code path is longer. It will certainly be MUCH longer. The single update will be fastest.




vivek, February 25, 2002 - 4:23 pm UTC

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,


Tom Kyte
February 25, 2002 - 7:36 pm UTC

static sql in plsql is much faster then dynamic sql.

In your case, plsql isn't even needed, just do the update.

Excellent

Raza, February 25, 2002 - 5:05 pm UTC

Thats what I was looking! You are impressive

I want it to be automated

vivek, February 25, 2002 - 9:11 pm UTC

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 ?

Tom Kyte
February 26, 2002 - 8:58 am UTC

my point is -- if you run your process 2 times, it will change the data it already changed! look at your logic. Every day you will update the rows you've already updated.

Why not just put a trigger on the table that is:

create or replace trigger trigger_name
before insert on t
for each row
begin
if ( :new.url like '/view%/%' )
then
:new.uri := substr( :new.uri, 1, instr(:new.uri,'/',-1) );
:new.query := :new.uri;
end if;
end;

Update the existing data ONCE, fix the newly added rows as they come in. Else every day your update will take longer then the day before.


Your "update row/commit/update row/commit" is the SLOWEST way to do this, it is the way that generates the MOST redo, it wastes resources. If thats how you want to do it -- so be it -- it'll be slow. Just how often does your machine crash? if that is your overriding concern, I would expend my energies on making it so my machine doesn't crash -- not by committing. Also, since your process is not restartable from where it left off -- it will update all of the rows it already updated ANYWAY (you haven't saved a bit of time!).

thanks,

vivek, February 26, 2002 - 11:28 am UTC

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 ?

Tom Kyte
February 26, 2002 - 11:56 am UTC

to answer:

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 ?



is simple.

If you can use static sql, use it. If not, if what you are doing demands dynamic sql, use that.

If you have my book -- i cover this in detail in the chapter on dynamic sql showing the differences in performance/functionality between the two.

DBMS_SQL - Insert into the same table

SL, April 29, 2002 - 12:54 pm UTC

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 Kyte
April 29, 2002 - 1:20 pm UTC

To go faster, just make it be:

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
);


I don't see why you would "chop up" the insert and insert 1000 rows at a time.

My mantra:

o if you can do it in a single sql statement DO IT.

You can -- you need no procedural code. Procedural Code = extra overhead = more runtime. Your DBMS_SQL solution is a great case of this -- you are doing MORE work in plsql, slowing down the SQL. Just let SQL do it.

Never use dynamic sql where static sql will do just as well (fetch bulk collect and FORALL inserts would have been better then DBMS_SQL but still NOT AS GOOD as just doing it in SQL!!!!!




DBMS_SQL : using bind variables in insert stmt

Geet Sachidananda, April 29, 2002 - 3:42 pm UTC

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

Tom Kyte
April 29, 2002 - 8:57 pm UTC

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 ;)

 

DBMS_SQL - Insert into the same table

SL, April 29, 2002 - 4:29 pm UTC

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!



Tom Kyte
April 29, 2002 - 9:04 pm UTC

Consider using the /*+ append */ hint
Consider losing the order by in the subquery
Consider using the CBO (an index might not be the right way to access this table)
Consider using a parallel direct path insert

Make sure to read the chapter in the concepts guide on direct path inserts (append) before using it so as to fully understand it.

(51 seconds, depending on your system, doesn't sound horrible. What kind of speed are you going for here?)

But what about setting up a cursor....

Ivan, February 10, 2003 - 10:38 am UTC

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


Tom Kyte
February 11, 2003 - 7:35 am UTC

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>

 

I missed some of my point

Ivan, February 20, 2003 - 9:26 pm UTC

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



Tom Kyte
February 21, 2003 - 9:12 am UTC

yes, you can do that.....

Bulk fetch and inserting for every rows fetch

Vikas Sharma, March 04, 2003 - 2:46 pm UTC

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


Tom Kyte
March 04, 2003 - 6:48 pm UTC

yuck. wouldn't want to do it at all.

if you are moving any serious volume of data -- use array fetches and inserts.

yes but what about...

Ivan, March 11, 2003 - 3:01 pm UTC

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

Tom Kyte
March 11, 2003 - 3:19 pm UTC

I don't understand the context here or why you would use dynamic sql vs static sql....




How could I use static sql?

Ivan, March 11, 2003 - 9:01 pm UTC

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

Tom Kyte
March 11, 2003 - 9:09 pm UTC

you will have to benchmark:

o the effect of a softparse (at least) per row updated (DYNAMIC) vs
o executing an update per column, for the columns you want to update (STATIC) vs
o a single update of all columns and let streams sort it out later (STATIC)


DBMS_SQL vs, EXECUTE IMMEDIATE in dynamic SQL

Pallabi, March 26, 2003 - 12:24 am UTC

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 Kyte
March 26, 2003 - 7:28 am UTC

execute immediate -- seems to me you execute the statement ONCE and then throw it away.

dbms_sql is appropriate when you will execute the same dynamically generated statement 10's or more times.

execute immediate is appropriate when you execute it once.



I would use limit, and limit myself to between 100 and 500 rows at a time. Else, you'll be using quite a huge amount of ram and you'll find diminishing marginal returns (I've even seen it go negative returns) on larger array sizes.

the execute immediate trick -- building a block of code that uses global package variables -- works nicely. If you have that going, no need to record using dbms_sql.

improving native dynamic sql

Senthil, April 18, 2003 - 4:47 pm UTC

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

Tom Kyte
April 18, 2003 - 6:46 pm UTC

tell me, why would you even consider obscure, hard to understand, hard to code, hard to maintain code -- over a simple, single, insert into?

my mantra:

if you can do it in a single sql statement -- do it (dont even CONSIDER anything else)



agreed...but...

Senthil, April 18, 2003 - 7:31 pm UTC

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 Kyte
April 18, 2003 - 7:52 pm UTC

but what? you are in test, test is when you fix bugs, this is a bug.

Think about it -- look at the logic. Every time you run the procedural block, it HAS to process the rows it did last time and the time before and the time before and the time before (so on...). Not only that, but it runs a single row subquery for each and every row.

Everytime you run the insert -- it processes ALL prior data AND runs a subquery for each row. it gets slower and slower and slower (and so on)

Perfect!!

Jim, August 12, 2003 - 1:08 pm UTC

Tom, you are a machine!

Help!

SD, September 02, 2003 - 6:06 am UTC

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?



Tom Kyte
September 02, 2003 - 7:35 am UTC

why do you believe you need dynamic sql when all it appears you need is static sql with a bind variable????




Above Query!!

SD, September 02, 2003 - 6:12 am UTC

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;



Tom Kyte
September 02, 2003 - 7:36 am UTC

there are only 7 statements. use static sql.

faster
more efficient
more managable
more readable
easier to debug
all around better.

P4 - above

Bala, September 08, 2003 - 10:14 pm UTC

"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)


Tom Kyte
September 09, 2003 - 11:27 am UTC

it does a "trivial check" -- called a soft parse.

it is still a parse however, a parse is a parse is a parse. the more you parse, the worse off you are.



Please help

John, September 25, 2003 - 10:36 am UTC

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 Kyte
September 25, 2003 - 11:24 pm UTC

hmm

a ton of code

that i cannot run (big, yet incomplete - SMALL, yet complete is what we are looking for in test cases)

without any indication as to the issue you are encountering other then "it doesn't work"

plsql IF/ELSE Vs, sql decode

LB, November 13, 2003 - 9:50 am UTC

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;

Binding Table names

Shahrouz DARVISHZADEH, November 20, 2003 - 3:04 am UTC

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

Tom Kyte
November 21, 2003 - 3:39 pm UTC

you cannot bind an identifier.

if table_name changes EVERY time, it'll be hard parsed every time.

if table_name is the same two times, then one will be hard and one will be soft.

Thanks

A reader, November 24, 2003 - 5:10 am UTC


can this be dynamic /binding?

phil, February 11, 2004 - 4:59 am UTC

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

Tom Kyte
February 11, 2004 - 9:20 am UTC

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>
 

Phil, February 11, 2004 - 9:37 am UTC

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

thanks




Tom Kyte
February 11, 2004 - 11:18 am UTC

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
 

So good ....

Phil, February 13, 2004 - 5:39 am UTC

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 ?

Tom Kyte
February 13, 2004 - 10:00 am UTC

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 );
  

Error with the IN technique

Phil, February 17, 2004 - 5:18 am UTC

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

Tom Kyte
February 17, 2004 - 8:23 am UTC

show the entire example please. including what is in wc_dtl for you.

Phil, February 17, 2004 - 6:23 am UTC

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

Tom Kyte
February 17, 2004 - 8:56 am UTC

i don't understand that?

data tables

phil, February 17, 2004 - 8:42 am UTC

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

Tom Kyte
February 17, 2004 - 10:04 am UTC

you have binds in the id=1, not sys_context values. you would have needed to bind x, y, z.

binds do not belong in the wc table.
sys_context calls do.

Concept is well explained but

Narasayya, April 27, 2004 - 12:25 am UTC

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 Kyte
April 28, 2004 - 11:43 am UTC

my car won't start.

why?

if you can answer that correctly, I'll be able to answer your query correctly too. (problem is, you have approximately a 0% chance of answering my question given the amount of information I've provided :)

reparse using dbms_sql

A reader, April 29, 2004 - 8:01 pm UTC

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 

Tom Kyte
April 30, 2004 - 7:08 am UTC

it was parsed 4 times because the code calls parse each and ever time.


in order to have only one parse, you would have to "cache" the queries -- perform a lookup to see if you already have the query parsed and reuse the handle.

It is best used in the case where the query is built once per session but executed over and over and over. With really dynamic dynamic sql (like that which print_table does, or sqlplus for that matter), it probably isn't worth it. What are the odds you'll frequently issue the same query over and over with print table?


Use dbms_sql when you have something like:

parse
loop
bind
execute
end loop
close


so you can parse once, execute many...

reparse using dbms_sql

A reader, April 29, 2004 - 8:24 pm UTC

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 Kyte
April 30, 2004 - 7:25 am UTC

You'd have to make the code "lots fancier" -- not sure if it is worth it. but here is a first cut (based on the 'better' print_table from "Effective Oracle By Design")


create or replace package print
AUTHID CURRENT_USER
as
procedure tbl
( p_query in varchar2,
p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' );
end;
/


create or replace package body print
as
-- we'll be caching parsed queries in a lookup table
-- and reusing their cursor over time...
type array is table of long index by binary_integer;
g_parsed_queries array;


-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore( p_cs in varchar2, p_date_fmt in varchar2 )
is
begin
if ( upper(p_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;
end restore;

procedure tbl
( p_query in varchar2,
p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )

-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
as
l_theCursor integer;

l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be "friendly"
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;

execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;

-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;


-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.

l_theCursor := g_parsed_queries.first;
while ( l_theCursor IS NOT NULL and (g_parsed_queries(l_theCursor) != p_query) )
loop
l_theCursor := g_parsed_queries.next(l_theCursor);
end loop;

if ( l_theCursor is NULL or (g_parsed_queries(l_theCursor) != p_query) )
then
l_theCursor := dbms_sql.open_cursor;
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
g_parsed_queries(l_theCursor) := p_query;
end if;


dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;

-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);

-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end if;
end loop;
dbms_output.put_line( '-----------------' );
end loop;


-- now, restore the session state, no matter what
restore( l_cs, l_date_fmt );
exception
when others then
restore( l_cs, l_date_fmt );
raise;
end tbl;

end print;
/


reparse using dbms_sql

A reader, May 01, 2004 - 10:57 am UTC

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.

Tom Kyte
May 01, 2004 - 11:11 am UTC

sure there is -- you have a finite number of cursors available to your session.

if you try to open more than that, you'll be told about it via an ora error message.

reparse using dbms_sql

A reader, May 03, 2004 - 9:48 am UTC

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.


Tom Kyte
May 03, 2004 - 3:27 pm UTC

correct - -that is the way is works.

thanks

Barry Sperling, June 11, 2004 - 1:06 pm UTC

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.

Tom Kyte
June 11, 2004 - 4:33 pm UTC

sure it does.

you'll have to be more clear in what isn't working. the nls_date_format changes the default date format for input and output.

I would suggest using explict TO_DATES() with formats and TO_CHAR with formats personally.

using dbms_sql

A reader, October 14, 2004 - 9:03 pm UTC

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.

Tom Kyte
October 15, 2004 - 10:53 am UTC

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

use that technique, no dbms_sql needed, just open a ref cursor using sys_context to bind.

Small que

Yogesh, March 11, 2005 - 7:45 am UTC

What is the equivalent of

execute immediate s1 into v1 using var1; in dbms_sql?

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


Tom Kyte
March 11, 2005 - 10:49 am UTC

dbms_sql.open
dbms_sql.parse
dbms_sql.bind_variable (for the input)
dbms_sql.define_variable (for the output count)
dbms_sql.execute
dbms_sql.variable_value (to get the output)
dbms_sql.close

if you have expert one on on Oracle -- chapter on dynamic sql

if you don't, the documentation exists for this package.

count(*)

Yogesh, March 11, 2005 - 12:06 pm UTC

What should be the second argument?

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

Tom Kyte
March 12, 2005 - 8:12 am UTC

have you checked the docs?

oh well, here you go:

create or replace
function get_row_cnts( p_tname in varchar2 ) return number
as
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue number default NULL;
l_status integer;
begin
dbms_sql.parse( l_theCursor,
'select count(*) from ' || p_tname,
dbms_sql.native );

dbms_sql.define_column( l_theCursor, 1, l_columnValue );
l_status := dbms_sql.execute(l_theCursor);
if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
then
dbms_sql.column_value( l_theCursor, 1, l_columnValue );
end if;
dbms_sql.close_cursor( l_theCursor );
return l_columnValue;
end;
/



count(*)

A reader, March 12, 2005 - 1:59 pm UTC

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.


Tom Kyte
March 12, 2005 - 2:19 pm UTC

looks like your query returns zero I guess? given I cannot run your example........... and I'm really not a compiler, and even if i was, this is a runtime thing - needing your data..........

count(*)

Yogesh, March 13, 2005 - 9:30 am UTC

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.

Tom Kyte
March 13, 2005 - 10:04 am UTC

You debug it in the way you debug anything (the art of debugging our own code seems to be getting lost over time.....)


have you thought about

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

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

c) getting a debugger like jdeveloper to walk through the code

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

for starters?


And if you were on software written this century, I can turn the entire procedure into a SINGLE SQL statement.


insert into test_log
select h_crn
from external_table
where exists ( select null
from table1 c, table2 a
where c.cno = EXTERNAL_TABLE.H_CRN
and c.cno = a.cno
and a.tdate between TO_DATE('01-jan-1999','dd-mon-yyyy')
and sysdate
and a.ono not in ( select ono from test3 where .....);


And even in 8.0, I'd probably load the file into a stage table and just run a simple single query rather than write and debug code.

count(*)

Yogesh, March 14, 2005 - 5:16 am UTC

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.



Tom Kyte
March 14, 2005 - 8:07 am UTC

sorry that you are using toad. not too much I can do then.


you have a bug in the developed code.

I cannot run your code.

sqlplus is available to everyone

toad should be able to dump dbms_output.put_lines as well.

debugging is a lost art.

Debugging comment

Jim, March 14, 2005 - 3:38 pm UTC

"(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!

Two questions on this -

Nilendu, March 14, 2005 - 6:20 pm UTC

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".



Tom Kyte
March 14, 2005 - 8:05 pm UTC

1) Hmm, when opinion is backed up by a fact -- doesn't it cease being an opinion?


but in anycase -- you can bulk bind with native dynamic sql now (and it was all about BULKING UP that made dbms_sql faster -- doing more with less calls. It'll probably be "a tie" or maybe even native dynamic sql besting dbms_sql.

to do a single dynamic sql call -- native dynamic sql wins
to bulk load 1,000 rows using the same sql statement -- dbms_sql used to win

in 10g/9i using bulk binds with native dynamic sql, the tables may well have turned again.



2) Never say always, I always say....

No, it should not be "is better" -- for if you have a routine that takes 3 optional inputs, we would be perhaps best off creating where clauses like:

where a = :a and b = :b and c = :c
where b = :b
where b = :b and c = :b
where <nothing>...

based on the inputs you sent -- so we can have multiple plans -- so we can use the right index at the right time or a full scan when appropriate.

Using a single static sql statement (which would be possible) would lead to an optimal plan for one set of inputs and suboptimal for the rest.

DBMS_SQL

Mohit Kalra, March 15, 2005 - 8:57 am UTC

you are the best!

perfect explanation

Jay Mag, April 04, 2005 - 12:25 pm UTC

Thanks for providing examples and statistics.

Quick question...hopefully

Brett, April 07, 2005 - 1:51 pm UTC

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?

Tom Kyte
April 07, 2005 - 4:21 pm UTC


just:

declare
text_date varchar2(1000) := '20050323';
begin
execute immediate '..... values (:1)' using to_date(text_date,'yyyymmdd');


Unfortunately...

Brett, April 08, 2005 - 8:02 am UTC

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.

Tom Kyte
April 08, 2005 - 8:39 am UTC

can we just substr out what we need?

look for the first (
look for the last )
get that ''20050323'',''RRRRMMDD''
look for ,

now you have ''20050323'' and ''RRRRMMDD''

simple matter to use that.

Bigger Issues

Brett, April 11, 2005 - 3:44 pm UTC

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?

Tom Kyte
April 11, 2005 - 4:08 pm UTC

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")))
 
 

A reader, April 25, 2005 - 1:22 pm UTC

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.


Tom Kyte
April 25, 2005 - 1:32 pm UTC

give me a for example and a version

A reader, April 25, 2005 - 1:48 pm UTC

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


Tom Kyte
April 25, 2005 - 2:08 pm UTC

if 'y' is 'y' every time you execute this procedure...
and 'n' is 'n' every time....

then -- NO....

the only things you bind are things that change. constants should be constants.


It is almost impossible to bind incorrectly in plsql when using static sql. the only mistake you can make is "over binding" (which is where you would be going....)

A reader, April 25, 2005 - 2:32 pm UTC

Excellent! Thanks for your response.

A reader, April 25, 2005 - 2:39 pm UTC

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

1.Execute_Immediate
2.Dbms_SQL

Thanks.

Tom Kyte
April 25, 2005 - 3:08 pm UTC

depends on other factors.

In case of varying values -- I prefer static sql. Look at the above example with 'y' and 'n'.

OID varies and is compared to KEY.

Sooooo -- OID is bound in, 'y' and 'n' are left as literals. That is how I prefer to handle varying values -- with static SQL in plsql :)


Now, if you are FORCED to use dynamic sql (that is when you use it, when you have no other choice in plsql), then:

if (db_version < 10)
then
if ( statement is executed "one off" -- not expected to be executed more
than once or twice in a session )
then
execute immediate rules
else
dbms_sql to parse it ONCE and
repeated bind/execute over and over and over to avoid the parse
end if
else /* db_version >= 10 */
then
execute immediate rules as long as you do the same statement in the
execute immediate over and over, that is:
for i in 1 .. 1000
loop
execute immediate 'insert into ' || p_tname || ' values(:x)' using i;
end loop;

will parse ONCE and bind/execute 1000 times (in 9i, it would parse 1000
times! in 10g, it'll parse ONCE)


But if you have a case like this:

for i in 1 .. 1000
loop
execute immediate 'insert into ' || p_tname || mod(i,5) || ' value(:x)'
using i;
end loop;

that'll parse 1000 times (soft) since the statement changes each time (it
caches only the last statement). So, here I might parse 5 statements up
front using dbms_sql and bind/execute each 200 times....


but actually, we should be using array processing but that is another
page :)
end if;



A reader, April 25, 2005 - 3:39 pm UTC

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>



Tom Kyte
April 25, 2005 - 3:48 pm UTC

nope, it is neither syntactically correct, nor the right way to do it.


this screams "static sql"


I do not see you in any way being forced down the dynamic sql route.

A reader, April 25, 2005 - 3:52 pm UTC

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

Tom Kyte
April 25, 2005 - 4:01 pm UTC

begin
select count(*) into v_cnt
from table
where key = PLSQL_VARIABLE;

....


is the only way to go. Plsql variables referenced in sql ARE bind variables. No need for any sort of dynamic sql here at all.

A reader, April 25, 2005 - 4:05 pm UTC

Thank you

Execute Immediate

Hawk, August 29, 2005 - 3:36 pm UTC

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;

Tom Kyte
August 30, 2005 - 12:57 am UTC

laughing out loud.

"here is code that doesn't work"
"it is failing for <some> reason"
"advise how to make it work"

I don't even know what it is supposed to do! or why it fails!

so, what exactly is the code supposed to do (requirements)

and then create tables, and insert intos...

Please ignore my above request

Hawk, August 29, 2005 - 5:56 pm UTC

Its resolved, my query formation was incorrect

Thankyou.

here

Hawk, August 30, 2005 - 10:52 am UTC

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.

Tom Kyte
August 30, 2005 - 12:23 pm UTC

you are MISSING BIND VARIABLES.....

... wehre id1 = :x' USING v_id;



Thankyou

Hawk, August 30, 2005 - 1:04 pm UTC

Thankyou TOM,

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

Tom Kyte
August 30, 2005 - 1:44 pm UTC

right, but HARD CODING them into your sql insert.


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

will build something like

insert into c select id1, XXXX from XXXX where id1 = 123;

it should be:

insert into c select id1, XXXX from XXXX where id1 = :x;


and then execute immediate that with USING v_id

array binding with varchar2(4000)

Connor, October 05, 2005 - 9:39 pm UTC

vi $ORACLE_HOME/rdbms/admin/dbmssql.sql

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

Hmmmmmmmmmm

dbms_sql array fetching is broken with Varchar2(4000)

Mike, November 11, 2005 - 1:48 pm UTC

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

 

Tom Kyte
November 12, 2005 - 8:45 am UTC

can you clarify what you mean by:

"And Oracle doesn't seem to want to do anything about it." If you mean "support has said this is not a bug but an implementation restriction" - I would agree with them. It would be at that point you have them file an enhancement request to get this modified in subsequent releases.

and why "of course with billions you have to batch copy"? Why not

a) disable indexes
b) insert /*+ APPEND */ data (no undo)
c) rebuild indexes


that'll be tons faster than any procedural code you write, generate much less REDO and no undo to speak of.

I found a workaround for the above VARCHAR2(4000) problem

Mike, November 11, 2005 - 6:40 pm UTC

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?

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

Mike, November 14, 2005 - 10:50 am UTC

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".


Tom Kyte
November 14, 2005 - 1:33 pm UTC

I'll see if I can get them to change their minds since new life is breathed into dbms_sql by htmldb!


Just sent the email.

VARCHAR2(4000) CLOB binding workaround is much slower

Mike, January 19, 2006 - 9:01 pm UTC

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.

Can you explain why this happens ?

Russell Steel, July 26, 2006 - 4:44 am UTC

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 

Tom Kyte
July 26, 2006 - 10:51 am UTC

<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> 

Conditional binding

A reader, September 27, 2006 - 8:14 am UTC

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!


Tom Kyte
September 27, 2006 - 3:06 pm UTC

ORA-01031: insufficient privileges

A reader, September 28, 2006 - 2:01 am UTC

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

Tom Kyte
September 28, 2006 - 3:13 am UTC

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>

 

test case

A reader, September 28, 2006 - 7:50 am UTC

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!
 

Tom Kyte
September 28, 2006 - 7:55 am UTC

the syntax for a context is:

create or replace context CONTEXT_NAME using <schema.>thing

where thing is a package, a procedure, or a function.


your command:

create or replace context MY_CTX using test_cntxt.MY_PROCEDURE;

allows the context my_ctx to be set by the package/procedure/function named MY_PROCEDURE in the schema TEST_CNTXT.


believe you meant to just do:

create context my_ctx using test_cntxt;


then, anything in that package - test_cntxt - can set the context.

Thanks for the ultra quick response!!!!

A reader, September 28, 2006 - 8:05 am UTC

Thanks for the ultra quick response!!!!

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

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

problem in using DBMS_SQL ...

Amit, January 16, 2007 - 4:59 pm UTC

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 ...

dynamic sql? Please help.

Jay, September 24, 2007 - 1:30 pm UTC

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 Kyte
September 26, 2007 - 8:24 pm UTC

no, we want to run A SINGLE QUERY that returns two years worth of data.

and in order to do that, I'll need to see the original query.

previous question.. continuation

Jay, September 24, 2007 - 2:04 pm UTC

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!
Tom Kyte
September 26, 2007 - 8:33 pm UTC

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

col_type in Describe columns

A reader, April 10, 2008 - 3:03 pm UTC

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,
Tom Kyte
April 10, 2008 - 3:19 pm UTC

I'd use the decode found in dba_tab_cols - this is from 10g:

       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'),


that way, the code is almost done for you :)

Got Confused !!!

A reader, April 10, 2008 - 3:46 pm UTC

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..
Tom Kyte
April 10, 2008 - 3:50 pm UTC

1) i gave you a decode that decodes the type# into a name???

... decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), ...

so, type = 1 is either a varchar2 or nvarchar2 - depending on the charsetform value (which dbms_sql returns as well)

2) see that big decode?? It does just that - turns a type# into a human readable string...

I'm suggesting you steal the decode from dba_tab_cols - it converts all of this stuff. You'd want to re-steal it with each release as new datatypes are added over time...

Now I got It ..

A reader, April 10, 2008 - 4:32 pm UTC

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..

A more effective alternative

Amit Nair, November 10, 2008 - 2:58 am UTC

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!

Tom Kyte
November 11, 2008 - 3:34 pm UTC

indeed - yes, that makes sense, avoid calling plsql from sql, just bind.

Dynamic bulk insert using collections

Kyle, December 02, 2008 - 3:32 pm UTC

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?

Tom Kyte
December 09, 2008 - 9:23 am UTC

you have to use SQL types - not plsql types - in native dynamic sql. You can only bind sql types to sql.



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


Question

A reader, March 08, 2009 - 5:39 pm UTC

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.
Tom Kyte
March 09, 2009 - 3:47 am UTC

well, that is a bug isn't it. should be return l_status and l_columnValue didn't belong there at all.

should have seen that in the output:

  9  end;
 10  /
Updated  rows
its rowid was AAAGnuAAFAAAAESAAA



updated <blank> rows.... no one else caught that in 8 years...

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library