Terimakasih sarannya, Akhirnya berhasil dengan menggunakan function array to table of varchar dengan tetapi menggunan SYS_CONTEXT .
Salam On 4/23/09, Awaluddin Hamid <[email protected]> wrote: > Yulius Wibowo wrote: >> >> >> Buat dan gunakan fungsi yg mengubah array menjadi string dengan >> delimiter koma. >> >> Contoh: >> Input:: >> x(1) = 'ABC' >> x(2) = 'DEF' >> x(3) = 'HIJ' >> >> Output:: >> ABC,DEF,GHI >> >> create or replace function array_to_str(x in list_acc) >> is >> y VARCHAR2(1000); >> begin >> for i in x.first..x.last loop >> y := y || ',' || x(i); >> end loop; >> y := substr(y,2); >> return(y); >> end; >> / >> >> dbms_session. set_context( namespace => 'var_dr', attribute => >> 'account_id' , *value => array_to_str(account_id)*) ; >> >> CMIIW, >> bw >> >> --- In [email protected] >> <mailto:indo-oracle%40yahoogroups.com>, zainal arifin >> <zainal.em...@...> wrote: >> > >> > ada yang bisa ngasih workaround nya gak ya. >> > goal nya saya buat parameterized view.. tetapi salah satu parameternya >> > sebenarnya adalah >> > where customer_id in (:parameter) dimana :parameter ini adalah array >> dari >> > varchar2 >> > >> > >> > wassalam >> >> >> >> __,_.____ > > > Bisa juga dengan menggunakan parameter diambil dari package (bukan dari > SYS_CONTEXT). > > SQL> CREATE OR REPLACE TYPE var_typ AS TABLE OF VARCHAR2(9) > 2 / > Type created. > > SQL> CREATE OR REPLACE PACKAGE param_pack AS > 2 PROCEDURE set_record(v_typ var_typ); > 3 FUNCTION get_record RETURN var_typ; > 4 END; > 5 / > Package created. > > SQL> CREATE OR REPLACE PACKAGE BODY param_pack AS > 2 var_tab var_typ; > 3 PROCEDURE set_record(v_typ var_typ) IS > 4 BEGIN > 5 var_tab := v_typ; > 6 END; > 7 FUNCTION get_record RETURN var_typ IS > 8 BEGIN > 9 RETURN var_tab; > 10 END; > 11 END; > 12 / > Package body created. > > SQL> SELECT * FROM cities > 2 / > REGION COUNRTY CITY > ------------------------------ ------------------------------ > ---------------- > AMERICA UNITED STATED NEW YORK > EUROPE FRANCE PARIS > ASIA JAPAN TOKYO > ASIA INDIA MUMBAI > EUROPE UNITED KINGDOM LONDON > AMERICA UNITED STATES WASHINGTON DC > > SQL> CREATE OR REPLACE VIEW vw_cities AS > 2 SELECT * FROM cities > 3 WHERE region IN ( > 4 SELECT COLUMN_VALUE FROM THE ( > 5 SELECT CAST(param_pack.get_record AS var_typ) FROM DUAL)) > 6 / > View created. > > SQL> EXEC param_pack.set_record(var_typ('ASIA','EUROPE')) > > PL/SQL procedure successfully completed. > > SQL> SELECT * FROM vw_cities > 2 / > REGION COUNRTY CITY > ------------------------------ ------------------------------ ------- > ASIA INDIA MUMBAI > ASIA JAPAN TOKYO > EUROPE UNITED KINGDOM LONDON > EUROPE FRANCE PARIS > > > CMIIW, > AH >
