Solved

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

Posted on 2006-07-20
10
840 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now