Oracle OCI - Retrieving data from Oracle

Author

Retrieving data from Oracle

PerlDiscuss - Perl Newsgroups and mailing lists

2005-06-04, 3:24 am

Hi,

I'm migrating a perl application in postgres to Oracle. Here, the data is
being retrieved as below.

while ($row = $sth->fetchrow_hashref()) {
push (@list,$row);
}

It looks like fetchrow_hashref() is not working with Oracle. But I'm able
to retrieve the data through fetchrow_array. Any idea why
fetchrow_hashref() is not working? Is there any other alternative for
fetchrow_hashref()? How do I accomplish the above with any other fetch
methods?

Thanks in advance.

Sangeeth

Andrew Markiewicz

2005-06-04, 3:24 am

I use fetchrow_hashref with Oracle successfully.

Try specifying the case of the columns (uc or lc depending on preference or
current usage in the program):
$sth-> fetchrow_hashref('NA
ME_lc')

-----Original Message-----
From: sangeeth.vs@masconit.com [mailto:sangeeth.vs@masconit.com]
Sent: Tuesday, May 31, 2005 9:36 AM
To: oracle-oci@perl.org
Subject: Retrieving data from Oracle

Sent by mailto:oracle-oci-return-160- AMarkiewicz=american
tv.com@perl.org
____________________
____________________
__________________

Hi,

I'm migrating a perl application in postgres to Oracle. Here, the data is
being retrieved as below.

while ($row = $sth->fetchrow_hashref()) {
push (@list,$row);
}

It looks like fetchrow_hashref() is not working with Oracle. But I'm able
to retrieve the data through fetchrow_array. Any idea why
fetchrow_hashref() is not working? Is there any other alternative for
fetchrow_hashref()? How do I accomplish the above with any other fetch
methods?

Thanks in advance.

Sangeeth

Andrew Markiewicz

2005-06-04, 3:24 am

One additional thing I noticed.
The fetchrow_hashref currently returns a different reference each time it is
executed. So looping through and pushing the references in a list works
fine for now. But from the perldoc DBI:

"Currently, a new hash reference is returned for each
row. This will change in the future to return the same
hash ref each time, so don't rely on the current
behaviour."

Your code relies on the fact that the references are different for each
call.
When/if the DBI module is changed to return the same reference, your code
will break since it will store a list of all the same hash reference, all
pointing to your last row in the cursor.

To continue using an array of hash references, copy the hash results into an
anonymous hash reference and push that reference on the array.

while ($row = $sth->fetchrow_hashref()) {
push (@list,$row); # original
push (@list,{%$row}); # new reference
}

You can check both hex addresses of the references to verify that they are
different.

-----Original Message-----
From: Sangeeth V S [mailto:sangeeth.vs@masconit.com]
Sent: Wednesday, June 01, 2005 4:32 AM
To: Markiewicz, Andrew
Subject: Re: Retrieving data from Oracle

Sent by mailto:sangeeth.vs@masconit.com
____________________
____________________
______

Hi Andrew,

$sth-> fetchrow_hashref('NA
ME_lc') worked for me! Thanks for the help.

Sangeeth

----- Original Message -----
From: "Markiewicz, Andrew" < AMarkiewicz@american
tv.com>
To: <sangeeth.vs@masconit.com>; <oracle-oci@perl.org>
Sent: Tuesday, May 31, 2005 8:53 PM
Subject: RE: Retrieving data from Oracle

> I use fetchrow_hashref with Oracle successfully.
>
> Try specifying the case of the columns (uc or lc depending on
> preference
or
> current usage in the program):
> $sth-> fetchrow_hashref('NA
ME_lc')
>
>
>
> -----Original Message-----
> From: sangeeth.vs@masconit.com [mailto:sangeeth.vs@masconit.com]
> Sent: Tuesday, May 31, 2005 9:36 AM
> To: oracle-oci@perl.org
> Subject: Retrieving data from Oracle
>
>
> Sent by
> mailto:oracle-oci-return-160- AMarkiewicz=american
tv.com@perl.org
> ____________________
____________________
__________________
>
> Hi,
>
> I'm migrating a perl application in postgres to Oracle. Here, the
> data is being retrieved as below.
>
> while ($row = $sth->fetchrow_hashref()) {
> push (@list,$row);
> }
>
> It looks like fetchrow_hashref() is not working with Oracle. But I'm
> able to retrieve the data through fetchrow_array. Any idea why
> fetchrow_hashref() is not working? Is there any other alternative for
> fetchrow_hashref()? How do I accomplish the above with any other
> fetch methods?
>
> Thanks in advance.
>
> Sangeeth
>

Sponsored Links