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?
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.
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.
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.
SQL Plus is the most basic Oracle Database utility, with a basic command-line interface, commonly used by users, administrators, and programmers.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With