Solved

How to do input redirection in windows or cygwin from Perl  to run Oracle sqlplus

Posted on 2006-07-20
10
855 Views
Last Modified: 2008-01-09
The following simple perl script works fine on unix but it doesn't work on windows  cygwin and also from a command window.
In both cases i get the following error:
<< was unexpected at this time.

Here is the script:
    my $s_un = "onair/onair@uktvcust";
    my $sql_command = "sqlplus /nolog";
   
    my $connect_cmd = "connect $s_un";
   
        $sqloutput =    `$sql_command  <<END
         $connect_cmd ;
         select * from app_user where rownum < 26;
         quit
        END `;

print      $sqloutput;


0
Comment
Question by:mordi
10 Comments
 
LVL 4

Expert Comment

by:NorCal2612
ID: 17146945
Not sure exactly, but I think you need to change:

my $s_un = "onair/onair@uktvcust";

to:

my $s_un = "onair/onair\@uktvcust";

as well... hopefully someone else can help with the other thing. :)
0
 
LVL 6

Expert Comment

by:GnarOlak
ID: 17149133
Try:

my $s_un = "onair/onair@uktvcust";
    my $sql_command = "sqlplus /nolog";
   
    my $connect_cmd = "connect $s_un";
   
        $sqloutput =    qq{$sql_command  <<END
         $connect_cmd ;
         select * from app_user where rownum < 26;
         quit
END;
};
print     $sqloutput;
0
 
LVL 48

Expert Comment

by:Tintin
ID: 17149578
If you had

use warnings;

or

#!/usr/bin/perl -w

you would have seen an warning along the lines of;

Possible unintended interpolation of @uktvcust in string at /tmp/script line 2.

my $s_un = "onair/onair@uktvcust";

should be written as

my $s_un = 'onair/onair@uktvcust';
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 8

Expert Comment

by:Perl_Diver
ID: 17149922
I'm surprised it works on unix. Perl on unix will still try and interpolate @uktvcust into a string inside of double-quotes.
0
 
LVL 5

Expert Comment

by:wlfs
ID: 17150565
Hi mordi,

your problem is actually a problem of the windows shell not perl. After variable interpolation, everything inside the backtick quotes is sent to the shell - also the <<END ... END thing (this is not the here-document operator of perl but a literal string). The unix shell you were using understands the <<END ... END thing, the windows shell doesn't.
  << was unexpected at this time.
is the error message of the windows shell.

Solution:
(i) Case 1 - sqlplus does *not* require line breaks to separate command line parameters:
Just get rid of all the line breaks inside the backticks and remove the <<END stuff
         $sqloutput =    `$sql_command  $connect_cmd; select * from app_user where rownum < 26; quit`;
         print $sqloutput;

(ii) Case 2 - sqlplus *does* require line breaks to separate command line parameters:
You need to find out how to span one command over several lines in the windows shell. I did a quick search but couldn't find anything. Maybe the windows shell doesn't allow for multi-line commands at all?
Whatever multi-line syntax is required by windows, incorporate it into the backtick-quoted string.


@GnarOlak:
I'm afraid your solution won't help. You are exchanging the backticks with double-quoting. Thus the quoted command is never sent to the shell, i.e. it is never executed.
0
 
LVL 17

Expert Comment

by:mjcoyne
ID: 17164799
Does it work if you send along the carriage returns?

        $sqloutput =    `$sql_command\n$connect_cmd;\nselect * from app_user where rownum < 26;\nquit';
0
 
LVL 3

Author Comment

by:mordi
ID: 17166173
Before ansering your questions: remember that i get the error in Cygwin also which is unix shell under windows.
0
 
LVL 3

Author Comment

by:mordi
ID: 17166277
All your comments posted before, didn't work.
The sql_command must be run first in order to run the other commands. It is like you will replace the sqlplus command with a notepad command.
0
 
LVL 17

Accepted Solution

by:
mjcoyne earned 126 total points
ID: 17166563
Try opening a filehandle that pipes to the DB, and print your commands to it (examples from http://rootshell.be/~yong321/computer/OracleAndPerl.html):

open ORA, "| $ORACLE_HOME/bin/sqlplus -s $usr/$pasw" or die "Can't pipe to sqlplus: $!";

print ORA "exec mystoredprocedure\n";
print ORA "set trimspool on pagesize 500 linesize 200 colsep '      '\n"
print ORA "spool output.txt\n"
print ORA "select * from mytable;\n";
print ORA "spool off\n";
print ORA "exit\n";

close ORA;

Or (a better idea, I think), use Perl's Database Interface Module:

use DBI;

$dbh=DBI->connect("dbi:Oracle:DBName","usr","pasw",{AutoCommit=>0}) or die "Can't log in: $!";

$sth=$dbh->prepare("select mycolumn from mytable") or die "$DBI::errstr";
$sth->execute or die "Can't execute sth: $DBI::errstr.";

while (($mycolumn)=$sth->fetchrow_array)
 { print "$mycolumn" if defined $mycolumn;
 }

$sth->finish;
$dbh->disconnect;

0
 
LVL 3

Author Comment

by:mordi
ID: 17170593
The open filehandle is the solution
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question