CeDeT: OIF (Oracle is funny (that way)) #3

NULLS FIRST (or LAST) not recognized by PL/SQL

The ability to specify whether NULL values should be sorted first (NULLS FIRST) or last (NULLS FIRST) with an ORDER BY-clause is not recognized by PL/SQL. This is demonstrated below using SQL*Plus:

The test

SQL> SELECT * FROM v$version WHERE ROWNUM<=2;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
PL/SQL Release 8.1.7.2.0 - Production

SQL> CREATE TABLE test_table (
  2    id NUMBER NOT NULL PRIMARY KEY,
  3    description VARCHAR2(255)
  4  );

Tabel er oprettet.

SQL> INSERT INTO test_table(id,description) VALUES(1,'Første');

1 række er oprettet.

SQL> INSERT INTO test_table(id,description) VALUES(2,'Anden');

1 række er oprettet.

SQL> INSERT INTO test_table(id,description) VALUES(3,NULL);

1 række er oprettet.

SQL> INSERT INTO test_table(id,description) VALUES(4,'Fjerde');

1 række er oprettet.

SQL> COMMIT;

Bekræftelse er udført.

SQL> COLUMN description FORMAT a20
SQL> SELECT id,description FROM test_table ORDER BY description;

        ID DESCRIPTION
---------- --------------------
         2 Anden
         4 Fjerde
         1 Første
         3

SQL> SELECT id,description FROM test_table ORDER BY description NULLS FIRST;

        ID DESCRIPTION
---------- --------------------
         3
         2 Anden
         4 Fjerde
         1 Første

SQL> SET SERVEROUTPUT ON SIZE 20000
SQL> BEGIN -- start anonymous PL/SQL block
  2    FOR r IN (SELECT id,description FROM test_table ORDER BY description) LOOP
  3      DBMS_OUTPUT.PUT_LINE(r.id||': '||r.description);
  4    END LOOP;
  5  END;
  6  /
2: Anden
4: Fjerde
1: Første
3:

PL/SQL-procedure er udført.

SQL> BEGIN -- start anonymous PL/SQL block
  2    FOR r IN (SELECT id,description FROM test_table ORDER BY description NULLS FIRST) LOOP
  3      DBMS_OUTPUT.PUT_LINE(r.id||': '||r.description);
  4    END LOOP;
  5* END;
  FOR r IN (SELECT id,description FROM test_table ORDER BY description NULLS FIRST) LOOP
                                                                       *
FEJL i linie 2:
ORA-06550: line 2, column 72:
PLS-00103: Encountered the symbol "NULLS" when expecting one of the following:
. ( ) , * @ % & - + / at for mod rem <an exponent (**)> asc
desc ||
The symbol ". was inserted before "NULLS" to continue.

SQL> PROMPT Workaround 1 - sort by NVL - could disable use of index on description column (if there was one)
Workaround 1 - sort by NVL - could disable use of index on description column (if there was one)
SQL> BEGIN -- start anonymous PL/SQL block
  2    FOR r IN (SELECT id,description FROM test_table ORDER BY NVL(description,' ')) LOOP
  3      DBMS_OUTPUT.PUT_LINE(r.id||': '||r.description);
  4    END LOOP;
  5* END;
3:
2: Anden
4: Fjerde
1: Første

PL/SQL-procedure er udført.

SQL> PROMPT Workaround 2 - Native Dynamic SQL (NDS)
Workaround 2 - Native Dynamic SQL (NDS)
SQL> DECLARE -- start anonymous PL/SQL block
  2    TYPE cType IS REF CURSOR;
  3    c cTYPE;
  4    r test_table%ROWTYPE;
  5  BEGIN
  6    OPEN c FOR 'SELECT id,description FROM test_table ORDER BY description NULLS FIRST';
  7    LOOP
  8      FETCH c INTO r.id,r.description;
  9      EXIT WHEN c%NOTFOUND;
 10      DBMS_OUTPUT.PUT_LINE(r.id||': '||r.description);
 11    END LOOP;
 12    CLOSE c;
 13* END;
3:
2: Anden
4: Fjerde
1: Første

PL/SQL-procedure er udført.