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

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

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

Information about my database version.

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

Prepare the user SCOTT for COM automation.

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

create library utils_lib as 'C:\oracle\product\10.2.0\db_3\bin\orawpcom.dll'; 

 Running comwrap.sql and ExcelSolution.sql .....

SQL> conn scott@orclsb
Enter password: *****
Connected.
 
SQL> @c:\comwrap.sql
drop library utils_lib
*
ERROR at line 1:
ORA-04043: object UTILS_LIB does not exist
 
 
 
Library created.
 
drop package ORDCOM
*
ERROR at line 1:
ORA-04043: object ORDCOM does not exist
 
 
drop TYPE OAArgTable
*
ERROR at line 1:
ORA-04043: object OAARGTABLE does not exist
 
 
 
Type created.
 
drop TYPE OAArgTypeTable
*
ERROR at line 1:
ORA-04043: object OAARGTYPETABLE does not exist
 
 
 
Type created.
 
drop function OAgetNumber
*
ERROR at line 1:
ORA-04043: object OAGETNUMBER does not exist
 
 
 
Function created.
 
drop function OAgetStr
*
ERROR at line 1:
ORA-04043: object OAGETSTR does not exist
 
 
 
Function created.
 
drop function OAgetBool
*
ERROR at line 1:
ORA-04043: object OAGETBOOL does not exist
 
 
 
Function created.
 
drop function OAsetNumber
*
ERROR at line 1:
ORA-04043: object OASETNUMBER does not exist
 
 
 
Function created.
 
drop function OAsetString
*
ERROR at line 1:
ORA-04043: object OASETSTRING does not exist
 
 
 
Function created.
 
drop function OAsetBoolean
*
ERROR at line 1:
ORA-04043: object OASETBOOLEAN does not exist
 
 
 
Function created.
 
drop function OAInvokeDouble
*
ERROR at line 1:
ORA-04043: object OAINVOKEDOUBLE does not exist
 
 
 
Function created.
 
drop function OAInvokeBoolean
*
ERROR at line 1:
ORA-04043: object OAINVOKEBOOLEAN does not exist
 
 
 
Function created.
 
drop function OAInvokeString
*
ERROR at line 1:
ORA-04043: object OAINVOKESTRING does not exist
 
 
 
Function created.
 
drop function OACreate
*
ERROR at line 1:
ORA-04043: object OACREATE does not exist
 
 
 
Function created.
 
drop function OADestroy
*
ERROR at line 1:
ORA-04043: object OADESTROY does not exist
 
 
 
Function created.
 
drop function OAGetLastError
*
ERROR at line 1:
ORA-04043: object OAGETLASTERROR does not exist
 
 
 
Function created.
 
drop function OAQueryMethods
*
ERROR at line 1:
ORA-04043: object OAQUERYMETHODS does not exist
 
 
 
Function created.
 
 
Package created.
 
 
Package body created.
 
SQL> 
 
SQL> @c:\ExcelSolution.sql
drop package ORDExcel
*
ERROR at line 1:
ORA-04043: object ORDEXCEL does not exist
 
 
 
Package created.
 
 
Package body created.
 
SQL>

I have modified ORDExcel a little bit and renamed it as 

ORDExcelSB. You need this version for reading the excel.

 SQL> @C:\ExcelSolutionSB.sql
 
Package dropped.
 
 
Package created.
 
 
Package body created.
 
SQL>

The actual code of  ORDExcelSB (ExcelSolutionSB.sql) Is:

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

 I have created an excel named as C:\Example.xls.

Name SlNo Job Dept Salary Bonus

Saubhik Banerjee 706090 IT Specialist GBS 100 10

Partha S Mohanty 706091 Pogrmmer APPS 70 20

Partha Sarkar 889300 Condultant FIN 200 30

Useless 98009 PM PM 900 90

 You can use this code to read excel file (.xls)

SQL> SET SERVEROUT ON
SQL> DECLARE
  2  
  3    v_Name          varchar2(90);
  4    v_SlNo          varchar2(100);
  5    v_Job           varchar2(200);
  6    v_Dept          varchar2(100);
  7    v_recon_remark  varchar2(50);
  8    v_sal_amt_usd   number;
  9    v_Bonus_amt_usd number;
 10  
 11    result INTEGER;
 12  
 13    i        binary_integer;
 14    filename varchar2(255);
 15  
 16  BEGIN
 17  
 18    filename := 'C:\Example.xls';
 19  
 20    result := ORDExcelSB.CreateExcelApplication('');
 21    result := ORDExcelSB.OpenExcelFile(filename, 'Sheet1');
 22  
 23    /* Excluding the header row and reading the first 5 row */
 24    FOR n in 2 .. 5 LOOP
 25    
 26      v_Name          := ORDExcelSB.GetDataStr('A' || n);
 27      v_SlNo          := ORDExcelSB.GetDataReal('B' || n);
 28      v_Job           := ORDExcelSB.GetDataStr('C' || n);
 29      v_Dept          := ORDExcelSB.GetDataStr('D' || n);
 30      v_sal_amt_usd   := ORDExcelSB.GetDataNum('E' || n);
 31      v_Bonus_amt_usd := ORDExcelSB.GetDataNum('F' || n);
 32    
 33      dbms_output.put_line(v_Name || '  ' || v_SlNo || '  ' || v_Job || '  ' ||
 34                           v_Dept || '  ' || v_sal_amt_usd || '  ' ||
 35                           v_Bonus_amt_usd);
 36    
 37    END LOOP;
 38  
 39    result := ORDExcelSB.ExitExcel();
 40  EXCEPTION
 41    WHEN OTHERS THEN
 42      result := ORDExcelSB.ExitExcel();
 43      RAISE;
 44  END;
 45  / 
Creating Excel application...
Invoking Workbooks...
Opening Excel file C:\Example.xls ...
Opening WorkBook
Invoking WorkSheets..
Invoking WorkSheet
Opened
Saubhik Banerjee  706090  IT Specialist  GBS  100  10
Partha S Mohanty  706091  Pogrmmer  APPS  70  20
Partha Sarkar  889300  Condultant  FIN  200  30
Useless  98009  PM  PM  900  90
Closing workbook and quitting...
Closing workbook...
Closing workbooks...
 
PL/SQL procedure successfully completed.
 
SQL> 

 You can use this code to write to excel file (.xls)

DECLARE 
 
CURSOR c1 IS 
 SELECT empno, ename, dname, sal, hiredate
 FROM emp e, dept d
 WHERE e.deptno = d.deptno;
error_message varchar2(1200);
n binary_integer:=2;
i binary_integer;
filename varchar2(255);
cellIndex varchar2(40);
cellValue varchar2(40);
cellColumn varchar2(10);
returnedTime varchar2(20);
currencyvalue double precision;
datevalue DATE;
empno binary_integer;
 
looptext varchar2(20);
 
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
 
begin
filename:='c:\example2.xls';
i:=ORDExcel.CreateExcelWorkSheet('');
i:=ORDExcel.InsertData('A1', 'EmpNo', 'BSTR');
i:=ORDExcel.InsertData('B1', 'Name', 'BSTR');
i:=ORDExcel.InsertData('C1', 'Dept', 'BSTR');
i:=ORDExcel.InsertData('D1', 'Salary', 'BSTR');
i:=ORDExcel.InsertData('E1', 'HireDate', 'BSTR');
 
For c1_rec IN c1 LOOP
 
cellColumn:=TO_CHAR(n);
 
cellIndex:=CONCAT('A',cellColumn);
cellValue:=TO_CHAR(c1_rec.empno);
empno:=cellValue;
i:=ORDExcel.InsertData(cellIndex, empno, 'I2');
 
 
cellIndex:=CONCAT('B',cellColumn);
cellValue:=c1_rec.ename;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
 
cellIndex:=CONCAT('C',cellColumn);
cellValue:=c1_rec.dname;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');
 
cellIndex:=CONCAT('D',cellColumn);
cellValue:=c1_rec.sal;
currencyValue:=cellValue;
i:=ORDExcel.InsertData(cellIndex, currencyValue, 'CY');
 
cellIndex:=CONCAT('E',cellColumn);
dateValue:=c1_rec.hiredate;
i:=ORDExcel.InsertData(cellIndex, dateValue, 'DATE');
 
 
n:=n+1;
END LOOP;
 
i:=ORDExcel.SaveExcelFile(filename);
i:=ORDExcel.ExitExcel();
EXCEPTION
 WHEN OTHERS THEN
  i:=ORDExcel.ExitExcel();
  RAISE;
END;

PL/SQL Procedure OUT parameter in Shell Script.

This is based on one o my posting in OTN. Many times you call PL/SQL procedure from a shell script. This is an example of handling OUT parameter in shell script.
Fist, we are creating a procedure with OUT paramater for testing.

SQL> /* My database version */
SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
 
SQL> /* Creating a procedure for testing of OUT parameter */
SQL> CREATE OR REPLACE PROCEDURE test_out_param(p1 OUT VARCHAR2,p2 OUT VARCHAR2, p3 OUT NUMBER) IS
BEGIN
p1:='First Out Param';
p2:='Second Out Param';
p3:=99;
END test_out_param;
  2    3    4    5    6    7  / 
 
Procedure created.
 
SQL>

Now the shell script and the testing of that script.

$ uname -a
SunOS saubhik 5.10 Generic_142910-17 i86pc i386 i86pc
$ cat test_script
#!/bin/ksh
run_sql() {  sqlplus -s scott/tiger << EOF
SET FEEDBACK OFF;
var v1 VARCHAR2(500);
var v2 VARCHAR2(500);
var v3 NUMBER;
exec test_out_param(:v1,:v2,:v3);
print :v1 :v2 :v3
exit
EOF
}
 
set -A myarray $(run_sql)
# Now I am printing the array. I know there are 13 arguments.
# 1-> V1, 2->Space/Return, 3-> First, 4-> Out etc...
# You can do it more dynamically also (if required).
# Yu can also awk/grep/sed etc the OUT param name (for example V1)
# to know the values.
 
for i in 0 1 2 3 4 5 6 7 8 9 10 11 12 13
do
    print ${myarray[$i]}
done
# Decision making.....
 
if [ ${myarray[12]}  == "99" ]
then
echo Got it!
fi
 
$ ./test_script
V1
 
First
Out
Param
V2
 
Second
Out
Param
V3
 
99
 
Got it!

$ 

Generating scripts using DBMS_METADATA

This is another example of using DBMS_METADATA to generate scripts.

SQL> /* My database 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> /* My PL/SQL block */
SQL>
SQL> DECLARE
  2    myddl clob;
  3    fname VARCHAR2(200);
  4    extn VARCHAR2(200);
  5 
  6  /* Main function to get the DDls with DBMS_METADATA */ 
  7    FUNCTION get_metadata(pi_obj_name  dba_objects.object_name%TYPE,
  8                          pi_obj_type  IN dba_objects.object_type%TYPE,
  9                          pi_obj_owner IN dba_objects.owner%TYPE) RETURN clob IS
 10      h   number;
 11      th  number;
 12      doc clob;
 13    BEGIN
 14      h := DBMS_METADATA.open(pi_obj_type);
 15      DBMS_METADATA.set_filter(h, 'SCHEMA', pi_obj_owner);
 16      DBMS_METADATA.set_filter(h, 'NAME', pi_obj_name);
 17      th := DBMS_METADATA.add_transform(h, 'MODIFY');
 18      th := DBMS_METADATA.add_transform(h, 'DDL');
 19      --DBMS_METADATA.set_transform_param(th,'SEGMENT_ATTRIBUTES',false);
 20      doc := DBMS_METADATA.fetch_clob(h);
 21      DBMS_METADATA.CLOSE(h);
 22      RETURN doc;
 23    END get_metadata;
 24 
 25  ---Main execution begins. 
 26  BEGIN
 27    FOR i in (SELECT object_name, object_type, owner
 28                FROM dba_objects
 29               WHERE owner IN ('SCOTT', 'HR')
 30                 AND object_type IN ('PACKAGE', 'TRIGGER')) LOOP
 31      --Calling the function.              
 32      myddl := get_metadata(i.object_name,i.object_type,i.owner);
 33      --Preparing the filename.
 34      fname := i.owner||i.object_name || '.' ||
 35               CASE WHEN i.object_type='PACKAGE' THEN 'pkb'
 36               ELSE 'trg'
 37               END;
 38     --Writing the file.
 39      DBMS_XSLPROCESSOR.clob2file(myddl, 'SAUBHIK', fname);
 40    END LOOP;
 41  END;
 42  /

PL/SQL procedure successfully completed.

SQL> 

In case, You can not use  DBMS_XSLPROCESSOR, then UTL_FILE is another way to write the files into disk.

SQL> DECLARE
  2    myddl clob;
  3    fname VARCHAR2(200);
  4    extn VARCHAR2(200);
  5 
  6  /* Main function to get the DDls with DBMS_METADATA */ 
  7    FUNCTION get_metadata(pi_obj_name  dba_objects.object_name%TYPE,
  8                          pi_obj_type  IN dba_objects.object_type%TYPE,
  9                          pi_obj_owner IN dba_objects.owner%TYPE) RETURN clob IS
 10      h   number;
 11      th  number;
 12      doc clob;
 13    BEGIN
 14      h := DBMS_METADATA.open(pi_obj_type);
 15      DBMS_METADATA.set_filter(h, 'SCHEMA', pi_obj_owner);
 16      DBMS_METADATA.set_filter(h, 'NAME', pi_obj_name);
 17      th := DBMS_METADATA.add_transform(h, 'MODIFY');
 18      th := DBMS_METADATA.add_transform(h, 'DDL');
 19      --DBMS_METADATA.set_transform_param(th,'SEGMENT_ATTRIBUTES',false);
 20      doc := DBMS_METADATA.fetch_clob(h);
 21      DBMS_METADATA.CLOSE(h);
 22      RETURN doc;
 23    END get_metadata;
 24 
 25  ---Writing the CLOB using UTL_FILE
 26  PROCEDURE write_clob(p_clob in clob, pi_fname VARCHAR2) as
 27      l_offset number default 1;
 28      fhandle UTL_FILE.file_type;
 29      buffer VARCHAR2(4000);
 30    BEGIN
 31      fhandle:=UTL_FILE.fopen('SAUBHIK',pi_fname,'A');
 32      loop
 33        exit when l_offset > dbms_lob.getlength(p_clob);
 34        buffer:=dbms_lob.substr(p_clob, 255, l_offset);
 35        UTL_FILE.put_line(fhandle,buffer);
 36        l_offset := l_offset + 255;
 37        buffer:=NULL;
 38      end loop;
 39      UTL_FILE.fclose(fhandle);
 40    END write_clob; 
 41 
 42  ---Main execution begins. 
 43  BEGIN
 44    FOR i in (SELECT object_name, object_type, owner
 45                FROM dba_objects
 46               WHERE owner IN ('SCOTT', 'HR')
 47                 AND object_type IN ('PACKAGE', 'TRIGGER')) LOOP
 48      --Calling the function.              
 49      myddl := get_metadata(i.object_name,i.object_type,i.owner);
 50      --Preparing the filename.
 51      fname := i.owner||i.object_name || '.' ||
 52               CASE WHEN i.object_type='PACKAGE' THEN 'pkb'
 53               ELSE 'trg'
 54               END;
 55     --Writing the file using UTL_FILE.
 56      write_clob(myddl,fname);
 57    END LOOP;
 58  END;
 59  /

PL/SQL procedure successfully completed.

SQL>  

Searching a particular value in all tables.

Sometimes, We need to search a particular data from all tables (We don't know the table name). There are some good examples in OTN provided by MichaelS.

I am consolidating those here.

SQL> /* My database 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> SET LINE 1000
SQL> VAR val VARCHAR2(30)
SQL> EXECUTE :val:='SMITH';

PL/SQL procedure successfully completed.

SQL> /* Searching the value */
SQL> SELECT DISTINCT SUBSTR(:val, 1, 11) "Searchword",
  2                   SUBSTR(table_name, 1, 14) "Table",
  3                   SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
  4     FROM cols,
  5          table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype('select ' || column_name ||
  6                                                   ' from ' || table_name ||
  7                                                   ' where (UPPER(''' || :val ||
  8                                                   ''')=UPPER(' ||
  9                                                   column_name || '))')
 10                            .EXTRACT('ROWSET/ROW/*'))) t
 11    WHERE table_name IN ('EMP', 'DEPT', 'EMPLOYEES') --limiting the table names, you can omit thi
s.
 12    ORDER BY "Table";

no rows selected

SQL> EXECUTE :val:='Sourav';

PL/SQL procedure successfully completed.

SQL>  SELECT DISTINCT SUBSTR(:val, 1, 11) "Searchword",
  2                   SUBSTR(table_name, 1, 14) "Table",
  3                   SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
  4     FROM cols,
  5          table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype('select ' || column_name ||
  6                                                   ' from ' || table_name ||
  7                                                   ' where (UPPER(''' || :val ||
  8                                                   ''')=UPPER(' ||
  9                                                   column_name || '))')
 10                            .EXTRACT('ROWSET/ROW/*'))) t
 11   WHERE table_name IN ('EMP', 'DEPT', 'EMPLOYEES') --limiting the table names, you can omit this
.
 12    ORDER BY "Table";

Searchword  Table          Column/Value
----------- -------------- --------------------------------------------------
Sourav      EMP            <ENAME>Sourav</ENAME>

SQL> EXECUTE :val:='NEW YORK'

PL/SQL procedure successfully completed.

SQL>  SELECT DISTINCT SUBSTR(:val, 1, 11) "Searchword",
  2                   SUBSTR(table_name, 1, 14) "Table",
  3                   SUBSTR(t.COLUMN_VALUE.getstringval(), 1, 50) "Column/Value"
  4     FROM cols,
  5          table(XMLSEQUENCE(DBMS_XMLGEN.getxmltype('select ' || column_name ||
  6                                                   ' from ' || table_name ||
  7                                                   ' where (UPPER(''' || :val ||
  8                                                   ''')=UPPER(' ||
  9                                                   column_name || '))')
 10                            .EXTRACT('ROWSET/ROW/*'))) t
 11   WHERE table_name IN ('EMP', 'DEPT', 'EMPLOYEES') --limiting the table names, you can omit this
.
 12    ORDER BY "Table";

Searchword  Table          Column/Value
----------- -------------- --------------------------------------------------
NEW YORK    DEPT           <LOC>NEW YORK</LOC>

SQL>