Solved

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

Posted on 2006-07-20
10
852 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Suggested Solutions

Title # Comments Views Activity
PERL get the value for query 4 151
Removing permissions on a Cyrus mailbox via a wrapper script 3 97
Awk Question 2 127
Regular Expression question to filter with negation. 6 95
On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

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