Re: Variable In List in PL/SQL

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in news:3e9d66e1$0$29717$ed9e5944_at_reading.news.pipex.net:

> I'd like to produce a procedure (or more than one) that can parse a
> comma delimited string and use it as a variable in-list in PL/SQL on
> 8i std edition. Jonathan's neat use of objects is therefore out, but I
> was hoping that the code below would work However it errors out with
> ora-00902 invalid datatype for the select. I've now been staring at
> this for a couple of hours and so cannot see my , no doubt daft,
> error. If anyone can cast an eye over it, or suggest a better method
> that would be great. constraints are, has to be in a package so it can
> return a ref cursor to an asp page and has to run on 8i/9i std.
>
> Create or replace package DWPack
>
> as
>
> Type DWPack_ListArray is table of varchar2(500);
>

Hello Niall,

Everything else can stay in the package but this type has to come out, the rest of the code should work as is. You should create it in SQL as

create or replace type

    DWPack_ListArray is table of varchar2(500); /

That should do it I think.

>
> and t2.dataset_id in
>
> (
>
> select * from THE
>
> (
>
> select cast(ParseInList(i_percentageList) as DWPack_ListArray) from
> dual
>
> )
>
> )
>

I think in 8i you can also simplify this to

select * from TABLE

(

cast(ParseInList(i_percentageList) as DWPack_ListArray)

)

And as I just found out in 9i it could become

select * from table(ParseInList(i_percentageList))

Hth
Martin Received on Wed Apr 16 2003 - 21:43:18 CDT