2 Replies Latest reply: Nov 9, 2012 1:16 AM by Tarun.Oracle RSS

    ole2 error when reading excel from form 6i

    khan786
      APPLICATION OLE2.OBJ_TYPE;
      WORKBOOKS OLE2.OBJ_TYPE;
      WORKBOOK OLE2.OBJ_TYPE;
      WORKSHEETS OLE2.OBJ_TYPE;
      WORKSHEET OLE2.OBJ_TYPE;
      CELL OLE2.OBJ_TYPE;

      SROW NUMBER:=0;
      COL NUMBER:=0;
      CELLVALUE VARCHAR2(89);
      V_RC_1 VARCHAR2(1000); -- TRANSACTION DATE
      V_RC_2 VARCHAR2(1000); -- STORE #
      V_RC_3 VARCHAR2(1000); -- INVOICE #
      V_RC_4 VARCHAR2(1000); -- CASES SOLD
      V_RC_5 VARCHAR2(1000);
      V_RC_6 VARCHAR2(1000);

      ARGS OLE2.OBJ_TYPE;

      BEGIN
           
           :STIME := TO_CHAR(SYSDATE, 'HH:MI:SS');
      SYNCHRONIZE;
           
           --------------INITIATE EXCEL APPLICATION---------------------------
      APPLICATION := OLE2.CREATE_OBJ('EXCEL.APPLICATION');
      OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','TRUE');



      ----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------

      WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'WORKBOOKS');



      ----------------OPEN REQUIRED WORKBOOK-----------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,:VFILE);
      WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);



      ----------------OPEN REQUIRED WORKSHEET---------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,1);
      WORKSHEET := OLE2.GET_OBJ_PROPERTY(WORKBOOK,'WORKSHEETS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);

      ----------------GET CELL VALUE-------------------------------------

           
      LOOP
           SROW := SROW+1;
      -----------------------COLUMN1-------------------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,SROW);
      OLE2.ADD_ARG(ARGS,1);
      CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);
      ARGS := OLE2.CREATE_ARGLIST;
      V_RC_1 := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');

      -----------------------COLUMN2-------------------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,SROW);
      OLE2.ADD_ARG(ARGS,2);
      CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);
      ARGS := OLE2.CREATE_ARGLIST;
      V_RC_2 := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
      -----------------------COLUMN3-------------------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,SROW);
      OLE2.ADD_ARG(ARGS,3);
      CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);
      ARGS := OLE2.CREATE_ARGLIST;
      V_RC_3 := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
      -----------------------COLUMN4-------------------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,SROW);
      OLE2.ADD_ARG(ARGS,4);
      CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);
      ARGS := OLE2.CREATE_ARGLIST;
      V_RC_4 := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
      -----------------------COLUMN5-------------------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,SROW);
      OLE2.ADD_ARG(ARGS,5);
      CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);
      ARGS := OLE2.CREATE_ARGLIST;
      V_RC_5 := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
      -----------------------COLUMN6-------------------------------------
      ARGS := OLE2.CREATE_ARGLIST;
      OLE2.ADD_ARG(ARGS,SROW);
      OLE2.ADD_ARG(ARGS,6);
      CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
      OLE2.DESTROY_ARGLIST(ARGS);
      ARGS := OLE2.CREATE_ARGLIST;
      V_RC_6 := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');

      IF SROW >8 THEN
           EXIT;
      END IF;



      ---------------INSERT THE VALUE IN THE TABLE ---------------------
      INSERT INTO EMP VALUES(V_RC_1,V_RC_2,V_RC_3,V_RC_4,V_RC_5,V_RC_6);
      END LOOP;
      --END LOOP;

      COMMIT;


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




      :MESSAGE := 'DATA INSERTED INTO THE TABLE '; SYNCHRONIZE;

      :ETIME := TO_CHAR(SYSDATE, 'HH:MI:SS');
      SYNCHRONIZE;
      END;
        • 1. Re: ole2 error when reading excel from form 6i
          khan786
          this code return error of ora-305500 non-oracle exception..
          the first row in read but when the loop go to next row that is srow=2 then it return error..

          Can any one help me.
          • 2. Re: ole2 error when reading excel from form 6i
            Tarun.Oracle
            Hi Kundi,
            by this time you might have solve your problem.

            If not, in your case,
            This could be you or user is closing the "Excel" application just appearing in front of there screen.

            To solve this replace the following line with the new line of code in your application:

            OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','TRUE');
            with
            OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','FALSE');

            Hope this will solve the problem.

            Regards,

            Tarun

            Edited by: Tarun.Oracle on Nov 9, 2012 12:44 PM

            Edited by: Tarun.Oracle on Nov 9, 2012 12:45 PM