Why Doesn't this query work in a PL/SQL Procedure?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Apr 2001
    Location
    West Sussex, England
    Posts
    15

    Angry 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>

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Apr 2001
    Location
    West Sussex, England
    Posts
    15
    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 05:18 AM.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width