CODEWITHSUNDEEP
sqloracle-databaseplsql
SantoshSrinivas
SantoshSrinivas

Reputation: 65

PL/SQL: ORA-00942, error reported in logically unreachable else block. Needed to add EXECUTE IMMEDIATE for it to work. Why?

I am trying to write a PL/SQL script, which executes a few SQL statements if a certain table exists/not exists.

For eg:

SET SERVEROUTPUT ON;
DECLARE
  cnt NUMBER := 0;
  cnt_2 NUMBER := 0;
BEGIN
  SELECT count(*) INTO cnt FROM all_tables where TABLE_NAME='DOES_NOT_EXIST';
  IF cnt = 0 THEN
    dbms_output.put_line('Table does not exist');
  ELSE
    dbms_output.put_line('Table exists ' || cnt);
    SELECT COUNT(*) INTO cnt_2 from DOES_NOT_EXIST;
  END IF;
END;
/

When I execute this, I get below error

    SELECT COUNT(*) INTO cnt_2 from DOES_NOT_EXIST;
                                    *
ERROR at line 10:
ORA-06550: line 10, column 37:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 5:
PL/SQL: SQL Statement ignored

But, if I add EXECUTE IMMEDIATE in line 11. Then it works fine.

SET SERVEROUTPUT ON;
DECLARE
  cnt NUMBER := 0;
  cnt_2 NUMBER := 0;
BEGIN
  SELECT count(*) INTO cnt FROM all_tables where TABLE_NAME='DOES_NOT_EXIST';
  IF cnt = 0 THEN
    dbms_output.put_line('Table does not exist');
  ELSE
    dbms_output.put_line('Table exists ' || cnt);
    EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO cnt_2 from DOES_NOT_EXIST';
  END IF;
END;
/

Now it works

Table does not exist

PL/SQL procedure successfully completed.

  1. Could you please help me understand why an error is being reported if the ELSE block cannot be reached logically? The cnt variable will always be 0, then, I would assume the ELSE block is never reached.

  2. How come adding EXECUTE IMMEDIATE is not producing the error?

  3. Does this mean, I should add EXECUTE IMMEDIATE for all such statements in the ELSE block?

I searched on SO and other places but couldn't find an answer. Honestly, I don't know what other search terms to use. All search hits leads to generic errors. So, I am asking this question. If this is already answered, please point me to it and close this.

Thank you.

Upvotes: 0

Views: 84

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Looks like you need something like this:

select
  owner,
  table_name,
  xmlcast(
    xmlquery(
      '/ROWSET/ROW/CNT' 
      passing xmltype(dbms_xmlgen.getXML('select count(*) cnt from "'||owner||'"."'||table_name||'"'))
      returning content null on empty
      )
      as int
   ) as cnt
from all_tables
where owner in ('XTENDER',user);

This query returns number of rows for each table from all_tables filtered by your predicates

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

What you are missing is that statements are processed in two steps; they are compiled before they are executed.

During the compilation phase, the identifiers (tables and columns and more) are identified and looked up. If a table name (or column name or whatever) is not found, then you get a compilation error.

Using execute immediate short-circuits this process. Instead of "executing immediately" this is really "delaying compilation". The statement is both compiled and executed during run time. That is why execute immediate prevents the error.

Upvotes: 1

Related Questions