Sunday, May 8, 2011

Reading and Writing Excel file (.xls) From PL/SQL Using COM - PART I

This is also based on one of my posting in OTN. We can use COM to read/write excel from PL/SQL.
My orawpcom.dll file exists in the directory C:\oracle\product\10.2.0\db_2\bin

C:\oracle\product\10.2.0\db_2\bin>dir orawpco*.dll
 Volume in drive C is C_Drive
 Volume Serial Number is 8A93-1441
 
 Directory of C:\oracle\product\10.2.0\db_2\bin
 
03/20/2006  05:06 PM            61,440 orawpcom.dll
10/11/2006  03:20 PM            81,920 orawpcom10.dll
               2 File(s)        143,360 bytes
               0 Dir(s)  65,407,717,376 bytes free
 
C:\oracle\product\10.2.0\db_2\bin>


Information about my database version.

SQL> /* My databaser version */
SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
 
SQL>
 
Prepare the user SCOTT for COM automation.

Now, I will run comwrap.sql from scott user. I have edited the comwrap.sql to adjust my library path here:

create library utils_lib as 'C:\oracle\product\10.2.0\db_3\bin\orawpcom.dll'
 
 Running comwrap.sql and ExcelSolution.sql .....
SQL> conn scott@orclsb
Enter password: *****
Connected.
 
SQL> @c:\comwrap.sql
drop library utils_lib
*
ERROR at line 1:
ORA-04043: object UTILS_LIB does not exist
 
 
 
Library created.
 
drop package ORDCOM
*
ERROR at line 1:
ORA-04043: object ORDCOM does not exist
 
 
drop TYPE OAArgTable
*
ERROR at line 1:
ORA-04043: object OAARGTABLE does not exist
 
 
 
Type created.
 
drop TYPE OAArgTypeTable
*
ERROR at line 1:
ORA-04043: object OAARGTYPETABLE does not exist
 
 
 
Type created.
 
drop function OAgetNumber
*
ERROR at line 1:
ORA-04043: object OAGETNUMBER does not exist
 
 
 
Function created.
 
drop function OAgetStr
*
ERROR at line 1:
ORA-04043: object OAGETSTR does not exist
 
 
 
Function created.
 
drop function OAgetBool
*
ERROR at line 1:
ORA-04043: object OAGETBOOL does not exist
 
 
 
Function created.
 
drop function OAsetNumber
*
ERROR at line 1:
ORA-04043: object OASETNUMBER does not exist
 
 
 
Function created.
 
drop function OAsetString
*
ERROR at line 1:
ORA-04043: object OASETSTRING does not exist
 
 
 
Function created.
 
drop function OAsetBoolean
*
ERROR at line 1:
ORA-04043: object OASETBOOLEAN does not exist
 
 
 
Function created.
 
drop function OAInvokeDouble
*
ERROR at line 1:
ORA-04043: object OAINVOKEDOUBLE does not exist
 
 
 
Function created.
 
drop function OAInvokeBoolean
*
ERROR at line 1:
ORA-04043: object OAINVOKEBOOLEAN does not exist
 
 
 
Function created.
 
drop function OAInvokeString
*
ERROR at line 1:
ORA-04043: object OAINVOKESTRING does not exist
 
 
 
Function created.
 
drop function OACreate
*
ERROR at line 1:
ORA-04043: object OACREATE does not exist
 
 
 
Function created.
 
drop function OADestroy
*
ERROR at line 1:
ORA-04043: object OADESTROY does not exist
 
 
 
Function created.
 
drop function OAGetLastError
*
ERROR at line 1:
ORA-04043: object OAGETLASTERROR does not exist
 
 
 
Function created.
 
drop function OAQueryMethods
*
ERROR at line 1:
ORA-04043: object OAQUERYMETHODS does not exist
 
 
 
Function created.
 
 
Package created.
 
 
Package body created.
 
SQL> 
 
SQL> @c:\ExcelSolution.sql
drop package ORDExcel
*
ERROR at line 1:
ORA-04043: object ORDEXCEL does not exist
 
 
 
Package created.
 
 
Package body created.
 
SQL>
 
I have modified ORDExcel a little bit and renamed it as 
ORDExcelSB. You need this version for reading the excel.

 SQL> @C:\ExcelSolutionSB.sql
 
Package dropped.
 
 
Package created.
 
 
Package body created.
 
SQL>
 
The actual code of  ORDExcelSB (ExcelSolutionSB.sql) Is:

set serveroutput on;
drop package ORDExcelSB; 
CREATE PACKAGE ORDExcelSB AS
 
 
   /* Declare externally callable subprograms. */
   
   FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer;
   
   FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2) RETURN binary_integer;
    
   FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer;
 
   FUNCTION InsertData(range varchar2, data binary_integer, type varchar2) return binary_integer;
 
   FUNCTION InsertDataReal(range varchar2, data double precision, type varchar2) return binary_integer;
 
   FUNCTION GetDataNum(range varchar2) return binary_integer;
 
   FUNCTION GetDataStr(range varchar2) return varchar2;
 
   FUNCTION GetDataReal(range varchar2) return double precision;
 
   FUNCTION GetDataDate(range varchar2) return date;
 
   FUNCTION InsertData(range varchar2, data varchar2, type varchar2) return binary_integer;
 
   FUNCTION InsertData(range varchar2, data Date, type varchar2) return binary_integer;
 
   FUNCTION InsertChart(xpos binary_integer, ypos binary_integer, width binary_integer, 
      height binary_integer, range varchar2, type varchar2) return binary_integer;
 
 
   FUNCTION SaveExcelFile(filename varchar2) return binary_integer;
   
   FUNCTION ExitExcel return binary_integer;
 
END ORDExcelSB;
 

CREATE PACKAGE BODY ORDExcelSB AS
 
   DummyToken  binary_integer; 
   applicationToken binary_integer:=-1;
   WorkBooksToken binary_integer:=-1;
   WorkBookToken binary_integer:=-1;
   WorkSheetToken binary_integer:=-1;
   WorkSheetToken1 binary_integer:=-1;
   RangeToken  binary_integer:=-1;
   ChartObjectToken binary_integer:=-1;
   ChartObject1  binary_integer:=-1;
   Chart1Token  binary_integer:=-1;
   i    binary_integer;
   retNum   binary_integer;
   retReal   double precision;
   retStr   varchar2(255);
   retDate   DATE;
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
 
 
FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Creating Excel application...');
    i := OrdCOM.CreateObject('Excel.Application',
                             0,
                             servername,
                             applicationToken);
  
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking Workbooks...');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkBooks',
                            0,
                            WorkBooksToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    RETURN i;
  END CreateExcelApplication;
 
  FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2)
    RETURN binary_integer IS
  BEGIN
    dbms_output.put_line('Opening Excel file ' || filename || ' ...');
    ORDCOM.InitArg();
    ORDCOM.SetArg(filename, 'BSTR');
  
    i := ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Opening WorkBook');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'ActiveWorkbook',
                            0,
                            WorkBookToken);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheets..');
  
    i := ORDCOM.GetProperty(applicationToken,
                            'WorkSheets',
                            0,
                            WorkSheetToken1);
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Invoking WorkSheet');
    ORDCOM.InitArg();
    ORDCOM.SetArg(sheetname, 'BSTR');
  
    i := ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
 
    IF (i != 0) THEN
      ORDCOM.GetLastError(error_src,
                          error_description,
                          error_helpfile,
                          error_helpID);
      dbms_output.put_line(error_src);
      dbms_output.put_line(error_description);
      dbms_output.put_line(error_helpfile);
    END IF;
  
    dbms_output.put_line('Opened ');
  
    RETURN i;
  END OpenExcelFile;
 
/***************************************************************************
 * Invoke the Excel Automation Server and create a Workbook object as 
 * well as a worksheet object
 ***************************************************************************/
FUNCTION CreateExcelWorkSheet(servername varchar2) return binary_integer IS
BEGIN
 dbms_output.put_line('Creating Excel application...');
 i:=ORDCOM.CreateObject('Excel.Application', 0, servername,applicationToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 dbms_output.put_line('Invoking Workbooks...');
 /*i:=ORDCOM.Invoke(applicationToken, 'WorkBooks',0, WorkBooksToken);*/
 i:=ORDCOM.GetProperty(applicationToken, 'WorkBooks', 0, WorkBooksToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 dbms_output.put_line('Invoking Add to WorkBooks...');
 ORDCOM.InitArg();
 ORDCOM.SetArg(-4167,'I4');
 i:=ORDCOM.Invoke(WorkBooksToken, 'Add', 1, WorkBookToken);
IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 dbms_output.put_line('Invoking WorkSheets..');
 ORDCOM.InitArg();
 ORDCOM.SetArg('Sheet 1','BSTR');
 
/* i:=ORDCOM.Invoke(applicationToken, 'WorkSheets', 1, WorkSheetToken);*/
i:=ORDCOM.GetProperty(applicationToken, 'WorkSheets', 0, WorkSheetToken1);
IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
i:=ORDCOM.Invoke(WorkSheetToken1, 'Add', 0, WorkSheetToken);
IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 return i;
END CreateExcelWorkSheet;
 
 
/***************************************************************************
 * Invoke the Range method to obtain a range token. Then set the property value
 * at the specified range to the data required
 ***************************************************************************/
FUNCTION InsertData( range varchar2,
     data binary_integer,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 IF (i=0) THEN
       i:=ORDCOM.SetProperty(RangeToken, 'ColumnWidth', 15, 'I2');
 END IF;
 
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertData;
 
/***************************************************************************
 * Invoke the Range method to obtain a range token. Then set the property value
 * at the specified range to the data required
 ***************************************************************************/
FUNCTION GetDataNum( range varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retNum);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retNum;
END GetDataNum;
 
FUNCTION GetDataReal( range varchar2) 
     RETURN double precision IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retReal);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retReal;
END GetDataReal;
 
FUNCTION GetDataStr( range varchar2) 
     RETURN varchar2 IS
BEGIN
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retStr);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retStr;
END GetDataStr;
 
FUNCTION GetDataDate( range varchar2) 
     RETURN Date IS
BEGIN
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.GetProperty(RangeToken, 'Value', 0, retDate);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN retDate;
END GetDataDate;
 
FUNCTION InsertData( range varchar2,
     data DATE,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertData;
 
FUNCTION InsertDataReal( range varchar2,
     data double precision,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertDataReal;
 
FUNCTION InsertData( range varchar2,
     data varchar2,
     type varchar2) 
     RETURN binary_integer IS
BEGIN
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken, 'Range', 1, RangeToken);
 i:=ORDCOM.SetProperty(RangeToken, 'Value', data, type);
 i:=ORDCOM.DestroyObject(RangeToken);
 RETURN i;
END InsertData;
 
/******************************************************************************
 * Insert a chart at the x and y position of the spreadsheet with the desired
 * height and width. Then also uses the ChartWizard to draw the graph with data
 * in a specified range area with a specified charting type.
 *******************************************************************************/
FUNCTION InsertChart(xpos binary_integer, ypos binary_integer, 
      width binary_integer, height binary_integer, 
      range varchar2, type varchar2) RETURN binary_integer IS
 charttype binary_integer:= -4099;
BEGIN
 ORDCOM.InitArg();
 i:=ORDCOM.GetProperty(WorkSheetToken, 'ChartObjects', 0, ChartObjectToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(xpos,'I2');
 ORDCOM.SetArg(ypos,'I2');
 ORDCOM.SetArg(width,'I2');
 ORDCOM.SetArg(height,'I2');
 i:=ORDCOM.Invoke(ChartObjectToken, 'Add', 4, ChartObject1);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 i:=ORDCOM.GetProperty(ChartObject1, 'Chart', 0,Chart1Token);
 ORDCOM.InitArg();
 ORDCOM.SetArg(range, 'BSTR');
 i:=ORDCOM.GetProperty(WorkSheetToken,'Range', 1, RangeToken);
 ORDCOM.InitArg();
 ORDCOM.SetArg(RangeToken, 'DISPATCH');
 IF type='xlPie' THEN
  charttype := -4102;
 ELSIF type='xl3DBar' THEN
  charttype := -4099;
 ELSIF type='xlBar' THEN
  charttype := 2;
 ELSIF type='xl3dLine' THEN
  charttype:= -4101;
 END IF;
 ORDCOM.SetArg(charttype,'I4');
 i:=ORDCOM.Invoke(Chart1Token,'ChartWizard', 2, DummyToken);
 i:=ORDCOM.DestroyObject(RangeToken);
 i:=ORDCOM.DestroyObject(ChartObjectToken);
 i:=ORDCOM.DestroyObject(ChartObject1);
 i:=ORDCOM.DestroyObject(Chart1Token);
 RETURN i;
END InsertChart;
 
/******************************************************************************
 * Save the Excel File. WARNING: Do not specify a filename that already exist
 * since there is no graphical context, Oracle would not be able to pop
 * out a warning message for existing file. This causes Excel to hang
 *******************************************************************************/
FUNCTION SaveExcelFile(filename varchar2) return binary_integer IS
BEGIN
 dbms_output.put_line('Saving Excel file...');
 ORDCOM.InitArg();
 ORDCOM.SetArg(filename,'BSTR');
 
 i:=ORDCOM.Invoke(WorkBookToken, 'SaveAs', 1, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 RETURN i; 
END SaveExcelFile;
 
/******************************************************************************
 * Close the Excel spreadsheet and exit from it
 ******************************************************************************/
FUNCTION ExitExcel return binary_integer is
BEGIN
 dbms_output.put_line('Closing workbook and quitting...');
 ORDCOM.InitArg();
 
 ORDCOM.InitArg();
 ORDCOM.SetArg(FALSE,'BOOL');
 dbms_output.put_line('Closing workbook...');
 i:=ORDCOM.Invoke(WorkBookToken, 'Close', 0, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(WorkBookToken); 
 ORDCOM.InitArg();
 dbms_output.put_line('Closing workbooks...');
 i:=ORDCOM.Invoke(WorkBooksToken, 'Close', 0, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 i:=ORDCOM.DestroyObject(WorkBooksToken);
 i:=ORDCOM.Invoke(applicationToken, 'Quit', 0, DummyToken);
 IF (i!=0) THEN
 ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
 dbms_output.put_line(error_src);
 dbms_output.put_line(error_description);
 dbms_output.put_line(error_helpfile);
 END IF;
 
 i:=ORDCOM.DestroyObject(WorkSheetToken); 
 i:=ORDCOM.DestroyObject(WorkSheetToken1); 
 
 
 i:=ORDCOM.DestroyObject(applicationToken);
 i:=ORDCOM.DestroyObject(ChartObjectToken);
 i:=ORDCOM.DestroyObject(Chart1Token);
 i:=ORDCOM.DestroyObject(ChartObject1);
 i:=ORDCOM.DestroyObject(dummyToken);
 RETURN i;
END ExitExcel;
 
 
END ORDExcelSB;
 
 I have created an excel named as C:\Example.xls.

Name SlNo Job Dept Salary Bonus

Saubhik Banerjee 706090 IT Specialist GBS 100 10

Partha S Mohanty 706091 Pogrmmer APPS 70 20

Partha Sarkar 889300 Condultant FIN 200 30

Useless 98009 PM PM 900 90
 
 You can use this code to read excel file (.xls)
 
SQL> SET SERVEROUT ON
SQL> DECLARE
  2  
  3    v_Name          varchar2(90);
  4    v_SlNo          varchar2(100);
  5    v_Job           varchar2(200);
  6    v_Dept          varchar2(100);
  7    v_recon_remark  varchar2(50);
  8    v_sal_amt_usd   number;
  9    v_Bonus_amt_usd number;
 10  
 11    result INTEGER;
 12  
 13    i        binary_integer;
 14    filename varchar2(255);
 15  
 16  BEGIN
 17  
 18    filename := 'C:\Example.xls';
 19  
 20    result := ORDExcelSB.CreateExcelApplication('');
 21    result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');
 22  
 23    /* Excluding the header row and reading the first 5 row */
 24    FOR n in 2 .. 5 LOOP
 25    
 26      v_Name          := ORDExcelSB.GetDataStr('A' || n);
 27      v_SlNo          := ORDExcelSB.GetDataReal('B' || n);
 28      v_Job           := ORDExcelSB.GetDataStr('C' || n);
 29      v_Dept          := ORDExcelSB.GetDataStr('D' || n);
 30      v_sal_amt_usd   := ORDExcelSB.GetDataNum('E' || n);
 31      v_Bonus_amt_usd := ORDExcelSB.GetDataNum('F' || n);
 32    
 33      dbms_output.put_line(v_Name || '  ' || v_SlNo || '  ' || v_Job || '  ' ||
 34                           v_Dept || '  ' || v_sal_amt_usd || '  ' ||
 35                           v_Bonus_amt_usd);
 36    
 37    END LOOP;
 38  
 39    result := ORDExcelSB.ExitExcel();
 40  EXCEPTION
 41    WHEN OTHERS THEN
 42      result := ORDExcelSB.ExitExcel();
 43      RAISE;
 44  END;
 45  / 
Creating Excel application...
Invoking Workbooks...
Opening Excel file C:\Example.xls ...
Opening WorkBook
Invoking WorkSheets..
Invoking WorkSheet
Opened
Saubhik Banerjee  706090  IT Specialist  GBS  100  10
Partha S Mohanty  706091  Pogrmmer  APPS  70  20
Partha Sarkar  889300  Condultant  FIN  200  30
Useless  98009  PM  PM  900  90
Closing workbook and quitting...
Closing workbook...
Closing workbooks...
 
PL/SQL procedure successfully completed.
 
SQL> 
 You can use this code to write to excel file (.xls)
DECLARE 
 
CURSOR c1 IS 
 SELECT empno, ename, dname, sal, hiredate
 FROM emp e, dept d
 WHERE e.deptno = d.deptno;
error_message varchar2(1200);
n binary_integer:=2;
i binary_integer;
filename varchar2(255);
cellIndex varchar2(40);
cellValue varchar2(40);
cellColumn varchar2(10);
returnedTime varchar2(20);
currencyvalue double precision;
datevalue DATE;
empno binary_integer;
 
looptext varchar2(20);
 
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
 
begin
filename:='c:\example2.xls';
i:=ORDExcel.CreateExcelWorkSheet('');
i:=ORDExcel.InsertData('A1', 'EmpNo', 'BSTR');
i:=ORDExcel.InsertData('B1', 'Name', 'BSTR');
i:=ORDExcel.InsertData('C1', 'Dept', 'BSTR');
i:=ORDExcel.InsertData('D1', 'Salary', 'BSTR');
i:=ORDExcel.InsertData('E1', 'HireDate', 'BSTR');
 
For c1_rec IN c1 LOOP
 
cellColumn:=TO_CHAR(n);
 
cellIndex:=CONCAT('A',cellColumn);
cellValue:=TO_CHAR(c1_rec.empno);
empno:=cellValue;
i:=ORDExcel.InsertData(cellIndex, empno, 'I2');
 
 
cellIndex:=CONCAT('B',cellColumn);
cellValue:=c1_rec.ename;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
 
cellIndex:=CONCAT('C',cellColumn);
cellValue:=c1_rec.dname;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
 
cellIndex:=CONCAT('D',cellColumn);
cellValue:=c1_rec.sal;
currencyValue:=cellValue;
i:=ORDExcel.InsertData(cellIndex, currencyValue, 'CY');
 
cellIndex:=CONCAT('E',cellColumn);
dateValue:=c1_rec.hiredate;
i:=ORDExcel.InsertData(cellIndex, dateValue, 'DATE');
 
 
n:=n+1;
END LOOP;
 
i:=ORDExcel.SaveExcelFile(filename);
i:=ORDExcel.ExitExcel();
EXCEPTION
 WHEN OTHERS THEN
  i:=ORDExcel.ExitExcel();
  RAISE;
END;

12 comments :

  1. Hi Saubhik,
    First thanks for you code. I spent a lot of time lookig for this and finally I found oyur blog.

    I'm doing some test and I got the following errors in the dbms output:

    Creating Excel application...
    COM-0004: The registered CLSID for the ProgID is invalid.
    Invoking Workbooks...
    COM-0004: The registered CLSID for the ProgID is invalid.
    Invoking Add to WorkBooks...
    COM-0002: Invalid Token or no interface for token

    I tried to register the orawpcom10.dll on the server but I could not.

    Any idea?

    Thanks again,
    Javier

    ReplyDelete
  2. Hi Saubhik,
    Its really useful information regarding xls files loading into database.
    Could you please tell me how to locate the below file ?
    Or can you post the code please ?

    comwrap.sql

    ReplyDelete
    Replies
    1. This should be on the companion CD of Oracle Installer.

      Delete
    2. thank you Saubhik.
      Now i'm able to run the code.

      Is there any chance of getting total number of rows present in a xls file?
      So that by using this count, i can read the lines from xls file using loop.

      Thank you

      Delete
    3. Yes. That should be possible, but currently not included in the package.
      The work around may be looping through all 65000 rows (1..65000) and exit as soon as some mandatory cell (e.g. Primary Key) is blank (null).

      Delete
    4. Also, You can use Oracle Heterogeneous Service for much easier readings.
      You can find more about this in OTN. https://forums.oracle.com/forums/ann.jspa?annID=1535

      Delete
  3. Hi,

    How do I use this dll on an AIX Unix server?

    Thanks,

    Ravi

    ReplyDelete
    Replies
    1. You can not use this dll in *INX platform. Read the Oracle COM Automation guide.

      Delete
  4. Hi,
    Thnx for it,
    I am getting this as an output
    """""""""""""""""""""""""""""""""""""""""""""""""""""""""""
    Creating Excel application...
    Invoking Workbooks...
    Opening Excel file E:\Example.xls ...
    Closing workbook and quitting...
    Closing workbook...
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "SCOTT.ORDCOM", line 289
    ORA-06512: at "SCOTT.ORDEXCELSB", line 452
    ORA-06512: at line 42
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    """""""""""""""""""""""""""""""""""""""""""""""""""""""""""

    Please help me .....

    ReplyDelete
    Replies
    1. Hi,
      Please Help, I am using oralce 10g and saved the excel sheet(Example.xlsx) in drive E. Ihave tried a lot but unable to find a solution. It will be kind enough of you.

      Delete
    2. Please check the value length in given excel and variables declared in procedural block, this would resolve your concern

      Delete
  5. I have follow this article.

    If see ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    Solution: redefine error_description varchar2(1000)

    If see error: Microsoft canot access excel file 'C:\Exampe.xls'
    There are several possible reasons:
    •The file name or path does not exist.
    •The file is being used by another program.
    •The workbook you are trying to save has the same name as a currently open workbook.
    Solution:
    Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)

    ReplyDelete