ko has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to do some simple inserts into Oracle 8i using DBI and its treating all the data as long if I use placeholders (ok if I don't). I've tried using bind_param with TYPE arguments but that hasn't helped. Any thoughts?
ko

Replies are listed 'Best First'.
Re: DBI and Oracle 8i
by grinder (Bishop) on Mar 09, 2001 at 22:24 UTC

    Rather than using bind(), I've found that just passing an array to execute(), that corresponds to the order of the placeholder, does The Right Thing.

    Have you tried doing that?

    Are you using a non-US locale?

[reply]
Re: DBI and Oracle 8i
by CiceroLove (Monk) on Mar 09, 2001 at 20:06 UTC
    Next time you should post some code so that we can see what you are actually doing. But I know I had a lot of trouble with Oracle and the use of quotation marks for different types of data when trying to do updates or inserts. Numbers couldn't have quotations marks (even if it was a variable) but strings had to have them. It was messy at best.

    CiceroLove
    Fates! We will know your pleasures: That we shall die, we know; 'Tis but the time, and drawing days out, that men stand upon. - Act III,I, Julius Caesar
[reply]
      Thanks. Here's the code I'm trying..
      my $sth = $dbh->prepare("INSERT INTO MY_TABLE(KEY,DATA) values (?,?)")
+;
my $key = 'key_worked';
my $data = 'data_worked';

$sth->bind_param(1, $key, { TYPE=>DBI::SQL_VARCHAR } );
$sth->bind_param(2, $data, { TYPE=>DBI::SQL_VARCHAR } );
$sth->execute;
$sth->finish;

      [download]
      I'm thinking it may be a driver problem, I'm using dbd::oracle::version=1.03.
      The error I'm getting is: DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for insert into a LONG column
      cheers ko
[reply]
[d/l]
        Try:
        use DBD::Oracle qw(:ora_types);

        and then you can bind $data with:
        $sth->bind_param( 2, $data, { ora_types => 'ORA_LONG'} )

        which'll almost certainly work.

        Hope you have more fun than I did

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom