Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Zimbabwe
    Posts
    2

    Unanswered: FORALL statement

    Suppose the following is my procedure:

    PROCEDURE RETIRE_THEM_NOW IS
    BULK_ERRORS EXCEPTION;
    PRAGMA EXCEPTION_INIT(BULK_ERRORS,-24381);
    TYPE employees_t IS TABLE OF EMPLOYEES%ROWTYPE
    INDEX BY PLS_INTEGER;
    retirees employees_t;

    BEGIN
    FOR rec IN(SELECT *
    FROM EMPLOYEES
    WHERE HIRE_DATE < ADD_MONTHS(SYSDATE, -1 * 8 * 40))
    LOOP
    retirees(SQL%ROWCOUNT) := rec;
    END LOOP;

    FORALL indx IN retirees.FIRST .. retirees.LAST
    SAVE EXCEPTIONS
    INSERT INTO EMPLOYEES
    VALUES retirees(indx);

    EXCEPTION
    WHEN BULK_ERRORS THEN
    FOR J IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
    LOOP
    DBMS_OUTPUT.PUT_LINE('Error from element #'||
    TO_CHAR(SQL%BULK_EXCEPTIONS(J).ERROR_INDEX)|| ': ' ||
    SQLERRM(SQL%BULK_EXCEPTIONS(J).ERROR_CODE));
    END LOOP;
    END;

    However when I try to execute this procedure I get the error:

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at "HR.RETIRE_THEM_NOW", line 16
    ORA-06512: at line 1


    Can anyone help on where I am getting it wrong?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    From the PL/SQL Manual: http://download-west.oracle.com/docs...olls.htm#28178


    input collections of composite values cannot be decomposed and bound to database columns.




    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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