Here's my problem. I have an oracle database several of the tables have a "raw" field. When I use perl to manipulate these fields, I don't get the expected results.

What I'm trying to do is select all the ID's (a raw field) from one table and loop through these values. A 2nd query is run on another table looking for the same ID value. If more than one row is found, then an UPDATE has to be done to the first table to set a text field equal to a text string. One field in the 2nd table also has a count so in my code if that value > 0 then I do the update. The text string is basically a concatenation of another field in the 2nd table across all the rows that match the ID.

I would love to come up with a better way of doing this. But I'll just be happy if I can figure out how to work with these raw fields.

Thanks in advance

Here's the code I've attempted:

#!/usr/bin/perl

use strict;
use DBI;

my $dbh_o = DBI->connect ('dbi:Oracle:devdb, 'user', 'pass') or die("C
+annot connect");

my $SQL = "SELECT ID FROM THE_TABLE"; # ID is the primary key for THE_
+TABLE and is a RAW field

my $sth_o = $dbh_o->prepare( $SQL );
$sth_o->execute();

my $object_id;

$sth_o->bind_columns( undef, \$object_id); 

while ($sth_o->fetch()) {

    my $SQL2 = "SELECT THE_NUM, THE_VALUE FROM THE_OTHER_TABLE WHERE P
+ROPERTY_ID = 'biglongstring' AND PARENT_ID = '" . $object_id . "'"; #
+ PROPERTY_ID and PARENT_ID are both raw fields.  I only need rows whe
+re PROPERTY_ID is a certain value.  If the data was entered correctly
+ THE_NUM should indicate the order the item was added to the record (
+i.e. the first item added will be 0, the 2nd item added will be 1, et
+c)

    my $sth_o2 = $dbh_o->prepare($SQL2);
    $sth_o2->execute();

    my($num, $val);
    $sth_o2->bind_columns(undef, \$num, \$val);
    
    while($sth_o2->fetch()) {

       if ($num > 0) {
          # omitting stuff that produces the value $text;

          my $SQL3 = "UPDATE THE_TABLE SET THE_TEXT_FIELD='" . $text .
+ "' WHERE OBJECT_ID = '$object_id';
          # omitting code to run this query.


       }

    }

}
[download]