SQLPlus with Perl

[The alMIGHTY N]

Hi all,

I've been assigned a project involving SQLplus calls to an Oracle
database within Perl code. I have examples of how to perform insert and
update statements that don't return anything back to the code, but I
now need to perform a select statement to check values in the database.

How would I go about executing SQLplus calls and assigning the results
to a Perl string or array?

Thanks for any help.

Please e-mail me at (e-mail address removed) if possible.

Thanks again!

Nathaniel

 

[neil.shadrach]

How would I go about executing SQLplus calls and assigning the results
to a Perl string or array?

for ( split( /\n/, qx(su - $unix_user "-c echo \\"${sql}\\" |
$ORACLE_HOME/bin/sqlplus -S $ora_name/$ora_password\@$ora_sid") ) )
{
print "$_\n";
}

 

[The alMIGHTY N]

for ( split( /\n/, qx(su - $unix_user "-c echo \\"${sql}\\" |
$ORACLE_HOME/bin/sqlplus -S $ora_name/$ora_password\@$ora_sid") ) )
{
print "$_\n";
}

Thanks for the response! Is there any chance you'd be able to explain
the code to me? I've kind of been pulled back into this coding (I'm an
interface designer and developer by trade) and I have only rudimentary
knowledge of the Perl programming language.

Here's the code that currently exists in the Perl file so you can see
how SQLplus is running.

SQL="UPDATE my_table SET my_variable = $theValue WHERE another_variable
= $anotherValue;"
export SQL
echo "Running this query:"
echo $SQL
sqlplus username/password@DATABASENAME 2>&1 <<EOF
set wra off
set pages 999
set lin 999
$SQL
commit;
EOF

I just want to be able to change the SQL statement to a SELECT
statement and then cram the results into some sort of array.

I think the part that's really getting me is the UNIX portion. The Perl
code I need to run will be part of a script that generates an HTML page
as opposed to being run from a command line interface. Would that
change how the code is written?

I assume $ora_name and $ora_password correspond to the username and
password to log into the database and $ora_sid corresponds to the name
of the database I'm trying to connect to. Are these correct
assumptions?

Thanks for all your help!

NL

 

[xhoster]

Hi all,

I've been assigned a project involving SQLplus calls to an Oracle
database within Perl code. I have examples of how to perform insert and
update statements that don't return anything back to the code, but I
now need to perform a select statement to check values in the database.

How would I go about executing SQLplus calls and assigning the results
to a Perl string or array?

SQL*Plus is Oracle's command line user interface for a *human* to connect
to and use an Oracle database. Perl is not a human. 99+% of the time,
you should use DBI and DBD::Oracle, not SQL*Plus, to connect Perl to an
Oracle database server. It might take a little more work to get
DBD::Oracle installed and set up, but from then on it will be much better
than hacking things together with SQL*Plus.

That said, you could run sqlplus in backticks and parse the returned
value.

Xho

 

[neil.shadrach]

Ysgrifennodd The alMIGHTY N:

Thanks for the response! Is there any chance you'd be able to explain
the code to me? I've kind of been pulled back into this coding (I'm an
interface designer and developer by trade) and I have only rudimentary
knowledge of the Perl programming language.

The stuff within qx() is a UNIX command string. It runs the unix
commands within double quotes as a different user. Those commands pipe
the sql to sqlplus. The split command breaks the output of the unix
commands into lines and the loop prints each one. You could replace
this by assignment to an array. As Xho has pointed out you wouldn't
normally want to work this way. The DBI module is the proper way. This
allows you to write stuff like ( from the CPAN page ):

$sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");
$sth->execute( $baz );
while ( @row = $sth->fetchrow_array ) {
print "@row\n";
}

Here's the code that currently exists in the Perl file so you can see
how SQLplus is running.

SQL="UPDATE my_table SET my_variable = $theValue WHERE another_variable
= $anotherValue;"
export SQL
echo "Running this query:"
echo $SQL
sqlplus username/password@DATABASENAME 2>&1 <<EOF
set wra off
set pages 999
set lin 999
$SQL
commit;
EOF

That's ksh or similar not perl!

I just want to be able to change the SQL statement to a SELECT
statement and then cram the results into some sort of array.

I think the part that's really getting me is the UNIX portion. The Perl
code I need to run will be part of a script that generates an HTML page
as opposed to being run from a command line interface. Would that
change how the code is written?

Not necessarily but everything you say makes me think you'd be better
off going down the CPAN module route.

I assume $ora_name and $ora_password correspond to the username and
password to log into the database and $ora_sid corresponds to the name
of the database I'm trying to connect to. Are these correct
assumptions?

More or less. The $ora_sid is the Oracle SID, a unique instance ID

 

[The alMIGHTY N]

SQL*Plus is Oracle's command line user interface for a *human* to connect
to and use an Oracle database. Perl is not a human. 99+% of the time,
you should use DBI and DBD::Oracle, not SQL*Plus, to connect Perl to an
Oracle database server. It might take a little more work to get
DBD::Oracle installed and set up, but from then on it will be much better
than hacking things together with SQL*Plus.

That said, you could run sqlplus in backticks and parse the returned
value.

Xho

Hi, thanks for the reply! After all the searching I've done these past
couple of days, I came to the same conclusion. I'm putting together a
quick script that uses DBI to do everything, but I'm still holding out
hope that there's some solution to this (the senior developers are not
keen on adding a new module to the system especially since this is such
a small part of the applicatiion).

How would one go about running sqlplus "in backticks"?

Thanks,

NL

 

[J. Gleixner]

(e-mail address removed) wrote:

Hi, thanks for the reply! After all the searching I've done these past
couple of days, I came to the same conclusion. I'm putting together a
quick script that uses DBI to do everything, but I'm still holding out
hope that there's some solution to this (the senior developers are not
keen on adding a new module to the system especially since this is such
a small part of the applicatiion).

If they are "Senior Developers" and they use perl to access
the database, then DBI would already be installed. Also, why
not go to them for help? It's likely you'd already have finished
this project and done it in a way that's supported at your
company.

How would one go about running sqlplus "in backticks"?

One would first look through the documentation for 'backticks' so
one would learn what it means and one would probably find the
answer on one's own.

The answer would be, "The same as you would run any other command 'in
backticks'."

Look for "qx" in perldoc perlop.

and

perldoc -q "Why can't I get the output of a command with system()"

and

perldoc -q "How can I capture STDERR from an external command"


If you have shell scripts already set-up and, for some reason, you
want to execute those scripts and get the output into a variable in a
perl script, then you could call the shell script, in backticks.

my $script_output = `/some/path/to/script`;

SQLPlus can offer some nice formatting options so depending on your need
using DBI or calling a shell script would be possible solutions.

 

[Peter Scott]

Hi, thanks for the reply! After all the searching I've done these past
couple of days, I came to the same conclusion. I'm putting together a
quick script that uses DBI to do everything, but I'm still holding out
hope that there's some solution to this (the senior developers are not
keen on adding a new module to the system especially since this is such a
small part of the applicatiion).

How would one go about running sqlplus "in backticks"?

That is really a false optimization. Firstly, it takes about 5 minutes to
add DBI to a machine under most circumstances, and most of that is
waiting. Secondly, you wanted the results of the query put in "some kind
of array", which in DBI would be done with

use DBI;
my $db = DBI->connect('dbi:Oracle...
my $rowref = $db->selectall_arrayref(<<'EOSQL');
... query ...
EOSQL

No one can make a program to do the same by parsing sqlplus output easier.
It can't *get* any easier than that.

Now the only fly in the ointment is that you need DBD::Oracle installed,
and in some circumstances that can be difficult to install, not because of
the module itself, but because of needing the right Oracle client files
for it. But if you have them this also is a 5 minute job, so why not try.