Passing multiple rows into oracle PL/SQL procedure

Introduction
Oracle off course uses standard Java database connectivity interfaces for communication with it's database. This is all nice and good, but if you know that you will use only Oracle database for your project, maybe it is useful to use some Oracle specific Java libraries in you project.

In this tutorial I will represent some specific Oracle Java libraries that can help you pass structured data into PL/SQL stored procedures. This structured data can contain multiple rows (each row contain multiple columns). So how can you do that?

Implementation

We will use two oracle specific classes:
oracle.sql.ARRAY
and
oracle.sql.STRUCT

First class (ARRAY) help us creating array of STRUCT object. STRUCT object is created by using static oracle.sql.StructDescriptor.createDescriptor method. This method uses existing Object type from database.
ARRAY object is created by using oracle.sql.ArrayDescriptor.createDescriptor method. This method uses existing collection type from database. This collection elements are of type that is same as our object type (I hope you understand this :) - This is basically "array of our object type" defined on database as collection type).

I create simple helper class to handle creation of structure and call to PL/SQL procedure. Here it is...

import java.sql.Connection;

import oracle.jbo.client.Configuration;
import oracle.jbo.server.DBTransaction;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class TransferStructure {

    private StructDescriptor structureDescriptor;
    private ArrayDescriptor arrayDescriptor;
    private Object[] structValues;
    private int fieldsCount;
    private Connection connection;
    private STRUCT[] allRows;
    private String[] structDescColumnNames;
    private DBTransaction dbTransaction;
    private int currentRowIndx = 0;

    public TransferStructure(String objectTypeName, String arrayTypeName,
                             int numberOfRows,
                             DBTransaction dbTransaction) throws Exception {
        this.dbTransaction = dbTransaction;
        this.connection =
                Call_Pl_SqlCodeUtil.getCurrentConnection(dbTransaction);
        allRows = new STRUCT[numberOfRows];

        structureDescriptor =
                StructDescriptor.createDescriptor(objectTypeName, this.connection);
        arrayDescriptor =
                ArrayDescriptor.createDescriptor(arrayTypeName, this.connection);
        structValues = new Object[fieldsCount];
        fieldsCount = structureDescriptor.getMetaData().getColumnCount();
        structDescColumnNames = new String[fieldsCount];

    }

    public void initializeRow() throws Exception {
        structValues = new Object[fieldsCount];        
    }
    
    public void setFiledValue(String fieldName,
                              Object value) throws Exception {
        structValues[fieldPos(fieldName)] = value;
    }
    
    /**
     * Sadly but you need to call this at the end of row populatio.
     * @throws Exception
     */
    public void finalizeRow() throws Exception {
      STRUCT struct =
          new STRUCT(structureDescriptor, this.connection, structValues);
      allRows[currentRowIndx++] = struct;
    }
    
    /**
     * Finds field position in structure.
     * @param fieldName
     * @return
     * @throws Exception
     */
    private int fieldPos(String fieldName) throws Exception {
        int fieldPosition = -1;

        for (int i = 1; i < fieldsCount + 1; i++) {
            String currentField =
                structureDescriptor.getMetaData().getColumnName(i);
            if (currentField.equals(fieldName)) {
                fieldPosition = i - 1;
                break;
            }
        }
        return fieldPosition;
    }

    private ARRAY getArrayStructure() throws Exception {
        return new ARRAY(arrayDescriptor, this.connection, allRows);
    }

    public void makeProcedureCall(String procedureName) throws Exception {
        Call_Pl_SqlCodeUtil.callStoredProcedure(dbTransaction, procedureName,
                                                new Object[] { getArrayStructure() });
    }
}

You can use this helper class like so:

public static void main(String[] args) throws Exception {

        TransferStructure ts =
            new TransferStructure("YOUR_OBJECT_TYPE", "YOUR_ARRAY_TYPE_OF_OBJECTS",
                                  2, "getDatabaseConnection");
        ts.initializeRow();
        ts.setFiledValue("YOUR_FIRST_ATTRIBUTE", "value1");
        ts.finalizeRow();
        ts.initializeRow();
        ts.setFiledValue("YOUR_SECOND_ATTRIBUTE", new oracle.jbo.domain.Number(1234));
        ts.finalizeRow();
        ts.makeProcedureCall("YOUR_PACKAGE.your_procedure_with_in_parameter(?)");

}

You will also need method to call stored procedure from Java. This is not so hard to find on net. Also I want to note that this example is mainly implemented to run on Oracle ADF framework, but I think it will also run on some non-oracle development environments if you acquire necessary libraries.

Faces generator for Oracle ADF (ver. 0.5)

Introduction


Hello everyone. I wish to present you one small project that I worked on in spare time (which I don't have plenty). The ultimate purpose of this "generator project" is to create JDeveloper plugin that will generate complete view controller project for Oracle ADF using just model project. This generator will enable programmer to create complete view controller (or several view controllers) project(s) in any state of application model project. So for example we can in about half an hour create model from database schema and create complete view controller based on that model. Then we can show that prototype to our customer and let him to decide what are necessary changes we need to perform.

Beside prototyping this can also help us in overcome many problems that JDeveloper programming has to offer. :) I know what you may be thinking: "Yes I think this idea is similar to existing Oracle product named HeadStart". But  this generator is flexible and can be customized to organization needs and to organization usual development methods for Oracle ADF. Also I think that programmers like (go figure) to program, so they will not just be clicking but they will create business logic and unit tests on model and that is important. You can also leverage existing skills inside your organization using generator and there is practically no learning curve as opposite to HeadStart.

You can watch how generator work by watching these two videos on youtube:

Hope you like it, full source code for project that is used in demonstration can be download from here.

Slow JDeveloper

Speed up JDeveloper


There are times when JDeveloper slow down so much that it becomes unusable. For example you need to wait 5-30 seconds for simple text change or you wait for structure window to refresh. This can happen when you have complex .jspx pages and when all your components are binds to backing bean.

There are some serious issues with JDeveloper memory management. This can be due to Java Swing API, but this is just wild guest and I really don't know nothing about this.

Eater way, to solve this problem you have two choices:
Use external editor (This is not so good solution).
Other (much better) is to disable automatic component binding by removing binding line from your jspx page. This will rise performance to acceptable level (noting spectacular).

General tip is not to have complex Java backing bean pages and complex .jspx pages (use fragments to simplify layout).

PL/SQL Cursors example

Introduction

PL/SQL provides a number of different ways for data retrieval all of which include working with cursors. You can think of cursor as a pointer to the results of a query run against one or more tables in current database. PL/SQL cursor and Java Database Connectivity (JDBC) cursors also share some similarities. Now when Oracle buys Sun it is only matter of time when we will have natural mapping between JDBC cursors and PL/SQL cursors! :) Only kidding here, this things should never be mixed together because JDBC spec should be independent from vendor and it is designed to prevent vendor locking and whole point of Java are clever interfaces and delegation of vendor specific stuff to vendor.

Ok, let's get back to point of this tutorial.

Why use cursors? Well when you retrieve subset of data from table (or whole table), then that data remains stored in SGA (Shared memory) until cursor is closed, so in this way you cache data and caching on database is good idea.

Choosing explicit or implicit cursor in your PL/SQL program?

Implicit cursors are used when you have a simple SELECT ... INTO single row of data into local program variables. It's the easiest path to your data, but it can often lead to coding the same or similar SELECTs in multiple places in your code.

Explicit cursors are defined in declaration section (package or block) and in this way, you can open and fetch from cursor in one or more places.

Implicit cursor will run more efficient than equivalent explicit cursor (from Oracle 8 Database onwards). So is there reasons to use explicit cursors at all? Off course. Explicit cursor can still be more efficient and they off course offer much programmatic control.

Implicit cursor

Implicit cursors are used when you need to retrieve single row from database. If you want to retrieve more than one row, then you must use either an explicit cursor or bulk collect.

Here one example of implicit cursor usage:

SET serveroutput on;

DECLARE

   PROCEDURE find_employee (employee_id_v employees.employee_id%TYPE)
   IS
      --Record in which we will fetch entire row.  
      emp_rec   employees%ROWTYPE;
   BEGIN
      --Begining of implicit cursor statement.
      SELECT *
        INTO emp_rec --Fetch into record.
        FROM employees
       WHERE employee_id = employee_id_v;
       --Write result.
      DBMS_OUTPUT.put_line (emp_rec.employee_id || ' ' || emp_rec.first_name);
   --Catch exception when there is no such employee.
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN         
         DBMS_OUTPUT.put_line ('Unknown employee with id: ' || employee_id_v);
   END find_employee;
BEGIN
   find_employee (101);
   find_employee (102);
   --This one will produce exeption (OK, only if you do not have employee  with id 1021).
   find_employee (1021);
END;

We encapsulate query with function (this is _aways_ a good idea). This function print employee information from database to output. Also we introduce some exception handling (when
no employee is found).

Because PL/SQL is so tightly integrated with the Oracle database, you can easily retrieve complex datatypes (entire row for example - as we did in our example).

You can see that using implicit cursor is quite simple (with basic understanding of SQL) we just create simple select statement and insert rowset into record (that we declared as local variable).

Explicit cursor

Explicit cursor is explicitly defined in the declaration section. With explicit cursor, you have complete control over the different PL/SQL steps involved in retrieving information from the database. You decide when to open, when fetch and how many records and when to close cursor. Information about the current state of cursor is available through examination of cursor attributes.

Example:

SET SERVEROUTPUT on;

DECLARE
   PROCEDURE get_all_employees
   IS
      --Employee record variable.
      employee_rec   employees%ROWTYPE;
      --Cursor variable for explicit use.
      CURSOR employee_cur
      IS
         SELECT *
           FROM employees;
   BEGIN
      --Open cursor so you can use it.      
      OPEN employee_cur;
      --Go through all employees.
      LOOP
         --Load current row from cursor into employee record. 
         FETCH employee_cur
          INTO employee_rec;
         --Loop until cursor attribute signals that no rows are found.
         EXIT WHEN employee_cur%NOTFOUND;
         DBMS_OUTPUT.put_line (   employee_rec.employee_id
                               || ', '
                               || employee_rec.first_name
                              );
      END LOOP;

      CLOSE employee_cur;
   EXCEPTION
      --Remember to close cursor even if there was some error.
      WHEN OTHERS
      THEN
         IF employee_cur%ISOPEN
         THEN
            CLOSE employee_cur;
         END IF;
   END get_all_employees;
BEGIN
   get_all_employees ();
END;

This PL/SQL block performs following:
Declare the cursor.
Declare а record based on that cursor.
Open the cursor.
Fetch rows until there are no rows left.
Close cursor.
Handle exception and close cursor if it is not closed.

You can see that in this way we have complete control of cursor variable and cursor initialization, fetching and so on.

PL/SQL (Forms) Word and Excel manipulation using OLE (OLE2)

Introduction

Several years ago I was given requirement for excel/word reporting through PL/SQL Oracle Forms 10g application. One catch was that these reports need to fill existing reports with data and I can not use reporting tool of any kind. Because I came from Java and OO word I started developing Java bean that will be inserted into Forms. I had idea to create reports through this bean (using Apache POI) and then I will fuse this bean into Forms applet. I was almost done, but then I find out that Oracle deliver library that can (among other things) handle Forms <-> Word or Excel communication called WEBUTIL. So I start to leverage this library and came up with following PL/SQL package for Word and Excel communication using this package and OLE2. I hope you will find them useful I will not comment them because they are quite complex, but if you have questions please comment (or ask me privately) and I will try to response in shortest time possible.

Excel package
Package specification:

PACKAGE excel
IS
   /*
             Global excel.Application Object --> this represent excel Object.
     */
   appl_id   client_ole2.obj_type;

   /*
           Open file that act as template. Parameter are:
           _application_ -- global word parameter that we initialize at 
           begining.
           _file_ -- file name we wish to open --> it can be from database, or filesystem...
   */
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type;

   /*
           Close current file.
   */
   PROCEDURE file_close (document client_ole2.obj_type);

   /*
           Saves current file (It is useful if we need to save current 
           file using another name)
   */
   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2);

   /*
           Isert number (not formated)
           x - horizontal axei.
           y - vertical axis.
           v - value.
   */
   PROCEDURE insert_number (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   );

   /*
           Insert number and format it as decimal value. 
           x - horizontal axei.
           y - vertical axis.
           v - value.
           Napomena: !!!THIS DOES NOT WORK IN EXCEL 2007!!!
   */
   PROCEDURE insert_number_decimal (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   );

   /*
           Insert characters  (not formated)
           x - horizontal axei.
           y - vertical axis.
           v - value.
   */
   PROCEDURE insert_char (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2
   );

   /*
           Insert character - formated
           color - numbers (15 for example is gray)           
           style - BOLD' or 'ITALIC'
           x - horizontal axei.
           y - vertical axis.
           v - value.
   */
   PROCEDURE insert_char_formated (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2,
      color       NUMBER,
      style       VARCHAR2
   );

   /*
           Set autofit on whole sheet.
   */
   PROCEDURE set_auto_fit (worksheet client_ole2.obj_type);

   /*
           Set autofit for range r. For example. r can be: 'A2:E11'
   */
   PROCEDURE set_auto_fit_range (worksheet client_ole2.obj_type, r VARCHAR2);

   /*
           Put decimal format (0.00) on range r.
   */
   PROCEDURE set_decimal_format_range (
      worksheet   client_ole2.obj_type,
      r           VARCHAR2
   );

   /*
           Create new workbook.
   */
   FUNCTION new_workbook (application client_ole2.obj_type)
      RETURN client_ole2.obj_type;

   /*
           Create new worksheet.
   */
   FUNCTION new_worksheet (workbook client_ole2.obj_type)
      RETURN client_ole2.obj_type;

   /*
           Saves file in client tempfolder (It is necessary to save file if edit template).
   */
   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2;

   /*
           Run macro on client excel document.
   */
   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   );

   /*
           Limit network load...not important.
   */
   PROCEDURE insert_number_array (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2
   );
END;

Package body:

PACKAGE BODY excel
IS
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type
   IS
      arg_list    client_ole2.list_type;
      document    client_ole2.obj_type;
      documents   client_ole2.obj_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      documents := client_ole2.invoke_obj (application, 'Workbooks');
      client_ole2.add_arg (arg_list, FILE);
      document := client_ole2.invoke_obj (documents, 'Open', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (documents);
      RETURN document;
   END file_open;

   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2)
   IS
      arg_list   client_ole2.list_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, FILE);
      client_ole2.invoke (document, 'SaveAs', arg_list);
      client_ole2.destroy_arglist (arg_list);
   END file_save_as;

   FUNCTION new_workbook (application client_ole2.obj_type)
      RETURN client_ole2.obj_type
   IS
      workbook    client_ole2.obj_type;
      workbooks   client_ole2.obj_type;
   BEGIN
      workbooks := client_ole2.get_obj_property (application, 'Workbooks');
      workbook := client_ole2.invoke_obj (workbooks, 'Add');
      client_ole2.RELEASE_OBJ (workbooks);
      RETURN workbook;
   END new_workbook;

   FUNCTION new_worksheet (workbook client_ole2.obj_type)
      RETURN client_ole2.obj_type
   IS
      worksheets   client_ole2.obj_type;
      worksheet    client_ole2.obj_type;
   BEGIN
      worksheets := client_ole2.get_obj_property (workbook, 'Worksheets');
      worksheet := client_ole2.invoke_obj (worksheets, 'Add');
      client_ole2.RELEASE_OBJ (worksheets);
      RETURN worksheet;
   END new_worksheet;

   PROCEDURE file_close (document client_ole2.obj_type)
   IS
   BEGIN
      client_ole2.invoke (document, 'Close');
   END file_close;

   /*
       Macro:    Cells(3, 4).Value = 3
                   Cells(3, 4).Select
                   Selection.NumberFormat = "0.00"
   */
   PROCEDURE insert_number_decimal (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   )
   IS
      args        client_ole2.list_type;
      cell        client_ole2.obj_type;
      selection   client_ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.invoke (cell, 'Select');
         selection := client_ole2.invoke_obj (appl_id, 'Selection');
         client_ole2.set_property (selection, 'Numberformat', '#.##0,00');
         client_ole2.RELEASE_OBJ (selection);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;

   /* Macro:
                       Cells(x, y).Value = v
   */
   PROCEDURE insert_number (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           NUMBER
   )
   IS
      args   client_ole2.list_type;
      cell   ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;


   PROCEDURE insert_char (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2
   )
   IS
      args   client_ole2.list_type;
      cell   client_ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;

  
   /*
           Macro:
                       Cells(x, y).Value = v
                       Cells(x, y).Select
                       Selection.Interior.ColorIndex = color
                       if (style in 'BOLD')
                           Selection.Font.Bold = True
                       else if (style in 'ITALIC')
                           Selection.Font.Italic = True
   */
   PROCEDURE insert_char_formated (
      worksheet   client_ole2.obj_type,
      x           NUMBER,
      y           NUMBER,
      v           VARCHAR2,
      color       NUMBER,
      style       VARCHAR2
   )
   IS
      args        client_ole2.list_type;
      cell        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      font        client_ole2.obj_type;
      interior    client_ole2.obj_type;
   BEGIN
      IF v IS NOT NULL
      THEN
         args := client_ole2.create_arglist;
         client_ole2.add_arg (args, x);
         client_ole2.add_arg (args, y);
         cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
         client_ole2.destroy_arglist (args);
         client_ole2.set_property (cell, 'Value', v);
         client_ole2.invoke (cell, 'Select');
         selection := client_ole2.invoke_obj (appl_id, 'Selection');
         font := client_ole2.invoke_obj (selection, 'Font');
         interior := client_ole2.invoke_obj (selection, 'Interior');

         IF UPPER (style) IN ('BOLD', 'ITALIC')
         THEN
            client_ole2.set_property (font, style, TRUE);
         END IF;

         client_ole2.set_property (interior, 'ColorIndex', color);
         client_ole2.RELEASE_OBJ (interior);
         client_ole2.RELEASE_OBJ (font);
         client_ole2.RELEASE_OBJ (selection);
         client_ole2.RELEASE_OBJ (cell);
      END IF;
   END;

   /*
           Macro:
                       Range(r).Select
                       Selection.Columns.AutoFit
                       Cells(1,1).Select
   */
   PROCEDURE set_auto_fit_range (worksheet client_ole2.obj_type, r VARCHAR2)
   IS
      args        client_ole2.list_type;
      --range
      rang        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      colum       client_ole2.obj_type;
      cell        client_ole2.obj_type;
   BEGIN
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, r);
      rang := client_ole2.get_obj_property (worksheet, 'Range', args);
      client_ole2.destroy_arglist (args);
      client_ole2.invoke (rang, 'Select');
      selection := client_ole2.invoke_obj (appl_id, 'Selection');
      colum := client_ole2.invoke_obj (selection, 'Columns');
      client_ole2.invoke (colum, 'AutoFit');
      --now select upper (1,1) for deselection.      
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, 1);
      client_ole2.add_arg (args, 1);
      cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
      client_ole2.invoke (cell, 'Select');
      client_ole2.destroy_arglist (args);
      client_ole2.RELEASE_OBJ (colum);
      client_ole2.RELEASE_OBJ (selection);
      client_ole2.RELEASE_OBJ (rang);
   END set_auto_fit_range;

   /*
           Macro:
                       Range(r).Select
                       Selection.Numberformat = "0.00"
                       Cells(1,1).Select
   */
   PROCEDURE set_decimal_format_range (
      worksheet   client_ole2.obj_type,
      r           VARCHAR2
   )
   IS
      args        client_ole2.list_type;
      --range
      rang        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      --colum Client_OLE2.Obj_Type;
      cell        client_ole2.obj_type;
   BEGIN
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, r);
      rang := client_ole2.get_obj_property (worksheet, 'Range', args);
      client_ole2.destroy_arglist (args);
      client_ole2.invoke (rang, 'Select');
      selection := client_ole2.invoke_obj (appl_id, 'Selection');
      --colum:= Client_OLE2.invoke_obj(selection, 'Columns');
      client_ole2.set_property (selection, 'Numberformat', '#.##0,00');
      --Client_OLE2.invoke(colum, 'AutoFit');
      --now select upper (1,1) for deselection.      
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, 1);
      client_ole2.add_arg (args, 1);
      cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
      client_ole2.invoke (cell, 'Select');
      client_ole2.destroy_arglist (args);
      --Client_OLE2.release_obj(colum);
      client_ole2.RELEASE_OBJ (selection);
      client_ole2.RELEASE_OBJ (rang);
   END set_decimal_format_range;

   /*
           Macro:Cells.Select
                       Selection.Columns.AutoFit
                       Cells(1,1).Select
   */
   PROCEDURE set_auto_fit (worksheet client_ole2.obj_type)
   IS
      args        client_ole2.list_type;
      cell        client_ole2.obj_type;
      selection   client_ole2.obj_type;
      colum       client_ole2.obj_type;
   BEGIN
      cell := client_ole2.get_obj_property (worksheet, 'Cells');
      client_ole2.invoke (cell, 'Select');
      selection := client_ole2.invoke_obj (appl_id, 'Selection');
      colum := client_ole2.invoke_obj (selection, 'Columns');
      client_ole2.invoke (colum, 'AutoFit');
      --now select upper (1,1) for deselection.      
      args := client_ole2.create_arglist;
      client_ole2.add_arg (args, 1);
      client_ole2.add_arg (args, 1);
      cell := client_ole2.get_obj_property (worksheet, 'Cells', args);
      client_ole2.invoke (cell, 'Select');
      client_ole2.destroy_arglist (args);
      client_ole2.RELEASE_OBJ (colum);
      client_ole2.RELEASE_OBJ (selection);
      client_ole2.RELEASE_OBJ (cell);
   END set_auto_fit;

   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, macro);
      client_ole2.invoke (excel.appl_id, 'Run', arg_list);
      client_ole2.destroy_arglist (arg_list);
   END;

   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      l_ok          BOOLEAN;
      c_file_name   VARCHAR2 (512);
      c_path        VARCHAR2 (255);
   BEGIN
      SYNCHRONIZE;
      c_path := client_win_api_environment.get_temp_directory (FALSE);

      IF c_path IS NULL
      THEN
         c_path := 'C:\';
      ELSE
         c_path := c_path || '\';
      END IF;

      c_file_name := c_path || file_name;
      l_ok :=
         webutil_file_transfer.db_to_client_with_progress
                                                   (c_file_name,
                                                    table_name,
                                                    column_name,
                                                    where_condition,
                                                    'Transfer on file system',
                                                    'Progress'
                                                   );
      SYNCHRONIZE;

      IF NOT l_ok
      THEN
         msg_popup ('File not found in database', 'E', TRUE);
      END IF;

      RETURN c_path || file_name;
   END download_file;
END;

Word package
Package specification

PACKAGE word
IS
   /*
           Global Word.Application Object --> represent word object.
   */
   appl_id   client_ole2.obj_type;

   /*
           Open file that act as template. Parameter are:
          _application_ -- global word parameter that we initialize at
          begining.
          _file_ -- file name we wish to open --> it can be from database, or filesystem...
   */
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type;

   /*
           Close current file.
   */
   PROCEDURE file_close (document client_ole2.obj_type);

   /*
           Saves current file (It is useful if we need to save current
          file using another name)
   */
   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2);

   /*
           (Bizniss end of this whole package;) ) Inserts value in specific word bookmark.
           _dokcument_ -- Word document.
           _bookmark_ -- Name of bookmark that is defined in word template,
           _content_ --  Content we wish to insert into bookmark.
   */
   PROCEDURE insertafter_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           InsertAfter_Bookmark insert after bookmark and then delete that bookmark and this is not
           good if you itarate through values, so this one do not delete bookmark after insert.
           same paramters as previous one.
   */
   PROCEDURE replace_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           Saame as previous procedure but it handle next for you.
   */
   PROCEDURE insertafter_bookmark_next (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           This one after value insert move itself on next row into table. When I say next I mean next-down.
           This is essential for iterating through word table (one row at the time)
           We need manualy create new row if it does not exists.!!!
   */
   PROCEDURE insertafter_bookmark_down (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   );

   /*
           Easy...delete bookmark,
   */
   PROCEDURE delete_bookmark (document client_ole2.obj_type, bookmark VARCHAR2);

   /*
           Create new table row (see InsertAfter_Bookmark_Next)
   */
   PROCEDURE insert_new_table_row (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   );

   /*
           Move bookmakr (ONLY IN TABLE) left, right, up, down.
           _direction_ can have following valyes'UP', 'DOWN', 'LEFT', 'RIGHT'
   */
   PROCEDURE move_table_bookmark (
      document    client_ole2.obj_type,
      bookmark    VARCHAR2,
      direction   VARCHAR2
   );

   /*
           File download.
           parametar _file_name_  -- client file name (name on client)
           _table_name_ -- Table name for where BLOB column is.
           _column_name_ -- BLOB column name that holds Word template.
           -where_condition_ -- filter.
   */
   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2;

   /*
           Calling macro's on bookmarks...only for test.
   */
   PROCEDURE run_macro_on_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      macro      VARCHAR2
   );

   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   );
END;

Package body

PACKAGE BODY word
IS
   FUNCTION file_open (application client_ole2.obj_type, FILE VARCHAR2)
      RETURN client_ole2.obj_type
   IS
      arg_list    client_ole2.list_type;
      document    client_ole2.obj_type;
      documents   client_ole2.obj_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      documents := client_ole2.invoke_obj (application, 'documents');
      client_ole2.add_arg (arg_list, FILE);
      document := client_ole2.invoke_obj (documents, 'Open', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (documents);
      RETURN document;
   END file_open;

   PROCEDURE file_close (document client_ole2.obj_type)
   IS
   BEGIN
      client_ole2.invoke (document, 'Close');
   --CLIENT_OLE2.RELEASE_OBJ(document);
   END file_close;

   PROCEDURE file_save_as (document client_ole2.obj_type, FILE VARCHAR2)
   IS
      arg_list   client_ole2.list_type;
   BEGIN
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, FILE);
      client_ole2.invoke (document, 'SaveAs', arg_list);
      client_ole2.destroy_arglist (arg_list);
   --CLIENT_OLE2.RELEASE_OBJ(document);
   END file_save_as;

   PROCEDURE replace_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content);
      client_ole2.invoke (selectionobj, 'Delete');
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END replace_bookmark;

   PROCEDURE insertafter_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content);
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insertafter_bookmark;

   PROCEDURE insertafter_bookmark_next (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content || CHR (13));
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insertafter_bookmark_next;

   PROCEDURE insertafter_bookmark_down (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      content    VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, content);
      client_ole2.invoke (selectionobj, 'InsertAfter', arg_list);
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveDown');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insertafter_bookmark_down;

   PROCEDURE delete_bookmark (document client_ole2.obj_type, bookmark VARCHAR2)
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Delete');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END delete_bookmark;

   PROCEDURE run_macro_on_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2,
      macro      VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, macro);
      client_ole2.invoke (word.appl_id, 'Run', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END;

   PROCEDURE run_macro_on_document (
      document   client_ole2.obj_type,
      macro      VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      --bookmarkCollection := CLIENT_OLE2.INVOKE_OBJ(document, 'Bookmarks', arg_list);
      --arg_list := CLIENT_OLE2.CREATE_ARGLIST;
      --CLIENT_OLE2.ADD_ARG(arg_list, bookmark);
      --bookmarkObj := CLIENT_OLE2.INVOKE_OBJ(bookmarkCollection, 'Item',arg_list);
      --CLIENT_OLE2.DESTROY_ARGLIST(arg_list);

      --CLIENT_OLE2.INVOKE(bookmarkObj, 'Select');
      --selectionObj := CLIENT_OLE2.INVOKE_OBJ(appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, macro);
      client_ole2.invoke (word.appl_id, 'Run', arg_list);
      client_ole2.destroy_arglist (arg_list);
   --CLIENT_OLE2.RELEASE_OBJ(selectionObj);
   --CLIENT_OLE2.RELEASE_OBJ(bookmarkObj);
   --CLIENT_OLE2.RELEASE_OBJ(bookmarkCollection);
   END;

   PROCEDURE insert_new_table_row (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, 1);
      client_ole2.invoke (selectionobj, 'InsertRowsBelow', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END insert_new_table_row;

   PROCEDURE move_down_table_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveDown');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_down_table_bookmark;

   PROCEDURE move_up_table_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveUp');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_up_table_bookmark;

   PROCEDURE move_left_table_bookmark (
      document   client_ole2.obj_type,
      bookmark   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');
      client_ole2.invoke (selectionobj, 'Cut');
      client_ole2.invoke (selectionobj, 'SelectCell');
      client_ole2.invoke (selectionobj, 'MoveUp');
      client_ole2.invoke (selectionobj, 'Paste');
      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_left_table_bookmark;

   PROCEDURE move_table_bookmark (
      document    client_ole2.obj_type,
      bookmark    VARCHAR2,
      direction   VARCHAR2
   )
   IS
      arg_list             client_ole2.list_type;
      bookmarkcollection   client_ole2.obj_type;
      bookmarkobj          client_ole2.obj_type;
      selectionobj         client_ole2.obj_type;
   BEGIN
      bookmarkcollection :=
                     client_ole2.invoke_obj (document, 'Bookmarks', arg_list);
      arg_list := client_ole2.create_arglist;
      client_ole2.add_arg (arg_list, bookmark);
      bookmarkobj :=
                client_ole2.invoke_obj (bookmarkcollection, 'Item', arg_list);
      client_ole2.destroy_arglist (arg_list);
      client_ole2.invoke (bookmarkobj, 'Select');
      selectionobj := client_ole2.invoke_obj (appl_id, 'Selection');

      IF UPPER (direction) IN ('UP', 'DOWN', 'LEFT', 'RIGHT')
      THEN
         client_ole2.invoke (selectionobj, 'Cut');
         client_ole2.invoke (selectionobj, 'SelectCell');
         client_ole2.invoke (selectionobj, 'Move' || direction);
         client_ole2.invoke (selectionobj, 'Paste');
      END IF;

      client_ole2.RELEASE_OBJ (selectionobj);
      client_ole2.RELEASE_OBJ (bookmarkobj);
      client_ole2.RELEASE_OBJ (bookmarkcollection);
   END move_table_bookmark;

   FUNCTION download_file (
      file_name         IN   VARCHAR2,
      table_name        IN   VARCHAR2,
      column_name       IN   VARCHAR2,
      where_condition   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      l_ok          BOOLEAN;
      c_file_name   VARCHAR2 (512);
      c_path        VARCHAR2 (255);
   BEGIN
      SYNCHRONIZE;
      c_path := client_win_api_environment.get_temp_directory (FALSE);

      IF c_path IS NULL
      THEN
         c_path := 'C:\';
      ELSE
         c_path := c_path || '\';
      END IF;

      c_file_name := c_path || file_name;
      l_ok :=
         webutil_file_transfer.db_to_client_with_progress
                                                   (c_file_name,
                                                    table_name,
                                                    column_name,
                                                    where_condition,
                                                    'Transfer on file system',
                                                    'Progress'
                                                   );
      SYNCHRONIZE;
      RETURN c_path || file_name;
   END download_file;
END;

Simple test

PROCEDURE Call(c_prog IN VARCHAR2,param1 IN VARCHAR2 DEFAULT NULL,value1 IN VARCHAR2 DEFAULT NULL, 
                                    param2 IN VARCHAR2 DEFAULT NULL,value2 IN VARCHAR2 DEFAULT NULL) IS 
  list_id Paramlist; 
BEGIN 
   --Check if list exists. 
   list_id := Get_Parameter_List('param_list'); 
   IF NOT Id_Null(list_id) THEN 
     Destroy_Parameter_List(list_id); -- Ako postoji, unisti je! 
   END IF; 
 
   list_id := Create_Parameter_List('param_list'); 
 
   Add_Parameter(list_id, 'ps_sif',TEXT_PARAMETER, :Global.ps_sif); 
   Add_Parameter(list_id, 'frm_sif',TEXT_PARAMETER, :Global.frm_sif); 
   Add_Parameter(list_id, 'god_sif',TEXT_PARAMETER, :Global.god_sif); 
   Add_Parameter(list_id, 'ana_id',TEXT_PARAMETER, :Global.ana_id); 
   Add_Parameter(list_id, 'id_radnik',TEXT_PARAMETER, :Global.id_radnik); 
   Add_Parameter(list_id, 'forma',TEXT_PARAMETER, UPPER(c_prog)); 
 
   IF param1 IS NOT NULL THEN 
     Add_Parameter(list_id, param1,TEXT_PARAMETER, value1); 
   END IF; 
 
   IF param2 IS NOT NULL THEN 
     Add_Parameter(list_id, param2,TEXT_PARAMETER, value2); 
   END IF; 
 
 
   CALL_FORM(c_prog || '.FMX', NO_HIDE, DO_REPLACE, NO_QUERY_ONLY, list_id); 
 
END; 

Oracle ADF advanced techniques - Dynamically changing query in view object

Introduction

If you have strange requirements then you may need sometimes to dynamically change SQL query where clause in View Object. I have such requirement when customer said that I can not create additional indexes on table. I overcome that problem by dynamically change SQL query where clause (to use indexes that were available).

To use this functionality, you need to override buildWhereClause(StringBuffer sqlBuffer, int noBindVars) method in view object implementation. You use sqlBuffer parameter in this method (which contains SQL query where in StringBuffer) to change SQL in correlation to you needs. Just replace, remove, add string in this parameter. Just remember to use same bind variables names which will be use in VO SQL call (generic bind variables names are named like :vc_temp_1, :vc_temp_2,...).

Ok, here is the code:

@Override
    protected boolean buildWhereClause(StringBuffer sqlBuffer, int noBindVars) {

        //call super and see if there is where clause.
        boolean hasWhereClause = super.buildWhereClause(sqlBuffer, noBindVars);

        if (hasWhereClause) { 
            //modify existing where query.
        }
        else { 
            //or if you add where clause then return true;
            hasWhereClause = false; 

        }

        return hasWhereClause;
    }

Dynamic typing in PL/SQL

Introduction

PL/SQL is a statically typed language. This means that datatypes must be declared and checked at compile time. There are also occasions when you really need the capabilities of dynamic typing and for those occasions, the Any types were introduced in PL/SQL (back then in 9i). These dynamic datatypes enable you to write programs that manipulate data when you don't know the type of that data until runtime. You determine the type of the value at runtime through introspection (using gettype function, as you will see in example).

You cannot manipulate the internal structure of Any types, you must use procedures and functions for that.

We will use following family members of Any:
AnyData (can hold a single value of any type, whatever it's built-in scalar datatype or user-defined object type).
AnyType (can hold a description of a type -- you will see).

In following example I create three user-defined types that are representing some kind of transport mean. The subsequent PL/SQL code then uses SYS.AnyType to define a heterogeneous array of transports.

CREATE OR REPLACE TYPE airplane_o AS OBJECT(
        engine_type VARCHAR2(35),
        lift NUMBER                                       
)
/
CREATE OR REPLACE TYPE car_o AS OBJECT(
        engine_power NUMBER,                             
        color VARCHAR2(35)       
)  
/
CREATE OR REPLACE TYPE train_o AS OBJECT (
       engine_type VARCHAR2(35),
       speed NUMBER                
)
/

SET SERVEROUTPUT ON;

DECLARE    
    TYPE transports_t IS VARRAY(6) OF SYS.AnyData;
    transports transports_t;
    airplane airplane_o;
    car car_o;
    train train_o;    
    ret_val NUMBER;
BEGIN
    
    transports := transports_t(
                    AnyData.ConvertObject(
                        airplane_o('turboprop', 2300)), 
                    AnyData.ConvertObject(
                        airplane_o('jet', 3500)),                              
                    AnyData.ConvertObject(
                        car_o(55, 'red')),
                    AnyData.ConvertObject(
                        train_o('electric', 80)),
                    AnyData.ConvertObject(
                        train_o('steam', 45)),
                    AnyData.ConvertObject(
                        airplane_o('ramjet', 9000))                                    
                              );
                
    
    FOR i IN 1..transports.COUNT LOOP        
        IF transports(i).GetTypeName = 'HR.AIRPLANE_O' THEN            
            ret_val := transports(i).GetObject(airplane);
            --ret_val can be success or no_data...I did not check this.
            DBMS_OUTPUT.put_line('Airplane: ' || 'engine type: ' || 
                        airplane.engine_type || ', lift: ' || airplane.lift || 'lbs');
        ELSIF transports(i).GetTypeName = 'HR.CAR_O' THEN
            ret_val := transports(i).GetObject(car);
            DBMS_OUTPUT.put_line('Car: ' || 'engine power: ' || 
                        car.engine_power || 'KW, color: ' || car.color);
        ELSIF transports(i).GetTypeName = 'HR.TRAIN_O' THEN
            ret_val := transports(i).GetObject(train);    
            DBMS_OUTPUT.put_line('Train: ' || 'engine type: ' || 
                        train.engine_type || ', speed: ' || train.speed || 'KMh');
        END IF;
        
    END LOOP;    
    
END;

Execution of this program in Toad generates following output:

Now I will comment important points in this code.
28 - 41: Here are heterogeneous transports stored in a VARRAY. airplane_o, car_o, train_o are constructors of an object, and AnyData.ConvertObject cast this objects into instance of AnyData.
45, 50, 54: Here we introspect current object (in the loop) and get its type.
46, 51, 55: Retrieve the specific object. We are ignoring return code.

48, 52, 56: Once I had the object in a variable, I can write its properties in DBMS_OUTPUT.

Caching data with PL/SQL Collections

Introduction

In many applications, there is always situation when you will need same data from database over and over again in your PL/SQL program. In same cases, the data that you need is static. This data can be some kind of codes and descriptions that rarely (if ever) change. Well, if the data isn't changing - especially during a user session - then why would you want to keep querying the same data from database?

Idea is that you create collection that will be stored in session's PGA. You will query only once your static data and put them into collection. Essentially, you use the collection's index as intelligent key.

Consider following code:

CREATE OR REPLACE PACKAGE onlyonce AS 
    
    TYPE names_t IS 
    TABLE OF employees.first_name%TYPE
            INDEX BY PLS_INTEGER;
            
    names names_t;

    FUNCTION get_name(employee_id_in IN employees.employee_id%TYPE)
        RETURN employees.first_name%TYPE;
        
END onlyonce; 
/
CREATE OR REPLACE PACKAGE BODY onlyonce AS
    
    FUNCTION name_from_database(employee_id_in IN employees.employee_id%TYPE)
            RETURN employees.first_name%TYPE
        IS
            local_names employees.first_name%TYPE;
     BEGIN
        SELECT first_name
            INTO local_names
            FROM employees
           WHERE employee_id = employee_id_in;
        RETURN local_names;
     END;

    FUNCTION get_name(employee_id_in IN employees.employee_id%TYPE) 
        RETURN employees.first_name%TYPE
    IS                
        return_value employees.first_name%TYPE;        
    BEGIN
        RETURN names(employee_id_in);
    -- I admit, that this slip of code that follows is little bit of hacking...
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            names(employee_id_in) := name_from_database(employee_id_in);
            RETURN names(employee_id_in);
    END;

END onlyonce;

SET SERVEROUTPUT ON

BEGIN    
    FOR j IN 1..10 LOOP
        FOR i IN 100..150 LOOP
            DBMS_OUTPUT.put_line(onlyonce.get_name(i));
        END LOOP;
    END LOOP;
END; 

Ok, here we have much stuff going on. So I will explain one step at the time. Off course (as in all almost oracle examples), this example use HR table.

3-7
Declare a collection type and collection to hold cached data.

16-26
Function for retrieve data from database (one by one).

28
Declaration of our main retrieval function. This function return value from database or from collection. This depend if there is already value with that key in collection or not. Only parameter for this function is id of employee which name we want to retentive.

32-40
This is _the_ meat of the caching. It is little bit of hacking, but what can you do. :) If it does not find element with that id in collection then it will pull it out from db.

46-52
We loop trough table. In first iteration in outer loop it will pull data from database, in all sequential iteration it will retrieve data from collection.

Sh how much of difference does this caching make? For example to execute 10.000 queries against some table it took about 2 second, while pulling same amount of data from collection it took 0.1 sec. That is really and oder of magnitude of improvement. And also caching static data will improve code quality, because of implicit documentation of static structures in your program.

Oracle PL/SQL Collections

It is strange that relatively few database programmers really know or use collections in PL/SQL. They tend to program more in SQL-like way and not thinking much about performance or readability of their programs.

With collections you can improve performances by cache data that are queried repeatedly in a single program or you can process data more quickly not using relational tables or global temporary tables.

PL/SQL collections are cumbersome and at least confusing (in compare to collections in other languages like Java for instance).

There are three different types of collections. I will show all types with examples.

Associative arrays

These are single-dimensional, unbounded, sparse (do not need to be filled up sequentially) collections of homogeneous elements. In the next example I declare such array, populate it with some data and iterate through the collection.

SET SERVEROUTPUT ON

DECLARE
    TYPE names_list_t IS TABLE OF VARCHAR2(255)
        INDEX BY PLS_INTEGER;
    people names_list_t;

    l_row PLS_INTEGER; -- Same type as index.

BEGIN

    people(1)         := 'Bob';
    people(33)        := 'Bruce';
    people(43)        := 'Rocky';
    people(-12)        := 'Grozni';
    people(1555555)   := 'Ivan'; 
  
    l_row := people.FIRST;

    WHILE (l_row IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(people(l_row));
        l_row := people.NEXT(l_row);
    END LOOP;
END;

This type of collection is the most efficient, but if you want to store your collection within a database table, you cannot use an associative array (you need to use one of other two types). If you need sparse collections, you only practical option is an associative array. This is also true if your PL/SQL application requires negative index subscript (like -12 in example).

Nested table

These are also single-dimensional, unbounded (unrestricted) collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are multisets, which means that there is no inherent order to the elements in a nested table. This can be problem if we need to preserve order of elements. We can use keys and indexes, but there is another type of collection VARRAY that will preserver order.

In following example we will first declare a nested table type as a schema-level type. Then we will declare couple of nested tables based on that type, create their union and display result of union.

CREATE OR REPLACE TYPE car_names_list_t IS TABLE OF VARCHAR2(100);

DECLARE
    great_cars          car_names_list_t := car_names_list_t();
    not_so_great_cars   car_names_list_t := car_names_list_t();
    
    all_this_cars       car_names_list_t := car_names_list_t();    
    
BEGIN
    great_cars.EXTEND(3);    
    great_cars(1) := 'Golf';
    great_cars(2) := 'Impreza';
    great_cars(3) := 'Focus';
    
    not_so_great_cars.EXTEND(2);
    not_so_great_cars(1) := 'Zastava';
    not_so_great_cars(2) := 'Dacia';
    
    all_this_cars := great_cars MULTISET UNION not_so_great_cars;
    
    FOR l_row IN all_this_cars.FIRST .. all_this_cars.LAST
    LOOP
        DBMS_OUTPUT.put_line(all_this_cars(l_row));
    END LOOP;
    
END; 

EXTENDS method is used for "making more room" in nested tables. So, when using nested tables we need explicitly resize our collection. MULTISET UNION (there are others like MULTISET EXCEPT) is used for high-level set operations. In this case (using some kind of operation on sets), we don't need to use EXTENDS (obliviously).

Nested tables are useful if you need to perform high-level set operations on your collections, but this is only true if you use older Oracle database (<=10g). Also this kind of collections are only choice if you intent to store large amounts of persistent data in column collection (this is because database will behind scene create real table to hold data).

VARRAY

Like the other two collection type, VARRAYs (variable-sized arrays) are also single-dimensional collections of homogeneous elements. However, the are always limited (bounded) and never sparse. When you declare a type of VARRAY, you must also specify the maximum number of elements it can contain. Important difference to nested tables is that when you store VARRAY as database column, its elements preserve the order.

In following example we will see demonstration simple usage of VARRAYs.

SET SERVEROUTPUT ON

DECLARE
    TYPE anime_movies_t IS VARRAY (3) OF VARCHAR2(100);
        
    anime_movies anime_movies_t := anime_movies_t();

BEGIN
    --extendet only to first
    anime_movies.EXTEND(1);
    anime_movies(1) := 'Akira';
    --here we extend to full length
    anime_movies.EXTEND(2);
    anime_movies(2) := 'Castle in the sky';
    anime_movies(3) := 'My neighbour totorro';
    
    --loop, or do something
END;

Ok, now. This example speak for it self, just note that you also need to use EXTEND to make room for elements.
You will probably use this type of collection when you need to preserve the order of the elements stored in the database collection column, and also when you have relatively small amount of data in collection. Also with VARRAY you also do not need to worry about deletion occurring in the middle of the data set; your data has intrinsic upper bound; or you need to retrieve entire collection simultaneously.

My tip for the end is that, if you are using collections in PL/SQL, you probably should create your set of procedures and functions (as package) that will encapsulate
managing collections, and maybe will hide also what kind of collection you are using...but this is probably not good idea for most of the situations (but collection package _is_ a good idea).