Hi i am using oracle form 6i with database 10g in 2 tire. OS: Windows.
I have working OLE2 code to Read a XLS or XLSX file and get the cell value in a variable and the insert them into a Oracle table.
In the same way i want to get OpenOffice or LibreOffice file (ODS) cell value into oracle table.
Can any one provide a working script. Thank you.
I have tried different combination like this: It is start throwing error from the line
" WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'spreadsheet'); "
------------------------------------------------------------------------
DECLARE
-- OLE2 VARIABLES
APPLICATION OLE2.OBJ_TYPE;
WORKBOOKS OLE2.OBJ_TYPE;
WORKBOOK OLE2.OBJ_TYPE;
--WORKSHEETS OLE2.OBJ_TYPE;
WORKSHEET OLE2.OBJ_TYPE;
CELL OLE2.OBJ_TYPE;
ARGS OLE2.OBJ_TYPE;
--PARAMETER SECTION
--cFILE_NAME VARCHAR2(1000):='R:\MFGWORK\TARUN\FORMS\Excel to Oracle\SAMPLE\SHEET1.xlsX';
cFILE_NAME VARCHAR2(1000);
nSHEET_NO NUMBER := 1;
nREADMAX_ROW NUMBER :=0;
nREADMAX_COL NUMBER :=0;
nBLANK_LINE NUMBER :=0;
nBLANK_COUNT NUMBER :=0;
cBLANK_LINE CHAR(1):='N';
--PL/SQL VARIABLES
nCURRENT_ROW NUMBER :=NVL(P_START_ROW_NUMBER,1)-1;--nCURRENT_ROW NUMBER :=0;
nSTART_COL NUMBER :=NVL(P_START_COL_NUMBER,1)-1; --** COLUMN NUMBER FROM WHICH DATA READ SHOULD START
nCOL_POSITION NUMBER ;--** nTH COLUMN OF THE COLUMN READING RANGE
nCURRENT_COL NUMBER :=0;
cCURRENT_CELL IMPORTEDFROMEXCEL.COL1%TYPE;
cCURRENT_COL VARCHAR2(61);
vSTART DATE;
vEND DATE;
nGAP NUMBER;
cGAP VARCHAR2(100);
cTABLE IMPORTEDFROMEXCEL%ROWTYPE;
cERR VARCHAR2(1000);
nSessionID NUMBER :=USERENV('SESSIONID');
cCMD VARCHAR2(1000);
BEGIN
SET_APPLICATION_PROPERTY(CURSOR_STYLE,'BUSY');
-----------CHECKING IF A FILE NAME HAS BEEN GIVEN OR NOT-----------
IF P_EXCELFILE_PATH_AND_NAME IS NULL THEN
P_OUTPUT_MESSAGE := 'NO EXCEL FILE HAS BEEN SELECTED.';
GOTO LASTLINE; --** IF FILE NAME IS NULL THEN JUST STOP WORKING
ELSIF UPPER(SUBSTR(P_EXCELFILE_PATH_AND_NAME,-4)) NOT IN ('XLSX','.XLS') THEN
P_OUTPUT_MESSAGE := 'A NON EXCEL FILE HAS BEEN SELECTED.';
GOTO LASTLINE; --** IF THE FILE EXTENSION IS NOT XLS OR XLSX THE STOP WORKING
ELSE
cFILE_NAME := P_EXCELFILE_PATH_AND_NAME;
END IF;
----------------SETTING SHEET NUMBER TO READ---------------
IF NVL(P_SHEET_NUM_TO_READ,0)= 0 THEN
nSHEET_NO := 1; --** IF NO "SHEET NUMBER" OPTION IS GIVEN OR OPTION IS 0 THEN READ SHEET 1
ELSE
nSHEET_NO := P_SHEET_NUM_TO_READ; --** ELSE READ SHEET MENTIONED BY PARTY
END IF;
----------------SETTING MAX NUMBER OF ROW NUMBER TO READ---------------
IF NVL(P_NUMBER_OF_ROW_TILL_READ,0) = 0 THEN
nREADMAX_ROW := 0;
ELSE
nREADMAX_ROW := P_NUMBER_OF_ROW_TILL_READ;
END IF;
IF nREADMAX_ROW NOT BETWEEN 1 AND 1048576 THEN
nREADMAX_ROW := 1048576; --** IF "MAX LINE TO READ" IS NOT BETWEEN 1 AND 1048576 THEN READ MAXIMUM 1048576 LINE
cBLANK_LINE := 'Y'; --** AND ALSO START BLANK LINE CHECKING
nBLANK_LINE := 5; --** IF THE SYSTEM FOUND "Consecutive 5" BLANK LINE THEN STOP
END IF;
IF NVL(P_STOP_IF_FOUND_BLANK_ROWS,0) > 0 THEN
cBLANK_LINE := 'Y'; --** AND ALSO START BLANK LINE CHECKING
nBLANK_LINE := P_STOP_IF_FOUND_BLANK_ROWS; --** IF THE SYSTEM FOUND "Consecutive 5" BLANK LINE THEN STOP
END IF;
----------------SETTING MAX NUMBER OF COLUMN TO READ---------------
SELECT COUNT(1)-4 INTO nREADMAX_COL FROM COL C WHERE C.tname = 'IMPORTED_FROM_EXCEL';
IF nREADMAX_COL = -4 THEN
P_OUTPUT_MESSAGE := 'THE BASE TABLE "IMPORTEDFROMEXCEL" IS MISSING.';
GOTO LASTLINE; --** THE DATABASE TABLE WHERE THE DATE SHOULD STORE IS MISSING
ELSIF NVL(P_NUMBER_OF_COLUMN_TILL_READ,0) = 0 THEN --** THAT IS NO COLUMN RANGE SET
nREADMAX_COL := 70 - NVL(P_START_COL_NUMBER,1) +1;
ELSIF NVL(P_START_COL_NUMBER,1) >= 1 THEN
nREADMAX_COL := P_NUMBER_OF_COLUMN_TILL_READ - NVL(P_START_COL_NUMBER,1) +1 ;
END IF;
--------------DELETE ANY EXISTING RECORD FROM CURRENT SESSION------
IF NVL(P_FLASH_LAST_FETCH,'Y') <> 'N' THEN
DELETE IMPORTEDFROMEXCEL WHERE USER_SESSION_ID LIKE nSessionID||'%';
END IF;
--------------INITIATE EXCEL APPLICATION---------------------------
APPLICATION := OLE2.CREATE_OBJ('com.sun.star.sheet');--xxAPPLICATION := OLE2.CREATE_OBJ('EXCEL.APPLICATION');
--OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','TRUE');
OLE2.SET_PROPERTY(APPLICATION,'VISIBLE','FALSE');
----------------GET WORKBOOKS FROM EXCEL APPLICATION---------------
WORKBOOKS := OLE2.GET_OBJ_PROPERTY(APPLICATION, 'spreadsheet');
----------------OPEN REQUIRED WORKBOOK-----------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,cFILE_NAME);
WORKBOOK := OLE2.GET_OBJ_PROPERTY(WORKBOOKS,'OPEN',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
----------------OPEN REQUIRED WORKSHEET---------------------------
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,nSHEET_NO);
WORKSHEET := OLE2.GET_OBJ_PROPERTY(WORKBOOK,'WORKSHEETS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
vSTART := SYSDATE;
----------------GET CELL VALUE-------------------------------------
LOOP
nCURRENT_ROW := nCURRENT_ROW+1;
--***********-----------
IF MOD(nCURRENT_ROW,50)= 0 THEN
IF P_SHOW_PROGRESS_AT IS NOT NULL THEN
Copy( 'Reading Row number::'|| nCURRENT_ROW, P_SHOW_PROGRESS_AT );
SYNCHRONIZE;
END IF;
COMMIT;
END IF;
--***********-----------
nCURRENT_COL := nSTART_COL;
-----------------------RESETTING CELL VALUE------------------------
cTABLE := NULL;
cTABLE.USER_SESSION_ID := nSessionID;
cTABLE.SRLNO := nCURRENT_ROW;
------READING COLUMNS----------
FOR I IN 1.. nREADMAX_COL LOOP
nCURRENT_COL := nCURRENT_COL+1;
cCURRENT_CELL:= NULL;
ARGS := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(ARGS,nCURRENT_ROW);
OLE2.ADD_ARG(ARGS,nCURRENT_COL);
CELL := OLE2.GET_OBJ_PROPERTY(WORKSHEET,'CELLS',ARGS);
OLE2.DESTROY_ARGLIST(ARGS);
--ARGS := OLE2.CREATE_ARGLIST;
cCURRENT_CELL := OLE2.GET_CHAR_PROPERTY(CELL,'TEXT');
OLE2.RELEASE_OBJ(cell);
...........