| If clause in cursors [message #222288] |
Fri, 02 March 2007 12:18  |
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 #222566 is a reply to message #222290] |
Mon, 05 March 2007 07:39  |
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
