1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 10:28 PM by user648448 RSS

    Open an Excel file to read and load it into Oracle - Oracle COM Automation

    860263
      Hello All,

      Please I need your help for this problem:

      I need to load Excel sheet data (read the data) and load it into Oracle database (insert into a table), the excel file created and has data before, and saved with xls format. and I need to do that using the Oracle® COM Automation (ordcom package). The examples I found open and create a new workbook and deletes the old saved data, so I need to open (edit) the sheet just for reading.

      I appreciate ans sample code to help me do that, Please help me out. This is very urgent.

      Thanks alot and best regards,

      Nabil
        • 1. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
          sybrand_b
          Using 'very urgent' on your very first post in a forum of volunteers? Do you know what 'volunteer' means?
          So why do you become insulting and rude on your very first post?

          You can access Excel sheet using Heterogeneous services for ODBC in Oracle, yes, they are documented, and this forum is not a free documentation look up service.

          For those really 'very urgent' questions there is paid support

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
            Saubhik
            For reading from Excel, there are some easy ways like Oracle Heterogenious Services. If you want to use COM then:

            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> 
            Preparing 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
            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> 
            Although, You haven't asked, but 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;
            • 3. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
              860263
              Dear Saubhik,

              Thank you very much for your nice reply and comprehensive solution, I may need some time to practice your solution and try it by myself, and I’ll inform you about and thank you again. However, I checked the file “” and I’m not able to find it in my installation directory, so should I download it from somewhere or you provide a downloadable link / send it by email for me? Many thanks again.

              Best regards,
              Nabil
              Nabil101@gmail.com
              • 4. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                Saubhik
                Which file you don't have ? You need two files
                1. orawpcom.dll -- This may be like ORAWPCOMxx.dll, Where xx stands your version. For Example, I also have orawpcom10.dll.
                2. comwrap.sql - This file should be in %ORACLE_HOME%\com directory. If it is not there then install it from companion CD.
                3. ExcelSolution.sql - This file creates the ORDExcel package. You can use ORDExcelSB package alone with ExcelSolutionSB.sql. The code is given in my post.
                • 5. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                  860263
                  Dear Saubhik,

                  Thank you very much for your reply, I don’t have the “orawpcom.dll” file, but as I can see from your response that it is not needed, as I have “orawpcom10.dll”, so as I said, I may need some time to practice your solution and try it by myself, and I’ll inform you about the result, please accept my appreciation and thank you again.

                  Best regards,

                  Nabil
                  • 6. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                    860263
                    Dear Saubhik,

                    Thank you very much for your reply, it is working, please accept my appreciation and thank you again.

                    Best regards,

                    Nabil
                    • 7. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                      860263
                      Hello,

                      Please I need your help regarding this issue, I need to edit the excel sheet. I need to open it read data, edit data, add new data, than save it with the amendment, I appreciate so much your help to achieve this functionality, many thanks.

                      Best regards,

                      Nabil
                      • 8. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                        860263
                        Hello,

                        Please I need your help regarding this issue, I need to edit the excel sheet. I need to open it read data, edit data, add new data, than save it with the amendment, I appreciate so much your help to achieve this functionality, many thanks.

                        Best regards,

                        Nabil
                        • 9. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                          Saubhik
                          I have already shown you, How to read and write data in Excel using COM. That should be sufficient to solve your problem. I can't write the whole code for you!. Write yourself and get back here, If you have any specific problem.
                          The same code is also posted in my BLOG: http://saubbane.blogspot.com/2011/05/reading-and-writing-excel-file-xls-from.html

                          P.S.: I will post about change in formatting in Excel in the other post you have posted.
                          • 10. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                            860263
                            Dear Saubhik,

                            Many thanks for your help, actually I tried your code and it is working, and used your reading routine to write to, but is not working, I mean reading part working fine, but no new data saved to the sheet, so how I can add some new data to an existing sheet (edit), sorry if I ask but I cnn’t figure it out or do it. I appreciate any help from you, the code I used (your code) is below, thanks again.

                            Nabil,

                            Best regards


                            ==========================================================
                            DECLARE

                            v_Name varchar2(90);
                            v_SlNo varchar2(100);
                            v_Job varchar2(200);
                            v_Dept varchar2(100);
                            v_recon_remark varchar2(50);
                            v_sal_amt_usd number;
                            v_Bonus_amt_usd number;

                            result INTEGER;

                            i binary_integer;
                            filename varchar2(255);

                            BEGIN

                            filename := 'C:\Example.xls';

                            result := ORDExcelSB.CreateExcelApplication('');
                            result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');

                            /* Excluding the header row and reading the first 5 row */
                            FOR n in 2 .. 5 LOOP

                            v_Name := ORDExcelSB.GetDataStr('A' || n);
                            v_SlNo := ORDExcelSB.GetDataReal('B' || n);
                            v_Job := ORDExcelSB.GetDataStr('C' || n);
                            v_Dept := ORDExcelSB.GetDataStr('D' || n);
                            v_sal_amt_usd := ORDExcelSB.GetDataNum('E' || n);
                            v_Bonus_amt_usd := ORDExcelSB.GetDataNum('F' || n);

                            i:=ORDExcel.InsertData('G' || n, 'Test New Data', 'BSTR');

                            dbms_output.put_line(v_Name || ' ' || v_SlNo || ' ' || v_Job || ' ' ||
                            v_Dept || ' ' || v_sal_amt_usd || ' ' ||
                            v_Bonus_amt_usd);

                            END LOOP;

                            result:=ORDExcel.SaveExcelFile(filename);
                            result := ORDExcelSB.ExitExcel();
                            EXCEPTION
                            WHEN OTHERS THEN
                            result:=ORDExcel.SaveExcelFile(filename);
                            result := ORDExcelSB.ExitExcel();
                            RAISE;
                            END;
                            ==========================================================
                            • 11. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                              Saubhik
                              Did you get any error message ?
                              • 12. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                                860263
                                Dear Saubhik,

                                Thanks a lot for your reply, I don’t believe that I received any error, but the edited file was unchanged at all, I don’t have access to the database right now, I’ll check and update you ASAP if you please .
                                Nabil,

                                Best regards
                                • 13. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                                  860263
                                  Dear Saubhik,

                                  Using ‘set serveroutput on size 100000’ , by applying this code, I received the error below, so any thoughts please? your help is highly appreciated.

                                  The code:
                                  i:=ORDExcel.InsertData('G' || 1, 'Test New Data', 'BSTR');
                                  dbms_output.put_line( ' Save Excel File ');
                                  result:=ORDExcel.SaveExcelFile(filename);
                                  result := ORDExcelSB.ExitExcel();

                                  Error message:

                                  Save Excel File
                                  Saving Excel file...

                                  COM-0002: Invalid Token or no interface for token

                                  Closing workbook and quitting...
                                  Closing workbook...
                                  Closing workbooks...

                                  Nabil,

                                  Best regards
                                  • 14. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
                                    Saubhik
                                    Use the latest version of ORDExcelSB package provided in the earlier post (about formatting) or take it from my
                                    BLOG :http://saubbane.blogspot.com/2011/05/writing-and-formatting-excel-using.html

                                    The trick is to save the modified/updated file with another name. The code snippet may looks like:
                                    DECLARE
                                    
                                      v_Name          varchar2(90);
                                      v_SlNo          varchar2(100);
                                      v_Job           varchar2(200);
                                      v_Dept          varchar2(100);
                                      v_recon_remark  varchar2(50);
                                      v_sal_amt_usd   number;
                                      v_Bonus_amt_usd number;
                                    
                                      result INTEGER;
                                    
                                      --n binary_integer:=2;
                                      i          binary_integer;
                                      filename   varchar2(255);
                                      cellIndex  varchar2(40);
                                      cellValue  varchar2(400);
                                      cellColumn varchar2(10);
                                      --returnedTime varchar2(20);
                                    
                                    BEGIN
                                    
                                      filename := 'C:\example2.xls';
                                    
                                      result := ORDExcelSB.CreateExcelApplication('');
                                      result := ORDExcelSB.OpenExcelFile(filename, 'Sheet2');
                                      dbms_output.put_line('*****************************');
                                      /* Excluding the header row and reading the first 5 row */
                                      FOR n in 2 .. 15 LOOP
                                      
                                        v_Name        := ORDExcelSB.GetDataStr('B' || n);
                                        v_SlNo        := ORDExcelSB.GetDataReal('A' || n);
                                        v_Dept        := ORDExcelSB.GetDataStr('C' || n);
                                        v_sal_amt_usd := ORDExcelSB.GetDataNum('D' || n);
                                      
                                        dbms_output.put_line(v_SlNo || ' ' || v_Name || '  ' || v_Dept || '  ' ||
                                                             v_sal_amt_usd);
                                      END LOOP;
                                    
                                      dbms_output.put_line('*****************************');
                                    
                                      --Isertig a new line
                                      i := ORDExcelSB.InsertData('A16',
                                                                 'This file is read,write, format and updated by PL/SQL with COM',
                                                                 'BSTR');
                                    
                                      --Savig the file as a new  name.
                                      result := Ordexcelsb.SaveExcelFile('c:\example3.xls');
                                    
                                      result := ORDExcelSB.ExitExcel();
                                    EXCEPTION
                                      WHEN OTHERS THEN
                                        result := ORDExcelSB.ExitExcel();
                                        RAISE;
                                    END;
                                    Note: If you give the name as existing file then the application hangs (drawback). You have to "save as" a new name. I will post another version if this package for saving it as same name latter.
                                    1 2 Previous Next