| Oracle Stored Procedure [message #35826] |
Wed, 17 October 2001 11:52  |
Laxsun
Messages: 10
Registered: October 2001
|
Junior Member |
|
|
Can some one help me, what is wrong with this stored
procedure. On execution of this code, I am getting
Warning: Procedure created with compilation errors.
create or replace procedure test_temp(num in number) IS
declare
cursor c1 is
select ename,empno,sal from test_emp where deptno = num
order by sal desc;
e_name char(10);
emp_no number(4);
e_sal number(7,2);
begin
open c1;
for i in 1..14 loop
fetch c1 into e_name,emp_no,e_sal;
exit when c1%notfound;
insert into temp values(e_sal,emp_no,e_name);
commit;
end loop;
close c1;
end test_temp;
----------------------------------------------------------------------
|
|
|
|
| Re: Oracle Stored Procedure [message #35828 is a reply to message #35826] |
Wed, 17 October 2001 12:28  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
For starters, take out the DECLARE (not used in procedures. Your variable declarations just go between IS/AS and BEGIN.
If you are running 8i or later, this whole proc could become just:
create or replace procedure test_temp(num in number)
is
begin
insert into temp
select * from
(select ename, empno, sal
from test_emp
where deptno = num
order by sal desc)
where rownum <= 14;
commit;
end;
/
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
