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
