Abstract
This article contains informations about the Oracle DBMS in a compact format.
Problem & Solution
PL/SQL Script Drop Table If Exists
Regretfully a simple mechanism like DROP TABLE IF EXISTS known from PostgreSQL or MariaDB/MySQL still doesn’t exists in Oracle SQL. However you can use PL/SQL and Dynamic SQL to emulate its behaviour.
BEGIN
-- (...)
SELECT count(*) INTO c from user_tables WHERE table_name = UPPER(val_table_name);
IF c > 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || val_table_name;
END IF;
-- (...)
END;
PL/SQL Script to remove duplicate vertices in all registered spatial tables
Spatial tables may contain a lot of troubling geometries. In the GIS world you usually have to cleanup incorrect geometries before a spatial dbms can use them in an accurate and performant manner. However the spatial system of Oracle DBMS provides you a lot of tools to handle these corrupt geometries. A common problem are duplicate vertices in polygon rings. To solve these you can use the function:
SDO_UTIL.REMOVE_DUPLICATE_VERTICES
To remove duplicate vertices in all registered spatial tables you can use the following pl/sql script. But be warned that this can take a long time, depending on the size of the tables.
SET SERVEROUTPUT ON; DECLARE v_sql VARCHAR(2000); c int; val_table_name VARCHAR(2000); BEGIN DBMS_OUTPUT.ENABLE; FOR spatial_table IN (SELECT * FROM USER_SDO_GEOM_METADATA) LOOP v_sql := 'UPDATE ' || spatial_table.table_name ||' c SET ' || spatial_table.column_name || '=SDO_UTIL.REMOVE_DUPLICATE_VERTICES(c.' || spatial_table.column_name || ', 0.005)'; DBMS_OUTPUT.PUT_LINE( v_sql ); EXECUTE IMMEDIATE v_sql; COMMIT; END LOOP; END;