This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 8:28 PM by user648448 RSS

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

860263 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Did you get any error message ?
  • 12. Re: Open an Excel file to read and load it into Oracle - Oracle COM Automation
    860263 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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