sql

SET SERVEROUTPUT ON;

DECLARE
LNAME VARCHAR2(200);
BEGIN
FOR X IN (select TABLE_NAME from user_tables
where table_name like @H_502_24@'SYMPHONY%'
) LOOP
LNAME:= X.TABLE_NAME;
EXECUTE IMMEDIATE @H_502_24@'DROP TABLE '|| X.TABLE_NAME ;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(LNAME);
RAISE;
END;
/

执行结果

sql> SET SERVEROUTPUT ON;
sql> DECLARE
  2  LNAME VARCHAR2(200);
  3  BEGIN
  4  FOR X IN (select TABLE_NAME from user_tables
  5  where table_name like 'SYMPHONY%'
  6  ) LOOP
  7  LNAME:= X.TABLE_NAME;
  8  EXECUTE IMMEDIATE 'DROP TABLE '|| X.TABLE_NAME ;
  9  END LOOP;
 10  EXCEPTION
 11  WHEN OTHERS THEN
 12  DBMS_OUTPUT.put_line(LNAME);
 13  RAISE;
 14  END;
 15  /


PL/sql procedure successfully completed