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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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