Re: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
perl-5.8.7, DBI-1.48, Oracle-9.2.0.4. I'm an idiot: DBD::Oracle-1.16 -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
Steve,
See the DBD::Oracle docs, section "Handling LOBs". Basically, you need to
insert a EMPTY_CLOB(), return the locator, and then call ora_write_lob() to
push the data into the locator.
-
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-Original Message-
From: Steven Lembark [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 13, 2005 11:00 AM
To: dbi-users@perl.org
Subject: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a
CLOB
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 '-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;
[ @[EMAIL PROTECTED] ]
}
}
Which works for tables without CLOB's.
Inserting into this table using the SQL below gives
me an oracle error:
SQL> desc entry_comment;
Name Null?Type
-
ENTRY_COMMENT_ID NOT NULL NUMBER(10)
COMMENTS CLOB
ENTRY_ID NUMBER(10)
CREATORVARCHAR2(16)
CREATE_DATEDATE
UPDATORVARCHAR2(16)
UPDATE_DATEDATE
DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for
insert into a LONG column (DBD ERROR: OCIStmtExecute) [for Statement
"insert into entry_comment
(
entry_comment_id,
comments,
creator,
create_date,
updator,
update_date,
entry_id
)
values
(
entry_comment_seq.nextval, -- entry_comment_id,
?, -- comments,
?, -- creator,
?, -- create_date,
?, -- updator,
?, -- update_date,
( -- entry_id
select entry_id
fromentry
where entry_accno = ?
)
)
returning
entry_comment_id into ?" with ParamValues: :p5='2005.06.16-09:16:41',
:p3='2005.06.16-00:00:00', :p6=
RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
-- "Reidy, Ron" <[EMAIL PROTECTED]> Steve, See the DBD::Oracle docs, section "Handling LOBs". Basically, you need to insert a EMPTY_CLOB(), return the locator, and then call ora_write_lob() to push the data into the locator. That's what I'm trying to avoid. The problem is that doing so requires pre-checking every incomming SQL to see if there is a CLOB in it. If there were some internal Oracle function for "insert the following string as a CLOB" then I could avoid the issue in the SQL rather than dealing with it in the closure. -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
-- "Reidy, Ron" <[EMAIL PROTECTED]> Steve, See the DBD::Oracle docs, section "Handling LOBs". Basically, you need to insert a EMPTY_CLOB(), return the locator, and then call ora_write_lob() to push the data into the locator. This is a generic query handler. In order to do that I have to investigate the data type of each '?' in the input sql, determine if it is a LOB, and handle that field specially. If there were something like convert for a clob then I could push the knowlege of clob-ness up into the SQL. This code is also used for other databases (e.g., MySQL, which handles text fields more gracefully). -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
Well, I am not sure how we can change the Oracle way of doing this and I know of no DB functions that will or could perform this task easily. Maybe you will need to sub class DBD::Oracle and create your own methods for handling LOBs to allow for this transparency - Ron Reidy Lead DBA Array BioPharma, Inc. -Original Message- From: Steven Lembark [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 13, 2005 12:24 PM To: Reidy, Ron; dbi-users@perl.org Subject: RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB -- "Reidy, Ron" <[EMAIL PROTECTED]> > Steve, > > See the DBD::Oracle docs, section "Handling LOBs". Basically, you need to > insert a EMPTY_CLOB(), return the locator, and then call ora_write_lob() > to push the data into the locator. This is a generic query handler. In order to do that I have to investigate the data type of each '?' in the input sql, determine if it is a LOB, and handle that field specially. If there were something like convert for a clob then I could push the knowlege of clob-ness up into the SQL. This code is also used for other databases (e.g., MySQL, which handles text fields more gracefully). -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
Steve,
There is an Oracle sql function TO_LOB which can be used to convert the input
to a lob You might also check out the Oracle 9i Application developer
documentation which has an excellent chapter on working with OBs You can
find that documentation at otn.oracle.com... you may be asked to create an
account, but it is well worth it Not sure if you have the ability to do so,
but upgrading to 9206 would be a good idea. Check out the bugs fixed in 9205
and 9206 against the error you are getting
catharine
-Original Message-
From: Steven Lembark [mailto:[EMAIL PROTECTED]
Sent: Wed 7/13/2005 10:00 AM
To: dbi-users@perl.org
Subject: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
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 '-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;
[ @[EMAIL PROTECTED] ]
}
}
Which works for tables without CLOB's.
Inserting into this table using the SQL below gives
me an oracle error:
SQL> desc entry_comment;
Name Null?Type
-
ENTRY_COMMENT_ID NOT NULL NUMBER(10)
COMMENTS CLOB
ENTRY_ID NUMBER(10)
CREATORVARCHAR2(16)
CREATE_DATEDATE
UPDATORVARCHAR2(16)
UPDATE_DATEDATE
DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for
insert into a LONG column (DBD ERROR: OCIStmtExecute) [for Statement
"insert into entry_comment
(
entry_comment_id,
comments,
creator,
create_date,
updator,
update_date,
entry_id
)
values
(
entry_comment_seq.nextval, -- entry_comment_id,
?, -- comments,
?, -- creator,
?, -- create_date,
?, -- updator,
?,
RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
-- "Drozdowski, Catharine" <[EMAIL PROTECTED]> Steve, There is an Oracle sql function TO_LOB which can be used to convert the input to a lob You might also check out the Oracle 9i Application Thanks. I'll check it out in 9.2.0.4. If it works that will be exactly what I need. -- Steven Lembark 85-09 90th Street Workhorse ComputingWoodhaven, NY 11421 [EMAIL PROTECTED] 1 888 359 3508
Re: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
On 2005-07-13 13:00:28 -0400, Steven Lembark wrote:
> 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 );
How generic has does the query handler have to be? Can you get rid of
the bind_param_inout? If you don't bind the parameters explicitely and
just pass them as parameters to execute, DBD::Oracle will perform the
necessary magic for you.
hp
--
_ | Peter J. Holzer \Beta means "we're down to fixing misspelled comments in
|_|_) | Sysadmin WSR \the source, and you might run into a memory leak if
| | | [EMAIL PROTECTED] \you enable embedded haskell as a loadable module
and
__/ | http://www.hjp.at/ \write your plugins upside-down in lisp". [EMAIL
PROTECTED]
pgp2mMv0ykRjr.pgp
Description: PGP signature
