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;