How can I handle this compilation error through exception?

declare
table_or_view_does_not_exist exception;
pragma exception_init(table_or_view_does_not_exist,-00942);
b exception;
pragma exception_init(b,-00942);
d_table varchar2(200);
c_table varchar2(200);
c_count Number;
begin
    begin
        d_table:='drop table audit_table PURGE';
        execute immediate d_table;
        exception
            when table_or_view_does_not_exist then
                null;
                end;
                <<lable>>
             c_table := 'create table audit_table
             (table_name varchar2(50),
             column_name varchar2(50),
             count_type varchar2(50),
             v_count number)';
             execute immediate c_table;
select count(*) into c_count from customer_profile where cust_id is null;
insert into audit_table columns (table_name,column_name,count_type,v_count) values('customer_profile','cust_id','null','c_count');
 exception
 when b then
 GOTO lable;
 end;

Error report:

ORA-06550: line 25, column 13:
PL/SQL: ORA-00942: table or view does not exist

ORA-06550: line 25, column 1:
PL/SQL: SQL Statement ignored

ORA-06550: line 28, column 2:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'LABLE'

ORA-06550: line 28, column 2:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

CodePudding user response：

What you do, is just bad practice. In Oracle, we don't create tables in PL/SQL but at SQL level and then use them in our procedures.

In your case, you'd

-- create table first
create table audit_table ...;

-- use it in PL/SQL procedure
declare
  ...
begin
  ...
  insert into audit_table ...
end;
/

You can't "handle" compilation error through exception. What you could do is to put insert statement into dynamic SQL. Also, it wouldn't harm if you used valid syntax (there's no columns "keyword" there).

execute immediate q'[insert into audit_table 
                     (table_name, column_name, count_type, v_count)
                     values('customer_profile', 'cust_id', 'null', :a)]'
                  using c_count;

but - once again - that's just bad practice. Don't do it that way, there's no benefit and many disadvantages.

As of goto - well, jumping around your code is almost always wrong. Error you got says that you can't jump out of the exception handler so your idea was wrong anyway.

CodePudding user response：

Could you consider another way to control the flow - comments in the code:

DECLARE
    cmd         VarChar2(200);
    c_count     Number(6);
    --
    Status      VarChar2(255);
BEGIN
    cmd :='drop table audit_table';
    Begin   -- 1st nested PL/SQL block
        Execute Immediate cmd;
        Status := 'OK';
    Exception                   -- this could be raised if  the table doesn't exist which is probably OK
            When OTHERS Then
                Status := 'OK'; -- SQLERRM = ORA-00942: table or view does not exist - nothing happens - it would be droped anyway
                -- Status := 'ERR - ' || SQLERRM;   -- this is alternative if you want to do something else with this ERR
    End;
    If Status != 'OK' Then  -- here you can check the status and decide what to do
        GoTo EndIt;
    End If;
    --
  <<lable>>  -- in this code there is no need for this label
    cmd :=  'create table audit_table (table_name varchar2(50), column_name varchar2(50), count_type varchar2(50), v_count number)';
    Begin   -- 2nd nested PL/SQL block
        Execute Immediate cmd;
        Status := 'OK';
    Exception
            When OTHERS Then
                Status := 'ERR - ' || SQLERRM;
    End;
    If Status != 'OK' Then  -- here you can check the status and decide what to do
        dbms_output.putline(Status);    -- send error message and exit - there is no audit_table
        GoTo EndIt;
    Else
        Null;    -- here you can do something else (if maybe the table already exists)
    End If;
    --
    -- think about what could go wrong below (2 lines) and either leave it as it is or put it in the 3rd nested PL/SQL block
    Select count(*) Into c_count From customer_profile Where cust_id Is Null;
    insert into audit_table (table_name, column_name, count_type, v_count) values('customer_profile', 'cust_id', 'Null', c_count);

  <<EndIt>>     -- used to end the block if needed
    Null;
EXCEPTION
        When OTHERS Then
            dbms_output.putline(SQLERRM);   -- You can not get out of here since it is main PL/SQL blok that went into an exception
END;