SQL*Plus redirect STDIN on WIndows [message #646906] |
Thu, 14 January 2016 05:58 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I need to invoke SQL*Plus in a Windows shell script, and send it some commands from within the script. On Unix, this is easy:$
$ cat test.sh
sqlplus -S /nolog <<EOF
conn / as sysdba
set serverout on
exec dbms_output.put_line('sussed');
EOF
$
$ sh test.sh
sussed
PL/SQL procedure successfully completed.
$
$
but on Windows it doesn't work:C:\tmp>
C:\tmp>type test.bat
sqlplus -S /nolog <<EOF
conn / as sysdba
set serverout on
exec dbms_output.put_line('sussed');
EOF
C:\tmp>
C:\tmp>.\test.bat
<< was unexpected at this time.
C:\tmp>sqlplus -S /nolog <<EOF
C:\tmp>
Is there a way to do this on Windows? The error message is being returned by the shell, not by SQL*Plus. Redirecting STDIN should work in Windows shell scripts, is it a problem with SQL*Plus? Or with the way I'm doing it? Any alternative?
Thank you for any insight.
|
|
|
Re: SQL*Plus redirect STDIN on WIndows [message #646907 is a reply to message #646906] |
Thu, 14 January 2016 06:59 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Sorta kinda. Kind of a pain do escape so much stuff with "^" though, especially closing ), and you have to preceed every SQL line with an "echo" ....
U:\>type test.cmd
(
echo conn user^/pass@test
echo set serverout on
echo exec dbms_output.put_line('sussed'^^^);
) | sqlplus /nolog
U:\>test.cmd
U:\>(
echo conn user/pass@test
echo set serverout on
echo exec dbms_output.put_line('sussed'^);
) | sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Thu Jan 14 13:56:50 2016
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> Connected.
SQL> SQL> sussed
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
U:\>
[Updated on: Thu, 14 January 2016 07:18] Report message to a moderator
|
|
|
|
Re: SQL*Plus redirect STDIN on WIndows [message #646912 is a reply to message #646910] |
Thu, 14 January 2016 08:07 |
John Watson
Messages: 8964 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Thank you for replying - I don't want to do it that way because creating files is an extra complication I could do without. There are permission issues in Windows that you have to sort out with the icacls utility (the equivalent of chown and chmod) that are really awkward.
|
|
|
|