3 Replies Latest reply: Apr 10, 2013 5:49 AM by sam8682 RSS

    importing only header of excel to oracle forms getting error  ORA-06502

    sam8682
      Hello,
      While importing excel column header to oracle forms 6i I am getting this error.
      "ORA-06502:PL/SQL : numeric value error" . column headers get displayed on my form but i get this error at the end.
      my code is
      PROCEDURE COLUMN_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);
           V_ID VARCHAR2(1000);
           V_TDATE VARCHAR2(1000);
           V_TTIME VARCHAR2(1000);
      ARGS OLE2.OBJ_TYPE;

      BEGIN
                
                     :progress:='Please wait...';
                     SYNCHRONIZE;
                     filename :=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');                
                     WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
                     ARGS := OLE2.CREATE_ARGLIST;
                     OLE2.ADD_ARG(ARGS, FILENAME);
                     WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
                     OLE2.DESTROY_ARGLIST(ARGS);
                     ARGS := OLE2.CREATE_ARGLIST;
                     OLE2.ADD_ARG(ARGS,'Sheet1');
                     WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
                     OLE2.DESTROY_ARGLIST(ARGS);
                     ctr := 1; --row number
                     cols := 1; -- column number
                     GO_BLOCK('BLOCK3');
                     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;
                     V_ROUTE := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
                     :COL1 :=V_ID;
                     -----------------------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');
                     :COL2 :=V_TDATA;
                     -----------------------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');
                     :COL3     :=V_TTIME;
                     

      OLE2.INVOKE(APPLICATION,'QUIT');
      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;

      please help.
        • 1. Re: importing only header of excel to oracle forms getting error  ORA-06502
          François Degrelle
          Hello,

          Run the form in debug mode to what on what line the error appears.

          Francois
          • 2. Re: importing only header of excel to oracle forms getting error  ORA-06502
            sam8682
            Thanks for reply Francois
            My actual code is

            PROCEDURE COLUMN_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);
                 V_ID VARCHAR2(1000);
                 V_TDATE VARCHAR2(1000);
                 V_TTIME VARCHAR2(1000);
            ARGS OLE2.OBJ_TYPE;

            BEGIN
                      
                           :progress:='Please wait...';
                           SYNCHRONIZE;
                           filename :=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');                
                           WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');
                           ARGS := OLE2.CREATE_ARGLIST;
                           OLE2.ADD_ARG(ARGS, FILENAME);
                           WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
                           OLE2.DESTROY_ARGLIST(ARGS);
                           ARGS := OLE2.CREATE_ARGLIST;
                           OLE2.ADD_ARG(ARGS,'Sheet1');
                           WORKSHEET := OLE2.GET_OBJ_PROPERTY (WORKBOOK,'WORKSHEETS',ARGS);
                           OLE2.DESTROY_ARGLIST(ARGS);
                           ctr := 1; --row number
                           cols := 1; -- column number
                           GO_BLOCK('BLOCK3');
                           FIRST_RECORD;

                           --FOR I IN 1..1
                           --I := 1;
                           --LOOP
                           -----------------------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');
                           :COL1 :=V_ID;
                           -----------------------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');
                           :COL2 :=V_TDATA;
                           -----------------------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');
                           :COL3     :=V_TTIME;
            last_record;
            :progress:='Excel Column for Mapping are Generated...';

            OLE2.INVOKE(APPLICATION,'QUIT');

            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;

            while debug it goes upto 'progress : Excel Column for Mapping are Generated ' then goes to exception section.
            all column headers are displayed on the form with the error.

            Thanks again
            Sam

            Edited by: sam8682 on Apr 10, 2013 3:11 PM

            Edited by: sam8682 on Apr 10, 2013 3:15 PM
            • 3. Re: importing only header of excel to oracle forms getting error  ORA-06502
              sam8682
              hello François,

              problem is solved. small change i've done
              just remove these two lines

              last_record;
              :progress:='Excel Column for Mapping are Generated...';

              length of progress filed 30, i change it to 100 and just commit the last_record,
              and it works.
              Thank u.

              Edited by: sam8682 on Apr 10, 2013 4:18 PM