execute immediate parsing update

From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 2 Mar 2010 19:47:52 +0700
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('<coll><e>' ||
    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',' ','</e><e>') ||
    '</e></coll>'), '/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;

        <<next_loop>>
        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
execute immediate parsing update

Other related posts:
