|
Re: DBI Perl Oracle , returs less number of rows than actual number
by talexb (Chancellor) on May 31, 2002 at 04:10 UTC
|
Are you absolutely positive that you're executing the same SQL statement in both cases?
What happens when you do a select count (*) instead of select name, year, ... using the prompt and through Perl/DBI .. still the same disagreement?
--t. alex
"Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny
| [reply]
[d/l]
[select] |
|
|
Yes sir, I am 101% sure that the SQL is same.
Now interestingly I tried to run this using TOAD, it returns 55000 records.
I was suspecting there was some buffer size reated issue so tried to use count(*), add few more columns, drop some columns, nothing changes it rerives 30767 records.
Is there any known issue with this large number of records? Is there any setting such as CacheInSize or something?
Alternatively can anyone suggest the debugging technique to check where the record set fails? (Except for prinitng the last retrieved record)
| [reply] |
|
|
Alternatively can anyone suggest the debugging technique to check where the record set fails? (Except for prinitng the last retrieved record)
You have tried DBI->trace(1) already, haven't you?
--t. alex
"Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny
| [reply]
[d/l] |
Re: DBI Perl Oracle , returs less number of rows than actual number
by rdfield (Priest) on May 31, 2002 at 09:45 UTC
|
It's probably a quoting issue. Unless you provide the SQL, there's no specific help to be given. One thought though - if you send Oracle an empty string through a DBI placeholder it is evaluated as a NULL. For example: select count(*) from my_table where some_column = ? and the paramater to the execute is the empty string, $sth->execute(''), it is actually executed as select count(*) from my_table where some_column = NULL which will always return 0, since NULL != NULL (or more generally NULL != anything). To summarise: it's probably a quoting issue. Try it without placeholders. Have a look in v$sql. Set client_trace_level = admin in your local sqlnet.ora. Turn on DBI tracing. There's loads you can do to isolate the problem rdfield
| [reply]
[d/l]
[select] |
|
|
Thanks !
I will try these things now. Trace on already there.But does not show why it stopped fetching further records.
About placeholder, I will try and let you guys know.
Thanks
| [reply] |
Re: DBI Perl Oracle , returs less number of rows than actual number
by lachoy (Parson) on May 31, 2002 at 02:25 UTC
|
How are you getting the rowcount? Are you actually fetching the rows and incrementing a counter for each? Or are you looking at the return value for execute()?
Chris
M-x auto-bs-mode
| [reply] |
|
|
I tried both options
1. $sth->rows;
2. fetching all records and the using a counter to itearte through the array.
Both way I get max 30787 records.
| [reply] |
|
|
Only the second way will work. The first is for finding how many rows were affected by an UPDATE/DELETE operation.
Since DBD::Oracle uses the same libraries as SQL*Plus, that strongly leads me to look at some difference between the Perl invocation and the SQL*Plus invocation: how the queries are constructed, who is creating the query, how are the results fetched.
Also, what happens when you issue a SELECT COUNT(*) FROM foo WHERE ... in each of the environments?
Chris
M-x auto-bs-mode
| [reply] |