Capturing output from DBI into datastructures 1

[sapatos]

Little perl problem,

I am trying to capture the following type of output from an oracle database.

IRCNFPP2, TRIGGER, POLLINGASSIGNQUEUEEVENT_UPDERQ
IRCNFPP2, TRIGGER, POLLINGASSIGNQUEUEEVENT_UPDERQ
IRCNFPP2, TRIGGER, NOTESADDEDEVENT_UPDCEQ
IRCNFPP2, TRIGGER, MESSAGESTATUSEVENT_UPDCEQ
IRCNFPP2, TABLE, CONFMASTERAGREEMENT
IRCNFPP2, TABLE, CONFIRMATIONSTRADEBUILDER_A
IRCNFPP2, TABLE, CONFIRMATIONSTRADEBUILDER
IRCNFPP2, TABLE, CONFIRMATIONSTATUSEVENT_A
IRCNFPP2, SEQUENCE, INCOMINGCONFSTLUSQ
IRCNFPP2, SEQUENCE, INCOMINGCONFSTATUSSQ
IRCNFPP2, SEQUENCE, INCOMINGCONFIRMATIONSQ
IRCNFPP2, SEQUENCE, INCOMINGCONFATTRIBUTESSQ

etc etc (but much more of..), into the following type of perl database structure.

%instance->object_type->[Array of object names]

i.e: IRCNFPP2->Trigger-> [POLLINGASSIGNQUEUEEVENT_UPDERQ, POLLINGASSIGNQUEUEEVENT_UPDERQ, NOTESADDEDEVENT_UPDCEQ, MESSAGESTATUSEVENT_UPDCEQ ]
IRCNFPP2->TABLE->

the code I have so far is:

*********************************

my ($object_type, $object_name);


$sql = qq(select object_type,object_name from user_objects where object_type in (
'DATABASE LINK',
'FUNCTION',
'INDEX',
'PACKAGE',
'PACKAGE BODY',
'PROCEDURE',
'SEQUENCE',
'SYNONYM',
'TABLE',
'TRIGGER'
)
order by 1
);

$sth = $dbh->prepare($sql);

$sth->execute();

my $type;
my @name;

while ( ($object_type, $object_name) = $sth->fetchrow_array ) {

#@{$XmlStructureLocal{$WorkingInstance}{$object_type}} = $object_name;

print "$WorkingInstance, $object_type, $object_name\n";


#$::CmpInstance->{$instance}->{$name} = $value;

}


*******************************************

I'm not sure how to get this structure set up, or to access it. Been looking at it for a while and would appreciate a pointer if anyone has time.

 

[rharsh]

I will probably not be any help with the DB portion of this, but it looks like you're doing fine with that. As far as setting up the data structure, this should help:

my %instances;

while (<DATA>) {
    chomp;
    my ($instance, $type, $name) = split /\s*,\s*/, $_;
    push @{$instances{$instance}{$type}}, $name;
}

foreach my $instance (sort keys %instances) {
    print $instance, "\n";
    
    foreach my $type (sort keys %{$instances{$instance}}) {
        print "\t$type\n";

        foreach (@{$instances{$instance}{$type}}) {
            print "\t\t$_\n";
        }
    }
}

__DATA__
IRCNFPP2, TRIGGER, POLLINGASSIGNQUEUEEVENT_UPDERQ
IRCNFPP2, TRIGGER, POLLINGASSIGNQUEUEEVENT_UPDERQ
IRCNFPP2, TRIGGER, NOTESADDEDEVENT_UPDCEQ
IRCNFPP2, TRIGGER, MESSAGESTATUSEVENT_UPDCEQ
IRCNFPP2, TABLE, CONFMASTERAGREEMENT
IRCNFPP2, TABLE, CONFIRMATIONSTRADEBUILDER_A
IRCNFPP2, TABLE, CONFIRMATIONSTRADEBUILDER
IRCNFPP2, TABLE, CONFIRMATIONSTATUSEVENT_A
IRCNFPP2, SEQUENCE, INCOMINGCONFSTLUSQ
IRCNFPP2, SEQUENCE, INCOMINGCONFSTATUSSQ
IRCNFPP2, SEQUENCE, INCOMINGCONFIRMATIONSQ
IRCNFPP2, SEQUENCE, INCOMINGCONFATTRIBUTESSQ

 

[sapatos]

excellent, thanks for the help will check it out in the morning.