Why Doesn't this query work in a PL/SQL Procedure?

[markmd]

The below query works fine in SQL*Plus, but if I place it in a procedure, it refuses to compile. I have tried a few other ways to rewrite the query, but this is the most efficient. Any help would be greatly appreciated.

1 create or replace procedure xx as
2 cursor gethisthdr is
3 select *
4 from schema.table H
5 where H.STATUS <> 'CANCELLED'
6 and TRUNC(H.THEDATE) > to_date(NVL((select TRUNC(S.THEDATE)
7 from schema1.table1 S
8 where H.CODE = S.CODE
9 and H.ORG||' 1 1' = S.ORG
10 and H.ID = S.ID), '01-JAN-0001'), 'DD-MON-YYYY');
11 begin
12 null;
13* end;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE XX:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/49 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
( - + mod not null others
avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date



10/87 PLS-00103: Encountered the symbol "," when expecting one of the
following:
. ( * % & - + ; / at for mod rem and or
group having intersect minus order start union where connect
||

14/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
begin function package pragma procedure form

SQL>

[jmodic]

A crucial information is missing in your post - which database release?

I suspect it is 8i or earlier, right? Before 9i, Oracle used two different SQL parsers, depending of whether SQL was called from "pure" SQL environment or from within PL/SQL. And in those releases, you could do some things in your SQL, but you couldn't do the same things in SQL from within PL/SQL. Using select statments as an argument of NVL() function was one of them - you can use it in SQL, but not within PL/SQL. From 9i on, the SQL parser is common, so in general you shouldn't have this type of problems any more.

[markmd]

Thanks Jurij. It is 8i, so I suppose that there is no way around it? Except for updating to 9i or above?

Thanks again.

Mark

[jmodic]

No need to upgrade just because of this procedure!

You can rewrite it in many ways, so that it will work in 8i. One of the possibilities would be (I haven't tested it in any way, so be cautious; neither do I claim it is the most performant solution - it's just one of them):

CREATE OR REPLACE PROCEDURE xx AS
 CURSOR gethisthdr IS
 SELECT *
 FROM SCHEMA.TABLE H
 WHERE H.STATUS <> 'CANCELLED'
 AND (TRUNC(H.THEDATE) > (SELECT TRUNC(S.THEDATE)
                                     FROM schema1.table1 S
                                     WHERE H.CODE = S.CODE
                                     AND H.ORG||' 1 1' = S.ORG
                                     AND H.ID = S.ID)
      OR NOT EXISTS (SELECT TRUNC(S.THEDATE)
                                     FROM schema1.table1 S
                                     WHERE H.CODE = S.CODE
                                     AND H.ORG||' 1 1' = S.ORG

                                     AND H.ID = S.ID)
    );
BEGIN
 NULL;
END;