Re: Drop Table in PLSQL Script

From: zhuchao <chao_ping_at_163.com>
Date: 25 Jun 2002 19:40:00 -0700
Message-ID: <d8e45edf.0206251840.5939695c_at_posting.google.com>

Just some simple example:
SQL> create table test1(id number);

Table created.

SQL> c/1/2
  1* create table test2(id number)
SQL> / Table created.

SQL> select table_name from user_tables where table_name like 'TEST%';

TABLE_NAME

---

TEST
TEST1
TEST2
SQL> set serveroutput on
SQL> declare

  2         my_table_name varchar(30);
  3          sql_text varchar2(200);
  4         cursor my_cursor is
  5         select table_name from user_tables
  6         where table_name like 'TEST%';
  7        begin
  8                 open my_cursor;
  9                 loop
 10                        fetch my_cursor into my_table_name;
 11                        exit when my_cursor%NOTFOUND;
 12                        sql_text:='drop table '||my_table_name;
 13                        dbms_output.put_line(sql_text);
 14                           execute immediate sql_text;
 15                end loop;
 16        end;

 17 /
drop table TEST
drop table TEST1
drop table TEST2

PL/SQL procedure successfully completed.

SQL> desc test
ERROR:
ORA-04043: object test does not exist Received on Wed Jun 26 2002 - 04:40:00 CEST