Using Concurrent Requests for running sqlplus scripts

Using Concurrent Requests for running sqlplus scripts

---------------------------------------------------------------------------

Concurrent request execution method can be pointed to a Sqlplus script. This option is available in the executable definition screen.

(1) Advantages

·                     Quick option to deploy a SQL script from outside the database and make it part of the application.

·                     Less development effort and involvement.

·                     Out-of-database solution, as script will be placed at OS level.

·                     Quick alternative to regularize commonly used scripts (and avoid back end access to users).

·                     Good alternative for administrative scripts that can be scheduled with application.

·                     One more quick alternative to generate formatted output reports and even print them directly to printers.

(2) Possible Disadvantages

·                     Not a good alternative to host important business logic. This should preferably reside within the database or in Java classes in mid-tier. 

·                     This alternative is not for a named "program" code, it is a script that is invoked every time the request runs.

·                     Frequently used compiled program units are cached and save on execution time. This will not apply to a script call, though the executed SQL will be cached (and phased out as per LRU method).

·                     Re-usability factor : external scripts are not as easy as reusing a database routine. Choose wisely.

·                     Out-of-site, out-of-mind; could result in chaos and security lapses if not controlled and organized. Most shops focus on programming languages and the objects used there of. Such alternatives have to be made part of the regular change process cycle to ensure ownership issues and control.

·                     Custom scripts may get removed during major updates to system. Have proper backups in place.

(3) Process of Deploying a sqlplus script
To explain this part, I am using a sample sqlplus script as an example that will return the market price of recently modified Inventory items. User will provide the organization and a date from when to pick up the items and the same needs to be passed to sqlplus script for generating the output.

(3.1) Create a SQL script.
- Makes use of FND_FILE to pass the output back to user as well as log errors.
- Make use of bind variables to accept defined argument values from the concurrent request call.
- If using a PL/SQL block, don't forget to add the slash at the end.
- Abort the script with error to mark the concurrent request status as Error also.

declare
  l_buffer varchar2(240);
  errexc   exception;
    
  -- Try to pass the file name in error log
  -- for immediately identifying the script..
  l_prgnam constant varchar2(60) :=
               ' [xx_mkt_price_for_items.sql]. ';

  -- The ampersand variable below get their
  -- values from the parameters defined in
  -- concurrent request.
  cursor c1 is
    select to_char( organization_id ) inv_org_id,
           to_char( inventory_item_id ) item_id,
           segment1 code,
           to_char( round( market_price, 2 ) ) mkt_price
    from   mtl_system_items_b
    where  last_update_date >=
                  to_date( '&1', 'YYYY/MM/DD HH24:MI:SS' )
    and    organization_id = &2;

begin

  -- putting a column header with the below statement.
  fnd_file.put_line( fnd_file.output,
         'InvOrgID ItemID    Code            Price' );

  -- cursor to fetch the data and dump it into the output file.
  for r1 in c1 loop
    l_buffer := rpad( r1.inv_org_id, 9, ' ' ) ||
                rpad( r1.item_id, 10, ' ' ) ||
                rpad( r1.code, 15, ' ' ) ||
                rpad( r1.mkt_price, 8, ' ');

    fnd_file.put_line( fnd_file.output, l_buffer );

  end loop;

exception
  when others then
    fnd_file.put_line( fnd_file.log,
        'Error : ' || substr( sqlerrm, 1, 100 ) || l_prgnam );
     -- Below exception will mark the 
     -- request status as Error (Red)

    raise errexc;
end;
/

(3.2) Place the SQL script under proper application top folder.
For instance, my inventory related script will be placed in $INV_TOP/sql folder.

(3.3) Create a concurrent program executable with the execution method as "SQL*Plus".

(3.4) Create a concurrent program for the executable. In my case, the program will have a date and a number parameter that will get passed to the sqlplus script.

How are variables passed?
Oracle will by default pass all variables received from the concurrent request as &1, &2, &3.. (and so on) to sqlplus script. Make use of these in the script to perform needful action. The arguments are passed in the same order as defined in the concurrent request parameter screen.


(4) What Concurrent Manager does automatically

·                     As per Oracle Development guide, Concurrent Manager will automatically insert the following prologue of commands in the sqlplus script call -

set term off
set pause off
set heading off
set feedback off
set verify off
set echo off
whenever sqlerror exit failure

·                     The manager also inserts a command into the script call to set the LINESIZE as per the print style of the request. This is handy if the generated output is being sent to a printer also.

·                     If you trap the error before exiting the script, the concurrent request will not receive any error status and will show as successfully completed. Instead, trap all errors at end of code (WHEN OTHERS) and raise an exception to abort the script after logging proper message.

·                     I found that using single '&' calls multiple time will reuse the value already passed. No need to use '&&' to reuse existing argument values.

(5) Bad script?
- The error will usually show up in the output file.

- The log file will have something similar to below lines -
Concurrent Manager encountered an error while running SQL*Plus for your concurrent request 26577406.
Review your concurrent request log and/or report output file for more detailed information.

- Any trapped error should be passed to the log file, as in above case. Error like below will be easier to debug when they show in the log file.
Error : ORA-01861: literal does not match format string [xx_mkt_price_for_items.sql]. 


(6) Bind variable not declared error?
Try putting quotes around the bind variable of date/varchar2 type. Ensure the date format is as per what is defined for the passing parameter.

---------------------------------------------------------------------------