Home » SQL & PL/SQL » SQL & PL/SQL » If clause in cursors
If clause in cursors [message #222288] Fri, 02 March 2007 12:18 Go to next message
saaba
Messages: 2
Registered: March 2007
Junior Member
Hi

I am trying to have a if statement inside a cursor clause. But it gives me error saying that "PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( select <a SQL statement>" I know this is not the right syntax so is there any way that I can specify the table that the cursor should get its information from based in a if clause. The typeID field tells me which table to go to to get the information from. So if typeId=1 then go to table1 if it is type=2 then go to table2 etc.....

<Code>
CREATE OR REPLACE PROCEDURE SP_SYNOPTEX_GENERATE_REPORT(
typeID IN NUMBER
) IS

data varchar2(16);
dataresult varchar2(16);

cursor c1 is
IF typeID=1 then
select value from TableName#1
ELSE
select value from TableName#2
END IF;
where id='1';

begin

open c1;
dataresult:='';
loop
fetch c1 into dataresult;
EXIT WHEN c1%NOTFOUND;
data:=concat(to_char(data), to_char(dataresult));
end loop;
close c1;
end
</code>

Thanks for your help
Re: If clause in cursors [message #222290 is a reply to message #222288] Fri, 02 March 2007 12:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The responses here will be similar to your other thread
http://www.dbforums.com/showthread.php?t=1615666

Re: If clause in cursors [message #222566 is a reply to message #222290] Mon, 05 March 2007 07:39 Go to previous message
t_santhi
Messages: 2
Registered: June 2006
Junior Member
hi,
use the ref cursors for this .

CREATE OR REPLACE PROCEDURE SP_SYNOPTEX_GENERATE_REPORT(
typeID IN NUMBER
) IS

data varchar2(16);
dataresult varchar2(16);
QRYSTR VARCHAR2(400);
TYPE REF_CUR IS REF CUSOR;
C1 REF_CUR;

begin
IF typeID=1 then
QRYSTR:='select value from TableName#1';
ELSE
QRYSTR:='select value from TableName#2';
END IF;
QRYSTR:=QRYSTR||' where id=''1'';

open c1 FOR QRYSTR;
dataresult:='';
loop
fetch c1 into dataresult;
EXIT WHEN c1%NOTFOUND;
data:=concat(to_char(data), to_char(dataresult));
end loop;
close c1;
end;
Previous Topic: time in microseconds
Next Topic: How to Copy drop table one schma to another schma
Goto Forum:
  


Current Time: Thu Dec 08 14:09:30 CST 2016

Total time taken to generate the page: 0.08752 seconds