-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Last edited by markmd; 11-24-2006 at 06:18 AM.
-
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;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|