See metalink note 241358.1 ----------------- Ron Reidy Lead DBA Array BioPharma, Inc.
-----Original Message----- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 29, 2004 4:14 PM To: DBI-Users Subject: ORA-01461: can bind a LONG value only.. while insert Folks, I see following error popping up while inserting into a non-LONG table. "DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR: OCIStmtExecute)". The said table doesn't have any LONG columns. Did anyone face this kind of error. Can you suggest me what should be done to supress this error. Versions: DBI/DBD::Oracle = 1.42/1.14 SQL> desc recon_detail Name Null? Type ----------------------------------------- SOURCE_ID NOT NULL NUMBER TABLE_NAME NOT NULL VARCHAR2(30) FILE_NAME VARCHAR2(50) TYPE NOT NULL VARCHAR2(15) SCOPE VARCHAR2(6) RUN_ID NOT NULL NUMBER SRC_KEY VARCHAR2(4000) TGT_KEY VARCHAR2(4000) MISMATCHED_COLUMNS VARCHAR2(4000) FILE_LINE_NO NUMBER UPDATE_DATE DATE --------- begin code block ------------ #!/usr/perl5/bin/perl # Import packages use Getopt::Long; use DBI; # Import user defined packages use ReconGenModule; use ReconDbPkcolumnTest; use DBD::Oracle qw(:ora_types); eval { $dbh = DBI->connect( "dbi:Oracle:$host_db", $host_user, $host_passwd, { AutoCommit => 1, RaiseError => 1, PrintError => 0 } ); }; if ( $@ ) { warn "Unable to connect to the host database in main() routine\n"; exit 1; } sub insert_allcol_mismatch_details { my $sth_temp = $dbh->prepare( "insert into recon_detail ( source_id, table_name, type, scope, run_id, src_key, tgt_key, update_date) values ( $source_id,?,?,?,$run_id,?,?,sysdate )" ); $sth_temp->trace(3); while ( my $key = shift @tgt_only ) { my $src_key_col_and_val_str; my $tgt_key_col_and_val_str; my @tgt_key_values = split /\|/,$key; for my $k ( 0..$#tgt_key_values ) { $tgt_key_col_and_val_str = $tgt_key_col_and_val_str.$tgt_key_columns[$k]."=".$tgt_key_values[$k].$delim; } chop( $tgt_key_col_and_val_str); # Cut off string so that it doesn't exceed maximum byte size(4000) of Oracle's VARCHAR2 column # Allow some cushion for multi-byte characters $tgt_key_col_and_val_str = substr( $tgt_key_col_and_val_str,0,3500 ); $tgt_key_col_and_val_str =~s/[[:cntrl:]]//g; # remove control characters $src_key_col_and_val_str = "SRC ROW MISSING"; $sth_temp->bind_param( 1, $ods_table_name ); $sth_temp->bind_param( 2, $recon_type ); $sth_temp->bind_param( 3, $recon_scope ); $sth_temp->bind_param( 4, $src_key_col_and_val_str, { ora_type => ORA_VARCHAR2 } ); $sth_temp->bind_param( 5, $tgt_key_col_and_val_str, { ora_type => ORA_VARCHAR2 } ); $sth_temp->execute(); $tgt_only_count++; } $sth_temp->finish(); } } ----- end code block ----- Trace output near error point ----------------------- -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x489400)~0x44fd9c 1 'VALID_POS_TRANSACTION') bind :p1 <== 'VALID_POS_TRANSACTION' (type 0) bind :p1 <== 'VALID_POS_TRANSACTION' (size 21/22/0, ptype 4, otype 1) bind :p1 <== 'VALID_POS_TRANSACTION' (size 21/21, otype 1, indp 0, at_exec 1) bind :p1 done with ftype 1 <- bind_param= 1 at ReconDbColbycolTest.pm line 994 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x489400)~0x44fd9c 2 'PKCOLUMN') bind :p2 <== 'PKCOLUMN' (type 0) bind :p2 <== 'PKCOLUMN' (size 8/9/0, ptype 4, otype 1) bind :p2 <== 'PKCOLUMN' (size 8/8, otype 1, indp 0, at_exec 1) bind :p2 done with ftype 1 <- bind_param= 1 at ReconDbColbycolTest.pm line 995 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x489400)~0x44fd9c 3 'NET') bind :p3 <== 'NET' (type 0) bind :p3 <== 'NET' (size 3/4/0, ptype 4, otype 1) bind :p3 <== 'NET' (size 3/3, otype 1, indp 0, at_exec 1) bind :p3 done with ftype 1 <- bind_param= 1 at ReconDbColbycolTest.pm line 996 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x489400)~0x44fd9c 4 'SRC ROW MISSING' HASH(0xa23864)) bind :p4 <== 'SRC ROW MISSING' (type 0, attribs: HASH(0xa23864)) bind :p4 <== 'SRC ROW MISSING' (size 15/1469/0, ptype 4, otype 1) bind :p4 <== 'SRC ROW MISSING' (size 15/1468, otype 1, indp 0, at_exec 1) bind :p4 done with ftype 1 <- bind_param= 1 at ReconDbColbycolTest.pm line 997 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 -> bind_param for DBD::Oracle::st (DBI::st=HASH(0x489400)~0x44fd9c 5 'BATCH_ID=1574;POS_TRAN_ID=505362;PROGRAM_TYPE=PSR;TRAN_SOURCE=POSLOAD;PARENT_AGREE_CODE=GSO-IT-040001-SYP;PARENT_AGREE_NAME=ATLANTICA SISTEMI SRL;AGREE_CODE=;AGREE_TYPE=;RESELLER_NAME=;MKT_PART_NUMBER=XTA-3000-2URK-19U;MKT_PART_DESC=SE3000 2U Univ Rk Rail Kit;SIC=M;SERIAL_NUMBER=;SHIP_DATE=07-30-2004:00:00:00;QUANTITY=2;NET_PRICE=245;LIST_PRICE=350;PRICE_CLASS=P;TOTAL_PRICE=490;TRAN_CURRENCY=...' HASH(0xa23864)) bind :p5 <== 'BATCH_ID=1574;POS_TRAN_ID=505362;PROGRAM_TYPE=PSR;TRAN_SOURCE=POSLOAD;PARENT_AGREE_CODE=GSO-IT-040001-SYP;PARENT_AGREE_NAME=ATLANTICA SISTEMI SRL;AGREE_CODE=;AGREE_TYPE=;RESELLER_NAME=;MKT_PART_NUMBER=XTA-3000-2URK-19U;MKT_PART_DESC=SE3000 2U Univ Rk Rail Kit;SIC=M;SERIAL_NUMBER=;SHIP_DATE=07-30-2004:00:00:00;QUANTITY=2;NET_PRICE=245;LIST_PRICE=350;PRICE_CLASS=P;TOTAL_PRICE=490;TRAN_CURRENCY=...' (type 0, attribs: HASH(0xa23864)) bind :p5 <== 'BATCH_ID=1574;POS_TRAN_ID=505362;PROGRAM_TYPE=PSR;TRAN_SOURCE=POSLOAD;PARENT_AGREE_CODE=GSO-IT-040001-SYP;PARENT_AGREE_NAME=ATLANTICA SISTEMI SRL;AGREE_CODE=;AGREE_TYPE=;RESELLER_NAME=;MKT_PART_NUMBER=XTA-3000-2URK-19U;MKT_PART_DESC=SE3000 2U Univ Rk Rail Kit;SIC=M;SERIAL_NUMBER=;SHIP_DATE=07-30-2004:00:00:00;QUANTITY=2;NET_PRICE=245;LIST_PRICE=350;PRICE_CLASS=P;TOTAL_PRICE=490;TRAN_CURRENCY=...' (size 1394/1395/0, ptype 4, otype 1) bind :p5 <== 'BATCH_ID=1574;POS_TRAN_ID=505362;PROGRAM_TYPE=PSR;TRAN_SOURCE=POSLOAD;PARENT_AGREE_CODE=GSO-IT-040001-SYP;PARENT_AGREE_NAME=ATLANTICA SISTEMI SRL;AGREE_CODE=;AGREE_TYPE=;RESELLER_NAME=;MKT_PART_NUMBER=XTA-3000-2URK-19U;MKT_PART_DESC=SE3000 2U Univ Rk Rail Kit;SIC=M;SERIAL_NUMBER=;SHIP_DATE=07-30-2004:00:00:00;QUANTITY=2;NET_PRICE=245;LIST_PRICE=350;PRICE_CLASS=P;TOTAL_PRICE=490;TRAN_CURRENCY=EUR;U' (size 1394/1394, otype 1, indp 0, at_exec 1) bind :p5 done with ftype 1 <- bind_param= 1 at ReconDbColbycolTest.pm line 998 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 -> execute for DBD::Oracle::st (DBI::st=HASH(0x489400)~0x44fd9c) dbd_st_execute INSERT (out0, lob0)... in ':p1' [0,0]: len 21, ind 0 in ':p2' [0,0]: len 8, ind 0 in ':p3' [0,0]: len 3, ind 0 in ':p4' [0,0]: len 15, ind 0 in ':p5' [0,0]: len 1394, ind 0 !! ERROR: 1461 'ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR: OCIStmtExecute)' (err#0) <- execute= undef at ReconDbColbycolTest.pm line 1000 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 1 -> FETCH for DBD::Oracle::st (DBI::st=HASH(0x44fd9c)~INNER 'ParamValues') ERROR: 1461 'ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR: OCIStmtExecute)' (err#0) 1 <- FETCH= HASH(0x1cc9e50)5keys at ReconDbColbycolTest.pm line 1000 via /apps/ods/scripts/ReconDbColbycolTest.pm line 241 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 recon_detail ( source_id, table_name, type, scope, run_id, src_key, tgt_key, update_date) values ( 504,?,?,?,82210,?,?,sysdate )" with ParamValues: :p4='SRC ROW MISSING', :p5='BATCH_ID=1574;POS_TRAN_ID=505362;PROGRAM_TYPE=PSR;TRAN_SOURCE=POSLOAD;PARENT_AGREE_CODE=GSO-IT-040001-SYP;PARENT_AGREE_NAME=ATLANTICA SISTEMI SRL;AGREE_CODE=;AGREE_TYPE=;RESELLER_NAME=;MKT_PART_NUMBER=XTA-3000-2URK-19U;MKT_PART_DESC=SE3000 2U Univ Rk Rail Kit;SIC=M;SERIAL_NUMBER=;SHIP_DATE=07-30-2004:00:00:00;QUANTITY=2;NET_PRICE=245;LIST_PRICE=350;PRICE_CLASS=P;TOTAL_PRICE=490;TRAN_CURRENCY=...', :p1='VALID_POS_TRANSACTION', :p2='PKCOLUMN', :p3='NET'] at /apps/ods/scripts/ReconDbColbycolTest.pm line 1000. -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x44fd9c)~INNER) ERROR: 1461 'ORA-01461: can bind a LONG value only for insert into a LONG column (DBD ERROR: OCIStmtExecute)' (err#0) <- DESTROY= undef ------- end trace output --------- Thanks, Ravi 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.
