Drop Tablespace If Do Not Exist

I have written pl/sql script (works, but doesn't look nice): DECLARE v_exists NUMBER; BEGIN SELECT count(*) INTO v_exists FROM dba_tablespaces WHERE tablespace_name = 'hr_tes

Solution 1:

No. You cannot issue DDL statements in static PL/SQL.

And yes, it is perfectly fine to use native dynamic SQL for DDL purposes:

You need dynamic SQL in the following situations:

You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL, such statements cannot be executed statically.

Oracle dynamic SQL