EOF problem with sqlplus on Windows

theophanie77 has asked for the wisdom of the Perl Monks concerning the following question:

Hi everybody, I searched the web a long time before posting here. I searched also for some solutions on PerlMonk but they do not work for me. Here is my problem : On Windows 2000 SP4 server with perl ( without the DBD::Oracle module), I would like to use sqlplus but my sql list inside "<<EOF" and "EOF" returns an error. "<< was unexpected" ( "<< était inattendu" in french ) Here is my code :

$retour_nodbd = qx { sqlplus -s system/password\@MYBASE <<EOF
select sysdate from dual;
exit;
EOF };
[download]

The following command works :

$retour_nodbd=`tnsping MYBASE`;
[download]

It works also for :

$retour_nodbd=`sqlplus system\/password\@MYBASE \@my_sql`;
[download]

The problem seems to come from the "<<" characters. How can I solve it ? Thanks Jean-michel, Nemours, FRANCE

Comment on EOF problem with sqlplus on Windows Select or Download Code

Replies are listed 'Best First'.
Re: EOF problem with sqlplus on Windows by Corion (Patriarch) on Mar 22, 2012 at 09:09 UTC
The Windows shell does not understand here-documents¹. You will need to either pass in a single line or open a double pipe using IO::Open2 (or IO::Open3) to isql. My approach (when I can't use DBD::ODBC or DBD::Oracle) is to write the SQL to a tempfile and then launch `sqlplus <$tempfile`. I recommend using DBD::ODBC instead, if you can't install other modules - DBD::ODBC comes included with ActiveState Perl and Strawberry Perl. Updated: Here-documents are not the problem, but the Windows shell (`cmd.exe`) and `CreateProcess` don't know and don't handle newlines in the arguments. ¹ That is not related to the problem.	[reply] [d/l] [select]
Re: EOF problem with sqlplus on Windows by rovf (Priest) on Mar 22, 2012 at 09:19 UTC
`qx` quotes its argument (similar to a double-quoted string, only that the string is executed afterwards. Hence the here document is not interpreted by Perl. Maybe you intended to have the HERE document being interpreted by the shell, but this doesn't work either, because passing a multi-line string to qx does not cause the shell to execute a multi-line script. Here a simple example from the command line (I'm using Windows for this, but the same applies to Linux etc): `C:\>perl -lwe "my $x=qx(echo a\necho b); print $x" a echo b` [download] As you can see, the shell is not executing two echo commands, but only one. If you want to execute multi-line shell commands, I suggest you create a temporary file (File::Temp), write the shell script into it, and execute it. -- Ronald Fischer <ynnor@mm.st>	[reply] [d/l] [select]
Re^2: EOF problem with sqlplus on Windows by choroba (Cardinal) on Mar 22, 2012 at 11:28 UTC
No. On Linux, I get `a b` [download] as output. Even this works: `perl -lwe 'my $x=qx(echo a echo b);print $x'` [download] i.e. using the newline itself. ~~Nevertheless, HERE docs are not interpreted by the shell.~~ Update: Stupid me. Used `echo` instead of `cat` in testing.	[reply] [d/l] [select]
Re^2: EOF problem with sqlplus on Windows by JavaFan (Canon) on Mar 22, 2012 at 11:31 UTC
Maybe you intended to have the HERE document being interpreted by the shell, but this doesn't work either, because passing a multi-line string to qx does not cause the shell to execute a multi-line script. Couldn't bother to try? $ export PS2=""; perl -E '$x = `cat <<FOO hello world FOO`; print uc $x' HELLO WORLD $ [download] qx doesn't filter out newlines, and the shell is smart enough to know how to deal with newlines.	[reply] [d/l]
Re^3: EOF problem with sqlplus on Windows by rovf (Priest) on Mar 22, 2012 at 14:27 UTC
Couldn't bother to try? At that time, I only had access to a Windoze system, where I tried it of course (as you can see from my posting). Thanks for demonstrating that this is different on *nix. -- Ronald Fischer <ynnor@mm.st>	[reply] [d/l]
Re^3: EOF problem with sqlplus on Windows by theophanie77 (Novice) on Mar 23, 2012 at 15:22 UTC
JavaFan, It still does not work. But we tried with a colleague to execute sqlplus with the "<<" on the server itself. The same error occurs : "<< etait inattendu" ( << was unexpected ) We need to search the way to execute multiple sqlplus commands at the windows prompt now. Thanks Jean-michel	[reply]
Re^4: EOF problem with sqlplus on Windows by JavaFan (Canon) on Mar 23, 2012 at 15:44 UTC
Re^4: EOF problem with sqlplus on Windows by choroba (Cardinal) on Mar 23, 2012 at 15:26 UTC
Re^4: EOF problem with sqlplus on Windows by theophanie77 (Novice) on Mar 23, 2012 at 15:48 UTC
Re: EOF problem with sqlplus on Windows by kcott (Archbishop) on Mar 22, 2012 at 09:15 UTC
~~The closing `EOF` needs to be on a line by itself.~~ Update: Sorry, I didn't notice this was Windows. Answer based on UNIX, e.g. `ken@ganymede: ~/tmp $ rm fred ken@ganymede: ~/tmp $ perl -Mstrict -Mwarnings -E 'qx{cat <<EOT >fred > hello > EOT > };' ken@ganymede: ~/tmp $ cat fred hello ken@ganymede: ~/tmp $` [download] -- Ken	[reply] [d/l] [select]