SET ECHO OFF;
SET SHOW OFF;
SET DEFINE ON;
SET SERVEROUTPUT ON;

DECLARE
      err_num          NUMBER;
      err_msg          VARCHAR2 (255);

V_USER VARCHAR2(100) := upper('&1');
BEGIN

--TABLAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TABLE '||OWNER||'.'||OBJECT_NAME ||' CASCADE CONSTRAINTS PURGE' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TABLE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE(substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;
   
END LOOP;

--PURGAR TABLAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'PURGE TABLE '||OWNER||'."'||OBJECT_NAME||'"' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TABLE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SQLERRM;
      DBMS_OUTPUT.PUT_LINE(substr('ERROR:' || ERR_NUM || '-' || ERR_MSG, 1, 255));
END;
   
END LOOP;

--PROCEDIMIENTOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP PROCEDURE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'PROCEDURE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;


--FUNCIONES-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP FUNCTION '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'FUNCTION') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;


--SINONIMOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP SYNONYM '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'SYNONYM') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;


--PACKAGE-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP PACKAGE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'PACKAGE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;


--SECUENCIAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP SEQUENCE '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'SEQUENCE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;


--TIPOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TYPE '||OWNER||'."'||OBJECT_NAME||'"' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TYPE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;


--VISTAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP VIEW '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'VIEW') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;

--VISTAS MATERIALIZADAS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP MATERIALIZED VIEW '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'MATERIALIZED VIEW') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;

--TIPOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TYPE '||OWNER||'."'||OBJECT_NAME||'"' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TYPE') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;

--BODY TIPOS-------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP TYPE BODY '||OWNER||'."'||OBJECT_NAME||'"' AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'TYPE BODY') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;

--SINONIMOS -------------------------------------------------------------------------------------
FOR C IN
(SELECT 'DROP SYNONYM '||OWNER||'.'||OBJECT_NAME AS COMANDO
FROM DBA_OBJECTS
WHERE OWNER = V_USER
AND OBJECT_TYPE = 'SYNONYM') LOOP
BEGIN
   EXECUTE IMMEDIATE C.COMANDO;
EXCEPTION
   WHEN OTHERS THEN 
   NULL;
END;
END LOOP;

END;
/