Wheres my table???

[howzatUK]

I am trying to create a proc to delete a table if its exsists in the user_tables. The syntax of the following proc is accurate but the problem is that my tables tabel_name does not exists in the user_tables tables. (thats a lot of tables in one sentence).

Can any one offer me any advice so i can find the table and use the following proc or a similar proc to delete it if it exists. AND IT DOES EXIST.

Thanks in advance.

CREATE OR REPLACE PROCEDURE DeleteIfExists
AS
iexists Number;
BEGIN
select count(*) into iexists from user_tables where table_name = 'tmpFIXDCActns';
if
iexists = 1
then execute immediate 'drop table tmpFIXDCActns';
end if;
END;

 

[sem]

Read carefully my answer, especially 2.

P.S. 1 was written to help you make your code both correct and efficient while 2 and 3 only to correct or prevent your errors caused by not following 1.

BEGIN
  EXECUTE IMMEDIATE 'drop table tmpFIXDCActns';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -942 THEN --table doesn't exist
      NULL;
    ELSE --some other error
      RAISE;
    END IF;
END;

Regards, Dima

http://seminihin.narod.ru