Home » Infrastructure » Windows » SQL*Plus redirect STDIN on WIndows
SQL*Plus redirect STDIN on WIndows [message #646906] Thu, 14 January 2016 05:58 Go to next message
John Watson
Messages: 8922
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 Go to previous messageGo to next message
ThomasG
Messages: 3211
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 #646910 is a reply to message #646906] Thu, 14 January 2016 07:18 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Just one more reason I hate Windows.

My usual technique is to echo the sqlplus commands to another file, then have sqlplus execute that:

echo set echo on feedback on verify on trimspool on > tempscript.sql
echo spool sqlplus.log >> tempscript.sql
echo select name from v$database >> tempscript.sql
echo select sysdate from dual: >> tempscript.sql
echo spool off >> tempscript.sql
sqlplus / as sysdba @tempscript.sql

But yeah, in a *nix shell script, input redirection is much easier and more straight-forward.
Re: SQL*Plus redirect STDIN on WIndows [message #646912 is a reply to message #646910] Thu, 14 January 2016 08:07 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: SQL*Plus redirect STDIN on WIndows [message #646913 is a reply to message #646907] Thu, 14 January 2016 08:08 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Sussed! Thank you. It will take me a while to sort out escaping characters, but with that example I think I can hack it.
Previous Topic: PATH variable issue during installation
Next Topic: Load data of multiple files from dynamic
Goto Forum:
  


Current Time: Thu Mar 28 09:20:44 CDT 2024