CODEWITHSUNDEEP
oracle-databaseplsql
user9599919
user9599919

Reputation: 63

runtime plsql throwing table not exist error

I have below code which checks for table exists & proceed further. Here problem is why its proceeding to else part even though table is not present in the database & also its returning "0"

SQL> SET serveroutput ON trimspool on feed off echo on
declare
c_cnt number;
t_cnt number;
begin
select count(1) into t_cnt from dba_tables where owner='PRODDBA' and table_name='IOT_LIST';
dbms_output.put_line(t_cnt);
if t_cnt = 0 then
dbms_output.put_line('NO_ACTION');
else
select count(1) into c_cnt from PRODDBA.IOT_LIST;
if c_cnt = 0 then
dbms_output.put_line('NO_ACTION');
else
for i in (select temp_table_name from PRODDBA.IOT_LIST) 
loop
begin
dbms_output.put_line(i.temp_table_name);
execute immediate 'drop table PRODDBA.'||i.temp_table_name||' purge';
EXCEPTION WHEN OTHERS then
CONTINUE;
end;
end loop;
end if;
end if;
end;
/

select count(1) into c_cnt from PRODDBA.IOT_LIST;
ERROR at line 10:
ORA-06550: line 10, column 41:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 14, column 47:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 14, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 17, column 22:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 17, column 1:
PL/SQL: Statement ignored
ORA-06550: line 18, column 42:
PLS-00364: loop index variable 'I' use is invalid
ORA-06550: line 18, column 1:
PL/SQL: Statement ignored

Upvotes: 0

Views: 233

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You should make use of dynamic REFCURSOR to run a loop to drop tables dynamically.

SET SERVEROUTPUT ON
DECLARE
     c_cnt NUMBER;
     t_cnt NUMBER;
     v_schema_name VARCHAR2(40) := 'PRODDBA';
     v_table_name  VARCHAR2(40) := 'IOT_LIST';
     v_tabs        VARCHAR2(40);
     refcur        SYS_REFCURSOR;
BEGIN
     SELECT COUNT(1)
     INTO t_cnt
       FROM dba_tables
     WHERE owner = v_schema_name
           AND table_name =v_table_name;

     dbms_output.put_line(t_cnt);
  IF t_cnt = 0 THEN dbms_output.put_line('NO_ACTION');
     ELSE


      OPEN   refcur FOR 'SELECT  temp_table_name 
                         FROM '||v_schema_name||'.'||v_table_name;
       LOOP
            BEGIN
              FETCH refcur INTO v_tabs;
               EXIT WHEN refcur%NOTFOUND;
                    EXECUTE IMMEDIATE 'drop table '||v_schema_name||'.'
                                      || v_tabs
                                      || ' purge';
                   dbms_output.put_line('DROPPED ' || v_tabs);

              EXCEPTION WHEN OTHERS THEN
                  dbms_output.put_line( 'TABLE NOT FOUND: '||v_tabs);
                 CONTINUE;
              END;
      END LOOP; 

      IF refcur%ROWCOUNT = 0 THEN
          dbms_output.put_line('NO_ACTION');
      END IF;

 END IF;
END;
/

Result

Before table creation

<execute the block>
 0
 NO_ACTION


PL/SQL procedure successfully completed.

After table creation

create table IOT_LIST ( temp_table_name varchar2(40));
INSERT INTO IOT_LIST values('T1');
INSERT INTO IOT_LIST values('T2');
INSERT INTO IOT_LIST values('T4');

<execute the block>

1
DROPPED T1
DROPPED T2
TABLE NOT FOUND: T4


PL/SQL procedure successfully completed.

Upvotes: 1

Related Questions