Table Function in Open For

From: Claudy <claudia.przioda_at_gmx.de>
Date: 4 Oct 2004 09:54:31 -0700
Message-ID: <7c101571.0410040854.2a3c472d@posting.google.com>

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; Received on Mon Oct 04 2004 - 11:54:31 CDT