SQL* Plus is one of the executable methods available while defining a concurrent program in Oracle Apps. It allows running SQL* Plus script against the Oracle ERP database under Oracle Apps Environment following all security and access control.
This method helps to create some ad-hoc script as a concurrent program. Ideally, convert this standalone script as a database object and define PL SQL Concurrent Program on it.
One warning here, this script executes with the privilege of Apps user when you submit it through a concurrent program, so be careful. Test the script thoroughly. A simple mistake in a script may wipe out/corrupt data in the instance.
What is SQL* Plus?
SQL*Plus is an interactive and batch query tool. It installs with every Oracle Database installation. It has a command-line user interface, a Windows Graphical User Interface (GUI). There iSQL*Plus web-based user interface is also available.
Let’s see how to define SQL* Plus concurrent program.
Pre-requisite
Basic working knowledge of Oracle Apps and knowledge of SQL* Plus commands.
Software/Hardware environment
- Oracle apps 11i or R12
- Notepadd++
- Winscp
Step 1 – Create SQL Script (. SQL)
This is a simple script. You can use any script which can run using SQL* Plus editor. Script with select, insert, update, or any SQL commands. Use Notepad++ to create and update the script.
Let’s take the below script.
It selects 5 database rows from the DBA_OBJECTS table. The owner is an input parameter. Parameters are referenced as &1, &2 and passed in the same sequence defined in the concurrent program. Enclose character and date parameter is single quotes, else you will get an error something like this.
ERROR at line 7:
ORA-00904: “APPS”: invalid identifier
COLUMN owner HEADING Owner
COLUMN object_name HEADING 'Object Name'
COLUMN object_type HEADING 'Object Type'
COLUMN status HEADING Status
COLUMN owner FORMAT A10 WORD_WRAPPED
COLUMN object_name FORMAT A20 WORD_WRAPPED
COLUMN object_type FORMAT A20 WORD_WRAPPED
COLUMN status FORMAT A10 WORD_WRAPPED
SET HEADING ON
SELECT owner
, object_name
, object_type
, status
FROM dba_objects
where rownum <5
and owner ='&1';
COLUMN command formats heading and display length. Please note, usage of the SET HEADING ON command. This is to display the heading for the columns in output as by default Oracle Concurrent Manager turns it off.
One point to note here, for SQL*Plus programs, the concurrent manager automatically inserts the following prologue (set) 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 the SQL*Plus script to continue after a SQL error, you must insert the following line into your SQL*Plus script.
WHENEVER SQLERROR CONTINUE – This instructs the concurrent manager to continue even there is an error in the script.
Quick SQL Plus Command Reference
- SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen.
- SET PAUSE OFF Displays the specified text then waits for the user to press RETURN.
- SET HEADING OFF suppresses column headings.
- SET FEEDBACK OFF also turns off the statement confirmation messages such as ‘Table created’ and ‘PL/SQL procedure successfully completed’ that are displayed after successful SQL or PL/SQL statements
- SET ECHO OFF Turns command output on or off. This command only affects output being displayed in a script or using the spool mode.
- SET VERIFY OFF The VERIFY setting controls whether or not SQL*Plus displays before and after images of each line that contains a substitution variable.
- WHENEVER SQLERROR EXIT FAILURE – Performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error.
Step 2 – FTP .sql script to $XXCUSTOM_TOP/sql path
Copy/FTP .sql script /sql path to Oracle Apps Unix Server. Use WinSCP or FileZilla. Make sure to move it to the respective top of the application where the executable is registered.
e.g copy it to $PO_TOP/sql if you are going to register executable under Purchasing Application.
Note:- Copy this script on all the nodes if you have a multi-node instance.
Step 3 – Define Concurrent Program Executable
Navigate to Application Developer -> Concurrent-> Executable. Provide valid information like Executable, Short Name, Application, Execution method should be SQL* Plus and enter the name of the file without extension in Execution Filename.
Step 4 – Define Concurrent Program
Navigate to Application Developer -> Concurrent-> define. Enter all information like the program, short name and choose the executable defined in the earlier step. Click the parameters button.
Define parameter as shown below. Script has only one parameter. This will be passed as &1 to script.
Step 5 – Add Concurrent Program to Responsibility
Add this concurrent program to any valid responsibility so that you can submit it from SRS window.
Run the Program
Submit the concurrent program from the SRS window to check the output. Below is sample output.
Owner Object Name Object Type Status
---------- -------------------- -------------------- ----------
APPS /108eea7b_SpatialElo JAVA CLASS VALID
cUtilRevGe
APPS /10b116c5_JservAdmin JAVA CLASS VALID
APPS /11022d36_BinaryAtta JAVA CLASS VALID
chmentWrit
APPS /11183808_Attributes JAVA CLASS VALID
Points to remember,
- Script spools/copy output to the output file of the concurrent program
- Any error in the script gets printed in the Output file.
- You can use fnd_file to write a log and out file. As this is PL/SQL API, either use Begin end or exec command
- Do not use fnd_file to write output in SQL script as it always writes it at the end of script output.
I hope I explained the topic well. Please do share and subscribe.