RE: RE: How to run sql*plus and its command in Windows as b

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Thu, 20 Mar 2003 10:54:22 -0500
Message-Id: <24726.322549@fatcity.com>

Dick,

Just for the example I stated. Sometimes, the sql I want to run is variable, depending upon something that happens in the .BAT file. This way, I can construct the sql file using NT substitution environmental's, and the sql statement changes with each run. The most common thing I due is to spool the output with a file name that includes the date and time that the sql ran. I'm developing one right now to produce a flat file to be loaded by a cobol program to another Oracle database (don't even ask why we can't do this directly). the date and time included in the file name will help us keep things straight.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com] Sent: Thursday, March 20, 2003 10:05 AM
To: Mercadante; Thomas F; Multiple recipients of list ORACLE-L Subject: Re:RE: How to run sql*plus and its command in Windows as b

Thom,

If your going to go to all that trouble, why not simply create a .sql file &
call it on the command line.

Dick Goulet

____________________Reply Separator____________________

Subject: RE: How to run sql*plus and its command in Windows as batc Author: "Mercadante; Thomas F" <NDATFM_at_labor.state.ny.us> Date: 3/20/2003 4:53 AM

Chuan,

What I do is the following:

set DBA_TEMP=\sometempdirectory
echo connect test/test > %DBA_TEMP%\run.sql echo alter sesion set sql_trace=true >>%DBA_TEMP%\run.sql echo select count(*) from product_temp p, invoice_temp i >> %DBA_TEMP%\run.sql
echo where p.invpsid=i.invoiceid; >>%DBA_TEMP%\run.sql echo disconnect >>%DBA_TEMP%\run.sql
echo exit >>%DBA_TEMP%\run.sql $ORACLE_HOME/bin/sqlplus /nolog @%DBA_TEMP%\run.sql

del %DBA_TEMP%\run.sql

just be aware of special characters that will not be echo'ed properly in NT scripting - thing like the | char will not work. You will need to "escape" these like this to get them to echo properly.

echo select col1^|^|col2 from sometable;

The advantage of this within NT bat files is that you can develop and use substitution characters inside the sql files. for example, if you wanted to spool a file where the name contains information from outside the Oracle session, you could do the following:

set ORACLE_SID=WTWD
echo spool %ORACLE_SID%_outfile.dat > run.sql

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, March 19, 2003 10:19 PM To: Multiple recipients of list ORACLE-L

Hi, All,

In unix, we can put following commands in a file and run that file. For example:

$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect test/test
alter sesion set sql_trace=true
select count(*) from product_temp p, invoice_temp i where p.invpsid=i.invoiceid;
disconnect
exit
EOF What's the corresponding format on windows?

Your input is precious,

TIA Chuan
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Chuan Zhang
INET: Chuan.Zhang_at_transact.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------