ORA-01461 with DBI when using placeholders

From: Derek MacDonald <derekmd_at_hotmail.com>
Date: 22 Oct 2001 07:56:03 -0700
Message-ID: <371ea325.0110220656.31792774@posting.google.com>

It seems no Perl script SQL insertions will work using DBI and placeholders. We are running Oracle 8.0.5.1, DBD-Oracle-1.03, DBI-1.13, and Perl v5.6.0 built for 9000/777-hpux.

For a small test, I made a very simple Perl script (see below) to insert two integers into a test table but I get this error:   DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for   insert into a LONG column (DBD: oexec error)

However, the script will work fine if the insert is made using this method:
$sth = DBI->prepare( "INSERT INTO num_test VALUES (5, 6)" );
$sth->execute();

The testing table I made is:
CREATE TABLE num_test (one NUMBER, two NUMBER);

Here are all the scripts I've tested. The first one is the only one which works and the rest spit back the above long error. I've stripped the db, user, and password. (I'm paranoid, ok!?)

---

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect( "DBI:Oracle:HOST_HERE", "USER_HERE", "PWORD_HERE" );
$sth = $dbh->prepare( "INSERT INTO num_test VALUES( 5, 6 )" );
$sth->execute();
$sth->finish();
$dbh->disconnect();

---

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect( "DBI:Oracle:HOST_HERE", "USER_HERE", "PWORD_HERE" );
$sth = $dbh->prepare( "INSERT INTO num_test VALUES( ?, ? )" );
$sth->execute( 5, 6 );
$sth->finish();
$dbh->disconnect();

---

#!/usr/bin/perl

use DBI;
use DBI qw(:sql_types);

$dbh = DBI->connect( "DBI:Oracle:HOST_HERE", "USER_HERE", "PWORD_HERE" );
$sth = $dbh->prepare( "INSERT INTO num_test VALUES( ?, ? )" );

$sth->bind_param( 1, 5, SQL_INTEGER );
$sth->bind_param( 2, 6, SQL_INTEGER );

$sth->execute();
$sth->finish();
$dbh->disconnect();

---

#!/usr/bin/perl

use DBI;

$dbh = DBI->connect( "DBI:Oracle:HOST_HERE", "USER_HERE", "PWORD_HERE" );
$dbh->do( "INSERT INTO num_test VALUES( ?, ? )", undef, 5, 6 );
$dbh->disconnect();

---

#!/usr/bin/perl

use DBI;

my @input = ( 5, 6 );

$dbh = DBI->connect( "DBI:Oracle:HOST_HERE", "USER_HERE", "PWORD_HERE" );
$sth = $dbh->prepare( "INSERT INTO num_test VALUES( ?, ? )" );
$sth->execute( @input );
$sth->finish();
$dbh->disconnect();

---

The issue is that I have a large amount of records with over 20 columns for each insert, so preparing the insert statement for every record is very costly when it comes to processing hundreds and thousands of files as they come in. At the moment, the fastest transactions for my larger script was 3.5 rows/s, but it all averages about 2 rows/s. This larger script _needs_ to be quicker.

What I need to know is how to correct this long error problem which developers here in the past have gotten before. Our DBA tried some tests and research but didn't find a solution... is it likely a DBI/DBD compile-time problem or a database config? I know nothing about databases beyond basic table concepts and SQL syntax, but I'd like to help our DBA get this problem fixed. :/

Thank you,
Derek MacDonald
derekmd_at_hotmail.com Received on Mon Oct 22 2001 - 09:56:03 CDT