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@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;
USER

------------------------------

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
------------------------------
AF_DBA
SQL> 
SYSDATE
-------------------
07/23/2004 09:58:50

SQL>   2    3    4    5    6    7  lDummy=07/23/2004 09:58:50

PL/SQL procedure successfully completed.

SQL> 
USER
------------------------------
AF_DBA

SQL> SQL> SQL> SQL> SQL> Disconnected from Oracle Database 10g
Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


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@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 ^);
echo end;
echo /
echo select user from dual;
echo spool off
echo --
) | sqlplus /nolog
rem --end batch file


produces the following log file:

SQL> connect af_dba/af_dbapwd@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  );
  9  end;
 10  /
lDummy=07/22/2004 15:27:24


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@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Reardon, Bruce
(CALBBAY)
Sent: Wednesday, July 21, 2004 6:46 PM
To: oracle-l@xxxxxxxxxxxxx
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@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@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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: