Skip to Main Content
  • Questions
  • From PL SQL test a query syntactic and semantic.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hans.

Asked: September 30, 2024 - 3:43 pm UTC

Last updated: October 01, 2024 - 1:29 pm UTC

Version: 19c

Viewed 1000+ times

You Asked

Can I, within a package/procedure/function/..., test a query, stored as text in a variable, for syntactic and semantic soundness? Without executing the query, that is.
And when the answer is 'yes': how?
In what way does the test result present itself to me? Is the answer if-then-able?

and Chris said...

You can parse it with dbms_sql.parse. This checks the statement is valid. If it's invalid, you'll get whatever ORA error you'd get if you ran the statement manually.

e.g.:

declare
  procedure test_stmt ( stmt varchar2 ) as
    cur  int;
  begin
    dbms_output.put_line ( 'Testing statment ' || stmt );
    cur := dbms_sql.open_cursor;
    dbms_sql.parse ( cur, stmt, dbms_sql.native );
    dbms_sql.close_cursor ( cur );
  end;
begin
  test_stmt ( 'select count(*) from hr.employees' );
  test_stmt ( 'select count(*) from junk_table' );
end;
/
Testing statment select count(*) from hr.employees
Testing statment select count(*) from junk_table

ORA-00942: table or view "CHRIS"."JUNK_TABLE" does not exist


Note that this does run DDL statements. You have to pass DML statements to dbms_sql.execute to run them.

Rating

  (1 rating)

Comments

I will test this asap

Hans Looyschelder, September 30, 2024 - 5:40 pm UTC

The answer came as quick as it appears uncomplicated.
I'm going to try this as soon as possible.
Thanks!
Chris Saxon
October 01, 2024 - 1:29 pm UTC

You're welcome

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library