CREATE OR REPLACE PROCEDURE sp_fco_baja_tablas
(
    vvc_tabla    char
)
AS
    vvc_sql         varchar2(255);
    vin_id_cursor   integer;
    vin_rows	    integer;
BEGIN
    -- Eliminar la tabla
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, 'DROP TABLE ' || vvc_tabla, dbms_sql.v7);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error (-20001, 'Error al eliminar la tabla');
    END;

    -- Eliminar el sinonimo
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, 'DROP PUBLIC SYNONYM ' || vvc_tabla, dbms_sql.v7);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error (-20001, 'Error al eliminar el sinonimo de la tabla');
    END;


    -- Eliminar la informacion relacionada a la tabla
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, 'DELETE ADAM.fco_ala_datos_adi WHERE Upper(RTrim(tabla)) = ''' || Upper(RTrim(vvc_tabla)) || '''', dbms_sql.v7);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            raise_application_error (-20001, 'Error al eliminar los datos relacionados con la tabla');
    END;


    COMMIT;
    
END;
/
CREATE PUBLIC SYNONYM sp_fco_baja_tablas FOR ADAM.sp_fco_baja_tablas;
GRANT EXECUTE ON sp_fco_baja_tablas TO adama;
