?
Solved

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

Posted on 2006-07-20
10
Medium Priority
?
877 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 504 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Six Sigma Control Plans
Suggested Courses

762 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