Re: Table Function in Open For

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

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/