Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: multiple updates, pl/sql, subprogram

    I would like to request help writing a pl/sql statement that allows two update statements within one subprogram.

    The code below completes sucessfully with one or the other update statements, but not both together.

    First, here's the error message:

    PLS-00201: identifier 'I' must be declared
    ORA-06550: line 11, column 1:
    PL/SQL: SQL Statement ignored

    The identifier "I" corresponds to the first tab(i) in the second update statement.

    DECLARE
    TYPE table_type IS TABLE OF CHAR(3);
    thetable table_type:=table_type('355','356','357','35X');
    PROCEDURE update_number_table (tab in out nocopy table_type) IS
    BEGIN
    forall i in tab.FIRST..tab.LAST

    UPDATE x
    set field_in_x=
    (select count(field_in_y) from y where other_field_in_y=tab(i))
    where other_field_in_x=tab(i);

    UPDATE a
    set field_in_a=
    (select count(field_in_b) from b where other_field_in_b=tab(i))
    where other_field_in_a=tab(i);

    END;
    BEGIN
    update_number_table(thetable);
    END;
    /

    If an expert out there spots the mistake, the help sure would be appreciated.

    JT

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

    Re: multiple updates, pl/sql, subprogram

    FORALL is attached to the specific UPDATE or whatever that follows it, so your second UPDATE is not within its scope. You need another FORALL.

Posting Permissions

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