Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBI/Oracle Nested SELECT query

by terroirman (Initiate)
on Aug 31, 2015 at 21:45 UTC ( #1140570=perlquestion: print w/replies, xml ) Need Help??

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

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 +;

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 !

Replies are listed 'Best First'.
Re: DBI/Oracle Nested SELECT query
by erix (Parson) 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 +;

    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)?

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.

Re: DBI/Oracle Nested SELECT query
by pme (Prior) on Sep 01, 2015 at 03:05 UTC
    It should work. Try to run your script with tracing.
    $ DBI_TRACE=2 ./yourscript.pl
    See TRACING chapter in DBI for the details.
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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1140570]
Approved by stevieb
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2019-12-07 16:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (162 votes). Check out past polls.

    Notices?