validate Oracle non-SELECT query

Is there any way to check if an SQL query for Oracle would run successfully (by successfully I mean that the query is syntactically correct, all the table/column names exist, the user has proper permissions, etc.) without actually running it? The query may be not SELECT, but I do not want the开发者_JAVA百科 changes to actually happen if it would modify any data.

I thought about something like:

$valid = false;
$stmt = oci_parse($db, $query);
if(!empty($stmt)) {
  $res = oci_execute($stmt, OCI_DESCRIBE_ONLY|OCI_NO_AUTO_COMMIT);
  if(!empty($res)) {
     $name = oci_field_name($res, 1);
     if(!empty($name)) {
       $valid = true;
     }
  }
  oci_rollback($db);
}

But if $query has some DDL in it, I understand that Oracle would commit it immediately. So is there any way to check the query without any modifications happening?

---

You could create the query in a Package. If the package is created without error then the query must be correct.

Role based rights that can cause issues with any syntax check. Role based rights are not checked until runtil, so it is possible for a query to be correct at design time (because the developer has been granted direct rights to the tables) but fail at runtime (because the user has been granted the same rights in a role).

---

STEP 1:

on your PHP code, create a string containing the text:

Dim myValidationString as string 

myValidationString = " create or replace procedure PROC_TEST_QUERY is " &
                     "       begin                                    " &
                     "         for i in (                             " &
                     "                   {MY_QUERY}                   " &
                     "                  ) loop                        " &
                     "             null;                              " &
                     "         end loop;                              " &
                     "       end;                                     "

STEP 2:

create a variable containing the SQLquery you want to validate:

Dim mySQLstring as string 

mySQLstring =  " SELECT *                               " &
               "   from all_objects                     " &
               "  where owner <> 'SYS'                  " &
               "    and created > sysdate -30           " &
               " UNION ALL                              " &
               " Select *                               " &
               "   from all_objects                     " &
               "  where owner = 'SYS'                   " &
               "    and object_name like 'T%'           " &
               "    and object_type in ('TABLE','VIEW') " 

STEP 3:

replace the string content "{MY_QUERY}" with the content of the SQL statement you want to validate:

Dim myValidationStringToExecute as string

myValidationStringToExecute = replace(myValidationString, "{MY_QUERY}" , mySQLstring)

STEP 4:

execute the validation query:

DB.EXECUTE(myValidationStringToExecute)

STEP 5:

verify if the last call (DB.EXECUTE.....) raise an error. if yes, the query is invalid if no , the query is valid