execute immediate parsing update

From: Ujang Jaenudin <ujang.jaenudin_at_gmail.com>
Date: Tue, 2 Mar 2010 19:47:52 +0700
Message-ID: <3edcb66e1003020447r9820869w35b1205d52791ff3_at_mail.gmail.com>

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

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

Received on Tue Mar 02 2010 - 06:47:52 CST