Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

perl script to delete or update an ORACLE table hangs during execution

by pmModule2012 (Initiate)
on Jun 04, 2012 at 18:29 UTC ( #974346=perlquestion: print w/ replies, xml ) Need Help??
pmModule2012 has asked for the wisdom of the Perl Monks concerning the following question:

Recently I have written a perl script using PERL DBI module to perform SQL operations on an ORACLE table. "SELECT" query is working fine and I got the expected result but the "DELETE" and "UPDATE" statements are not happening and the script is getting hanged.

I don't think this as a permission issue for the userid cause when I am trying to execute those "DELETE' and UPDATE" statements from SQL promt (with the same userid used in perlscript), both the operations are successful. Please guide me on this.. Thanks in advance :)

I am using the below script.. Please verify if there is some mistake I am making
#!/usr/bin/perl -w use strict; use DBI; my $user="@@@@@"; my $pw="******"; my $database="mydb"; my $dsn = "dbi:Oracle:$database"; my $connect = DBI->connect($dsn,$user,$pw) or die "Unable +to connect: $DBI::errstr\n"; print "\n Connection Established\n"; my ($query,$query_handle); # PREPARE THE QUERY $query = qq/Delete from <mytable1> /; $query_handle = $connect->prepare($query); # EXECUTE THE QUERY $query_handle->execute(); print "\n Deletion in progress...\n"; $query_handle->finish(); $connect->commit; print "\n Commit Done\n"; $connect->disconnect();
If I manually go to the sql promt and fire the statement, it works. Moreover, the same code works when I am trying it for some other table.

Comment on perl script to delete or update an ORACLE table hangs during execution
Download Code
Re: perl script to delete or update an ORACLE table hangs during execution
by davido (Archbishop) on Jun 04, 2012 at 18:52 UTC

    Here is where you post a minimal code snippet that shows the behavior.

    Lacking that snippet, I would ask if you're checking for failure in a way documented in DBI. When something fails silently it's usually because you're not asking for it to fail noisily. Noisy failures are a good thing; they tend to tell you what the problem is.


    Dave

Re: perl script to delete or update an ORACLE table hangs during execution
by erix (Vicar) on Jun 04, 2012 at 20:40 UTC

    Perhaps you forgot to commit? (see $dbh->commit in the DBI docs; see also AutoCommit, begin_work, rollback)

Re: perl script to delete or update an ORACLE table hangs during execution
by davido (Archbishop) on Jun 05, 2012 at 09:23 UTC

    Thanks for updating your post with code.

    Now, what happens when you alter your DBI->connect() line to look like this:

    my $connect = DBI->connect( $dsn, $user, $pw, { RaiseError => 1 } );

    Or as an alternative, check for an error explicitly after every single DBI call; your prepare(), your execute(), your finish(), and your commit().

    Like I said in my earlier post, the error messages are often helpful.


    Dave

      hi, Can try DML query with 'NOLOCK' to check if any deadlock is there.

        How about trying { RaiseError => 1} like I suggested. I still haven't seen from the code you posted that you're actually doing error checking. Without error checking, you don't get the help provided by error messages. Why look at locking when you haven't even looked to see what errors you're getting?

        When you go to the doctor and say, "My chest hurts.", he will probably want to run some tests. He has in his mind tests that will help him to identify the problem. It could be that the tests will come back inconclusive. But often they provide insight to the problem you're having. If you refuse to let him run the tests, he can't narrow down why you're having chest pain. He could offer you this pill or that suggestion, but his suggestions could be way off if he hasn't had an opportunity to run some tests on you.

        I suggested turning on error checking so that you could get some diagnostic information. The diagnostics may be inconclusive. But on the other hand, they could point right to the problem, in which case there needn't be any guesswork in finding a solution.


        Dave

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (8)
As of 2014-12-25 01:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (159 votes), past polls