I am developing a web page that will allow our developers to easily recompile Oracle object that have gone invalid. However, I am having trouble distinguishing when objects compile fine from when they compile with errors. Here's a code snipit:
$sql = "SELECT object_type\n".
",object_name\n".
"FROM user_objects\n".
"WHERE status != 'VALID'\n".
"ORDER BY object_type, object_name";
$sql_stmt = OCIParse($db, $sql);
OCIDefineByName($sql_stmt, "OBJECT_TYPE", $object_type);
OCIDefineByName($sql_stmt, "OBJECT_NAME", $object_name);
OCIExecute($sql_stmt);
while (OCIFetch($sql_stmt)) {
if ($object_type == "PACKAGE") {
$ddl = "ALTER PACKAGE $object_name COMPILE PACKAGE";
} elseif ($object_type == "PACKAGE BODY") {
$ddl = "ALTER PACKAGE $object_name COMPILE BODY";
} else {
$ddl = "ALTER $object_type $object_name COMPILE";
}
$ddl_stmt = OCIParse($db, $ddl);
$err = OCIExecute($ddl_stmt);
}
Everything is fine when there are no errors in the objects. However, I get an OCI_SUCCESS_WITH_INFO warning for those with errors. While I can bypass the default error handling with @OCIExecute(), the OCIError() function gives me nothing and my $err variable is 1, both of which is the same for successfully compiled objects.
How do I trap this situation so that I can report which object compiled successfully and which didn't?? Ideally, I would like to also get that "_WITH_INFO" bit so that I can even report why it might be invalid.
