Thanks that does indeed work exactly as you state.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Martin Burbridge" <pobox002_at_bebub.com> wrote in message
news:Xns935FE7313EF7Apobox002bebubcom_at_216.148.227.77...
> "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 Thu Apr 17 2003 - 03:24:11 CDT
