File Output from a Block

The technique I describe in this section provides a convenient method for outputting the records in a block without having to write code using the TEXT_IO package supplied by Forms. The advantages are a more elegant implementation. Also the option for outputting ALL, VIEWED or DISPLAYED records eases the effort of putting in extra logic for getting ALL and VIEWED records. It does loop through the block implicitly and POST-QUERY is executed for each record.

The F50WRITE.WRITE_BLOCK does the job and has the following definition:

FUNCTION F50WRITE.WRITE_BLOCK(block_name VARCHAR2, output_file VARCHAR2 output_mode VARCHAR2, column_align BOOLEAN, sep_char VARCHAR2, rec_option VARCHAR2, displayed_only BOLEAN) RETURN NUMBER;

The elements of this definition are as follows :

• OUTPUT_FILE requires the full path to be specified, including the extension.
• OUTPUT_MODE is either W (for write) or A (for append).
• COLUMN_ALIGN specifies whether the columns should be aligned.
• SEP_CHAR is the interfield separator in the output file.
• REC_OPTION is either ALL, VIEWED, or DISPLAYED. VIEWED refers to the records to which the user has navigated.
• DISPLAYED_ONLY refers to output-only DISPLAYED items or all items (null canvas items, nondisplayed items).

You can use F50WRITE.WRITE_BLOCK to do file output from a block, as follows: >

FUNCTION write_to_file (ip_block_name IN VARCHAR2, op_file_name IN VARCHAR2, op_mode IN VARCHAR2, ip_sep_char IN VARCHAR2, ip_rec_option IN VARCHAR2) RETURN NUMBER IS v_column_align BOOLEAN := TRUE; v_displayed_only BOOLEAN := FALSE; ret_code NUMBER; BEGIN ret_code := F50WRITE.WRITE_BLOCK(ip_block_name, op_file_name, op_mode, v_column_align, ip_sep_char, ip_rec_option, v_displayed_only); RETURN (ret_code); END write_to_file;

Here is a generic function named write_to_file that wraps the call to F50WRITE.WRITE_BLOCK and returns the constant on success and -1 on failure. A wrapper procedure like this helps in modularization of the code, as well as giving the capability of encapsulating it as part of a more generic package. The important thing to note here is that the wrapper subprogram is a function that returns the success or failure of the code within it and hence has greater flexibility of being used everywhere a function can be used.

Now, use the following function to perform the operation. Remember to check the return value for success. This function returns on success and -1 on failure:

DECLARE ip_block_name VARCHAR2(30); op_file_name VARCHAR2(30); op_mode VARCHAR2(30); ip_sep_char VARCHAR2(3); ip_rec_option VARCHAR2(10); v_column_align BOOLEAN := TRUE; v_displayed_only BOOLEAN := FALSE; Ret_Code NUMBER; BEGIN ret_code := write_to_file(ip_block_name, op_file_name, op_mode, v_column_align, ip_sep_char, ip_rec_option, v_displayed_only); IF (ret_code <> 0) THEN RAISE FORM_TRIGGER_FAILURE; END IF; END write_to_file

Note that the function's parameters have no default values: Specifying null values for rec_option and output mode results in an error. Also, if sep_char is omitted, a single blank is not taken as a field separator.

This technique is useful for data loading using SQL*Loader ”for example, from production to test environments. Create a test form that runs against the production database, do a simple EXECUTE_QUERY to the corresponding block, and then execute the preceding procedure to get the loader data file created.

Note that the output file is created on the client machine and not on the server.