sorry pak...rada2 londo dikit :) it seems couldnot using array.... one more case is the data type should match during DML by execute immediate....
l_str3 := ' l_str_var2(1),l_str_var2(2),l_ str_var2(3),l_str_var2(4),l_str_var2(5),l_str_var2(6),l_str_var2(7),l_str_var2(8)'; execute immediate l_str2 using l_str3; UPDATE emp SET ename =:b1 ,hiredate =:b2 ,sal =:b3 WHERE empno =:b4 AND deptno = :b5 AND job =:b6 AND ENAME =:b7 AND SAL =:b8 using l_str_var2(1),l_str_var2(2),l_str_var2(3),l_str_var2(4),l_str_var2(5),l_str_var2 (6),l_str_var2(7),l_str_var2(8) declare * ERROR at line 1: ORA-01008: not all variables bound ORA-06512: at line 85 this code works, but it should be hardcoded after 'USING'.... when all the string l_str_201 till l_str208 bound to a variable... got complains that missing expressions.... execute immediate l_str2 using l_str201,l_str202,l_str203,l_str204,l_str205,l_str206,l_str207,l_str208; UPDATE emp SET ename =:b1 ,hiredate =:b2 ,sal =:b3 WHERE empno =:b4 AND deptno = :b5 AND job =:b6 AND ENAME =:b7 AND SAL =:b8 using l_str201,l_str202,l_str203,l_str204,l_str205,l_str206,l_str207,l_str208 PL/SQL procedure successfully completed. 2010/3/4 yulius_wibowo <[email protected]> > > > > 1. Pada nilai parameter yg dimasukkan, seharusnya utk nilai character > digunakan tanda petik (') > Jadi pada ['UPDATE emp SET ename = xxx ,hiredate = SYSDATE ,sal = 100 WHERE > empno = 7934 AND deptno IN(10,20) AND job = CLERK AND ENAME = MILLER AND > SAL= 1300'] > seharusnya ['UPDATE emp SET ename = xxx ,hiredate = SYSDATE ,sal = 100 > WHERE empno = 7934 AND deptno IN(10,20) AND job = ''CLERK'' AND ENAME = > ''MILLER'' AND SAL= 1300'] > > 2. Utk nilai setelah USING, masukkan nilainya ke dalam varray terlebih dulu > (extendable array, pada penambahan ":bxxx"), > kemudian nantinya di LOOP dan dimasukkan ke dalam sebuah variable (mis: > v_using .Di concat dgn menggunakan tanda koma (,)), > setelah itu dimasukkan ke dalam EXECUTE IMMEDIATE ... USING v_using > > cmiiw, > Bowo > > > --- In [email protected] <indo-oracle%40yahoogroups.com>, > "ora...@..." <ora...@...> wrote: > > > > mungkin ada yg bisa bantu, potongan kode ini untuk parsing suatu text > > sebagai parameter procedure. > > ini dalam case dimana aplikasi sudah give up dgn bind variable :) > > > > sehingga mau diakali yg melakukan bind dari sisi oracle dgn procedure, > namun > > demikian text parameter nantinya dinamis, so 1 prosedur ini untuk semua > > query baik select maupun dml. > > > > code di bawah ini sudah bekerja dgn baik, tapi sayangnya masih harus > > hardcode dalam sintaks execute immediate setelah keyword USING :) > > > > jika dipaksa array yg dimasukan kedalam USING, ada masalah pula dgn > > perbedaan variable dalam runtime nantinya.... duhhhhh > > > > mungkin pak bowo mau ngasih pencerahan? :) > > > > > > 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 = 100 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_str201 varchar2(400); > > l_str202 date := sysdate; > > l_str203 varchar2(400); > > l_str204 varchar2(400); > > l_str205 number; > > l_str206 varchar2(400); > > l_str207 varchar2(400); > > l_str208 varchar2(400); > > 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||':b'||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(:b'||n||')'; > > l_str2 := l_str2||' = :b'||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_str201 := l_str_var2(1); > > l_str203 := l_str_var2(3); > > l_str204 := l_str_var2(4); > > l_str205 := substr(l_str_var2(5),1,2); > > l_str206 := l_str_var2(6); > > l_str207 := l_str_var2(7); > > l_str208 := l_str_var2(8); > > l_str3 := ' > > l_str201,l_str202,l_str203,l_str204,l_str205,l_str206,l_str207,l_str208'; > > > > dbms_output.put_line(l_str2 ||' using '||l_str3); > > execute immediate l_str2 using > > l_str201,l_str202,l_str203,l_str204,l_str205,l_str206,l_str207,l_str208; > > end; > > / > > > > > > [Non-text portions of this message have been removed] > > > > > -- thanks and regards ujang | oracle dba | mysql dba jakarta - indonesia [Non-text portions of this message have been removed] ------------------------------------ -- -----------I.N.D.O - O.R.A.C.L.E--------------- Keluar: [email protected] Website: http://indooracle.wordpress.com http://www.facebook.com/group.php?gid=51973053515 ----------------------------------------------- Bergabung dengan Indonesia Thin Client User Groups, Terminal Server, Citrix, New Moon Caneveral, di: http://indo-thin.blogspot.comYahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/indo-oracle/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/indo-oracle/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
