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