Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Running SQLPlus from command line
RLN wrote:
> ok....trudging along here....
>
> After the batch file runs, Test12.txt contains no data and is zero
> bytes in size.
> What else am I missing in this process?
> Here are my updated files:
>
> - - - - - - -
> <begin TEST12.BAT>
> rem Connect to SQL...
> SQLPLUS -s myLogon/myPass_at_myDBName @TEST12.SQL
> Echo done running TEST12.BAT
> <end TEST12.BAT>
> - - - - - - -
>
> <begin TEST12.SQL>
> --Spool output to text file
> SPOOL test12.txt
>
> SELECT DISTINCT A.PROCESS_INSTANCE,
> A.BUSINESS_UNIT,
> A.VOUCHER_ID,
> A.VOUCHER_LINE_NUM,
> A.BUSINESS_UNIT_PO,
> A.PO_ID, A.LINE_NBR,
> A.SCHED_NBR,
> A.BUSINESS_UNIT_RECV,
> A.RECEIVER_ID,
> A.RECV_LN_NBR,
> A.RECV_SHIP_SEQ_NBR,
> A.MATCH_RULE_ID,
> A.SETID,
> A.MATCH_CNTRL_ID,
> A.BUYER_ID,
> B.MERCHANDISE_AMT,
> B.QTY_VCHR,
> C.IMAGE_REF_ID
> FROM PS_VCHR_MTCH_EXCPT A,
> PS_VOUCHER_LINE B,
> PS_VOUCHER C
> WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
> AND A.VOUCHER_ID = B.VOUCHER_ID
> AND A.VOUCHER_LINE_NUM = B.VOUCHER_LINE_NUM
> AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
> AND B.VOUCHER_ID = C.VOUCHER_ID
> And A.PO_ID in('0000877894')
>
> --turn off spooling...
> SPOOL OFF
>
> -- Exit SQLPlus...
> EXIT
> <end TEST12.SQL>
>
> Thanks.
>
Things look good to me. Your batch file is constucted correctly and so is your SQL script. The only suggestion I would have is to explicitly denote the directory in the SPOOL command similar to the following:
SPOOL C:\my_directory\test12.txt
That way, you know exactly where the output will be placed.
I'm not sure why your file is empty. Are you sure that the query returns a result set?
Cheers,
Brian
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - UnknownReceived on Thu Oct 12 2006 - 11:34:54 CDT