Home » SQL & PL/SQL » SQL & PL/SQL » using inline subquery in a PL/SQL cursor
using inline subquery in a PL/SQL cursor [message #36582] Wed, 05 December 2001 09:00 Go to next message
Bill McClure
Messages: 2
Registered: December 2001
Junior Member
I tried defining a cursor with the following code and got an error related to the ORDER clause. The query works fine as a stand alone query. Why will it not work as a CURSOR?

DECLARE
CURSOR sal_cr IS
SELECT empno, ename, sal, rownum
from (select empno, ename, sal
from emp
order by sal)
where rownum <= 5;

----------------------------------------------------------------------
Re: using inline subquery in a PL/SQL cursor [message #36584 is a reply to message #36582] Wed, 05 December 2001 09:16 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
what version of oracle you are using?

----------------------------------------------------------------------
Re: using inline subquery in a PL/SQL cursor [message #36588 is a reply to message #36584] Wed, 05 December 2001 10:33 Go to previous messageGo to next message
Bill McClure
Messages: 2
Registered: December 2001
Junior Member
I am using Oracle8i.

----------------------------------------------------------------------
Re: using inline subquery in a PL/SQL cursor [message #36603 is a reply to message #36582] Thu, 06 December 2001 00:32 Go to previous messageGo to next message
tinel
Messages: 42
Registered: November 2001
Member
Hi
this work fine on Oracle8i Release 8.1.7.0.0 - Production

DECLARE
CURSOR sal_cr IS
SELECT empno, ename, sal, ROWNUM
FROM (SELECT empno, ename, sal
FROM emp
ORDER BY sal)
WHERE ROWNUM <= 5;
c_det sal_cr%ROWTYPE;
BEGIN
OPEN sal_cr;
LOOP
FETCH sal_cr INTO c_det;
EXIT WHEN sal_cr%NOTFOUND;
dbms_output.put_line(c_det.ename);
END LOOP;
CLOSE sal_cr;
END;
Bye

----------------------------------------------------------------------
Re: using inline subquery in a PL/SQL cursor [message #36612 is a reply to message #36584] Thu, 06 December 2001 04:42 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
i tested your pl/sql block in oracle 8i environment. it works fine without any errors..

what error you are getting?. and check pl/sql version.

use following query to get version info
select * from v$version

my version info is
Personal Oracle8i Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

----------------------------------------------------------------------
Previous Topic: loop
Next Topic: PL/SQL (ORA-1410) Deleting Several Hundred Thousand Record
Goto Forum:
  


Current Time: Thu Mar 28 17:20:59 CDT 2024