Hi There,
We have a procedure that populates and drops few temp tables. This morning the procedure failed to run as it was trying to drop a temporary table that didn't exist.
The code it self does the following:
--
execute immediate 'DROP TABLE TEMP_MEMBER_COUNT';
--
execute immediate 'create table TEMP_MEMBER_COUNT AS (
SELECT ...
...)';
--
There are a couple of hundreds lines of code after this.. so how can we check if the table exists before attempting the drop please?
We tried adding the following exception to the end of the procedure:
...
COMMIT;
exception when others then null;
END;
/
However, the problem still exists:
create procedure calc_counts as (source_cnt VARCHAR2) AS
...
336 --
337 END IF;
338
339 COMMIT;
340 exception when others then null;
341 END;
342 /
Procedure created.
Elapsed: 00:00:00.09
SQL>
SQL> drop table TEMP_MEMBER_COUNT;
Table dropped.
Elapsed: 00:00:00.01
SQL>
SQL> EXECUTE CNT_SCH.CALC_COUNTS ('SOURCE');
BEGIN cnt_sch.calc_counts ('SOURCE'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 18:
PLS-00905: object CNT_SCH.CALC_COUNTS is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Elapsed: 00:00:00.06
SQL>
The procedure have been invalidated because the temp table does not exist anymore.. and this is the problem that we're trying to resolve :-/
Help please..
Thanks