Inserting CLOBS into Oracle using perl
- From: simpsonjohnj@xxxxxxxxx
- Date: 21 Jun 2006 07:57:37 -0700
Hi everyone. I trying to write a perl script that will insert data
from a text file into Oracle. The database contains a few clob fields
which are giving me errors. I am not too familiar with insertingn LOB
fields, but from what I have seen, I need to use ora_types for when
binding the fields that are clobs. When I run the script below, I get
the ORA-01008 error that says that not all parameters are bound. I
have tried a number of different methods and I continue to get the same
error. When I run the script and skip the CLOB fields, it works fine.
Can anyone help me with this? Thanks in adavance.
use DBD::Oracle qw(:ora_types);
my @array = ();
my $x;
my $sth;
my $y;
my $input = "dms_HPLPATHDATA_FORMATTED.sql";
open (IN, $input) or die "Cannot open $input\n";
#open db connection
my $dbh = DBI->connect("dbi:Oracle:seerdw", "schema", "password")
or die "Can't make connection to database: $DBI::errstr\n";
$sth = $dbh->prepare("INSERT INTO hplpathdata (CASESTATUS, RESTYPE,
HTR_LName, HTR_FName, HTR_MName, Patient, SSN, BirthDate, Sex, Floor,
Accession, AccessionDate, ChartID, PreAddress, Address, City, State,
ZIP, PhoneNumber, MD_LName, MD_FName, MD_MName, MD, phy_street,
phy_city, phy_state, phy_zip, MDPhone, MDPrimary, clinhx, gross, micro,
final, dxcom, addendum, AdditionalMergedData, CasefindingCmment,
Site01, Site02, Site03, Site04, Site05, Site06, Site07, Site08, Site09,
Site10, DBSearchResults, DBStatusLastLinkage, PAT_ID, DISEASE_SEQ,
DOCUMENT_ID, MECSearchResults, MECStatusLastLinkage, MECNumber,
EntryNumber, EntryDate, CaseManagementNumber, CsfPossibleLastLinkage,
CsfPossibleBest, IPPossibleLastLinkage, IPPossibleBest,
DBPossibleLastLinkage, DBPossibleBest, SpecialStudySearch,
SpecialStudySite, SpecialStudyStatus, SpecialStudyDatePrinted,
RecordGroup, SnomedCode, FinalDx, PathologistComments, LoadFlag,
CSFTransferFlag, DoNotImport, TissueLinkStatus, TissueBlockCount)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?)");
$x = 0;
$y = 0;
while (<IN>) {
chomp($_);
if ($_=~/^INSERT INTO/) {
$sth->bind_param(1, @array[0]);
$sth->bind_param(2, @array[1]);
$sth->bind_param(3, @array[2]);
$sth->bind_param(4, @array[3]);
$sth->bind_param(5, @array[4]);
$sth->bind_param(6, @array[5]);
$sth->bind_param(7, @array[6]);
$sth->bind_param(8, @array[7]);
$sth->bind_param(9, @array[8]);
$sth->bind_param(10, @array[9]);
$sth->bind_param(11, @array[10]);
$sth->bind_param(12, @array[11]);
$sth->bind_param(13, @array[12]);
$sth->bind_param(14, @array[13]);
$sth->bind_param(15, @array[14]);
$sth->bind_param(16, @array[15]);
$sth->bind_param(17, @array[16]);
$sth->bind_param(18, @array[17]);
$sth->bind_param(19, @array[18]);
$sth->bind_param(20, @array[19]);
$sth->bind_param(21, @array[20]);
$sth->bind_param(22, @array[21]);
$sth->bind_param(23, @array[22]);
$sth->bind_param(24, @array[23]);
$sth->bind_param(25, @array[24]);
$sth->bind_param(26, @array[25]);
$sth->bind_param(27, @array[26]);
$sth->bind_param(28, @array[27]);
$sth->bind_param(29, @array[28]);
$sth->bind_param(30, @array[29]);
$sth->bind_param(31, @array[30], {ora_type => ORA_CLOB, ora_field =>
'gross'}); #, SQL_LONGVARCHAR);
$sth->bind_param(32, @array[31], {ora_type => ORA_CLOB, ora_field =>
'micro'}); #, SQL_LONGVARCHAR);
$sth->bind_param(33, @array[32], {ora_type => ORA_CLOB, ora_field =>
'final'}); #, SQL_LONGVARCHAR);
$sth->bind_param(34, @array[33]);
$sth->bind_param(35, @array[34], {ora_type => ORA_CLOB, ora_field =>
'addendum'}); #, SQL_LONGVARCHAR);
$sth->bind_param(36, @array[35]);
$sth->bind_param(37, @array[36]);
$sth->bind_param(38, @array[37]);
$sth->bind_param(49, @array[38]);
$sth->bind_param(40, @array[39]);
$sth->bind_param(41, @array[40]);
$sth->bind_param(42, @array[41]);
$sth->bind_param(43, @array[42]);
$sth->bind_param(44, @array[43]);
$sth->bind_param(45, @array[44]);
$sth->bind_param(46, @array[45]);
$sth->bind_param(47, @array[46]);
$sth->bind_param(48, @array[47]);
$sth->bind_param(49, @array[48]);
$sth->bind_param(50, @array[49]);
$sth->bind_param(51, @array[50]);
$sth->bind_param(52, @array[51]);
$sth->bind_param(53, @array[52]);
$sth->bind_param(54, @array[53]);
$sth->bind_param(55, @array[54]);
$sth->bind_param(56, @array[55]);
$sth->bind_param(57, @array[56]);
$sth->bind_param(58, @array[57]);
$sth->bind_param(59, @array[58]);
$sth->bind_param(60, @array[59]);
$sth->bind_param(61, @array[60]);
$sth->bind_param(62, @array[61]);
$sth->bind_param(63, @array[62]);
$sth->bind_param(64, @array[63]);
$sth->bind_param(65, @array[64]);
$sth->bind_param(66, @array[65]);
$sth->bind_param(67, @array[66]);
$sth->bind_param(68, @array[67]);
$sth->bind_param(69, @array[68]);
$sth->bind_param(70, @array[69]);
$sth->bind_param(71, @array[70], {ora_type => ORA_CLOB, ora_field =>
'finaldx'}); #, SQL_LONGVARCHAR);
$sth->bind_param(72, @array[71]);
$sth->bind_param(73, @array[72]);
$sth->bind_param(74, @array[73]);
$sth->bind_param(75, @array[74]);
$sth->bind_param(76, @array[75]);
$sth->bind_param(77, @array[76]);
$sth->execute();
my @array = ();
$x = 0;
}
else {
@array[$x] = $_;
$x++;
}
}
.
- Follow-Ups:
- Re: Inserting CLOBS into Oracle using perl
- From: Andy Hassall
- Re: Inserting CLOBS into Oracle using perl
- Prev by Date: Lemur parsing module
- Next by Date: Re: ANNOUNCE: WWW::YouTube
- Previous by thread: Lemur parsing module
- Next by thread: Re: Inserting CLOBS into Oracle using perl
- Index(es):
Relevant Pages
|
