Another example
DROP TYPE wa_itm_tbltyp
/
CREATE OR REPLACE TYPE wa_itm_rectyp AS OBJECT
( LICENSE_ITM_CD NUMBER(12)
,TAG_ITM_CD NUMBER(12)
,VIOLATION_DESC VARCHAR2(100)
)
/
CREATE or REPLACE TYPE wa_itm_tbltyp AS TABLE
of wa_itm_rectyp
/
BEGIN --{
l_err_cd := 40700;
FOR cx IN
(
SELECT 18 license_item FROM DUAL
UNION
SELECT 19 license_item FROM DUAL
)
LOOP --{
l_viol_desc := 'My violation
description';
FOR cy IN
(
SELECT 70 tag_item FROM DUAL
UNION
SELECT 71 tag_item FROM DUAL
UNION
SELECT 72 tag_item FROM DUAL
)
LOOP --{
buf_ctr := buf_ctr + 1;
buf_data.EXTEND;
buf_data(buf_ctr) :=
wa_itm_rectyp(cx.license_item,cy.tag_item,l_viol_desc);
END LOOP; --}
END LOOP;--}
END; --}
FOR curr_yr_lic IN
(
SELECT col1, col2, col3
FROM tbl1 , tb2
WHERE
condition1
AND (itm_cd) IN
(
( SELECT DISTINCT x.license_itm_cd
FROM
TABLE ( CAST( buf_data AS
wa_itm_tbltyp ) ) x
)
)
)
LOOP --{
do the needful;
END LOOP; --}

HTH

GovindanK
--- [Email Address Removed] wrote: > I need to make a variable
inlist. cant quite get this to
> work. any ideas?
>
> below is not the functionality i need it for. Im just
> trying to figure out how to do a cast function. any
> ideas?
>
> 1 declare
> 2 type mytable is table of number;
> 3 ltable mytable;
> 4 myval number;
> 5 cursor crid is
> 6 SELECT col_id
> 7 FROM tab1
> 8 WHERE col2 = 1111;
> 9 begin
> 10 for rid in crid loop
> 11 ltable.extend;
> 12 ltable(ltable.count) := rid.col_id;
> 13 dbms_output.put_line(ltable.count);
> 14 end loop;
> 15 select count(*)
> 16 into myval
> 17 from tab2
> 18 where my_id in
> 19 ( select *
> 20 from THE (select cast (ltable as mytable)
> 21 from dual)a );
> 22 dbms_output.put_line(myval);
> 23* end;
> ORACLE>/
> from THE (select cast (ltable as mytable)
> *
> ERROR at line 20:
> ORA-06550: line 20, column 25:
> PLS-00382: expression is of wrong type
> ORA-06550: line 15, column 1:
> PL/SQL: SQL Statement ignored
>
>


__________________________________________________
Yahoo! Plus - For a better Internet experience
http://uk.promotions.yahoo.com/yplus/yoffer.html
Oracle LazyDBA home page