http://www.perlmonks.org?node_id=11112567


in reply to Re: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
in thread RESOLVED - Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle

I'm not sure on what type of Oracle server it is, I've been in contact with my Oracle DBA team and they aren't sure why I'm seeing issues either. Unfortunately, they usually aren't that helpful for the non-sanctioned/curated connections, and most people end up fixing things themselves or hacking workarounds. The tnsnames.ora file my DBA team recommend on their intranet page for the connection I'm using is exactly like that given in both those links. When I spoke to one of the DBAs last he had me try a different file, but it didn't work either and I still see the same issues.

It just doesn't make any sense to me. I'm able to run the exact same queries in a SQL Developer environment making the exact same connection, and and Alation web environment (where the connection details are hidden from me). Can also connect and get data out of MicroStrategy web application, but there it's so heavily curated you can't even write your own SQL.

I'm about ready to give up and just resign myself back to the tedium of dumping big .tsv, .csv, .xlsx, or whatever files from those sorts of environments and reading them in to Perl that way, I'm just so sick of having to do that I was hoping I could make this work. :-/

My knowledge of this stuff is extremely limited, I'm not a programmer by profession, it's just something I do to make my regular @jobs a lot more efficient and I love Perl because it usually "just works" the way I want it to with very little cajoling necessary. This database stuff has been the first exception to that for me so far. It's killing me, because I feel like I'm so close to making this work, and I've already spent a lot of time on it on my employer's dime. Just being able to figure out how to connect and retrieve data at all was a huge win for me personally, but if I can't make it work consistently and reliably it's a moot point for my actual job productivity.

Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

Replies are listed 'Best First'.
Re^3: Problem Running More than Two Successive (I think) Sql SELECT statements, DBI, DBD::Oracle
by choroba (Cardinal) on Feb 07, 2020 at 15:17 UTC
    What happens if each prepare and execute run in a separate connection?

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

      A thousand upvotes upon you, choroba. This at least seems to make it run correctly and consistently... I'm not above doing cringe worthy stuff if it works correctly and consistently. :-)

      I fiddled a bit with the same basic idea, and alternatively I can also disconnect and reconnect with a bit of delay like the following prior to each prepare and it seems to work correctly, consistently, and cringe-ly. I had previously seen issues with a lot of connecting/disconnecting, but it hasn't complained yet when I do it like below with @connection = ($dsn, $user, $pass, {InactiveDestroy => 1, PrintError => 0, RaiseError => 1, AutoCommit => 0}).

      $dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n"; sleep(1); $dbh = DBI->connect_cached(@connection) or die;

      I'll take anything that even remotely resembles a win at this point, like I said in an earlier post on this same basic problem, I seem to be frequently muddling the line between winning and losing.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
        A google search against "perl dbi finish" brought be to this post:
        DBI and finish

        Maybe just remove those finish lines...