We help IT Professionals succeed at work.
Get Started

Problem running sql script through application

413 Views
Last Modified: 2013-12-14
I am running the following sql script through an application and I am getting Error setting output directory in the deamon log - suggests environment, but I have tried putting the absolute path in the script but I get the same.  Any ideas?

REM *************************************************************************:
REM *** System     : First Software Benefits                               ***
REM *** Subsystem  : Benefits                                              ***
REM *** Module     : bccsched                                              ***
REM *** File       : bccsched.sql                                          ***
REM *** Called By  : Generic Process Interface GPI                         ***
REM *** Author     : Rebecca Barry                                         ***
REM *** Request By : User request                                          ***
REM *** Purpose    : Batch Report                                         ***
REM *** Parameters : $1 Log file name                                      ***
REM ***            : $2 Schedule Start date                                ***
REM ***            : $3 Schedule End date                                  ***
REM ***            : $4 created date                                       ***
REM *** Tables     : BATCH_REQUEST_INSTANCES       (SELECT)                ***
REM ***            : BATCH_REQUESTS                (SELECT)                ***
REM ***            : PARAMETER_VALUES PVA          (SELECT)                ***
REM ***                                                                    ***
REM **************************************************************************
REM *** Change History                                                     ***
REM ***                                                                    ***
REM *** Date         Done By     Description                               ***
REM *** 24-Nov-2009  R. Barry    Initial Creation                          ***
REM ***                                                                    ***
REM **************************************************************************
whenever sqlerror exit 1;

define outfile=&&1
define startdt=&&2
define enddt=&&3
define created_date=&&4

set termout off trimspool on echo off verify off

spool &outfile
REM **************************************************************************
REM *** Set up Report Titles                                               ***
REM **************************************************************************

COLUMN usercol            NOPRINT NEW_VALUE uservar
COLUMN today              NOPRINT NEW_VALUE datevar
COLUMN who                NOPRINT NEW_VALUE whovar

SELECT user                                                           USERCOL,
       TO_CHAR(SYSDATE, 'DD-MON-YYYY')                                  TODAY,
       prm.prm_value                                                      WHO
FROM   parameters prm
WHERE  prm.prm_domain = 'SYSINFO'
AND    prm.prm_code   = 'OWNER'
/

ttitle LEFT uservar CENTER whovar RIGHT datevar SKIP 1 -
       LEFT 'Schedule' RIGHT 'Page ' FORMAT 9999 SQL.PNO  SKIP 1 -
       CENTER 'RBLIVE Schedule parameter report '&startdt' to '&enddt' Checked by: _________________' SKIP 2

REM **************************************************************************
REM *** Set up Column Formats, Totals and breaks                           ***
REM **************************************************************************



set lines 120 pages 42 trimspool on verify off  newpage 0

column  mname   format  a20            heading 'Module|'
column  refno   format  9999999        heading 'Run    |number '
column  edate   format  a10            heading 'Execution|time'
column  parmn   format  a25            heading 'Parameter name|'
column  pvalu   format  a30            heading 'Parameter value|'
column  qname   format  a8             heading 'Queue|'

break on qname skip page on mname skip 0 on refno skip 0 on edate skip 1 nodup

SELECT     BRI.BRI_BRE_MOD_NAME                                                 mname,
           TO_CHAR(BRI.BRI_REQUESTED_EXECUTION_DATE,'HH24:MI:SS')               edate,
           BRE.BRE_BQU_QUEUE_NAME                                               qname,
           NVL(PVA.PVA_PDU_PDF_NAME, 'N/A')                                     parmn,
           NVL(PVA.PVA_CHAR_VALUE, ' ')                                         pvalu,
           PVA.PVA_REUSABLE_REFNO                                               refno
FROM   BATCH_REQUEST_INSTANCES BRI,
       BATCH_REQUESTS BRE,
       PARAMETER_VALUES PVA
WHERE  BRI.BRI_REQUESTED_EXECUTION_DATE BETWEEN TO_DATE ('&startdt 12:00:00','DD-MON-YYYY HH24:MI:SS') AND
TO_DATE ('&enddt 11:59:00', 'DD-MON-YYYY HH24:MI:SS')
AND    BRI.BRI_BRE_CREATED_DATE >= '&created_date'
AND    BRI.BRI_REUSABLE_REFNO = PVA.PVA_REUSABLE_REFNO (+)
AND    BRI.BRI_BRE_USR_USERNAME = BRE.BRE_USR_USERNAME
AND    BRI.BRI_BRE_MOD_NAME = BRE.BRE_MOD_NAME
AND    BRI.BRI_BRE_CREATED_DATE  = BRE.BRE_CREATED_DATE
AND    BRE.BRE_BQU_QUEUE_NAME != 'FSW_I_QUEUE'
AND    BRI.BRI_BRE_MOD_NAME != 'FIPRELIST'
AND    BRI.BRI_BRE_MOD_NAME != 'LTRREQPRELIST1'
AND    SUBSTR(BRI.BRI_BRE_MOD_NAME,1,2) != 'CT'
AND    SUBSTR(BRI.BRI_BRE_MOD_NAME,1,2) != 'CA'
AND    BRI.BRI_BRE_USR_USERNAME = 'RB'
ORDER BY BRE.BRE_BQU_QUEUE_NAME,BRI_REQUESTED_EXECUTION_DATE, PVA.PVA_REUSABLE_REFNO, PVA.PVA_PDU_DISPLAY_SEQNO
/
spool off

exit

bccgpi.sql
Comment
Watch Question
This problem has been solved!
Unlock 2 Answers and 2 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE