?
Solved

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

Posted on 2006-07-20
10
Medium Priority
?
890 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
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.

 
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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 …
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
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

650 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