With Oracle dynamic SQL one is able to execute a string containing a SQL statement. e.g.
l_stmt := 'select count(*) from tab1';
execute immediate l_stmt;
Is it possible to not execute l_stmt but check that the syntax and semantics is correct programmitically?
EXPLAIN PLAN will check the syntax and semantics of almost all types of SQL statements. And unlike DBMS_SQL.PARSE it will not implicitly execute anything.
The point of the explain plan is to show how Oracle will execute a statement. As a side-effect of generating the plan it must also check syntax, privileges, and generally do everything except actually run the statement. The explain plan itself is pointless and can be ignored, the statement is only run to check for any errors. As long as there are no errors, the statement is valid.
For example, the PL/SQL blocks below check the validity of a SELECT statement and a CREATE TABLE statement. They run without error so the syntax is fine.
begin
execute immediate 'explain plan for select * from dual';
end;
/
begin
execute immediate 'explain plan for create table just_some_table(a number)';
end;
/
Running a bad statement will generate an error. In at least this one test case, it generates the same error as if the statement was run by itself.
begin
execute immediate 'explain plan for select * from this_table_does_not_exist';
end;
/
ORA-00942: table or view does not exist
ORA-06512: at line 2
The syntax diagram in the manual implies it should run for all statements. However, there appear to be at least a few statement types that do not work, such as ALTER SESSION.
begin
execute immediate 'explain plan for alter session set optimizer_features_enable = ''11.2.0.4''';
end;
/
ORA-00900: invalid SQL statement
ORA-06512: at line 2
Slightly off-topic - are you trying to build a completely generic SQL interface, like a private SQL Fiddle built in PL/SQL? Do you need to worry about things like preventing users from attempting to run certain statement types, and ensuring there are no trailing semicolons? If so I can edit the question to help with some of those difficult dynamic SQL tasks.
I think that the only "solution" is to use DBMS_SQL.PARSE().
It is not perfect but it is the best that you can get
Hope this way you can check the query formed before execution.
set serveroutput on;
DECLARE
lv_sql VARCHAR2(1000);
lv_cnt PLS_INTEGER;
BEGIN
lv_sql:='select count(*) from tab1';
dbms_output.put_line(lv_sql);
--EXECUTE IMMEDIATE lv_sql INTO lv_cnt;
END
DBMS_SQL.PARSE()docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS68277