----- Original Message -----
From: "Shao, Chunning" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, February 25, 2002 04:49
Subject: help: how do you in DBI?
> As spool command can not work in DBI, how can we spool a query output to
> a file?
>
> Somebody suggests me to use this:
>
> #!/usr/local/bin/perl -w
>
> open ORA, "| $ORACLE_HOME/bin/sqlplus -s $usr/$pasw" or die "Can't pipe
> to sqlplus: $!";
> print ORA "exec mystoredprocedure";
> print ORA "set trimspool on pagesize 500 linesize 200 colsep ' '"
> print ORA "spool output.txt"
> print ORA "select * from mytable;\n";
> print ORA "exit\n";
> close ORA;
>
> Which means we have to use another db connection within the DBI scripts,
> is this the only way of doing it or there is some better way?
You need spool with SQL*Plus because you have no other means for capturing
arbitrarily sized output. With DBI, you execute each SQL statement and
process the result in your Perl script. The method you were shown is
acceptable for one-off shell scripts, but it is not at all robust.
You really need to read the fine DBI (perldoc DBI) and DBD::Oracle (perldoc
DBD::Oracle) manuals to see what is available, including examples.
Here is a general boilerplate I use for simple SELECT and process
applications. I typed it from memory, so there may be some typos. There
are DBI methods available that allow you to combine some of the steps, but I
like to have them in front where I can see them. This also scales better
than the fetchall* methods.
#!/usr/local/bin/perl -w
## Always use -w and 'use strict;'
use DBI;
use strict;
# Normally get these from the command line
my $sInst = "Instance";
my $sUser = "User";
my $sPwd = "Password";
# Using implicit error checking
my $dbh = DBI -> connect( "dbi:Oracle:$sInst", $sUser, $sPwd,
{ AutoCommit => 0, RaiseErrors => 1} );
my $sth = $dbh -> prepare( "SELECT * FROM mytable" ); # Note: no ';' in SQL
$sth -> execute();
my @sCol;
$sth -> bind_columns( \( @sCol[@{$sth -> {NAME}}] ) );
while ( $sth -> fetch ) {
# Do something with one row
# The column values are in @sCol
}
$dbh -> disconnect;
exit 0;
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
