Any possible way to save output of sqlplus command into an array - Perl

Question

Currently, I am spooling the sqlplus command into a text file but even this is causing me problems as I would like to separate the values by commas. So far It has not worked.

I was hoping something like this would work

 @test = system('sqlplus un/pw@host @test.sql');

The test.sql file contains three statements all returning numbers. If I could save these to the @test array, this would be great.

Any ideas?

system does not return standard output, it returns the return value of the system call. To capture output use backticks or qx(), but you will need to escape the @ characters to avoid interpolation. However, if you are querying a database, you might be better off using the DBI module. — TLP
– TLP, Commented Jan 22, 2013 at 10:32
check this:theunixshell.blogspot.com/2013/01/… .where in here it uses sybase just remove the sybase part and put sqlplus instead of isql.the remaining it stores each line of the result into an array. — Vijay
– Vijay, Commented Jan 22, 2013 at 10:44

choroba · Accepted Answer · 2013-01-22 11:03:06Z

2

Use the DBI module with DBD::Oracle:

#!/usr/bin/perl
use warnings;
use strict;

use DBI;

my $dbh = DBI->connect("dbi:Oracle:host=host;sid=dbname",
                       $user, $password);

open my $IN, '<', 'test.sql' or die $!;
$/ = ';'; # Queries separated by semicolons, no other semicolons anywhere!
while (my $sql = <$IN>) {
    my $sth = $dbh->prepare($sql);
    $sth->execute;
    my @test = @{ $sth->fetchall_arrayref // [] };
    print "@$_\n" for @test;
}

answered Jan 22, 2013 at 11:03

choroba

245k27 gold badges221 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miguel Prz · Accepted Answer · 2013-01-22 13:04:10Z

0

The DBI script based is good, but if your point is about the use of sqlplus command in any case, you should take a look at this CPAN module: Expect. It provides you a deeper control of an external command in many and useful ways.

answered Jan 22, 2013 at 13:04

Miguel Prz

13.8k32 silver badges42 bronze badges

Collectives™ on Stack Overflow

Any possible way to save output of sqlplus command into an array - Perl

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related