opening cursor with dinamic sql in sore procedure

[Michali]

Hello all
i am working on orecle v 9.2 client. (server v 9.2)

i have tried un succefuly to open the cursor with execute immediate while having a dinamic sql.
this is my procedure:

CREATE OR REPLACE PROCEDURE P_415 (
r_415_Cursor IN OUT pa_rpt_415.rpt_415_Type,
param1 IN ent_card.date_created%TYPE,
param2 IN ent_card.date_created%TYPE)
AS
sString varchar2(5000);
PeopleReqCount number;
TotalRecvd number;
TotalRcvdNew number;
TotalRIssued number;
TotalDeclineSecurity number;
TotalDeclineInvit number;
TotalDeclinCryteria number;
TotalNonExpierd number;
TotalNonExpierdWemen number;
BEGIN
OPEN r_415_Cursor FOR
execute immediate 'select * ' || sString into PeopleReqCount,
TotalRecvd ,TotalRcvdNew
,TotalRIssued,TotalDeclineSecurity,TotalDeclineInvit
,TotalDeclinCryteria ,TotalNonExpierd ,TotalNonExpierdWemen ;
thank u
michal

 

[sem]

Wrong syntax. You mixed up 2 different approaches. Assuming sString contains FROM clause:

execute immediate 'select * ' || sString
into TotalRecvd ,TotalRcvdNew
,TotalRIssued,TotalDeclineSecurity,TotalDeclineInvit
,TotalDeclinCryteria ,TotalNonExpierd ,TotalNonExpierdWemen;

or

open r_415_Cursor FOR 'select * ' || sString;
fetch r_415_Cursor into TotalRecvd ,TotalRcvdNew
,TotalRIssued,TotalDeclineSecurity,TotalDeclineInvit
,TotalDeclinCryteria ,TotalNonExpierd ,TotalNonExpierdWemen;

Regards, Dima

 

[Michali]

thank u so very much.