UNIX for Advanced & Expert Users

 apple.pl
.
.
.
$file_cnt = 0;
 AGAIN:
#  $!
#  $! Define logical for error checking
#  $!
#ACE comment: log.name was replaced with file $job_logicals/PIPE_FAUCET_STATUS_$my_pid.
$job_logicals = $ENV{JOB_LOGICALS} or die "Can't get env. JOB_LOGICALS.\n";
$my_pid = $$;
$my_file = "$job_logicals/PIPE_FAUCET_STATUS_$my_pid";
system("touch $my_file");
use my_timestamp;
$time_stamp = file_suffix();
$time_stamp = substr($time_stamp,0,16);
$file_cnt = $file_cnt + 1;
$RUNFILE = $LOGFILES . "/E401226R_" . $time_stamp . "_" . $file_cnt . ".PL";
print "Run file: $RUNFILE\n";
#ACE comment: add $my_pid as second parameter to refer to $JOB_LOGICALS/PIPE_FAUCET_STATUS_$my_pid file.
system("sqlplus -S / \@$RATING/orange.SQL $RUNFILE $my_pid");
$jobstat = $?;
if ($jobstat != 0) {
goto ERROR_CHECK;
}
system("at -f $RUNFILE -q d now");        
goto AGAIN;
ERROR_CHECK:
#  $! If job is still waiting for message then shutdown gracefully
if (-e "$my_file") {
  unlink "$my_file";
  exit;
}
exit $jobstat;
Content of orange.sql
WHENEVER SQLERROR EXIT FAILURE
SET TERMOUT ON ECHO ON DOCUMENT OFF
SET LINESIZE 250 PAGES 0
REM *************************************************************************
REM *  orange.SQL - Run stored function that checks DBMS_PIPE for message.
REM *                 Spools message to a file; message is actual DCL command
REM *                 line that will execute a report script file.
REM *************************************************************************
DEFINE SPOOLFILE = &1
REM ACE Comment: add parameter to identify file that must be deleted (replaced the logical name).
DEFINE TOKEN = &2
VARIABLE BATCH_COMMAND VARCHAR2(250);
VARIABLE ERROR_CODE    NUMBER;
EXECUTE :ERROR_CODE := SFUNC_CHECK_DBMS_PIPE('MY_REPORT_REQUEST',:BATCH_COMMAND);
SPOOL &SPOOLFILE
PRINT BATCH_COMMAND
SPOOL OFF
REM ACE Comment: log.name was replaced with file $job_logicals/PIPE_FAUCET_STATUS_$my_pid.
REM HOST $DEFINE/JOB/NOLOG PIPE_FAUCET_STATUS "MESSAGE RECEIVED"
HOST rm $JOB_LOGICALS/PIPE_FAUCET_STATUS_&TOKEN
EXIT
Content of SFUNC_CHECK_DBMS_PIPE
CREATE OR REPLACE FUNCTION SFUNC_CHECK_DBMS_PIPE
  (PIPE_NAME     IN     VARCHAR2,
   BATCH_COMMAND IN OUT VARCHAR2)
   RETURN               NUMBER
IS
   STATUS        NUMBER  := 0;
   TIME_OUT      INTEGER := 86400;  /* in seconds */
BEGIN
   STATUS := DBMS_PIPE.RECEIVE_MESSAGE(PIPE_NAME,TIME_OUT);
   DBMS_PIPE.UNPACK_MESSAGE(BATCH_COMMAND);
   RETURN(STATUS);
END;
We pass message to the pipe from a db procedure which will be called from my Oracle forms application on clicking a button (generating reports)
Below is that procedure&
CREATE OR REPLACE PROCEDURE SPROC_SUBMIT_REPORT
                           (REPORT_TYPE  IN     VARCHAR2,
                            CASE_ID      IN     VARCHAR2,
                            REQUEST_ID   IN     VARCHAR2,
                            SCENARIO_ID  IN     VARCHAR2,
                            CVRGCTGRY_CD IN     VARCHAR2,
                            CVRGTYP_CD   IN     VARCHAR2,
                            NODE_PRINTQ  IN     VARCHAR2,
                            RATING_MTHD  IN     VARCHAR2,
                            STATUS_CD    IN OUT VARCHAR2,
                            ERROR_MSG    IN OUT VARCHAR2) IS
   BATCH_COMMAND   VARCHAR2(250);
   PIPE_NAME       VARCHAR2(30) := 'MY_REPORT_REQUEST';
   PIPE_SIZE       INTEGER      := 8192;
   SERVER_SYSDATE  DATE;
   TIME_OUT        INTEGER      := 60;   /* in seconds */
BEGIN
   ERROR_MSG := NULL;
   SERVER_SYSDATE := SPACK_GET_SERVER_TIME;
   BATCH_COMMAND := 'perl ${RATING}E401226E.PL '||report_type||' '||
                case_id||' '||request_id||' '||scenario_id||' '||cvrgctgry_cd||
                ' '||cvrgtyp_cd||' '||node_printq||' '||rating_mthd||'&';
   DBMS_PIPE.PACK_MESSAGE(BATCH_COMMAND);
   STATUS_CD := DBMS_PIPE.SEND_MESSAGE(PIPE_NAME,TIME_OUT,PIPE_SIZE);
EXCEPTION
   WHEN OTHERS THEN
      STATUS_CD := '9';
      ERROR_MSG := SQLERRM;
END SPROC_SUBMIT_REPORT;

AGAIN:

....

system("at -f $RUNFILE -q d now");        
goto AGAIN;