Integrate COM Component Functionality into Your PL/SQL Codebase : Page 3

Let's Get the Database in Play

There's not much point in running COM automation through PL/SQL if you aren't going to make use of the database. So let's put a few values from the database into an Excel spreadsheet. The following example uses the EMP table found in the SCOTT schema, which you can load into your current schema by running the CREATE TABLE and INSERT statements in %ORACLE_HOME%/rdbms/admin/SCOTT.SQL or UTLSAMPLE.SQL.

First, work up a function to insert data into a cell. Assume that the token for the current worksheet is in CurrSheetHandle. You'll want to do something roughly equivalent to this line of pseudocode:

   CurrSheetHandle.Range(cell_range).Value := data

Since the Oracle API won't let you call a method of a method's return value, you'll have to do it in two steps, equivalent to the following:

  RangeHandle := CurrSheetHandle.Range(cell_range)
  RangeHandle.Value := data

Here is the full function:

function InsertDataToRange ( cell_range varchar2, 
                             data varchar2)
return binary_integer
is
    reslt binary_integer := 0;
    rangeHandle binary_integer := -1;
begin
  --RangeHandle := CurrSheetHandle.Range(cell_range)    
  ORDCOM.InitArg();
  ORDCOM.SetArg(cell_range, 'BSTR');
  hresult:=ordCom.GetProperty(CurrSheetHandle,
           'Range', 1, RangeHandle);
  --RangeHandle.Value := data
  hresult:=Ordcom.SetProperty(RangeHandle, 'Value',
           data, 'BSTR');
  if hresult != 0 then
    . . . 
  end if;
  hresult:=OrdCom.DestroyObject(RangeHandle);
  RETURN reslt;
END InsertDataToRange;

To call the function, you'd say something like this:

reslt := InsertDataToRange('A1','Shoes');

So to insert data from the EMP table into the current worksheet, you can simply open a cursor on the EMP table and insert its contents into the spreadsheet row by row:

declare
  i binary_integer;
  cursor empCur is
    select * from emp order by ename;
  xlRowIndex number;
begin
  i := xlDemo.InsertDataToRange('A1', 'Emp Name');
  i := xlDemo.InsertDataToRange('B1', 'Emp Title');
  --Insert employee last name and title for each row
  --start inserting at row 2 in the excel spreadsheet
  xlRowIndex := 2;
  for emp in empCur 
  loop
    i := xlDemo.InsertDataToRange('A' || xlRowIndex,
                                  emp.ename);
    i := xlDemo.InsertDataToRange('B' || xlRowIndex,
                                  emp.job);
    xlRowIndex := xlRowIndex + 1;
    end loop;
end; 

Putting It All Together

In Listing 1, I've created a package that puts all of the preceding examples together to create five functions: StartExcel, CreateAWorkbook, InsertDataToRange, SaveCurrWorkbook, and QuitExcel. The procedure RunDemo calls these in order to complete the following:

1. Start Excel.
2. Create a new workbook and create a new worksheet in that workbook.
3. Call InsertDataToRange in a loop as above, to insert data from EMP into the current worksheet.
4. Save the workbook.
5. Quit Excel.

Listing 2 holds sample output from the XLDemo.RunDemo procedure.

Tap into All Those COM Objects

Thanks to Oracle's COM automation feature you can integrate the functionality of literally thousands of pre-built COM components with your PL/SQL codebase. Any Windows machine contains hundreds of COM objects, many of them documented online, and Oracle provides several COM demos, which you can find in the directory %ORACLE_HOME%\com\demo. Now that you've seen how to install the COM automation option for PL/SQL and use it to interact with COM objects, you can fire up your database and start playing!

Previous Page

Other Articles by this Author