Re: sp and data convertion

Thanks,
By "EXEC IMMEDIATE" which is not found in Oracle Press documentation, you mean:
"Starting a statement with BEGIN (or with DECLARE) you get immediate access to the PL/SQL engine and to its EXECUTE IMMEDIATE syntax (just make sure to add the closing END)"

Just taka a look at this

TYPE CURSOR_TYPE IS REF CURSOR;
RECORD_SET CURSOR_TYPE;
c_row products%ROWTYPE;

mystr VARCHAR2(50) := '1,2,10,11,12,20,21,30,123'; myqstr VARCHAR2(300) := 'SELECT products.* FROM products WHERE product_id IN(' || mystr || ')';

BEGIN
      OPEN RECORD_SET FOR myqstr;

• works fine
• but OPEN RECORD_SET FOR SELECT products.* FROM products WHERE product_id IN(mystr);
• does not, strange

How about returning a recordset from dynamic sql? I have found how to fetch one row or to bind variables only. L.J.

"Barbara Kennedy" <barbken_at_teleport.com> wrote in message news:tO8%5.439$V4.453004_at_nntp3.onemain.com...
> You could use dynamic sql. Just construct the query and concat the
 myparam
> in there so the result would be:
> 'select .... from ... where id in ('||myparam||')'
> see the dbms_sql package or if you are using Oracle 8i the execute
 immediate
> I believe.
> "Lee J." <ljeznach_at_csi.com> wrote in message
> news:G1c_5.81895$_5.17624360_at_news4.rdc1.on.home.com...
> > Hello,
> > I have one procedure
> >
> > PROCEDURE SP_TEST(RECORD_SET OUT CURSOR_TYPE, MYPARAM VARCHAR2)
> > AS ...
> > MYPARAM can hold as many characters as VARCHAR2 data type allows in the
> > format '1,2,3,22,500'
> > I would like to use it in my query where e.g. SELECT * FROM ... WHERE id
 IN
> > (MYPARAM)
> > Of course, it will not work because my parameter is of VARCAR2 type.
> > As fare, as I know there is no function that would convert my parameter
 to
> > anything that would work with my query.
> > If any of you guys has any idea how to make it working then I v.
 appreciate
> > your help.
> > Thanks.
> >
> >
> >

>
> Received on Mon Dec 18 2000 - 18:06:07 CST