FAQ
dear lists,

The application using dynamic sql which will flood library cache with SQL
for different entry.
I'm trying to utilize execute immediate and bind it as below.... but stuck
:(

set serveroutput on
declare
type str_var2 is varray(100) of varchar2(100);
l_str_var2 str_var2 := str_var2();
cursor c1 is SELECT EXTRACTVALUE(xt.column_value,'e') str
FROM TABLE(XMLSEQUENCE

( EXTRACT

( XMLTYPE('' ||
REPLACE('UPDATE emp SET ename = xxx,hiredate = SYSDATE,sal = 100000
WHERE empno = 7934 AND deptno IN(10,20) AND job = CLERK AND ENAME = MILLER
AND SAL = 1300',' ','') ||

''), '/coll/e') )) xt;

l_str1 varchar2(4000);
l_str2 varchar2(4000);
l_str3 varchar2(4000);
l_dummy pls_integer;
l_flag pls_integer;

n pls_integer;
m pls_integer;
p_rowid varchar2(100);
begin

n := 1;
l_flag := 0;
open c1;
loop

fetch c1 into l_str3;
exit when c1%notfound;
if l_flag = 1 then
l_str_var2.extend(n);
l_str_var2(n) := l_str3;
l_str2 := l_str2||':'||n;
n := n+1;
l_flag :=0;
goto next_loop;
end if;

if trim(l_str3) = '=' or trim(substr(l_str3,1,3)) ='IN(' then
l_flag := 1;
end if;

if trim(substr(l_str3,1,3)) ='IN(' then
l_flag := 2;
m := length(l_str3)-2;
l_str_var2(n) := substr(l_str3,4,m-2);
l_str2 := l_str2||' IN(:'||n||')';
l_flag := 0;
n := n+1;
goto next_loop;
end if;

l_str2 := l_str2 ||' '|| l_str3;

<>
l_dummy :=0;

end loop;
close c1;

l_str2 := l_str2 ||' returning rowid into :out using ';
l_str3 := '';
for i in 1..n-1 loop

if i=n-1 then
l_str3 := l_str3 ||''''||l_str_var2(i)||'''';
else
l_str3 := l_str3 ||''''||l_str_var2(i)||''''||',';
end if;

end loop;
l_str2 := l_str2 ||l_str3;
--|| substr(l_str3,1,length(l_str2)-1);
l_str2 := l_str2 || ' returning into p_rowid';
dbms_output.put_line(l_str2);
execute immediate l_str2; --line 68
end;
/

UPDATE emp SET ename =:1,hiredate =:2,sal =:3 WHERE empno =:4 AND deptno
IN(:5) AND job =:6 AND ENAME =:7 AND SAL =:8
returning rowid into :out using
'xxx','SYSDATE','100000','7934','10,20','CLERK','MILLER','1300' returning
into p_rowid
declare
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at line 68

even if I change the xx,sysdate and so on with array variable...got error
too...

--
thanks and regards
ujang | oracle dba | mysql dba
jakarta - indonesia

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 2, '10 at 12:47p
activeMar 2, '10 at 12:47p
posts1
users1
websiteoracle.com

1 user in discussion

Ujang Jaenudin: 1 post

People

Translate

site design / logo © 2022 Grokbase