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

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>

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.

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

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):

Code:

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;