Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    162

    Unanswered: stored proc compilation problem

    Hello people,

    This is my issue, when I run the following sql:

    SELECT emplid FROM asmt_employee
    WHERE TO_DATE(TO_CHAR(hire_dt ,'MM/DD/YYYY'), 'MM/DD/YYYY')
    BETWEEN (SELECT TO_DATE(TO_CHAR(MAX(TRUNC
    (log_date)),'MM/DD/YYYY'),'MM/DD/YYYY')
    FROM daily_load_log WHERE success_flag = 'Y')
    AND TO_DATE(TO_CHAR((TRUNC
    (SYSDATE)), 'MM/DD/YYYY'),'MM/DD/YYYY')
    AND empl_status IN('A','P')
    AND ola_level_id IN
    ('A','A1','A2','EA1','EA2','EA3','10','20','30','4 0','50','C1','C2','C3','C4',
    'C4B','C4AB','C4F','C4AF','C4M','C5','C5B','C5AB', 'C5F','C5AF','C5M');

    I get my results, no problem

    now when I incorporate this sql into the defination of a cursor like this:


    CURSOR cur_self_assessment IS
    SELECT emplid
    FROM asmt_employee
    WHERE TO_DATE(TO_CHAR(hire_dt ,'MM/DD/YYYY'), 'MM/DD/YYYY')
    BETWEEN (SELECT TO_DATE(TO_CHAR(MAX(TRUNC
    (log_date)),'MM/DD/YYYY'),'MM/DD/YYYY')
    FROM daily_load_log WHERE success_flag = 'Y')
    AND TO_DATE(TO_CHAR((TRUNC
    (SYSDATE)), 'MM/DD/YYYY'),'MM/DD/YYYY')
    AND empl_status IN('A','P')
    AND ola_level_id IN
    ('A','A1','A2','EA1','EA2','EA3','10','20','30','4 0','50','C1','C2','C3','C4',
    'C4B','C4AB','C4F','C4AF','C4M','C5','C5B','C5AB', 'C5F','C5AF','C5M');


    I get a PLS-00103 error:

    PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

    ( - + mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count c

    What is wrong with putting this SELECTstatement after the BETWEEN function in this sql

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: stored proc compilation problem

    I suspect this is because the ability to use a scalar subquery in the WHERE clause is supported by your SQL engine but not by the PL/SQL engine. The PL/SQL engine has always tended to lag behind the SQL engine for supporting new features. You will need to rewrite the query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •