Skip to Main Content
  • Questions
  • How to distinguish "Semantic error" and "Syntactic error"?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, David.

Asked: March 11, 2019 - 1:43 pm UTC

Last updated: March 12, 2019 - 4:32 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked


Hello Tom,

Is there a way, in a PL/SQL programm, to manage differently "Semantic error" and "Syntactic error"?

In the bloc "EXCEPTION", I wish to count these kind of errors, how can I do?

I found nothing in the documentation and I don't think that there are reserved numbers for the both errors but maybe I am wrong.

Have a nice day,

DAVID D.

and Chris said...

A statement is syntactically correct if it meets the rules of SQL: the keywords are spelt correctly, in the right place, etc.

For example, the following has invalid syntax:

slct * frm a_table;

Unknown Command


The semantic happens for statements with valid syntax. This verifies that the objects you've referenced really exist!

For example, the following is syntactically valid SQL. But it raises a semantic error because there's no object called a_table in the database!

select * from a_table;

ORA-00942: table or view does not exist


For the most part, you don't need to worry about this difference in PL/SQL. You can only compile programs with static SQL if they're syntactically and semantically valid. So you don't have to worry about exception handling, because the PL/SQL itself won't run!

The key exception is if you're writing dynamic SQL.

create table t (
  c1 int
);

insert into t values ( 1 );
commit;

create or replace function f 
  return int authid current_user as
  retval int;
begin
  
  execute immediate 
    'select count(*) from not_a_real_table'
  into retval;
  
  return retval;
end f;
/

select f from dual;

ORA-00942: table or view does not exist

create or replace function f 
  return int authid current_user as
  retval int;
begin
  
  execute immediate 
    'slct count(*) fr not_a_real_table'
  into retval;
  
  return retval;
end f;
/

select f from dual;

ORA-00900: invalid SQL statement


But even here, I'm not sure why you care about the difference between syntax vs semantics. In either case your program fails. And the ORA error will give details about what the issue is.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I need this to count the problem

David D., March 12, 2019 - 12:46 pm UTC


Thank you very much Chris for your answer.

My goal is to count the syntaxic errors and the semantic errors when developers are writing Dynamic PL/SQL.

If there are mainly semantic errors, I presume I have good developers but there is a problem with privileges, so I need to talk with DBAs.

If there are mainly syntaxic errors, I have a problem with my developers, I think they need training in SQL.

Have a nice day,

David D.

Chris Saxon
March 12, 2019 - 1:20 pm UTC

It's easy to make a mistake when writing dynamic SQL, so I'm not sure you can make this generalization.

But...

If your developers are writing lots of dynamic SQL... I think you need to train them in using SQL in PL/SQL ;)

.

David D., March 12, 2019 - 2:34 pm UTC

OK, I understand that there is no easy way to distinguish the kind of error.

Chris Saxon
March 12, 2019 - 3:57 pm UTC

Well:

ORA-00900: invalid SQL statement => syntactic error
ORA-00942: table or view does not exist => semantic error

But there's a whole stack of other errors which fall into one category or the other. If this really matters to you, you could go through and categorise them...

Yes

David D., March 12, 2019 - 4:12 pm UTC


Yes, I think it's the easiest: I will classify the most common errors into two groups.

Thank for your help.

David D.
Chris Saxon
March 12, 2019 - 4:32 pm UTC

There's a lot of them... good luck!

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.