Inserting CLOBS into Oracle using perl
am 21.06.2006 16:57:37 von simpsonjohnjHi 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 (
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++;
}
}