1 2 3 Previous Next 31 Replies Latest reply: May 30, 2013 6:13 AM by sam8682 Go to original post RSS
      • 15. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
        sam8682
        Thanks andreas,
        I've modified the code but still not able to insert record into database table.
        PACKAGE BODY PK_EXCEL_TO_DB IS
            PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
            C_ROUTE               VARCHAR2(255);
            V_ROUTE               VARCHAR2(1000);
            C_TRNDATE           VARCHAR2(255);
            V_TRNDATE           VARCHAR2(1000);
            C_TTIME             VARCHAR2(255);
            V_TTIME               VARCHAR2(1000);
            C_TID                   VARCHAR2(255);
            V_TID                   VARCHAR2(1000);
          BEGIN
         
            FOR i IN 1..i_ldata.count LOOP
                 
              C_ROUTE:=i_ldata(i).CROUTE;
              V_ROUTE:=i_ldata(i).VROUTE;
              --:T1:=V_ROUTE;
              
              C_TRNDATE :=i_ldata(i).CTRNDATE ;
              V_TRNDATE :=i_ldata(i).VTRNDATE;
              --:T2:=V_TRNDATE;
              
              C_TTIME :=i_ldata(i).CTTIME ;
              V_TTIME :=i_ldata(i).VTTIME;
              --:T3:=V_TTIME;
              
              C_TID :=i_ldata(i).CTID ;
              V_TID :=i_ldata(i).VTID;
              --:T4:=V_TID;
                      
              FORMS_DDL('INSERT INTO TEST (C_ROUTE, C_TRNDATE, C_TTIME, C_TID)
                                VALUES (V_ROUTE, V_TRNDATE, V_TTIME,V_TID)');                                                               
            END LOOP;                                         
             STANDARD.COMMIT;
        --COMMIT;                                    
          END;
        END
        Thanks again.
        • 16. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
          Andreas Weiden
          Now you don't use the columnsnames and values at all. Your insert is just a fixed string. As i said before, instead of doing the insert, put it into a forms-item and see what the result looks like. Ifmyou copy it from the forms-item into sql*plus it should work.
          • 17. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
            sam8682
            hello
            as per your suggestion i've commit the the insert logic and pass the values to form items.
            I come to know that values are seen in the form items. but at the end empty record is passed and all logic fails.
            (its my observation) i think i've to consider "rowcounter:=i_ldata.count;" first.
            Since i never work on collection before so i am unable to understand what is missing exactly.

            Edited by: sam8682 on May 21, 2013 5:20 PM
            • 18. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
              Andreas Weiden
              So, what about posting your new insert-logic along with the output you get in your forms-item?
              • 19. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                sam8682
                hello,
                According to me it suppose to be like this but at the same time i felt its totally wrong. and it doesn't work as well.
                PACKAGE BODY PK_EXCEL_TO_DB IS
                    PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
                  C_ROUTE         VARCHAR2(255);
                    V_ROUTE         VARCHAR2(1000);
                    C_TRNDATE       VARCHAR2(255);
                    V_TRNDATE       VARCHAR2(1000);
                    C_TTIME             VARCHAR2(255);
                    V_TTIME         VARCHAR2(1000);
                    C_TID           VARCHAR2(255);
                    V_TID           VARCHAR2(1000);
                  BEGIN
                 
                    FOR i IN 1..i_ldata.count LOOP
                            
                      C_ROUTE:=C_ROUTE || ',' || i_ldata(i).CROUTE;
                      V_ROUTE:=V_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';
                      :C1:=i_ldata(i).CROUTE;
                      :T1:=i_ldata(i).VROUTE;
                      FORMS_DDL('INSERT INTO TEST (' || SUBSTR(C_ROUTE, 2) ||',) 
                                                    VALUES (' || SUBSTR(V_ROUTE,2) || ')');
                       
                       
                       
                      C_TRNDATE :=C_TRNDATE  || ',' || i_ldata(i).CTRNDATE ;
                      V_TRNDATE :=V_TRNDATE || ',''' || i_ldata(i).VTRNDATE  || '''';
                       :C2:=i_ldata(i).CTRNDATE;
                      :T2:=i_ldata(i).VTRNDATE;
                      
                       FORMS_DDL('INSERT INTO TEST (' || SUBSTR(C_TRNDATE, 2) ||',) 
                                                    VALUES (' || SUBSTR(v_TRNDATE,2) || ')');
                      
                      C_TTIME :=C_TTIME  || ',' || i_ldata(i).CTTIME ;
                      V_TTIME :=V_TTIME || ',''' || i_ldata(i).VTTIME  || '''';
                      :C3:=i_ldata(i).CTTIME;
                      :T3:=i_ldata(i).VTTIME;
                      FORMS_DDL('INSERT INTO TEST (' || SUBSTR(C_TTIME, 2) ||',) 
                                                    VALUES (' || SUBSTR(V_TTIME,2) || ')');
                      
                      C_TID :=C_TID  || ',' || i_ldata(i).CTID ;
                      V_TID :=V_TID || ',''' || i_ldata(i).VTID|| '''';
                      :C4:=i_ldata(i).CTID;
                      :T4:=i_ldata(i).VTID;
                      FORMS_DDL('INSERT INTO TEST (' || SUBSTR(C_TID, 2) ||',) 
                                                    VALUES (' || SUBSTR(V_TID,2) || ')');
                      
                     /*FORMS_DDL('INSERT INTO TEST (' || SUBSTR(C_ROUTE, 2) || ', ' || SUBSTR(C_TRNDATE, 2) || ',
                                                          ' || SUBSTR(C_TTIME, 2) || ',' || SUBSTR(C_TID, 2) ||',) 
                                                    VALUES (' || SUBSTR(V_ROUTE,2) || ' , ' || SUBSTR(V_TRNDATE,2) || '
                                                           ' || SUBSTR(V_TTIME,2) || ' ' || SUBSTR(V_TID,2) || ')'); */
                STANDARD.COMMIT;
                             
                    END LOOP; 
                
                --COMMIT;                                    
                  END;
                END;
                now i am getting more confuse.

                Thanks and Regards
                Sam

                Edited by: sam8682 on May 22, 2013 12:34 PM
                • 20. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                  Andreas Weiden
                  You should go one step back and rethink what you are doing. Anyway, you still don't show us your resulting INSERT-statement.
                  The code should look somewhat like this
                  FOR i IN 1..i_ldata.count LOOP
                    vcInsert:='INSERT INTO TEST (' || i_ldata(i).CROUTE || ', ' || i_ldata(i).CTRNDATE || ', ' || i_ldata(i).CTTIME || ', 'i_ldata(i).CTID || ')' ||
                                   ' VALUES (' || i_ldata(i).VROUTE || ', ' || i_ldata(i).VTRNDATE || ', ' || i_ldata(i).VTTIME || ',' || i_ldata(i).VTID || ')');                                                               
                  
                    FORMS_DDL(vcInsert);
                  END LOOP;                     
                  Now, output vcInsert to a forms-item and check the syntax of the resulting insert. According to the datatypes of the different items, you may have to add TO_DATE or ' when constructing the insert.
                  • 21. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                    sam8682
                    Thanks a lot Andreas Weiden,
                    Finally it works, i have put your code and check the problem.
                    I came to know that, instade of providing table coumn name in insert statement i am providing excel columnames.
                    and my problem is solved.

                    But i have anothe problem.
                    actually i am importing excel into oracle table by column mapping. what have done is
                    1. display column heding of excle file on the oracle form. i created button and when the button is pressed following procedure is called,
                    PROCEDURE for_col_map IS
                         APPLICATION OLE2.OBJ_TYPE; 
                         WORKBOOKS OLE2.OBJ_TYPE; 
                         WORKBOOK OLE2.OBJ_TYPE; 
                         WORKSHEETS OLE2.OBJ_TYPE; 
                         WORKSHEET OLE2.OBJ_TYPE; 
                         CELL OLE2.OBJ_TYPE;
                         CTR                         NUMBER(12);
                         COLS                     NUMBER(2);
                         CELLVALUE      VARCHAR2(89);
                         C_ROUTE        VARCHAR2(1000);
                         C_TRNDATE      VARCHAR2(1000);
                         C_TTIME        VARCHAR2(1000);
                         C_TID          VARCHAR2(1000);
                         FILENAME        VARCHAR2(500);
                         v_path         VARCHAR2(1000):=:path;
                         I           NUMBER;
                         ARGS OLE2.OBJ_TYPE; 
                    BEGIN
                                   :progress:='Please wait...';
                                   SYNCHRONIZE;
                                   --------------INITIATE EXCEL APPLICATION---------------------------
                                   filename := V_PATH;--GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
                                   APPLICATION := OLE2.CREATE_OBJ('EXCEL.APPLICATION'); 
                                   OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','FALSE'); 
                                   ----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
                                   WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS'); 
                                   ----------------OPEN REQUIRED WORKBOOK-----------------------------
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   OLE2.ADD_ARG(ARGS, FILENAME);
                                   WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS); 
                                   ---------------OPEN REQUIRED WORKSHEET---------------------------
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,'Sheet1'); 
                                   WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS); 
                                   ----------------GET CELL VALUE-------------------------------------
                                   ctr := 1; --row number
                                   cols := 1; -- column number
                              --     FIRST_RECORD;
                                   -----------------------COLUMN1------------------------------------- 
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,1); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   :EXCL_ROUTE :=C_ROUTE;
                                   -----------------------COLUMN2------------------------------------- 
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,2); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_TRNDATE:= OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   :EXCL_DATE :=C_TRNDATE;
                                   -----------------------COLUMN3------------------------------------- 
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,3); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_TTIME := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   :EXCL_TIME     :=C_TTIME;
                                   -----------------------COLUMN4------------------------------------- 
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,4); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_TID := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   :EXCL_TID :=C_TID;
                                   
                    ----------------CLOSE THE EXCEL SHEET AFTER READING--------------
                    OLE2.INVOKE(APPLICATION,'QUIT'); 
                    -----------------RELEASE ALL OBJECTS
                    OLE2.RELEASE_OBJ(CELL); 
                    OLE2.RELEASE_OBJ(WORKSHEET); 
                    OLE2.RELEASE_OBJ(WORKBOOK); 
                    OLE2.RELEASE_OBJ(WORKBOOKS); 
                    OLE2.RELEASE_OBJ(APPLICATION); 
                    
                    exception  
                    WHEN OTHERS THEN 
                    MESSAGE(sqlerrm); 
                    END;
                    /     
                    2. Then i provide poplist infront of every excel colum

                    display is some what like this
                                                         excel column heading                              poplist for column
                                             excl_r                                                            route
                                             excl_d                                                            trndate
                                             excl_ti                                                            ttime
                                             excl_td                                                               tid
                                             2. Then i add another button called process, when button pressed following procedure is called
                    PROCEDURE get_excel IS
                      APPLICATION OLE2.OBJ_TYPE; 
                         WORKBOOKS OLE2.OBJ_TYPE; 
                         WORKBOOK OLE2.OBJ_TYPE; 
                         WORKSHEETS OLE2.OBJ_TYPE; 
                         WORKSHEET OLE2.OBJ_TYPE; 
                         CELL OLE2.OBJ_TYPE;
                         CTR NUMBER(12);
                         COLS NUMBER(2);
                         CELLVALUE VARCHAR2(89);
                         C_ROUTE   VARCHAR2(255);
                         V_ROUTE   VARCHAR2(1000);
                         C_TRNDATE VARCHAR2(255);
                         V_TRNDATE VARCHAR2(1000);
                         C_TTIME       VARCHAR2(255);
                         V_TTIME   VARCHAR2(1000);
                         C_TID     VARCHAR2(255);
                         V_TID     VARCHAR2(1000);
                         FILENAME   VARCHAR2(500);
                         v_path                        varchar2(1000):=:path;
                         ARGS OLE2.OBJ_TYPE;
                         l_sql varchar2(32767);
                         i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
                    --     tDataList     PK_EXCEL_TO_DB.tDataList;
                         rowcounter     number;
                    BEGIN
                                   :progress:='Please wait...';
                                   SYNCHRONIZE;
                                   --------------INITIATE EXCEL APPLICATION---------------------------
                                   filename := V_PATH;--GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
                                   APPLICATION := OLE2.CREATE_OBJ('EXCEL.APPLICATION'); 
                                   OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','FALSE'); 
                                   
                                   ----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
                                   
                                   WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS'); 
                                   
                                   ----------------OPEN REQUIRED WORKBOOK-----------------------------
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   OLE2.ADD_ARG(ARGS, FILENAME);
                                   WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS); 
                                   
                                   ----------------OPEN REQUIRED WORKSHEET---------------------------
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,'Sheet1'); 
                                   WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS); 
                                   
                                   ----------------GET CELL VALUE------------------------------------- 
                                   ctr := 2; --row number
                                   cols := 1; -- column number
                               --FIRST_RECORD;
                                    LOOP
                                              i_ldata.extend(1); 
                                        rowcounter:=i_ldata.count;
                                   -----------------------COLUMN1------------------------------------- 
                                   /*ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,COLS); --COLS
                                   OLE2.ADD_ARG(ARGS,1); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).CROUTE:=C_ROUTE;*/
                                   
                                   C_ROUTE :=:TAB_COL1;
                                   i_ldata(rowcounter).CROUTE:=C_ROUTE;
                                   
                                               
                                ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,1); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   V_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).VROUTE:=V_ROUTE;
                                   -----------------------COLUMN2------------------------------------- 
                              /*     ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,COLS); 
                                   OLE2.ADD_ARG(ARGS,2); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_TRNDATE:= OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).CTRNDATE:=C_TRNDATE;*/
                                   
                                   C_TRNDATE:=:TAB_COL2;
                                   i_ldata(rowcounter).CTRNDATE:=C_TRNDATE;
                                   
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,2); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   V_TRNDATE:= OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).VTRNDATE:=V_TRNDATE;
                                   -----------------------COLUMN3------------------------------------- 
                              /*     ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,COLS); 
                                   OLE2.ADD_ARG(ARGS,3); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_TTIME := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).CTTIME:=C_TTIME;*/
                                   
                                   C_TTIME:=:TAB_COL3;
                                   i_ldata(rowcounter).CTTIME:=C_TTIME;
                                   
                                   
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,3); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   V_TTIME := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).vttime:=v_ttime;
                                   -----------------------COLUMN4------------------------------------- 
                              /*     ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,COLS); 
                                   OLE2.ADD_ARG(ARGS,4); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   C_TID := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).CTID:=C_TID;*/
                                   
                                   C_TID:=:TAB_COL4;
                                   i_ldata(rowcounter).CTID:=C_TID;
                                   
                                   ARGS := OLE2.CREATE_ARGLIST; 
                                   OLE2.ADD_ARG(ARGS,CTR); 
                                   OLE2.ADD_ARG(ARGS,4); 
                                   CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                   OLE2.DESTROY_ARGLIST(ARGS);
                                   ARGS := OLE2.CREATE_ARGLIST;
                                   V_TID := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                   i_ldata(rowcounter).vtid:=v_tid;
                                        
                              EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;      
                              ctr := ctr + 1; 
                              cols := 1;
                              
                    END LOOP;
                                        PK_EXCEL_TO_DB.PR_DO_INSERT(i_ldata);          
                    :progress:='EXCEL READING IS DONE...';
                    ----------------CLOSE THE EXCEL SHEET AFTER READING--------------
                    OLE2.INVOKE(APPLICATION,'QUIT'); 
                    -----------------RELEASE ALL OBJECTS
                    OLE2.RELEASE_OBJ(CELL); 
                    OLE2.RELEASE_OBJ(WORKSHEET); 
                    OLE2.RELEASE_OBJ(WORKBOOK); 
                    OLE2.RELEASE_OBJ(WORKBOOKS); 
                    OLE2.RELEASE_OBJ(APPLICATION); 
                    :PROGRESS := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;
                    SYNCHRONIZE;
                    --exception  
                    --WHEN OTHERS THEN 
                    --MESSAGE(sqlerrm); 
                    END;
                    3. And the package is
                    PACKAGE PK_EXCEL_TO_DB IS
                      TYPE tKeyValue IS RECORD (
                        CROUTE         VARCHAR2(255),
                        VROUTE         VARCHAR2(1000),
                        CTRNDATE       VARCHAR2(255),
                        VTRNDATE       VARCHAR2(1000),
                        CTTIME             VARCHAR2(255),
                        VTTIME         VARCHAR2(1000),
                        CTID           VARCHAR2(255),
                        VTID           VARCHAR2(1000));
                        
                      TYPE tDataList IS TABLE OF tKeyValue;
                      --i_lData tDataList;
                      PROCEDURE PR_DO_INSERT(i_lData IN tDataList);     
                    END;
                    
                    PACKAGE BODY PK_EXCEL_TO_DB IS
                        PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
                    
                        vcInsert                           VARCHAR2(3500);
                      BEGIN
                          :BLK_MAIN.T4:= vcInsert;
                           
                              vcInsert :='INSERT INTO TEST (' || i_ldata(i).CROUTE || ', ' || i_ldata(i).CTRNDATE || ', ' || i_ldata(i).CTTIME || ', '||i_ldata(i).CTID || ')' ||
                                     ' VALUES (' || ''''||i_ldata(i).VROUTE ||''''|| ', ' ||''''|| i_ldata(i).VTRNDATE ||''''|| ', ' ||''''|| i_ldata(i).VTTIME ||''''|| ',' || ''''||i_ldata(i).VTID ||'''' || ')';
                           FORMS_DDL(vcInsert);
                                              
                        END LOOP;
                        STANDARD.COMMIT;
                        
                      END;
                    END;
                    Now my proble is that my colums in the foms became dynamic but rows are not, rows are still static. what should i do?
                    actually my target is

                    sing oracle forms 6i i want to carray out following task.
                    suppose i've table with following fields
                    ROUTE_ID, TRN_DATE,TRN_TIME,TRN_ID
                    and i've excel sheet with following details
                    date           rid               time                    tid
                    ----------------------------------------------------------------------------------
                    20-03-13         route1                  20:30:45                  RDT-01
                    25-03-13         route2                  15:25:30                  RTD-02
                    26-03-13         route3                  10:15:20                  RTD-03
                    i want to import this excel data to my oracle table by column mapping
                    i.e ROUTE_ID in my table is in first place but in excel sheet it is in second place,
                    TRN_DATE in my table is in second place but in excel sheet it is in first place.
                    next time i may get excel sheet which will be like this
                    tid               rid               date                    time
                    ---------------------------------------------------------------------------------------------
                    RTD-01                     route1                  20-03-13                  20:30:45
                    RTD-03                     route2             25-03-13                   15:25:30
                    This excel sheet also have different oreder than my database table and i want to import it to my database table using forms 6i.
                    please help me to solve this problem

                    Thanks a lot.
                    Regard
                    Sam8682
                    • 22. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                      sam8682
                      if anyone have any idea please help.
                      I am trying hard since long time.
                      • 23. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                        Andreas Weiden
                        I'm not sure if i understand your new problem. I thought that all the previous work was just to get the dynamic columns working. So what does not work?
                        • 24. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                          sam8682
                          yes it works perfectly.
                          I am explaining in details what my requirement is,
                          "I want to imopert excel data by coloumn mapping to oracle table using forms 6i". for this I've created a form which is having following details.
                          1. path, file name text field and button called browse.
                               when the form load. click on browse botton, and user will be asked to go to specific drive and select the excel file. The file path is stored in path text field
                               and file name is displayed on file name text field.
                          2. After doing this I've porvide a button called column. after selecting the file click on column button. Once the button is pressed all column heading of
                          the selected excel file get displayed on the text fields which i've provide on the form.
                          3. In frot of each column headig field I've provide list box which contains database table column heading. Now user have to select table column according to his excel column.
                          (i.e I am asking user to map his excel column with oracle table column.)
                          4. Once the maping from user is done, I've provide button called process. once the button is pressed all the data from excel is to get insert in to database colmum.

                               what i've achive -
                               - User is able to select file path and file name get displayed.
                               - Columns heding of excel get displayed on the form.
                               problem area
                               -In the process button code column heading get change dynamically but records i.e actual data does not.
                               -I am able to insert records into table, but only when excel column heading sequence is same as database table column.
                                    i.e
                                    excel column table column
                                    -----------------------------------------------------------------
                                    route = route
                                    date = del_date
                                    time = del_time
                               id = tran_id
                               In above situation record get insert into table.
                               
                               But when only excel column sequence change that time data is not inserted into table.
                               If required i can uploade my form and sample excel sheet also provde table script.
                               Please give me solution for the same, I am working hard on it.
                               
                               Thanks and Regards
                               Sam.

                          Edited by: sam8682 on May 25, 2013 12:18 PM
                          • 25. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                            Andreas Weiden
                            I would say the only thing you need to do is fill your column-names from your datablock.

                            These are the fields where you store your column-names in
                            i_ldata(i).CROUTE
                            i_ldata(i).CTRNDATE
                            i_ldata(i).CTTIME
                            i_ldata(i).CTID
                            But as they are variables, you only need to put the mapped column-names form your block into them (to make it more clear, you might rename them to COLUMN1, COILUMN2, COLUMN3 and COLUMN4). Also, as the names are the same for all rows, you can take them out of your collection and pass them as separate values
                            The code could look like
                            DECLARE
                              i PLS_INTEGER:=0;
                              vcColumn1 VARCHAR2(30);
                              vcColumn2 VARCHAR2(30);
                              vcColumn3 VARCHAR2(30);
                              vcColumn4 VARCHAR2(30);
                            BEGIN
                              GO_BLOCK('YOURBLOCK');
                              FIRST_RECORD;
                              LOOP
                                i:=i+1;
                                IF i=1 THEN
                                  vcColumn1:=:BLOCK.MAPPED_COLUMN;
                                ELSIF i=2 THEN
                                  vcColumn2:=:BLOCK.MAPPED_COLUMN;
                                ELSIF i=3 THEN
                                  vcColumn3:=:BLOCK.MAPPED_COLUMN;
                                ELSIF i=4 THEN
                                  vcColumn4:=:BLOCK.MAPPED_COLUMN;
                                END IF;
                                NEXT_RECORD;
                              END LOOP;
                            
                              --  extract your column-values here
                              ..
                              -- now call the insertion-procedure
                              PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,vcColumn3,vcColumn4,lData);
                            END;
                            The package for insetion would look something like
                            PACKAGE PK_EXCEL_TO_DB IS
                              TYPE tKeyValue IS RECORD (
                                VROUTE         VARCHAR2(1000),
                                VTRNDATE       VARCHAR2(1000),
                                VTTIME         VARCHAR2(1000),
                                VTID           VARCHAR2(1000));
                                
                              TYPE tDataList IS TABLE OF tKeyValue;
                              --i_lData tDataList;
                              PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList);     
                            
                            END;
                             
                            PACKAGE BODY PK_EXCEL_TO_DB IS
                                  PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
                             
                                vcInsert                           VARCHAR2(3500);
                              BEGIN
                                FOR i IN 1..i_lData.COUNT LOOP
                                   
                                 vcInsert :='INSERT INTO TEST (' || vcColumn1 || ', ' || vcColumn2 || ', ' || vcColumn3 || ', '||vcColumn4 || ')' ||
                                             ' VALUES (' || ''''||i_ldata(i).VROUTE ||''''|| ', ' ||''''|| i_ldata(i).VTRNDATE ||''''|| ', ' ||''''|| i_ldata(i).VTTIME ||''''|| ',' || ''''||i_ldata(i).VTID ||'''' || ')';
                                 FORMS_DDL(vcInsert);
                                                      
                                END LOOP;
                                STANDARD.COMMIT;
                                
                              END;
                            END;
                            • 26. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                              sam8682
                              Thanks a lot for reply,
                              I've modify my code as per your suggestion.
                              my code is like this now.
                              PROCEDURE get_excel IS
                                   APPLICATION OLE2.OBJ_TYPE; 
                                   WORKBOOKS OLE2.OBJ_TYPE; 
                                   WORKBOOK OLE2.OBJ_TYPE; 
                                   WORKSHEETS OLE2.OBJ_TYPE; 
                                   WORKSHEET OLE2.OBJ_TYPE; 
                                   CELL OLE2.OBJ_TYPE;
                                   CTR NUMBER(12);
                                   COLS NUMBER(2);
                                   CELLVALUE VARCHAR2(89);
                                   V_ROUTE   VARCHAR2(1000);
                                   V_TRNDATE VARCHAR2(1000);
                                   V_TTIME   VARCHAR2(1000);
                                   V_TID     VARCHAR2(1000);
                                      i PLS_INTEGER:=0;
                                      vcColumn1 VARCHAR2(30);
                                      vcColumn2 VARCHAR2(30);
                                      vcColumn3 VARCHAR2(30);
                                      vcColumn4 VARCHAR2(30);
                                      FILENAME   VARCHAR2(500);
                                      v_path          varchar2(1000):=:path;
                                      ARGS OLE2.OBJ_TYPE;
                                      l_sql varchar2(32767);
                                      i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
                                      rowcounter     number;
                              BEGIN
                                GO_BLOCK('BLK_MAIN');
                                FIRST_RECORD;
                                LOOP
                                  i:=i+1;
                                  IF i=1 THEN
                                    vcColumn1:=:BLK_MAIN.TAB_COL1;
                                  ELSIF i=2 THEN
                                    vcColumn2:=:BLK_MAIN.TAB_COL2;
                                  ELSIF i=3 THEN
                                    vcColumn3:=:BLK_MAIN.TAB_COL3;
                                  ELSIF i=4 THEN
                                    vcColumn4:=:BLK_MAIN.TAB_COL4;
                                  END IF;
                                NEXT_RECORD;
                                END LOOP;
                                --  extract your column-values here
                               :progress:='Please wait...';
                                             SYNCHRONIZE;
                                             --------------INITIATE EXCEL APPLICATION---------------------------
                                             filename := V_PATH;--GET_FILE_NAME('c:\', File_Filter=>'Excel Files (*.xls)|*.xls|'); -- to pick the file
                                             APPLICATION := OLE2.CREATE_OBJ('EXCEL.APPLICATION'); 
                                             OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','FALSE'); 
                                             
                                             ----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
                                             
                                             WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS'); 
                                             
                                             ----------------OPEN REQUIRED WORKBOOK-----------------------------
                                             ARGS := OLE2.CREATE_ARGLIST;
                                             OLE2.ADD_ARG(ARGS, FILENAME);
                                             WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS); 
                                             OLE2.DESTROY_ARGLIST(ARGS); 
                                             
                                             ----------------OPEN REQUIRED WORKSHEET---------------------------
                                             ARGS := OLE2.CREATE_ARGLIST; 
                                             OLE2.ADD_ARG(ARGS,'Sheet1'); 
                                             WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS); 
                                             OLE2.DESTROY_ARGLIST(ARGS); 
                                             ----------------GET CELL VALUE------------------------------------- 
                                             ctr := 2; --row number
                                             cols := 1; -- column number
                                         --FIRST_RECORD;
                                              LOOP
                                                   i_ldata.extend(1); 
                                                  rowcounter:=i_ldata.count;
                                             -----------------------COLUMN1------------------------------------- 
                                          ARGS := OLE2.CREATE_ARGLIST; 
                                             OLE2.ADD_ARG(ARGS,CTR); 
                                             OLE2.ADD_ARG(ARGS,1); 
                                             CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                             OLE2.DESTROY_ARGLIST(ARGS);
                                             ARGS := OLE2.CREATE_ARGLIST;
                                             V_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                             i_ldata(rowcounter).VROUTE:=V_ROUTE;
                                             -----------------------COLUMN2------------------------------------- 
                                             ARGS := OLE2.CREATE_ARGLIST; 
                                             OLE2.ADD_ARG(ARGS,CTR); 
                                             OLE2.ADD_ARG(ARGS,2); 
                                             CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                             OLE2.DESTROY_ARGLIST(ARGS);
                                             ARGS := OLE2.CREATE_ARGLIST;
                                             V_TRNDATE:= OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                             i_ldata(rowcounter).VTRNDATE:=V_TRNDATE;
                                             -----------------------COLUMN3------------------------------------- 
                                             ARGS := OLE2.CREATE_ARGLIST; 
                                             OLE2.ADD_ARG(ARGS,CTR); 
                                             OLE2.ADD_ARG(ARGS,3); 
                                             CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                             OLE2.DESTROY_ARGLIST(ARGS);
                                             ARGS := OLE2.CREATE_ARGLIST;
                                             V_TTIME := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                             i_ldata(rowcounter).vttime:=v_ttime;
                                             -----------------------COLUMN4------------------------------------- 
                                             ARGS := OLE2.CREATE_ARGLIST; 
                                             OLE2.ADD_ARG(ARGS,CTR); 
                                             OLE2.ADD_ARG(ARGS,4); 
                                             CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS); 
                                             OLE2.DESTROY_ARGLIST(ARGS);
                                             ARGS := OLE2.CREATE_ARGLIST;
                                             V_TID := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                                             i_ldata(rowcounter).vtid:=v_tid;
                                                  
                                        EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;      
                                        ctr := ctr + 1; 
                                        cols := 1;
                                        
                                        PK_EXCEL_TO_DB.PR_DO_INSERT(vcColumn1,vcColumn2,vcColumn3,vcColumn4,I_lData);     
                              END LOOP;
                                   :progress:='EXCEL READING IS DONE...';
                                   ----------------CLOSE THE EXCEL SHEET AFTER READING--------------
                                   OLE2.INVOKE(APPLICATION,'QUIT'); 
                                   -----------------RELEASE ALL OBJECTS
                                   OLE2.RELEASE_OBJ(CELL); 
                                   OLE2.RELEASE_OBJ(WORKSHEET); 
                                   OLE2.RELEASE_OBJ(WORKBOOK); 
                                   OLE2.RELEASE_OBJ(WORKBOOKS); 
                                   OLE2.RELEASE_OBJ(APPLICATION); 
                                   :PROGRESS := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;
                                   SYNCHRONIZE;
                                   --exception  
                                   --WHEN OTHERS THEN 
                                   --MESSAGE(sqlerrm); 
                                -- now call the insertion-procedure
                              END;
                              and package
                              PACKAGE PK_EXCEL_TO_DB IS
                              TYPE tKeyValue IS RECORD (
                                  VROUTE         VARCHAR2(1000),
                                  VTRNDATE       VARCHAR2(1000),
                                  VTTIME         VARCHAR2(1000),
                                  VTID           VARCHAR2(1000));
                                  
                                TYPE tDataList IS TABLE OF tKeyValue;
                                PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList);     
                               
                              END;
                              
                              
                              PACKAGE BODY PK_EXCEL_TO_DB IS
                               PROCEDURE PR_DO_INSERT(i_vcColumn1 IN VARCHAR2,i_vcColumn2 IN VARCHAR2,i_vcColumn3 IN VARCHAR2,i_vcColumn4 IN VARCHAR2,i_lData IN tDataList) IS
                               
                                  vcInsert                           VARCHAR2(3500);
                                BEGIN
                                  FOR i IN 1..i_lData.COUNT LOOP
                                     
                                   vcInsert :='INSERT INTO TEST (' || vcColumn1 || ', ' || vcColumn2 || ', ' || vcColumn3 || ', '|| vcColumn4 || ')' ||
                                               ' VALUES (' || ''''||i_ldata(i).VROUTE ||''''|| ', ' ||''''|| i_ldata(i).VTRNDATE ||''''|| ', ' ||''''|| i_ldata(i).VTTIME ||''''|| ',' || ''''||i_ldata(i).VTID ||'''' || ')';
                                   FORMS_DDL(vcInsert);
                                   
                                  END LOOP;
                                  STANDARD.COMMIT;
                                  
                                END;
                              END;
                              But in insert clause
                              i.e vcInsert :='INSERT INTO TEST (' || vcColumn1 || ', ' || vcColumn2 || ', ' || vcColumn3 || ', '|| vcColumn4 || ')' ||
                              i am getting error
                              Error 201
                              idengifier 'vcColumn1' must be declare.

                              Thank you very much once again.

                              Sam

                              Edited by: sam8682 on May 27, 2013 3:17 PM
                              • 27. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                                Andreas Weiden
                                I don't have a running forms-system here, so i write all the code "from memory". If you look at the procedure signature and the insert-statement, you see that the parameters to the procedur start with i_vcColumn and in the insert statement there's only vcColumn. So,m adjust the variable-names in the insert.
                                • 28. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                                  sam8682
                                  Hello,
                                  I've modify this part of code, because while debugging it shows only first column heading and goes into infinite loop.
                                  orignal code
                                  BEGIN
                                    GO_BLOCK('BLK_MAIN');
                                   FIRST_RECORD;
                                    LOOP
                                     i:=i+1;
                                      IF i=1 THEN
                                        vcColumn1:=:BLK_MAIN.TAB_COL1; --     i_ldata(rowcounter).CROUTE:=C_ROUTE;
                                      ELSIF i=2 THEN
                                        vcColumn2:=:BLK_MAIN.TAB_COL2;
                                      ELSIF i=3 THEN
                                        vcColumn3:=:BLK_MAIN.TAB_COL3;
                                      ELSIF i=4 THEN
                                        vcColumn4:=:BLK_MAIN.TAB_COL4;
                                      END IF;
                                      NEXT_RECORD;
                                  END LOOP;
                                  Modified
                                  BEGIN
                                    GO_BLOCK('BLK_MAIN');
                                   FIRST_RECORD;
                                    LOOP
                                     i:=i+1;
                                      IF i=1 THEN
                                        vcColumn1:=:BLK_MAIN.TAB_COL1; --     i_ldata(rowcounter).CROUTE:=C_ROUTE;
                                      ELSIF i=2 THEN
                                        vcColumn2:=:BLK_MAIN.TAB_COL2;
                                      ELSIF i=3 THEN
                                        vcColumn3:=:BLK_MAIN.TAB_COL3;
                                      ELSIF i=4 THEN
                                        vcColumn4:=:BLK_MAIN.TAB_COL4;
                                      --END IF;
                                     ELSIF :SYSTEM.LAST_RECORD = 'TRUE' THEN
                                      EXIT;
                                      ELSE
                                      NEXT_RECORD;
                                      END IF;
                                      --EXIT WHEN :BLK_MAIN.TAB_COL1 is null;
                                  END LOOP;
                                   
                                    --  extract your column-values here
                                  .....
                                  .......
                                  Above modified code works properly but thing is that, my excel sheet have 8 rows but when i run the form 28 rows get inserted into the table.
                                  I also observe that when excel colum heading matches to table column heading then and then only data get insert into table.i.e
                                  excel             table
                                  --------------------
                                  col1(route)   route
                                  col2(tdate)   date
                                  col3(id)     id
                                  in the above case data gets inserted in to table.
                                  but when condition change
                                  i.e
                                  excel       table
                                  -------------------
                                  col1(route)     id
                                  col2(tdate)     route
                                  col3(id)       date
                                  no data gets inserted into table.

                                  Thanks again
                                  Sameer.
                                  • 29. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
                                    sam8682
                                    Thanks Andreas,
                                    I've modify this part of code, because while debugging it shows only first column heading and goes into infinite loop.
                                    orignal code is
                                    BEGIN
                                      GO_BLOCK('YOURBLOCK');
                                      FIRST_RECORD;
                                      LOOP
                                        i:=i+1;
                                        IF i=1 THEN
                                          vcColumn1:=:BLOCK.MAPPED_COLUMN;
                                        ELSIF i=2 THEN
                                          vcColumn2:=:BLOCK.MAPPED_COLUMN;
                                        ELSIF i=3 THEN
                                          vcColumn3:=:BLOCK.MAPPED_COLUMN;
                                        ELSIF i=4 THEN
                                          vcColumn4:=:BLOCK.MAPPED_COLUMN;
                                        END IF;
                                        NEXT_RECORD;
                                      END LOOP;
                                     
                                      --  extract your column-values here
                                    .....
                                    .......
                                    modified code
                                    BEGIN
                                      GO_BLOCK('BLK_MAIN');
                                     FIRST_RECORD;
                                      LOOP
                                       i:=i+1;
                                        IF i=1 THEN
                                          vcColumn1:=:BLK_MAIN.TAB_COL1; --     i_ldata(rowcounter).CROUTE:=C_ROUTE;
                                        ELSIF i=2 THEN
                                          vcColumn2:=:BLK_MAIN.TAB_COL2;
                                        ELSIF i=3 THEN
                                          vcColumn3:=:BLK_MAIN.TAB_COL3;
                                        ELSIF i=4 THEN
                                          vcColumn4:=:BLK_MAIN.TAB_COL4;
                                        --END IF;
                                       ELSIF :SYSTEM.LAST_RECORD = 'TRUE' THEN
                                        EXIT;
                                        ELSE
                                        NEXT_RECORD;
                                        END IF;
                                        --EXIT WHEN :BLK_MAIN.TAB_COL1 is null;
                                    END LOOP;
                                        --extract your column-values here
                                    Above modified code works properly but thing is that, my excel sheet have 8 rows but when i run the form 28 rows get inserted into the table.
                                    I also observe that when excel colum heading matches to table column heading then and then only data get insert into table.i.e
                                    excel           table
                                    --------------------
                                    col1(route)   route
                                    col2(tdate)   date
                                    col3(id)     id
                                    in this case data gets inserted, but when condition change
                                    i.e
                                    excel         table
                                    -------------------
                                    col1(route)     id
                                    col2(tdate)     route
                                    col3(id)       date
                                    no data gets inserted into table.

                                    Thanks again
                                    Sameer.