Hi fellow monks,

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]

In reply to Working with Oracle Raw fields by xorl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.