This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: May 30, 2013 4:13 AM by sam8682 Go to original post RSS
  • 15. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points