Comments below marked with ###.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Shao, Chunning" <[EMAIL PROTECTED]>
To: "Tim Bunce" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, March 04, 2002 09:56
Subject: RE: Error in explain plan
My real question is:
Why it can be explained in Sqlplus with the EXACTLY the SAME sql
statment, and it failed in perl?
I think in Perl, $dbh->do($sql) should work if the $sql is working in
SQLPLUS.
### SQL*Plus != DBI (supported by DBD::Oracle in this case).
### It appears SQL*Plus modifies the bind variable markers before passing
the SQL to Oracle for parsing. DBD::Oracle does not do this partly to avoid
ambiguous bind variable names. For example:
SELECT * FROM a_table WHERE c1 = :p3 AND c2 = :3 AND c3 = ?
###
Besides, it is impossible for me to change anything in the sql
statement. It is dynamically get from the shared libary.
### If you have the text in a Perl program, you can _always_ modify it.
### This might be enough: $sql =~ s/:(\d+)/:p$1/g;
And every sql that fails is the ones with rowid, does not seem to be :3
which cause the problem,but i will watch more carefully.
### It is the combination of two different types of bind variables (AKA
placeholders) in the same statement.
And in my code, I use
while (( $failure != 0 ) && ( $counter < 4 ))
{
if (($dbh->state ne "") && ($dbh->errstr eq ""))
{
$failure = 1;
$counter = $counter + 1;
print "DO_explain failed",$dbh->state,"$stmt_id\n";
$error= $dbh->state;
$sql = "INSERT INTO system.plan_table(statement_id,remarks)
VALUES ('$s
tmt_id', '$error')";
$dbh->do($sql);
}
it did not even check for errors. It just fail out instead of inserting
the errors and continue.
### $dbh->state is usually not as descriptive as $dbh->err and $dbh->errstr.
In this particular case, I'd probably use $DBI::err and $DBI::errstr or
$sth->err and $sth->errstr.
### I'd want to see more of your code before commenting more.
