How to Export Any Block to CSV in Oracle Forms

Sooner or later, users ask for the rows on the screen in a spreadsheet. Writing an export for every block is repetitive, and each one breaks when the block changes.

This guide builds one generic procedure in Oracle Forms 14.1.2 that exports any block to a CSV file. It reads the block's items at run time, writes their names as the header, and then writes every record.

Sample Form for This Guide

The examples and screenshots use the sample form CH39_FIND from the Oracle Forms code repository on GitHub. Download it, open it in Forms Builder, and connect as CAREWELL to follow along.

The forms run against the CareWell Clinic sample schema, which you install first.

Walk the Items of a Block

The procedure knows nothing about the block it exports. It discovers the items at run time:

So the export contains exactly the columns the user sees, in their order. Item properties at run time are covered in how to change items at run time using SET_ITEM_PROPERTY.

The Export Procedure

Example (program unit EXPORT_BLOCK):

procedure export_block(p_block varchar2, p_file varchar2) is
  v_out   text_io.file_type;
  v_item  varchar2(100);
  v_line  varchar2(4000);
  v_n     pls_integer := 0;
  -- the displayed items of the block, other than buttons, in the order of the Object Navigator
  function is_column(p_item varchar2) return boolean is
  begin
    return get_item_property(p_item, ITEM_TYPE) <> 'BUTTON'
       and get_item_property(p_item, VISIBLE) = 'TRUE';
  end;
  function next_column(p_item varchar2) return varchar2 is   -- item names without the block
    v varchar2(100) := p_item;
  begin
    loop
      v := get_item_property(p_block || '.' || v, NEXTITEM);   -- qualified: another block may
      exit when v is null or is_column(p_block || '.' || v);   -- have an item of the same name
    end loop;
    return v;
  end;
begin
  v_out := text_io.fopen(p_file, 'w');            -- a file on the Forms server
  v_item := get_block_property(p_block, FIRST_ITEM);
  if not is_column(p_block || '.' || v_item) then
    v_item := next_column(v_item);
  end if;
  -- the header: the item names
  declare v varchar2(100) := v_item; begin
    while v is not null loop
      v_line := v_line || case when v_line is not null then ',' end || v;
      v := next_column(v);
    end loop;
  end;
  text_io.put_line(v_out, v_line);
  -- the records: every record of the block, fetched to the last
  go_block(p_block);
  first_record;
  loop
    v_line := null;
    declare v varchar2(100) := v_item; begin
      while v is not null loop
        v_line := v_line || case when v_line is not null then ',' end
                  || '"' || replace(name_in(p_block || '.' || v), '"', '""') || '"';
        v := next_column(v);
      end loop;
    end;
    text_io.put_line(v_out, v_line);
    v_n := v_n + 1;
    exit when :system.last_record = 'TRUE';
    next_record;
  end loop;
  text_io.fclose(v_out);
  first_record;
  message(v_n || ' records written to ' || p_file);
exception
  when others then
    if text_io.is_open(v_out) then
      text_io.fclose(v_out);
    end if;
    raise;
end;

How it writes the file:

• The header line is the item names, separated by commas.
• Each value is written as the item shows it, in its format mask, between double quotes, with any quote inside doubled, as spreadsheets expect.
• The record loop fetches every record of the query, not only those on the screen, and returns to the first record at the end.
• The exception handler closes the file before raising the error again, so a failure does not leave it open.

Call It from a Button

The button passes the block's name and a file name with the date and time.

Example (WHEN-BUTTON-PRESSED trigger on CTL.EXPORT):

export_block('PATIENTS', '/work/transfer/out/patients_' || to_char(sysdate, 'YYYYMMDD_HH24MI') || '.csv');

Pressed after a search for the patients of Pune, sorted by birth date, the export wrote the 15 patients in the order the user had sorted them.

Output (the start of the file):

MRN,LAST_NAME,FIRST_NAME,CITY,BIRTH_DATE,PHONE
"CW101281","Saxena","Priya","Pune","13-JUL-2024","+91 9349966805"
"CW100315","Rao","Noah","Pune","06-FEB-2024","+91 9808075560"
"CW100266","Nair","Arjun","Pune","24-NOV-2023","+91 9008122527"
...

Where It Can Run

Walking the records moves the cursor, so the procedure cannot run in a trigger that forbids navigation, such as WHEN-VALIDATE-ITEM or POST-QUERY. A button trigger or a menu item is the right place.

Because it takes the block's name as an argument, the procedure belongs in a PL/SQL library, where every form can call it for any of its blocks. See how to create a PL/SQL library in Oracle Forms.

Deliver the File to the User

TEXT_IO writes on the Forms server, not on the user's computer. The sample writes to a transfer directory on the server. To hand the file to the user, copy it to the client with WebUtil, or write it on the client directly with WebUtil's file package. Both are shown in how to work with client files using WebUtil in Oracle Forms, and an older article covers writing files at the client side using WebUtil.

Conclusion

A generic CSV export walks a block's items with FIRST_ITEM and NEXTITEM, keeps the visible ones that are not buttons, writes their names as a header, and writes every record with NAME_IN, quoting each value. Run it from a button or menu, keep it in a library so every form can use it, and use WebUtil to get the file from the Forms server to the user.