Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: Stored Procedure Error "PLS-00103: Encountered the symbol "SELECT" when expecting...&

    I have written the following procedure:

    CREATE OR REPLACE PROCEDURE UpdateLineShipDate AS
    Order_Num Number;
    CURSOR C1 IS
    SELECT
    order_number
    FROM
    ec_order_line
    WHERE
    order_type = 'SHIP';
    BEGIN
    FOR C1Rec IN C1 LOOP
    Order_Num := (SELECT
    created_date_time
    FROM
    ec_order_header
    WHERE
    order_number = C1Rec.order_number AND
    order_type = 'SHIP' AND
    order_group_no = 0);
    UPDATE ec_order_line
    SET
    actual_ship_date = Order_Num,
    created_date_time = Order_Num
    WHERE
    order_type = 'SHIP' AND
    order_number = C1Rec.order_number;
    END LOOP;
    END;

    I am getting a compile error:
    12/16 PLS-00103: Encountered the symbol "SELECT" when expecting one o
    the following:
    ( - + mod not null <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string>

    19/24 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    ) * & - + / at mod rem with <an exponent (**)> and or group
    having intersect minus start union where connect ||
    The symbol ")" was substituted for ";" to continue.

    28/1 PLS-00103: Encountered the symbol "END"

    Any help would be appreciated.

    David

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

    Re: Stored Procedure Error "PLS-00103: Encountered the symbol "SELECT" when expecting

    This is invalid:

    Order_Num := (SELECT
    created_date_time
    FROM
    ec_order_header
    WHERE
    order_number = C1Rec.order_number AND
    order_type = 'SHIP' AND
    order_group_no = 0);

    Should be:

    SELECT
    created_date_time
    INTO Order_num
    FROM
    ec_order_header
    WHERE
    order_number = C1Rec.order_number AND
    order_type = 'SHIP' AND
    order_group_no = 0;

    If it is possible for the query to return no rows, or more than one row, then you need to handle the NO_DATA_FOUND and TOO_MANY_ROWS exceptions that will be raised.

  3. #3
    Join Date
    Feb 2004
    Posts
    108
    you're using
    Order_Num := (SELECT
    created_date_time
    FROM
    ec_order_header
    WHERE
    order_number = C1Rec.order_number AND
    order_type = 'SHIP' AND
    order_group_no = 0);


    Replace this with :

    SELECT
    created_date_time
    INTO Order_Num -- Select desired data into your local vbls.
    FROM
    ec_order_header
    WHERE
    order_number = C1Rec.order_number AND
    order_type = 'SHIP' AND
    order_group_no = 0 ;


    Refer to some pl/sql document for more details.

Posting Permissions

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