This discussion is archived
2 Replies Latest reply: Nov 8, 2012 11:16 PM by Tarun.Oracle RSS

ole2 error when reading excel from form 6i

khan786 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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