Hi
On the basis of what you have mentioned, I have made a sample code.
Let me know if my understanding is different from yours.
The Perl Code is copied below :-
# cat u.pl
#!/usr/bin/perl
##!/u01/app/oracle/product/10.1.0/db_1/perl/bin/perl
#
# Hi all, I'm in need of a loop, can't seem to find what I'm looking for
online.
# I want to loop over this array ref inserting each hash value into the DB
at each iteration.
# I've got the following data structure which is assigned to $aref:
use DBI;
$VAR1 = [
{ 'rate' => '1.98', 'name' => 'Dollars
-> Sterling' },
{ 'rate' => '1.5', 'name' =>
'Sterling -> Euros' },
{ 'rate' => '0.75', 'name' => 'Dollars
-> Euros' },
{ 'rate' => '2.05', 'name' =>
'Sterling -> Dollars' },
{ 'rate' => '0.72', 'name' => 'Euro ->
Sterling' },
{ 'rate' => '1.52', 'name' => 'Euro ->
Dollars' },
{ 'rate' => '1.98', 'name' => 'Amex
Exchange Rate' }
];
$dbh = DBI->connect ("DBI:Oracle:demo1", "asaxen01", "asaxen01", {AutoCommit
=> 0, PrintError => 1} ) or die "Can not connect to the database : $!\n\n" ;
foreach $array_element (@$VAR1)
{
print "\n\$array_element = [$array_element]";
foreach $key1 (keys %{$array_element})
{
$value1 = ${$array_element}{$key1};
print "\nkey1 = [$key1] : value1 = [$value1]";
$query = "insert into mytable123 values ('$key1',
'$value1')";
$sth = $dbh->prepare ("$query");
$sth->execute ();
}
}
if (defined $dbh->errstr)
{
$dbh->rollback;
}
else
{
$dbh->commit;
}
$dbh->disconnect;
print "\n";
#
The output from this code in the database is :-
SQL> select * from mytable123;
CATEGORY
VALUE1
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
rate
1.98
name
Dollars -> Sterling
rate
1.5
name
Sterling -> Euros
rate
0.75
name
Dollars -> Euros
rate
2.05
name
Sterling -> Dollars
rate
0.72
name
Euro -> Sterling
rate
1.52
CATEGORY
VALUE1
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
name
Euro -> Dollars
rate
1.98
name
Amex Exchange Rate
14 rows selected.
SQL>
Regards,
Amit Saxena
On 6/18/08, Graeme McLaren <[EMAIL PROTECTED]> wrote:
>
>
> Hi all, I'm in need of a loop, can't seem to find what I'm looking for
> online. I want to loop over this array ref inserting each hash value into
> the DB at each iteration.I've got the following data structure which is
> assigned to $aref:
>
> $VAR1 = [ { 'rate' => '1.98', 'name' =>
> 'Dollars -> Sterling' }, { 'rate' =>
> '1.5', 'name' => 'Sterling ->
> Euros' }, { 'rate' => '0.75', 'name'
> => 'Dollars -> Euros' }, { 'rate' =>
> '2.05', 'name' => 'Sterling ->
> Dollars' }, { 'rate' =>
> '0.72', 'name' => 'Euro ->
> Sterling' }, { 'rate' =>
> '1.52', 'name' => 'Euro ->
> Dollars' }, { 'rate' =>
> '1.98', 'name' => 'Amex Exchange Rate' } ];
>
>
> Can anyone help out with this?
>
> Much appreciated.
>
> G :)
>
