ORDER BY clause inside a subquery in a cursor 1

[michal]

Hi,
I hope this subject isn't too confusing!
I am trying to excute the following

DECLARE
CURSOR p_cursor1 IS
SELECT rownum,z.*
FROM
(SELECT 'hello','world' FROM DUAL ORDER BY 1) z;
BEGIN
OPEN p_cursor1;
CLOSE p_cursor1;
END;

This generates the error
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin declare end exception exit for goto if loop mod null
pragma raise return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>

It seems like I cannot use the order by clause in the subquery when it is used for a cursor, however if i just run the query

SELECT
ROWNUM,z.*
FROM
(SELECT 'hello','world' FROM DUAL ORDER BY 1) z

in SQL-Plus it executes without a problem.
Can anyone explain why this happens and hopefully provide a work-aroud?

Thanks very much for your time,
Michal

 

[karluk]

Actually the query which you say works in SQL*Plus gives me an

ORA-00907: missing right parenthesis

error. I have to eliminate the order by clause from the subquery to get it to work.

I'm afraid I don't write a lot of SQL, so my ignorance is showing. However based on my tests it appears that order by clauses are disallowed from subqueries.

 

[michal]

Thanks karluk for your answer.
However, I have tested again and

SELECT
ROWNUM,z.*
FROM
(SELECT 'hello','world' FROM DUAL ORDER BY 1) z

does work in SQl-Plus (I am using oracle version 8.1.5.0.0 if that makes a difference)
I am still seeking an answer to my original question: why isn't this syntax accepted in a cursor declaration and is there a work-around?

Thanks,
Michal

 

[karluk]

Curious problem. You are right that the Oracle version makes a difference. I got the error I mentioned on 7.3.4 and 8.0.6. However we have one server upgraded to 8.1.6 and I tested again. This time both the cursor and the sql select statement executed successfully.

In summary, on my servers both statements are uniformly either failing or running successfully. My guess is that allowing order by in a subselect was an 8i enhancement which had a bug in 8.1.5 that was subsequently fixed in 8.1.6.

 

[michal]

Thanks again karluk. I suspect you might be right!!

BTW: Does anyone know where I can find a list of the bugfixes in oracle 8.1.6?

Thanks,
Michal

 

[karluk]

I checked Oracle's web site and this is indeed an 8.1.5 bug. There is an article suggesting a workaround. I haven't tried it, but maybe it will work for you:

Problem Description
-------------------

In 8.1.5, SQL allows you to embed the ORDER BY clause in a subquery and
place the ROWNUM condition in the top-level query; if you do this, you
can force the ROWNUM condition to be applied after the ordering of the
rows.

For example, the following query returns the four lowest employee
numbers and their names basing on their salaries. This is sometimes
referred to as a 'top-N query':


SQL> SELECT * FROM
( SELECT EMPNO, ENAME FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM < 5;

EMPNO ENAME
---------- ----------
7839 KING
7788 SCOTT
7902 FORD
7566 JONES

When attempting this in PL/SQL, you receive PLS-00103 and ORA-06550 errors
as illustrated below:

tar1.sql
=========

DECLARE
CURSOR C1 IS
SELECT * FROM ( SELECT empno, ename FROM emp ORDER BY sal DESC)
WHERE ROWNUM < 5;
C1_rec c1%ROWTYPE;
temp number := 0;
BEGIN
OPEN C1;
WHILE temp = 1 LOOP
FETCH c1 INTO c1_rec;
IF c1%NOTFOUND THEN
temp := 0;
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(c1_rec.empno));
END IF ;
end LOOP;
CLOSE c1;
END;
/
SHOW ERRORS

SQL> @ tar1.sql

ERROR at line 3:
ORA-06550: line 3, column 52:
PLS-00103: Encountered the symbol &quot;ORDER&quot; when expecting one of the following:
. ) , @ with <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus partition start union where connect
The symbol &quot&quot; was substituted for &quot;ORDER&quot; to continue.
ORA-06550: line 3, column 69:
PLS-00103: Encountered the symbol &quot&quot; when expecting one of the following:
, ; for


Problem Explanation
-------------------

'Top-N query' is a ORACLE 8i feature which is supported in SQL. However,
Bug:855720 states the following:

&quot;PL/SQL does not support top-N queries (ORDER BY in SUBSELECT/SUBQUERY
or VIEW. Since this feature is available in SQL, but not in PL/SQL,
it has been logged as a Bug that will be fixed in 8.1.6.&quot;

This article presents a workaround for use of the Top-N query in PL/SQL.


Additional Search Words
-----------------------

PLS-103 PLS-00103 PLS 103 ORA 6550 ORA-6550 ORA-06550

Solution: CREATE VIEW WITH SUB QUERY AND SELECT OUTER QUERY FROM PL/SQL


Solution Description
--------------------

To workaround this problem, create a view with sub query and select the
outer query from PL/SQL as follows:

SQL> CREATE VIEW SAM AS SELECT EMPNO, ENAME FROM EMP
2 ORDER BY SAL DESC;

View created.

SQL> tar.sql

tar.sql
========

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 is select * from
sam where rownum < 5;
c1_rec c1%rowtype;
temp number := 1;
BEGIN
OPEN C1;
WHILE temp = 1 LOOP
FETCH c1 INTO c1_rec;
IF C1%NOTFOUND THEN
temp := 0;
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(c1_rec.empno)||','||c1_rec.ename);
END IF;
END LOOP;
CLOSE C1;
END;
/
SHOW ERRORS

When executing the program, you obtain the same result as the SQL query.
For example:

SQL> @ tar.sql
7839,KING
7788,SCOTT
7902,FORD
7566,JONES

PL/SQL procedure successfully completed.

No errors.


References
----------

BUG:855720 PL/SQL DOES NOT SUPPORT TOP-N QUERIES (ORDER BY IN
SUBSELECT/SUBQUERY OR VIEW)

 

[michal]

Thanks very much. I will try this work-around tomorrow. Thank you so much for your time and patience!!