Thanks Ron, for pointing me to right link. It's resolved now. I was hitting 4000 bytes limit, eventhough the string length is around 1390 characters due to my NLS_LANG setting(WE8..) and Oracle8's interpretation of anything > 4000 as LONG. Now i'm truncating the string to 1300 characters and it worked!
modified code:
$tgt_key_col_and_val_str = substr( $tgt_key_col_and_val_str,0,1300 );
Ravi
-------- Original Message -------- Subject: RE: ORA-01461: can bind a LONG value only.. while insert Date: Thu, 30 Sep 2004 08:51:30 -0600 From: Reidy, Ron <[EMAIL PROTECTED]> To: [EMAIL PROTECTED]
1. Got to http://metalink.oracle.com 2. Log in 3. Click the "Advanced" button at the top center of the page 4. Enter the Note # in the field labeled "Document ID" 5. Click "Submit"
----------------- Ron Reidy Lead DBA Array BioPharma, Inc.
-----Original Message----- From: Ravi Kongara [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 29, 2004 4:53 PM To: Reidy, Ron Subject: Re: ORA-01461: can bind a LONG value only.. while insert
Can you tell me how can i access this link.
Thanks, Ravi
Reidy, Ron wrote:
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.
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.
