Re: ORA-01461 with DBI when using placeholders

Ron Reidy <rereidy_at_indra.com> wrote in message news:<3BD44E52.B74819FA_at_indra.com>...
> 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.

Thanks, but the problem wasn't with the code. For now, we believe the problem was a change to the French character set on the development database (8 bit instead of 7 bit characters). Production is still using 7 bit English and placeholders work fine on there! :)

As far as the eval/do/AutoCommit comments.. those were just small examples I threw together; of course the actual script I wrote had error checking everywhere. Also, presently each row has 34 columns so it's slow going even with placeholders. :/ I've also tested commiting for every insert or commiting just once for the entire script and there isn't much difference at all... for a test of inserting a couple thousand rows which took ~40 minutes, commiting only once only saved about 2 minutes on average.

Derek MacDonald
derekmd_at_hotmail.com Received on Tue Oct 30 2001 - 11:01:27 CST