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

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();
[download]

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

Replies are listed 'Best First'.
Re: perl script to delete or update an ORACLE table hangs during execution by davido (Cardinal) 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	[reply]
Re: perl script to delete or update an ORACLE table hangs during execution by erix (Prior) 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)	[reply]
Re: perl script to delete or update an ORACLE table hangs during execution by davido (Cardinal) 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 } );` [download] 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	[reply] [d/l] [select]
Re^2: perl script to delete or update an ORACLE table hangs during execution by prashantktyagi (Scribe) on Jun 05, 2012 at 11:34 UTC
hi, Can try DML query with 'NOLOCK' to check if any deadlock is there.	[reply]
Re^3: perl script to delete or update an ORACLE table hangs during execution by davido (Cardinal) on Jun 05, 2012 at 18:52 UTC
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	[reply] [d/l]