| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Instream SQL in an NT Batch script
Bruce,
I don't have "strait" solution for Option 3, But I have some kind of "workaround" (if you don't mind seeing couple error messages in your DOS-window.
Here is my batch file A.bat:
sqlplus /nolog < a.bat > a_bat.log
connect af_dba/af_dbapwd_at_af
spool c:\temp\A.log
set echo on
set serveroutput on size 10000
select user from dual;
select sysdate from dual;
declare lDummy DATE;
begin
select sysdate into lDummy from dual;
dbms_output.enable;
dbms_output.put_line(concat('lDummy=', to_char(lDummy, 'mm/dd/yyyy
hh24:mi:ss')));
end;
/
select user from dual;
spool off
As a result I get A.log file:
SQL> set echo on SQL> set serveroutput on size 10000 SQL> select user from dual;
AF_DBA
SQL> select sysdate from dual;
SYSDATE
07/23/2004 09:58:50
SQL> declare lDummy DATE;
2 begin
3 select sysdate into lDummy from dual;
4 dbms_output.enable;
5 dbms_output.put_line(concat('lDummy=', to_char(lDummy, 'mm/dd/yyyy
hh24:mi:ss')));
6 end;
7 /
lDummy=07/23/2004 09:58:50
PL/SQL procedure successfully completed.
SQL> select user from dual;
USER
AF_DBA SQL> spool off
And a_bat.log file:
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jul 23 09:58:50 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> SP2-0734: unknown command beginning "sqlplus /n..." - rest of line
ignored.
SQL> Connected.
SQL> SQL> SQL> SQL>
USER
SQL> 2 3 4 5 6 7 lDummy=07/23/2004 09:58:50
PL/SQL procedure successfully completed.
SQL>
USER
Besides, after exiting from sqlplus, it will try to execute all your SQL/PLSQL as DOS-commands, so you'll see some additional garbage on the screen.
Btw., if you try to execute the same code using Option 2, you'll have to use escape character (^) with parenthesis and add one additional "closing" parenthesis. The following batch file:
rem --start of batch file
( echo set serveroutput on
echo spool c:\temp\Atest_sqlplus.log
echo connect af_dba/af_dbapwd_at_af
echo set echo on
echo select user from dual;
echo select sysdate from dual;
echo declare lDummy DATE;
echo begin
echo select sysdate into lDummy from dual;
echo dbms_output.enable;
echo dbms_output.put_line^(concat^('lDummy=', to_char^(lDummy,
'mm/dd/yyyy hh24:mi:ss'^)
echo ^) echo ^) echo ^);
produces the following log file:
SQL> connect af_dba/af_dbapwd_at_af
Connected.
SQL> set echo on
SQL> select user from dual;
USER
AF_DBA SQL> select sysdate from dual;
SYSDATE
07/22/2004 15:27:24
SQL> declare lDummy DATE;
2 begin
3 select sysdate into lDummy from dual;
4 dbms_output.enable;
5 dbms_output.put_line(concat('lDummy=', to_char(lDummy, 'mm/dd/yyyy
hh24:mi:ss'
6 ) 7 ) 8 );
PL/SQL procedure successfully completed.
SQL> select user from dual;
USER
AF_DBA SQL> spool off
In the log file you can see, how it looses additional parenthesis I had to put in in order to make it working.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Reardon, Bruce
(CALBBAY)
Sent: Wednesday, July 21, 2004 6:46 PM
To: oracle-l_at_freelists.org
Subject: RE: Instream SQL in an NT Batch script
I raised this back in March and suggested the following 2 options:
Option 1
For single line commands, you can use something like:
echo connect user/pwd_at_sid | sqlplus /nolog
However, this isn't really very useful.
Option 2
More useful is something like:
rem --start of batch file
( echo connect user/pwd_at_sid
echo select user from dual;
echo select sysdate from dual;
) | sqlplus /nolog
rem --end batch file
Tanel Poder replied and said you could use Option 3
sqlplus "/ as sysdba" < con
select 1 from dual;
select 2 from dual;
exit
But when I copied that into a batch file and ran it, it sits at the =
sqlplus prompt until I hit <ctrl-z> and then the window goes (if I leave
=
the exit in) or it says "'select' is not recognized as an internal or =
external command,
operable program or batch file." if I take the exit out and put a pause
=
in - so I was missing something. I'd also be keen to work out how to =
get Option 3 working.
HTH,
Bruce Reardon
=20
NOTICE: This e-mail and any attachments are private and confidential and
=
may contain legally privileged information. If you are not an =
authorised recipient, the copying or distribution of this e-mail and any
=
attachments is prohibited and you must not read, print or act in =
reliance on this e-mail or attachments. This notice should not be =
removed.
-----Original Message-----
From: Smith, Ron L.
Sent: Thursday, 22 July 2004 7:00 AM
I am trying to move a couple of Unix scripts to an NT server. I know just about everything needs to change but the scripts use in stream SQL commands and for some reason NT doesn't like the syntax.
The code is simple just like:
sqlplus / <<EOF
select something from somewhere;
EOF
It complains and says <<EOF not expected here. =3D20
Works on Unix and Linux.
What am I missing?
Thanks!
Ron
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Fri Jul 23 2004 - 09:06:21 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
 |
 |