DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB

• From: lembark@xxxxxxxxxxx (Steven Lembark)
• Date: Wed, 13 Jul 2005 13:00:28 -0400

Summary
=======

perl-5.8.7, DBI-1.48, Oracle-9.2.0.4.

This is proably an Oracle question in the long
run: How to frame a query so that bind_param_inout
can pass strings as varchar's and have them inserted
into a clob. It'd be nicer if there were a way to
handle this in Perl however...

In theory I could write something to query the
data dictionary for LOB fields and adjust the
binding type used with bind_param_inout; in
reality I have a short time to finish this and
need a quicker fix.

The default handling of varchar bumping into a
CLOB w/in Oracle is probably what is killing me...
This code has worked for any number of tables,
so the CLOB issue is probably what's biting me.

Any help would be appreciated.

======
Detail
======

Google on qw( perl DBI bind_param_inout ORA-01461 )
in various combinations gets me nowhere. The POD
for bind_param_inout refrences bind_parm, which
notes that:

  Perl only has string and number scalar data types. All
  database types that aren't numbers are bound as
  strings and must be in a format the database will
  understand except where the bind_param() TYPE
  attribute specifies a type that implies a particular
  format. For example, given:

    $sth->bind_param(1, $value, SQL_DATETIME);

  the driver should expect $value to be in the ODBC
  standard SQL_DATETIME format, which is 'YYYY-MM-DD
  HH:MM:SS'. Similarly for SQL_DATE, SQL_TIME etc.

  As an alternative to specifying the data type in the
  "bind_param" call, you can let the driver pass the
  value as the default type ("VARCHAR").  You can then
  use an SQL function to convert the type within the
  statement.  For example:

    INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?))

  The "CONVERT" function used here is just an example.
  The actual function and syntax will vary between dif-
  ferent databases and is non-portable.

I cannot find a reference to a "convert varchar
to clob" function for Oracle (but, then, my
doc's on that are limited).

The default handling of varchar bumping into a
CLOB w/in Oracle is probably what is killing me...
This code has worked for any number of tables,
so the CLOB issue is probably what's biting me.

Aside: Searching the POD for "Placeholders and
Bind Values" gets me plenty of referencing lines
but no title with that name.

The code that blows up is a generic query handler.
The code here returns a closure bound for a
specific query. In this case,  for an
insert ... returning query:

   ...

   my @bound = ( 1..$total );

   my @copy   = ( 0..$countz{insert}-1 );
   my @return = ( $countz{insert} .. $#bound );

   $sth->bind_param_inout( $_+1, \$bound[$_], 4096 )
       for( 0 .. $#bound );
   sub
   {
       @bound[ @copy ] = @_[ @copy ];

       # caller gets back an anon-array
       # of the return values (a la
       # fetchrow_arrayref) or undef if
       # the call fails.

       eval
       {
           $sth->execute;

           [ @bound[@return] ]
       }
   }

Which works for tables without CLOB's.

Inserting into this table using the SQL below gives
me an oracle error:

--
Steven Lembark                                       85-09 90th Street
Workhorse Computing                                Woodhaven, NY 11421
lembark@xxxxxxxxxxx                                     1 888 359 3508
.

• Follow-Ups:
  • Re: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    • From: Peter J. Holzer
  • Re: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    • From: Steven Lembark

• Prev by Date: RE: selectall_hashref() when key field not unique
• Next by Date: Re: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
• Previous by thread: RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
• Next by thread: Re: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
• Index(es):
  • Date
  • Thread

Relevant Pages RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ... (perl.dbi.users) RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB ... into a clob. ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ... 'Activins are dimeric growth and differentiation factors which...', ... (perl.dbi.users) Re: CLOB SUPPORTED in Delphi 5/7 ADO ? ... Oracle CLOBs can be problematic using Delphi and ADO. ... when inserting data by specifying the datatype as 'ftMemo', ... Have writen the query ... >> i need to query a table which contains a clob field. ... (borland.public.delphi.database.ado) Oracle recent version improves bind variable performance? ... In another thread I was reporting how a query I do with bind variables ... performs worse than an equivalent query done via strings.. ... I'm running Oracle 10.2.0.1.0 on Windows platform. ... (comp.databases.oracle.misc) Re: Views - performance ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ... (comp.databases.oracle.server)

• Security
• UNIX
• Linux
• Coding
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint