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-----
Sent: Thursday, March 20, 2003 10:05 AM
To: Mercadante; Thomas F; Multiple recipients of list ORACLE-L
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____________________
Author: "Mercadante; Thomas F" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
