Re: ORA-01461 with DBI when using placeholders

Derek MacDonald wrote:
>
> 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

This looks like a DBD issue, however it is hard to tell. Below are my suggestions.

1. Did you test statements where the values to be inserted are hard-coded into the statement? Does this fail the same way? My guess of the problem is the array containing the values is not being interpreted correctly. To verify this, step through the debugger and validate the bind_param and execute methods.
2. While the DBI/DBD interface supports the use of '?' as place holders, this is not the "Oracle way". My suggesttion is to use placeholders as defined in the Oracle Pro*C docs and mentioned briefly in the DBI and DBD::Oracle POD docs.

Also, I suggest that you use eval {} blocks around DBI/DBD code and croak/die to trap errors.

As for speed, you are defaulting AutoCommit to 1, which will commit after each row is inserted - very time consuming. If speed is your biggest concern, you need to use SQL*Loader. This will be far faster than any kind of Perl DBD or Pro*C array processing strategy you will be able to devise. If you are adverse to using this, investigate Oracle::OCI and roll your own direct loader.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.