This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: May 30, 2013 4:13 AM by sam8682 RSS

ORA-06531 reference to uninitialized collection in oracle 6i form

sam8682 Newbie
Currently Being Moderated
Hello,

I am importing data from excel to database table with column mapping (oracle 6i forms)using ole2 package, then calling package

which is having a procedure. while executing the package procedure,

I am getting error 'ora-06531 reference to uninitialized collection' i.e. before for i in 1..in_lData.count

I am working on it but still not getting any solution. There is no problem in coding my form gets compiled but at run time i am getting this error.

And while debugging, data fetched from ole2 package is not passed to for loop of the procedure.
(first of all i am calling ole 2 package, then from ole2 package i am calling my package. procedure)

please help me. My code is this

------------------------------------------------------Ole 2 Package begins-----------------------------------------------------------------
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;

     tDataList     PK_EXCEL_TO_DB.tDataList;

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

               -----------------------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');

          

          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');

               -----------------------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');

               

               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');

               -----------------------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');

               

               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');

               -----------------------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');

               

               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');



PK_EXCEL_TO_DB.PR_DO_INSERT(tDataList);                          

EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;

ctr := ctr + 1;

          cols := 1;

i_ldata := PK_EXCEL_TO_DB.tDatalist(); --already define                    

PK_EXCEL_TO_DB.PR_DO_INSERT(tDataList);

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);

END;

----------------------------------------------------------------------------------------OLE2 PACKAGE ENDS-------------------------------------------------



------------------------------------------------------------PK_EXCEL_TO_DB PACKAGE SPECIFICATION---------------------------------------------

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;

PROCEDURE PR_DO_INSERT(i_lData IN tDataList);     

END;
-----------------------------------------------------------------PK_EXCEL_TO_DB PACKAGE BODY-----------------------------------------------------
PACKAGE BODY PK_EXCEL_TO_DB IS

PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS

CC_ROUTE VARCHAR2(255);

VV_ROUTE VARCHAR2(1000);

CC_TRNDATE VARCHAR2(255);

VV_TRNDATE VARCHAR2(1000);

CC_TTIME     VARCHAR2(255);

VV_TTIME VARCHAR2(1000);

CC_TID VARCHAR2(255);

VV_TID VARCHAR2(1000);

BEGIN

FOR i IN 1..i_lData.count

LOOP

CC_ROUTE:=CC_ROUTE || ',' || i_ldata(i).CROUTE;

VV_ROUTE:=VV_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';



CC_TRNDATE :=CC_TRNDATE || ',' || i_ldata(i).CTRNDATE ;

VV_TRNDATE :=VV_TRNDATE || ',''' || i_ldata(i).VTRNDATE || '''';



CC_TTIME :=CC_TTIME || ',' || i_ldata(i).CTTIME ;

VV_TTIME :=VV_TTIME || ',''' || i_ldata(i).VTTIME || '''';



CC_TID :=CC_TID || ',' || i_ldata(i).CTID ;

VV_TID :=VV_TID || ',''' || i_ldata(i).VTID|| '''';

END LOOP;

--EXECUTE IMMEDIATE

FORMS_DDL('INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' ||

                                                  SUBSTR(CC_TRNDATE, 2) || ' ' ||

                                                  SUBSTR(CC_TTIME, 2) || '' ||

                                                  SUBSTR(CC_TID, 2) || ')

VALUES (' || SUBSTR(VV_ROUTE,2) || ' ' ||

                                   SUBSTR(VV_TRNDATE,2) || '' ||

                                   SUBSTR(VV_TTIME,2) || ' ' ||

                                   SUBSTR(VV_TID,2)|| ')');

commit;

END;

END;

------------------------------------------------------------------------PK_EXCEL_TO_DB PACKAGE BODY ENDS--------------------------------------

Thank You
Sameer.
  • 1. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    You should check how the collection is defined (PK_EXCEL_TO_DB.tDataList).

    Francois
  • 2. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    Andreas Weiden Guru
    Currently Being Moderated
    tDataList is a collection, so you have to initialize it:
    tDataList:=PK_EXCEL_TO_DB.tDataList();
  • 3. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    Thanks Andreas,
    I've modified my code as per ur instruction. my modified code is given below. my package procedure runs properly now without any error. while debuggin the from, in ther procedure part of the package,debugger directly jumps to insert into clause of procedure.
    and no data is get inserted into table. array is not get populated with anything. I am trying hard for that but not able to get the reason. Please suggest something
         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;
         i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
         tDataList     PK_EXCEL_TO_DB.tDataList;
    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
              tDataList:=PK_EXCEL_TO_DB.tDataList();     
                   -----------------------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');
              
              
              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');
                   :D1:=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');
                   
                   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');
                   -----------------------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');
                   
                   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');
                   -----------------------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');
                   
                   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');
         
         PK_EXCEL_TO_DB.PR_DO_INSERT(tDataList);     --i_ldata                    
         --i_ldata := PK_EXCEL_TO_DB.tDatalist();
         
    EXIT WHEN length(V_Route) = 0 or length(V_Route) is null;
    ctr := ctr + 1;
    cols := 1;
    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);
    END;
    >
    package specification and body is given below
    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;
    PROCEDURE PR_DO_INSERT(i_lData IN tDataList);     

    END;
    -----------------------------------------------------------------PK_EXCEL_TO_DB PACKAGE BODY-----------------------------------------------------
    PACKAGE BODY PK_EXCEL_TO_DB IS
    PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
    CC_ROUTE VARCHAR2(255);
    VV_ROUTE VARCHAR2(1000);
    CC_TRNDATE VARCHAR2(255);
    VV_TRNDATE VARCHAR2(1000);
    CC_TTIME     VARCHAR2(255);
    VV_TTIME VARCHAR2(1000);
    CC_TID VARCHAR2(255);
    VV_TID VARCHAR2(1000);
    BEGIN
    FOR i IN 1..i_lData.count
    LOOP
    CC_ROUTE:=CC_ROUTE || ',' || i_ldata(i).CROUTE;
    VV_ROUTE:=VV_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';
    CC_TRNDATE :=CC_TRNDATE || ',' || i_ldata(i).CTRNDATE ;
    VV_TRNDATE :=VV_TRNDATE || ',''' || i_ldata(i).VTRNDATE || '''';
    CC_TTIME :=CC_TTIME || ',' || i_ldata(i).CTTIME ;
    VV_TTIME :=VV_TTIME || ',''' || i_ldata(i).VTTIME || '''';
    CC_TID :=CC_TID || ',' || i_ldata(i).CTID ;
    VV_TID :=VV_TID || ',''' || i_ldata(i).VTID|| '''';
    END LOOP;
    FORMS_DDL('INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' ||
    SUBSTR(CC_TRNDATE, 2) || ' ' ||
    SUBSTR(CC_TTIME, 2) || '' ||
    SUBSTR(CC_TID, 2) || ')
    VALUES (' || SUBSTR(VV_ROUTE,2) || ' ' ||
    SUBSTR(VV_TRNDATE,2) || '' ||
    SUBSTR(VV_TTIME,2) || ' ' ||
    SUBSTR(VV_TID,2)|| ')');
    commit;
    END;
    END;
    >
    Please help me.
    Thanks again.
    Regards
    Sameer
  • 4. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    Thanks François Degrelle,
    for your reply.
  • 5. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    <p>In case you would like to study more about collections</p>
    Francois
  • 7. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    Andreas Weiden Guru
    Currently Being Moderated
    Well, you never put anything into the collection. You just re-initialize it in every loop. You would just initialize it once (as you already did with the declaration), do an lData.EXTEND(1) in the loop and then assign the field-values to the collection-values. You should follow Francois' suggestion and read a bit about collections.
  • 8. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    Hii Andreas Weiden,
    Sorry for replying late. I am very thankful to Francois and your for your suggestion. I've gone through the documentation of collection and finally i got the solution.
    But I am unable to insert rercord to the table. Values are passed to the procedure, i am able to see the values while debugging and i get the message
    'DATA INSERTED INTO THE TABLE' but when i check it with sql, the table is empty. I've tried commit and standard.commit, but it doesn't works. My modified code is given below.
    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;
         i_ldata PK_EXCEL_TO_DB.tDataList:=PK_EXCEL_TO_DB.tDataList();
         tDataList     PK_EXCEL_TO_DB.tDataList;
    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(ctr); 
                        i_ldata.extend(cols); 
                        --tDataList:=PK_EXCEL_TO_DB.tDataList();
                   -----------------------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(cols).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(ctr).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(cols).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(ctr).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(cols).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(ctr).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(cols).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(ctr).vtid:=v_tid;
                   
         PK_EXCEL_TO_DB.PR_DO_INSERT(i_ldata);               
    --     i_ldata := PK_EXCEL_TO_DB.tDatalist();
         
    EXIT WHEN length(V_Route) = 0 or length(V_Route) is null; 
    ctr := ctr + 1; 
    cols := 1;
    
    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); 
    END;
    Package specification and package body 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;
           PROCEDURE PR_DO_INSERT(i_lData IN tDataList);     
     
    END;
    -----------------------------------------------------------------PK_EXCEL_TO_DB PACKAGE BODY-----------------------------------------------------
    PACKAGE BODY PK_EXCEL_TO_DB IS
              PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
              CC_ROUTE VARCHAR2(255);
              VV_ROUTE VARCHAR2(1000);
              CC_TRNDATE VARCHAR2(255);
              VV_TRNDATE VARCHAR2(1000);
              CC_TTIME      VARCHAR2(255);
              VV_TTIME VARCHAR2(1000);
              CC_TID VARCHAR2(255);
              VV_TID VARCHAR2(1000);
    BEGIN
            FOR i IN 1..i_lData.count 
           LOOP
                        CC_ROUTE:=CC_ROUTE || ',' || i_ldata(i).CROUTE;
                        VV_ROUTE:=VV_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';
                        CC_TRNDATE :=CC_TRNDATE || ',' || i_ldata(i).CTRNDATE ;
                        VV_TRNDATE :=VV_TRNDATE || ',''' || i_ldata(i).VTRNDATE || ''''; 
                        CC_TTIME :=CC_TTIME || ',' || i_ldata(i).CTTIME ;
                        VV_TTIME :=VV_TTIME || ',''' || i_ldata(i).VTTIME || '''';
                        CC_TID :=CC_TID || ',' || i_ldata(i).CTID ;
                        VV_TID :=VV_TID || ',''' || i_ldata(i).VTID|| '''';
             END LOOP;
              FORMS_DDL('INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' || 
                                 SUBSTR(CC_TRNDATE, 2) || ' ' || 
                                 SUBSTR(CC_TTIME, 2) || '' || 
                                 SUBSTR(CC_TID, 2) || ') 
                    VALUES (' || SUBSTR(VV_ROUTE,2) || ' ' || 
                                    SUBSTR(VV_TRNDATE,2) || '' || 
                                    SUBSTR(VV_TTIME,2) || ' ' || 
                                    SUBSTR(VV_TID,2)|| ')'); 
    --standard.commit;
          commit; 
     END;
    END;
    Thanks
    Sam

    Edited by: sam8682 on May 13, 2013 2:35 PM
  • 9. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    Andreas Weiden Guru
    Currently Being Moderated
    Your whole logic doesn't make sense to me.

    In each loop, you extend your collection by 3 entries (once with cols and once with ctr), but then your write some values in entry 1 and some values in entry 2, but never in the newly added entries (which would be 4,5 and 6 in the second loop).
    Then you call your Insert to db for each looping, which would insert record 1 over and over again.
    In your insert loop, you add column to your column-list again and again, so that it will contain the columns more than once starting with the second loop.

    What to do:
    - Extend your collection only by one for each loop
    - store the number of the record in a variable and use it to populate the appropiate record in your collection
    - call you insert-logic only once after the loop
    - adjust your insert-logic
    - as your insert is a string, output it to see if the syntax is correct.
    - get rid of your WHEN OTHERS-exception handlers.
  • 10. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    Thanks for ur reply Andreas,
    I've modify my code. I am able to see the record while debugging the code, i also get the message 'Data inserted successfully'. But still no data is save to database table. I don't understand, still anything missing in the code/ Please help.
    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;     
                               
                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;
                   
                   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;
                   
                   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;
                   
                   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;
    Thanks & Regards
    Sam.
  • 11. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    Andreas Weiden Guru
    Currently Being Moderated
    and what about the procedure which does the actual insert? Did you output the resulting insert-statement? Can you show it to us?
  • 12. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    code is
    package specification
    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
    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 || '''';
          
          C_TRNDATE :=C_TRNDATE  || ',' || i_ldata(i).CTRNDATE ;
          V_TRNDATE :=V_TRNDATE || ',''' || i_ldata(i).VTRNDATE  || '''';
          
          C_TTIME :=C_TTIME  || ',' || i_ldata(i).CTTIME ;
          V_TTIME :=V_TTIME || ',''' || i_ldata(i).VTTIME  || '''';
          
          C_TID :=C_TID  || ',' || i_ldata(i).CTID ;
          V_TID :=V_TID || ',''' || i_ldata(i).VTID|| '''';
          
        END LOOP;
        
        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;
    --COMMIT;                                    
      END;
    END;
    Thank u.
    Sam

    Edited by: sam8682 on May 17, 2013 9:49 AM
  • 13. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    sam8682 Newbie
    Currently Being Moderated
    Is there any mistake in the code?
  • 14. Re: ORA-06531 reference to uninitialized collection in oracle 6i form
    Andreas Weiden Guru
    Currently Being Moderated
    Yes, the construction of the insert will not work. Younconcat the columns for al records in your collection, so you have the column-names more than oncer. Same with the values. And then you do only one insert after the loop. As previously said, output the resulting inse-statemene t to a forms-item and check it.
1 2 3 Previous Next

Legend

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