Re: Running SQLPlus from command line

From: RLN <rlntemp-newsgroup_at_yahoo.com>
Date: 12 Oct 2006 15:03:12 -0700
Message-ID: <1160690592.051072.103270@e3g2000cwe.googlegroups.com>

Yes, the query returns approx 33 rows. This same query runs fine in a manual process via Toad. I suppose doing a "List" command just prior to the 'exit in the sql file would confirm that for sure, or would there be a more efficient way...maybe pipe the recordcount to an ascii text file like, "# of rows returned for QueryX was: <recordcount>" (or however SQL Plus would reference the recordcount of the result set returned.)

>>SPOOL C:\my_directory\test12.txt <<
yes, full qualification would be wise here.

Thanks Brian. Your help here is very much appreciated.

Brian Peasland wrote:
> 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 - 17:03:12 CDT