Sorry masih belum bisa euy... Error yg didapat: declare * ERROR at line 1: ORA-01008: not all variables bound ORA-06512: at line xxx
Hhhmmmm ..... --- In [email protected], "yulius_wibowo" <yulius_wib...@...> wrote: > > > 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], "oracle@" <oracle@> 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] > > >
