Re: Check for a table, then drop

Hi,

I don't really understand why you want to do this.

I would usually simply drop the table, if it exists it gets dropped, if it doesn't then it errors. As far as I know you can't use DDL in a PL/SQL block unless it is dynamic. If you wish to check if the table exists then you can query all_tables, if it exists you can then drop it. Another solution would be to execute the drop command and ignore the error that is generated if the table exists.

Whatever, the below procedure will check if the table exists in all_tables if it does then it will create and execute a drop statement dynamically. If the table does not exist then the no_data_found exception raised is ignored. You could remove the select statement from this procedure and ignore the table does not exist message from the drop statement.

CREATE OR REPLACE PROCEDURE droptab (
  p_tabname all_tables.table_name%TYPE) AS BEGIN
  DECLARE

    v_cursor NUMBER;
    v_check NUMBER;
    v_numrows NUMBER;
    v_dropstring varchar2(100);

      INTO v_check 
      FROM all_tables 
      WHERE table_name = p_tabname;    

/* Drop Table - dynamically create drop statement*/
    v_cursor := DBMS_SQL.OPEN_CURSOR;
    v_dropstring := 'DROP TABLE '||p_tabname;     DBMS_SQL.PARSE(v_cursor, v_dropstring, DBMS_SQL.V7);     v_numrows := DBMS_SQL.EXECUTE(v_cursor);     DBMS_SQL.CLOSE_CURSOR(v_cursor);

  EXCEPTION /*If an exception other than no_data_found then raise */     WHEN NO_DATA_FOUND THEN
      NULL;
    WHEN OTHERS THEN
      RAISE;
  END;
END droptab;

I Hope somewhere in here is what you wanted to know otherwise I'll feel really stupid!

Cheers,

Paul Received on Wed Mar 04 1998 - 00:00:00 CST