Inserting CLOBS into Oracle using perl

[simpsonjohnj@gmail.com]

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++;
}
}

[Andy Hassall]

On 21 Jun 2006 07:57:37 -0700, [email]simpsonjohnj@gmail.com[/email] wrote:

>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.

> $sth->bind_param(38, @array[37]);
> $sth->bind_param(49, @array[38]);

^^ spot the typo

> $sth->bind_param(40, @array[39]);

--
Andy Hassall :: [email]andy@andyh.co.uk[/email] :: [url]http://www.andyh.co.uk[/url]
[url]http://www.andyhsoftware.co.uk/space[/url] :: disk and FTP usage analysis tool