Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL*Plus inside Perl script

I am trying to connect to a table using SQL*Plus and fetch the data in a Perl script and store that output in a Perl variable.

In a shell script I would do this:

    SQL_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF
    ${SQLPLUS_SETTINGS}
    select foo||'|'||bar ||'|'|| xyz from temp where dfg='some';
    exit;
    EOF`

But how can I do this in Perl?

like image 944
Vijay Avatar asked Nov 13 '09 07:11

Vijay


People also ask

What is SQL*Plus script?

SQL*Plus is essentially an interactive query tool with some scripting capabilities. You can enter a SQL statement, such as a SELECT query, and view the results. You can execute data definition language (DDL) statements to create tables and other objects.

Which is an SQL*Plus command?

SQL*Plus is a command-line tool that provides access to the Oracle RDBMS. SQL*Plus enables you to: Enter SQL*Plus commands to configure the SQL*Plus environment. Startup and shutdown an Oracle database.

What is SQL*Plus and why would you want to use it?

SQL*Plus is the command-line interface to the Oracle database. Its fundamental reason for existence is to allow you to enter and execute ad hoc SQL statements and PL/SQL code blocks.

What is SQL Star Plus?

SQL Plus is the most basic Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.


1 Answers

Check out the DBI module. In fact, there's a whole website dedicated to it: dbi.perl.org. Also, check out the CPAN module reference for DBI.

Here's a code example, straight from the first DBI tutorial on google:

    use DBI;

    my $dbh = DBI->connect('DBI:Oracle:payroll')
        or die "Couldn't connect to database: " . DBI->errstr;
    my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
        or die "Couldn't prepare statement: " . $dbh->errstr;

    $sth->execute($lastname)             # Execute the query
        or die "Couldn't execute statement: " . $sth->errstr;

    # Read the matching records and print them out          
    while (@data = $sth->fetchrow_array()) {
        my $firstname = $data[1];
        my $id = $data[2];
        print "\t$id: $firstname $lastname\n";
    }
    if ($sth->rows == 0) {
      print "No names matched `$lastname'.\n\n";
    }
    $sth->finish;
    print "\n";
    print "Enter name> ";

    $dbh->disconnect;

Perl also has that EOF style multiline comment; you can make a long query like this:

my $query = <<'END_QUERY';
${SQLPLUS_SETTINGS}
select foo||'|'||bar ||'|'|| xyz from temp where dfg='some';
exit;
END_QUERY
like image 103
Robert P Avatar answered Sep 16 '22 21:09

Robert P