Forall Insert
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Forall Insert

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Forall Insert

    Hi All,

    Can I INSERT like the way they have done for update???

    DECLARE
    TYPE NumList IS TABLE OF NUMBER;
    TYPE NameList IS TABLE OF VARCHAR2(15);
    empnos NumList;
    enames NameList;
    BEGIN
    empnos := NumList(1,2,3,4,5);
    FORALL i IN 1..5
    EXECUTE IMMEDIATE
    'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1
    RETURNING ename INTO :2'
    USING empnos(i) RETURNING BULK COLLECT INTO enames;
    ...
    END;
    /

    Cheers!
    Cheers!
    OraKid.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    And what is the error you got?

    Tamil

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi TamilSelvan
    I thought its not possible to use FORALL and INSERT. I did this and it works. Sorry for the last reply.

    DECLARE
    TYPE NumList IS TABLE OF NUMBER;
    TYPE NameList IS TABLE OF VARCHAR2(15);
    empnos NumList;
    enames NameList;
    tab varchar2(100) := 'emp(empno) ';
    BEGIN
    empnos := NumList(1,2,3,4,5);
    FORALL i IN 1..5
    EXECUTE IMMEDIATE
    'INSERT INTO ' || tab || ' VALUES(:1) RETURNING ename INTO :2'
    USING empnos(i) RETURNING BULK COLLECT INTO enames;
    END;

    Many Thanks.
    Cheers!
    OraKid.

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