Determine whether DROP TABLE will succeed

Is it possible to determine whether DROP TABLE table_name operation will succeed without executing it?

I tried to build function with autonomous transaction and rollback whether it is successfull or not. It looked like this:

CREATE FUNCTION is_droppable RETURN BOOLEAN AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE tablename';
    ROLLBACK;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;

This function was always dropping my table.

Indeed, Oracle notices that DROP is unROLLBACKable in their docs in several places.

But I still want be able to determine whether my table is droppable from inside of my code.

P.S. I think table is droppable when and only when other tables don't link to it. Maybe it is a possible way to solve my problem, but I'm here to avoid it. I look for an easier method.

Solution

You may drop table cascade constraints, thus the table will be dropped together with all the constraints referencing it. Or, to see the foreign keys referencing a table:

select cfk.*
from all_constraints cp
join all_constraints cfk on cfk.r_constraint_name=cp.constraint_name 
  and cfk.r_owner=cp.owner
where table_name=&table_name and constraint_type='P';

But, generally, it's not good to drop tables in dynamic SQL in stored procedures. Have you thought that those tables may be referred in other stored procedures, which will decompile?