Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Running SQLPlus from command line

Re: Running SQLPlus from command line

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 12 Oct 2006 16:34:54 GMT
Message-ID: <J717EB.H1@igsrsparc2.er.usgs.gov>


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" - Unknown
Received on Thu Oct 12 2006 - 11:34:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US