Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? (Oracle 10.2.0.5, Solaris 10 x86-64)
| PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #476463] |
Thu, 23 September 2010 08:27  |
sojo
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
Hi,
I'm trying to optimize an application running heavy updates/inserts/deletes, by having it using bind variables instead of "string queries".
The columns to be updated can vary (possibly from one column to all columns of a table), thus I have made som logic to build the query accordingly. My problem is now that I cant get the EXECUTE below to handle the VARRAY passed in the USING clause, it fails with "PLS-00457: expressions have to be of SQL types"
Environment:
create table table_x (a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10));
insert into table_x values('a','b','c','d');
commit;
Code, simplified with static number of columns:
declare
type v is varray(10) of varchar2(20);
v_values v:=v('A','B','c');
myupdate varchar2(2000);
begin
myupdate:='update table_x set a=:a, b=:b where c=:c';
dbms_output.put_line(myupdate);
execute immediate myupdate using v_values;
end;
I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY. Does anyone have a clue what I am missing/doing wrong?
|
|
|
|
|
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #476537 is a reply to message #476463] |
Thu, 23 September 2010 14:53  |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
sojo wrote on Thu, 23 September 2010 09:27I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY. Does anyone have a clue what I am missing/doing wrong?
You can't use PL/SQL declared types. Use something like:
SQL> select *
2 from table_x
3 /
A B C D
---------- ---------- ---------- ----------
a b c d
SQL> declare
2 v_values sys.OdciVarchar2List := sys.OdciVarchar2List('A','B','c');
3 myupdate varchar2(2000);
4 begin
5 myupdate := 'declare
6 v_values sys.OdciVarchar2List;
7 begin
8 v_values := :1;
9 update table_x
10 set a=v_values(1),
11 b=v_values(2)
12 where c=v_values(3);
13 end;';
14 dbms_output.put_line(myupdate);
15 execute immediate myupdate using v_values;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> select *
2 from table_x
3 /
A B C D
---------- ---------- ---------- ----------
A B c d
SY.
[Updated on: Tue, 28 September 2010 23:30] by Moderator Report message to a moderator |
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #477075 is a reply to message #476537] |
Tue, 28 September 2010 05:52 |
sojo
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
Thanks SY, but not quite what I am after. I would like the query in line 9-12 to use bind variable, thus reading something like:
9 update table_x
10 set a=:a,
11 b=:b
12 where c=:c;
...
15 execute immediate myupdate using v_values;
My current solution (which works, but will be a pain to maintain when the numbers of columns that might be updated is around 50 and might increase) looks like this:
--i=number of values in v
if i=1 then execute immediate myupdate using v_values(1);end if;
if i=2 then execute immediate myupdate using v_values(1),v_values(2);end if;
if i=3 then execute immediate myupdate using v_values(1),v_values(2),v_values(3);end if;
--and so on, and so on...
Is it possible to achive what I am asking, to populate the bind variables in the query by only using one object in the USING clause (and thereby remove the need for my 50 if-thens above)?
Also, another detail have shown up, one table is having a CLOB column which I will have to investigate further how to be able to cope whith that datatype as well...
[Updated on: Tue, 28 September 2010 07:26] Report message to a moderator |
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #477121 is a reply to message #477075] |
Tue, 28 September 2010 16:08 |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I believe that Solomon gave you what you asked for, but you are just not recognizing it. If your objective is to use bind variables in order to save hard parsing to increase efficiency, then I believe it does that. The bind variable :1 is in this line:
The myupdate generated consists of an entire pl/sql block from declare to end. The dbms_output of that block does not show any hard-coded values:
declare
v_values sys.OdciVarchar2List;
begin
v_values := :1;
update table_x
set a=v_values(1),
b=v_values(2)
where c=v_values(3);
end;
The dynamic execution only uses one object:
execute immediate myupdate using v_values;
It may be slightly confusing that v_values was used in the generated block and the outer block. In the following example, I have replaced v_values with v_values1 in the outer block and v_values with v_values2 in the inner block in an attempt to clarify that the generated block in the update statement is using the bound values in values2 and the using clause is applying the values from values1 in the outer static block.
SCOTT@orcl_11gR2> select * from table_x
2 /
A B C D
---------- ---------- ---------- ----------
a b c d
1 row selected.
SCOTT@orcl_11gR2> declare
2 v_values1 sys.OdciVarchar2List := sys.OdciVarchar2List('A','B','c');
3 myupdate varchar2(2000);
4 begin
5 myupdate := 'declare
6 v_values2 sys.OdciVarchar2List;
7 begin
8 v_values2 := :1;
9 update table_x
10 set a=v_values2(1),
11 b=v_values2(2)
12 where c=v_values2(3);
13 end;';
14 dbms_output.put_line(myupdate);
15 execute immediate myupdate using v_values1;
16 end;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select * from table_x
2 /
A B C D
---------- ---------- ---------- ----------
A B c d
1 row selected.
SCOTT@orcl_11gR2>
[Updated on: Tue, 28 September 2010 23:28] by Moderator Report message to a moderator |
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #477151 is a reply to message #477121] |
Wed, 29 September 2010 01:22 |
sojo
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
Rookie as I am, please be patient with my very limited (so far, but hopefully increasing) knowledge... =)
I now (think I) realize what is happening here, but for the sake of clarity I will try to explain it, and please feel free to confirm/deny the correctness of it.
The complete "declare...begin...end" clause is parsed/compiled (and cached), when called by the execute immediate, and therefore it is possible to send the OdciVarchar2List into the code, rather than to the specific query part of the code.
My mistake was that I thought that only the query (within the total code) was the part being parsed/cached, and for some reason I did not "see" that the query anyway was written "statically"...
SY, my apologies, and thank you again, I will look further into this solution.
Barbara, thank you for making me read this again, and really think/understand.
As I also mentioned in the beginning, I have some logic for building up the query to handle an unknown number of columns, so I will still be parsing a high amount (currently any combination and number of a total of 55 columns) of queries through this code, but I guess it will still be better then having to parse each and every update call, as is the current situation.
|
|
|
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #477246 is a reply to message #477154] |
Wed, 29 September 2010 12:50 |
|
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following example combines the method provided by Solomon with the concepts in the article by Tom Kyte that Michel posted twice. I have separated the values into two sets of variables for binding, those that will be used in the set clause, and those that will be used in the where clause. The idea is that you create one update statement using all 55 columns with nvl applied to each column. Then you pass however many variables you need to with null values for the missing values. This way no matter how many columns you are updating or how many where conditions you have, or how many different values you use, you are still running the same update statement without re-parsing. I have demonstrated with only 4 columns, but indicated where you would need to increase to 55 columns.
SCOTT@orcl_11gR2> select * from table_x
2 /
A B C D
---------- ---------- ---------- ----------
a b c d
1 row selected.
SCOTT@orcl_11gR2> declare
2 v_set1 sys.OdciVarchar2List :=
3 sys.OdciVarchar2List
4 ('A','B');
5 v_where1 sys.OdciVarchar2List :=
6 sys.OdciVarchar2List
7 (null, null, 'c');
8 myupdate varchar2(2000);
9 begin
10 while v_set1.count < 55 loop
11 v_set1.extend;
12 v_set1(v_set1.count) := null;
13 end loop;
14 while v_where1.count < 55 loop
15 v_where1.extend;
16 v_where1(v_where1.count) := null;
17 end loop;
18 myupdate := 'declare
19 v_set2 sys.OdciVarchar2List;
20 v_where2 sys.OdciVarchar2List;
21 begin
22 v_set2 := :1;
23 v_where2 := :2;
24 update table_x
25 set a = nvl (v_set2(1), a),
26 b = nvl (v_set2(2), b),
27 c = nvl (v_set2(3), c),
28 d = nvl (v_set2(4), d)
29 -- and so on for all 55 columns
30 where a = nvl (v_where2(1), a)
31 and b = nvl (v_where2(2), b)
32 and c = nvl (v_where2(3), c)
33 and d = nvl (v_where2(4), d);
34 -- and so on for all 55 columns
35 end;';
36 -- dbms_output.put_line(myupdate);
37 execute immediate myupdate using v_set1, v_where1;
38 end;
39 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> select * from table_x
2 /
A B C D
---------- ---------- ---------- ----------
A B c d
1 row selected.
SCOTT@orcl_11gR2>
[Updated on: Wed, 29 September 2010 13:06] Report message to a moderator |
|
|
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #477317 is a reply to message #477248] |
Thu, 30 September 2010 02:59 |
sojo
Messages: 7
Registered: September 2006
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 29 September 2010 21:19
Ha ha ha, didn't realize that I already posted it. I'm quite constant in my advices... 
Regards
Michel
Rather that than the opposite... =)
I read the article, and although I get the basics, I do not feel it will meet my needs this time:
-if the application is sending an "update table set column=null", I will not get the correct update, but rather the old value
-although he writes that redo/undo impact is small if indexes are not updated, in my case I (currently) do not have the columns indexed, but rather PK:ed (which will not change), so to my setup, this looks like "unnecessary column updates" causing redo (which is the other thing I am looking to minimize)
|
|
|
|
| Re: PL/SQL:How to use VARRAY in the USING clause of EXECUTE IMMEDIATE? [message #477323 is a reply to message #477317] |
Thu, 30 September 2010 03:42 |
 |
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:-if the application is sending an "update table set column=null", I will not get the correct update, but rather the old value
Yes this is the behaviour the article wants, if yours is different then change the condition test.
Quote:this looks like "unnecessary column updates" causing redo (which is the other thing I am looking to minimize)
This is the cost to pay to have this variability flexibility with simple code. What do you expect?
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Aug 25 23:04:23 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
