Re: Table Function in Open For

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<slrncm3333.31k.rene.nyffenegger_at_zhnt60m34.netarchitects.com>...
> In article <7c101571.0410040854.2a3c472d_at_posting.google.com>, Claudy wrote:
> > Hello,
> >
> > I'm a PL/SQL beginner and I would like launch a procedure that call a
> > table function with dynamicals parameters. But I receive no error and
> > no records in my cursor. But when I enter the call in the commend line
> > the select runs well.
> > Who can me help?
> >
> > Thanks!
> > Claudy
> >
> > My test procedure is:
> >
> > DECLARE
> > lcSelect VARCHAR2(1000);
> > lcHochkomma VARCHAR(4):='''';
> > TYPE Sucherg_type IS REF CURSOR;
> > Sucherg_cur Sucherg_type;
> > TYPE Sucherg_rec_type IS RECORD (...)
> > Sucherg_rec Sucherg_rec_type ;
> > lncounter NUMBER (8);
> >
> > BEGIN
> > --sample-call on the command line
> > --select * from table(FindPersInst('A',0,'A',100,0,0,0,70179013000,'','','','','','','',''));
> >
> > lcSelect:='select * from table(...)';
> > Dbms_Output.PUT_LINE(lcSelect);
> >
> > OPEN Sucherg FOR lcSelect;
> > lncounter:=0;
> >
> > --But Sucherg_cur%ROWCOUNT=0 -> WHY?????????????????
> > --lcSelect has expected value
> > Dbms_Output.PUT_LINE(To_Char(Sucherg_cur%ROWCOUNT));
> > LOOP
> > lncounter:=lncounter+1;
> > FETCH Sucherg_cur INTO Sucherg_rec;
> > .
> > .
> > .
> > END LOOP;
> > CLOSE Sucherg_cur;
> >
> > END;
>
>
> Two things:
> 1. There is no slash after the final END; did you omit it in
> this posting or did you forget to enter it in SQL*Plus?
>
> 2. Have you set serveroutput on in SQL*Plus?
>
> hth
> Rene

Hello Rene!

I use a free SQL Tools 1.4 1 Beta build 55 for my PL/SQl-programming. The Tool is super, better than SQL*Plus.

1.I don't know nothings about slash after the final END. The other programs work without this slash.
2.Yes, I have set serveroutput in environment.

Thank's
Claudy Received on Thu Oct 07 2004 - 10:00:01 CDT