Skip Headers

Oracle E-Business Suite Developer's Guide
Release 12.1
Part Number E12897-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Coding Oracle Tools Concurrent Programs

Oracle Tool Concurrent Programs

Oracle Application Object Library lets you write concurrent programs in SQL*Plus, PL/SQL (if you have PL/SQL installed on your database), SQL*Loader, or Oracle Reports.

For SQL*Plus and PL/SQL programs, the concurrent manager logs onto the database, starts your program, automatically spools output to a report output file, and logs off the database when your program is complete. If your program produces report output, you can define your program to have the concurrent manager automatically print the report output file after your program completes. Reports submitted through Standard Request Submission have printing and submission information set at run time.

See: Concurrent Processing with Oracle Reports.

SQL*PLUS Programs

For SQL*Plus programs, the concurrent manager automatically inserts the following prologue of commands into your SQL*Plus script:

SQL*Plus Prologue

SET TERM OFF
SET PAUSE OFF
SET HEADING OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
WHENEVER SQLERROR EXIT FAILURE

The concurrent manager also inserts a command into your SQL*Plus script to set LINESIZE according to the print style of the script.

If you want your SQL*Plus script to continue after a SQL error, you must insert the following line into your SQL*Plus script:

WHENEVER SQLERROR CONTINUE 

PL/SQL Stored Procedures

PL/SQL stored procedures behave like immediate concurrent programs in that they do not require the concurrent manager to create an independent spawned process.

Concurrent programs using PL/SQL stored procedures can generate log files or output files.

See: PL/SQL APIs for Concurrent Processing.

PL/SQL File I/O Processing

Package FND_FILE contains routines which allow as concurrent programs to write to the request log and output files, stored under <PROD_TOP>/log and <PROD_TOP>/out.

Note: FND_FILE is supported in all types of concurrent programs.

Text written by the stored procedures is first kept in temporary files on the database server, and after request completion is copied to the log and out files by the manager running the request. Opening and closing files is handled behind the scenes by the concurrent manager. Every read and write to the temporary files is implicitly flushed to minimize risk of data loss.

The concurrent managers maintain a shared pool of temporary files; when a manager starts up, it attempts to use filenames from the pool. If no filenames exist, the manager creates new temporary log and output files. These two files are cleared after each concurrent request, and then reused for the next request. As a result no more temporary files are created than necessary.

The temporary files are named as follows, where the x's indicate a sequence number, padded to 7 digits:

lxxxxxxx.req
oxxxxxxx.req

The directory for temporary files must be set in the environment variable APPLPTMP when the managers are started. This directory must also be listed in the UTL_FILE_DIR parameter in init.ora.

To write to these log and output files, simply call the necessary procedures. Opening and closing the files is handled by the concurrent managers. Procedure arguments and exceptions are detailed below.

There are several limitations of these procedures. The temporary files cannot be deleted, but are reduced to 0-length. Deleting them must be handled by the system administrator. This package is not designed for generic PL/SQL text I/O. It is only used for writing to request log and output files.

Using these APIs may impact your application's performance. Temporary files are first created and then copied over the network to the request log and out files. Moving large files can be slow, and can create considerable network traffic. You may wish to be conservative with the amount of data written from your concurrent program.

To facilitate debugging and testing from SQL*Plus, you can use the procedure FND_FILE.PUT_NAMES(LOG, OUT, DIR). This function sets the temporary log and out filenames and the temporary directory to the user-specified values. DIR must be a directory to which the database can write. FND_FILE.PUT_NAMES should be called before calling any other FND_FILE function. If this function is not called when using SQL*Plus, FND_FILE will choose a filename from the pool, as described above. FND_FILE.PUT_NAMES works only once per session, and it does nothing if called from a concurrent program. Procedure FND_FILE.CLOSE will close the files in a command-line session. FND_FILE.CLOSE should not be called from a concurrent program; the concurrent manager will handle closing files.

See: FND_FILE: PL/SQL File I/O.

SQL*Loader

For SQL*Loader programs, the concurrent manager runs SQLLOAD on the control file specified on the Concurrent Program Executable form. If your program produces report output, you can define your program to have the concurrent manager automatically print the report output file after your program completes.

You can either supply information about the data file in the control file, or pass the full directory path and file name of your data file as an argument. The concurrent manager passes the "data=(full pathname of data file)" token at request run time. Without a data file name, the concurrent manager skips that token and SQL*Loader uses the data file name specified in the control file.

If you port your application to a different operating or hardware system, check the directory and file name of your data file and change the value of your program argument if necessary.

Accepting Input Parameters For Oracle Tool Programs

You should write your program to receive arguments in the same order that you specify when you call your program and pass arguments. Concurrent managers pass the arguments directly to your programs.

In SQL*Plus and PL/SQL programs, you must name your arguments &1, &2, &3, etc. so that you are guaranteed to get the first argument you pass in &1, the second in &2, and so on.

With PL/SQL stored procedures, you should define your arguments as IN parameters.

In SQL*Loader programs, pass the full directory path and file name of your data file as an argument. If you port your application to a different operating or hardware system, check the directory and file name of your data file and change the value of your program argument if necessary.

See: Oracle Reports Parameters

Naming Your Oracle Tool Concurrent Program

If your operating system is case-sensitive, the file name of your Oracle Tool concurrent program should always be in uppercase and the extension in lowercase.

Use the Oracle E-Business Suite System Administrator's Guide - Configuration for your operating system to determine the correct naming conventions for your Oracle Tool programs.