SQL* Plus Concurrent Program in Oracle Apps

SQL* Plus is one of the executable method 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 script.

Let’s take below script.

It selects 5 database rows from DBA_OBJECTS table. 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  SET HEADING ON command. This is to display heading for the columns in output as by default Oracle Concurrent Manager turn 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 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 respective top of the application where 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 node 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.

Define SQL Plus Concurrent Executable

Step 4 – Define Concurrent Program

Navigate to Application Developer -> Concurrent-> define. Enter all information like the program, short name and choose executable defined in the earlier step. Click parameters button.

SQL Plus define concurrent program

Define parameter as shown below. Script has only one parameter. This will be passed as &1 to script.

define concurrent program parameter

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 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.

 

Related Post