DBI/Oracle Nested SELECT query

terroirman has asked for the wisdom of the Perl Monks concerning the following question:

Hi!

I've been looking all around without finding. Here's my question : is this possible to do a nested query with DBI/oracle ?

Here's my query :

SELECT TROUBLE_TICKET_NUM FROM 
(SELECT TROUBLE_TICKET_NUM FROM TICKETTABLE WHERE TROUBLE_TICKET_NUM >
+ '123456789' AND WORK_GROUP='abc' ORDER BY TROUBLE_TICKET_NUM ASC)
WHERE ROWNUM <= 4
[download]

This is executed instantly in my Oracle client (toad). But in a script, that just hangs there forever. Basically, it doesn't get to the fetchall_arrayref statement.

$odstthandle = $dbttods->prepare($qry) or die $dbttods->errstr;
$odstthandle->execute() or die $odstthandle->errstr;
$billets = $odstthandle->fetchall_arrayref or die $odstthandle->errstr
+;
[download]

Is that even possible to do ? If so any way to troubleshoot whats taking so long ? If not, any alternative than DBI ?

Thanks for your time !

Comment on DBI/Oracle Nested SELECT query Select or Download Code

Replies are listed 'Best First'.
Re: DBI/Oracle Nested SELECT query by erix (Prior) on Sep 01, 2015 at 05:47 UTC
possible to do a nested query with DBI/oracle ? Yes. (BTW, a quick google shows me a few examples of oracle FROM subqueries and they all are aliased. I'm not sure if such an alias is necessary (in postgres it is) but it is probably a good habit to get into.) Basically, it doesn't get to the fetchall_arrayref statement. How do you know? `$odstthandle = $dbttods->prepare($qry) or die $dbttods->errstr; $odstthandle->execute() or die $odstthandle->errstr; $billets = $odstthandle->fetchall_arrayref or die $odstthandle->errstr +;` [download] Why are you surprised? There is no output in the code so there will be no output. Can you provide a better example? An example that actually shows some unexpected output (or even an unexpected lack of output)?	[reply] [d/l]
Re: DBI/Oracle Nested SELECT query by chilledham (Friar) on Aug 31, 2015 at 22:30 UTC
I've never had an issue running nested queries against an Oracle database using DBI (and DBD::Oracle). This is just an idea, but perhaps you need to update the Instant Client on your machine to something more current. It is the Instant Client that interacts with the Oracle database. DBI and DBD::Oracle are just wrappers around the drivers provided in the Instant Client. Hope that helps.	[reply]
Re: DBI/Oracle Nested SELECT query by pme (Monsignor) on Sep 01, 2015 at 03:05 UTC
It should work. Try to run your script with tracing. `$ DBI_TRACE=2 ./yourscript.pl` [download] See TRACING chapter in DBI for the details.	[reply] [d/l]
Re: DBI/Oracle Nested SELECT query by rdfield (Priest) on Sep 04, 2015 at 15:28 UTC
Check the running queries in the v$sql and v$sqlarea tables (or v$session_longops) to make sure it is actually doing something. Also try explain_plan or look at the output of tkprof (statistics need to be enabled). There must be something different about the sql in the client and the sql in your perl script. rdfield	[reply]