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.
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 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.
Your stored procedure concurrent program must have errbuf and retcode as the first two parameters. Use errbuf to return any error messages, and retcode to return completion status. The parameter retcode returns 0 for success, 1 for success with warnings, and 2 for error. After your concurrent program runs, the concurrent manager writes the contents of both errbuf and retcode to the log file associated with your concurrent request. See Overview of Designing Concurrent Programs for more information and an example.
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 a database directory defined for PL/SQL file I/O. See My Oracle Support Knowledge Document 2525754.1, Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2.
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.
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.
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
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 information in the Oracle E-Business Suite Setup Guide for your operating system to determine the correct naming conventions for your Oracle Tool programs.