Anonymous PL/SQL block checked exception

0
votes

I'm trying to catch an exception within my anonymous PL/SQL block

DECLARE
    ...

BEGIN
FOR herstell_row IN (
    ...
) 
LOOP
    ...

    DECLARE
        table_does_not_exists exception;
        pragma exception_init( table_does_not_exists, -942 );
    BEGIN
        INSERT INTO SMART_MONITORING_MACHINE_NAV_B (
            MACHINE, 
            NAVIGATION_LEVEL_ID
        ) 
        SELECT 
            old_binding.MACHINE, 
            pv_id 
        FROM 
            SMART_MACHINE_NAV_BINDING old_binding
        WHERE
            old_binding.NAVIGATION_LEVEL_ID = herstell_row.HENAME1;
    EXCEPTION
        WHEN table_does_not_exists THEN null;
    END;

END LOOP;
END;

I know the table SMART_MACHINE_NAV_BINDING doesn't exist in my case, so I need the nested anonymous block to ignore its code. But I always get this error:

Error report - ORA-06550: line 41, column 14: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 33, column 10: PL/SQL: SQL Statement ignored

2
plsqlexception-handlingoracle11g
Typo: you have an extra s in table_does_not_exist*s* - Sylvain Leroux
All database objects, that you use in your code, have to exist at runtime, otherwise you'll get such error. Why do you want to make select from a non-existent table? - Dmitriy
@Dmitry It's a migration script. It's executed during deploying of our newer application version. Some systems do have this table and some systems don't. - Jan Krakora

2 Answers

0
votes

You can't compile code with non-existent table, but you can try to execute it using EXECUTE EMMEDIATE:

DECLARE
    ...

BEGIN
FOR herstell_row IN (
    ...
) 
LOOP
    ...

    DECLARE
        table_does_not_exists exception;
        pragma exception_init( table_does_not_exists, -942 );
    BEGIN
      execute immediate 
        'INSERT INTO SMART_MONITORING_MACHINE_NAV_B (
            MACHINE, 
            NAVIGATION_LEVEL_ID
        ) 
        SELECT 
            old_binding.MACHINE, 
            pv_id 
        FROM 
            SMART_MACHINE_NAV_BINDING old_binding
        WHERE
            old_binding.NAVIGATION_LEVEL_ID = :P' using herstell_row.HENAME1;
    EXCEPTION
        WHEN table_does_not_exists THEN null;
    END;

END LOOP;
END;

Also you don't need exceptions here, you can check existence of a table using system view:

declare
  table_created number;
begin
  select count(*)
    into table_created
    from all_tables
   where table_name = ...
     and owner = ...;

  if table_created > 0 then
     execute immediate 'insert into ...';
  end if;
end;

More information about EXECUTE IMMEDIATE statement: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS01317

0
votes

Oracle does not know this error table_does_not_exists it is user defined, so you should handle in when others then, for eg.

Exception
  When Others then
     null;
  -- or at this time you can raise your error table_does_not_exists
    raise table_does_not_exists;
  -- and handle it in another parent block
end;
Exception
   when table_does_not_exists then
     null;
end;