SQLPlus with Perl

Discussion in 'Perl Misc' started by The alMIGHTY N, Nov 2, 2006.

  1. 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 if possible.

    Thanks again!

    Nathaniel
     
    The alMIGHTY N, Nov 2, 2006
    #1
    1. Advertising

  2. The alMIGHTY N

    Guest

    The alMIGHTY N wrote:

    > 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";
    }
     
    , Nov 2, 2006
    #2
    1. Advertising

  3. wrote:
    > The alMIGHTY N wrote:
    >
    > > 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";
    > }


    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
     
    The alMIGHTY N, Nov 2, 2006
    #3
  4. The alMIGHTY N

    Guest

    "The alMIGHTY N" <> wrote:
    > 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

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Nov 2, 2006
    #4
  5. The alMIGHTY N

    Guest

    Ysgrifennodd The alMIGHTY N:
    > wrote:
    > > The alMIGHTY N wrote:
    > >
    > > > 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";
    > > }

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

    > Thanks for all your help!
    >
    > NL
     
    , Nov 2, 2006
    #5
  6. wrote:
    > "The alMIGHTY N" <> wrote:
    > > 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
    >
    > --
    > -------------------- http://NewsReader.Com/ --------------------
    > Usenet Newsgroup Service $9.95/Month 30GB


    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
     
    The alMIGHTY N, Nov 2, 2006
    #6
  7. The alMIGHTY N

    J. Gleixner Guest

    The alMIGHTY N wrote:
    > wrote:


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


    > 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.
     
    J. Gleixner, Nov 2, 2006
    #7
  8. The alMIGHTY N

    Peter Scott Guest

    On Thu, 02 Nov 2006 08:06:14 -0800, The alMIGHTY N 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).
    >
    > 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.

    --
    Peter Scott
    http://www.perlmedic.com/
    http://www.perldebugged.com/
     
    Peter Scott, Nov 3, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dpackwood
    Replies:
    3
    Views:
    1,839
  2. Gianni

    Sqlplus - S stops ...sigh !

    Gianni, Jun 17, 2004, in forum: Perl
    Replies:
    4
    Views:
    6,442
    Karel Kubat
    Jul 1, 2004
  3. Gianni

    Sqlplus -S interrupt !!!!

    Gianni, Jun 17, 2004, in forum: Perl
    Replies:
    0
    Views:
    2,957
    Gianni
    Jun 17, 2004
  4. Replies:
    1
    Views:
    809
    Martin v. Löwis
    Jan 24, 2008
  5. Daniel Berger

    Driving Oracle sqlplus with open3

    Daniel Berger, Jul 26, 2006, in forum: Ruby
    Replies:
    11
    Views:
    292
    Pra Bhandar
    Mar 3, 2009
Loading...

Share This Page