Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> 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
![]() |
![]() |