Sunday, May 22, 2011

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.

Tuesday, May 17, 2011

Dell low on quality these days?

What's up with Dell these days? I was a die hard customer of Dell for about 10 years. The 3 notebooks I purchased during this period were from Dell and all had at least one visit to the service centre. The last series XPS M1330 was rigged two times to change the motherboard. The 2nd time around, I had to convince the Dell service centre to bear the expenses as this was a widely known issue on this model. I gave up buying Dell products in 2009. Moved on to Acer, have 3 of their units and not a single has been sent back.

Few months back I decided to take the Dell Streak mini-tab as this was the only 5-inch model available in the market. Guess what, it lasted me 4 months before having a hardware failure. I returned it to the shop and was informed that about 5 of these units have been returned due to hardware issues and there is no support available in this region (UAE) to fix these. Great, what a hogwash. Maybe it's just my stars..

I get a feeling that dell is more focused on the presentation than what the unit should deliver. For now, I am not wasting any more of my resources on Dell.