Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Unanswered: DB2 - Array /Cursor Fetch

    Having trouble in fetching the data to an array and then inserting to the table. Can any one tell what the issue in this?


    DECLARE
    CURSOR c1
    IS
    SELECT *
    FROM CUST A
    FOR UPDATE;

    my_record CUST.%ROWTYPE; ---this has to be an array

    BEGIN
    OPEN c1;

    LOOP
    FETCH c1
    BULK COLLECT INTO my_record
    LIMIT 1000; --bulk fetch is used..

    DBMS_OUTPUT.PUT_LINE ('Name = ' || my_record.CODE);

    FOR i IN 1 .. my_record.COUNT
    LOOP
    INSERT INTO CUSTOMER (XV,
    CODE,
    BXCODE,
    FIRST_NAME,
    MIDDLE_NAME,
    LAST_NAME,
    DATE_OF_BIRTH,
    GENDER,
    MOTHER_MAIDEN_NAME,
    ADDRESS1,
    ADDRESS2,
    CITY,
    COUNTRY,
    HOME_PHONE)
    VALUES (my_record (i).XV,
    my_record (i).CODE,
    my_record (i).BXCODE,
    my_record (i).FIRST_NAME,
    my_record (i).MIDDLE_NAME,
    my_record (i).LAST_NAME,
    my_record (i).DATE_OF_BIRTH,
    my_record (i).GENDER,
    my_record (i).MOTHER_MAIDEN_NAME,
    my_record (i).ADDRESS1,
    my_record (i).ADDRESS2,
    my_record (i).CITY,
    my_record (i).COUNTRY,
    my_record (i).HOME_PHONE);
    END LOOP;

    EXIT WHEN c1%NOTFOUND;
    END LOOP;

    CLOSE c1;
    END;

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    what is the error you get when compiling/executing
    db2 level and platform ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    hI, THIS IS THE ERROR ..
    Category Timestamp Duration Message Line Position
    Error 9/10/2013 5:47:13 PM 0:00:00.178 DB2 Database Error: ERROR [428H0] [IBM][DB2/xxxxxx] SQL20437N The array index operation cannot be applied to an object of a data type that is not ARRAY. LINE NUMBER=14. SQLSTATE=428H0
    1 0

    VERSION IS THE LATEST ..

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so what means latest ? platform ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by maxmilan View Post

    my_record CUST.%ROWTYPE; ---this has to be an array
    How a record (row) can be an array? May be what you meant to do is declare an array of rows?
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

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