Selecting all SDO_GTYPE values for all tables/sdo_geometry columns in a schema
A question was asked on the Oracle Technology Network Spatial forum:
I am trying to select sdo_gtype from all tables in schema, i mean to execute same sql statement given below for entire schema.
SELECT a.geometry.sdo_gtype FROM centerline a;
Any help on the usage of dynamic sql in PLSQL to execute select statement for all tables in the schema.
Here is a handy piece of anonymous PL/SQL for finding the sdo_gtype values for all sdo_geometry columns of data in any table/object in a schema.
SET serveroutput ON SIZE unlimited
DECLARE
v_sql varchar2(4000);
v_gtype varchar2(40);
v_gtype_count NUMBER;
c_gtypes sys_refcursor;
BEGIN
FOR rec IN (SELECT TABLE_NAME, column_name
FROM user_tab_columns
WHERE data_type = 'SDO_GEOMETRY'
ORDER BY TABLE_NAME, column_name) loop
BEGIN
v_sql := 'SELECT a.' || rec.column_name || '.sdo_gtype gtype, count(*) as gtype_count ' ||
' FROM ' || rec.TABLE_NAME || ' a ' ||
' WHERE a.' || rec.column_name || ' is not null ' ||
' GROUP BY a.' || rec.column_name || '.sdo_gtype';
OPEN c_gtypes FOR v_sql;
FETCH c_gtypes INTO v_gtype, v_gtype_count;
IF ( c_gtypes%NOTFOUND ) THEN
dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||' has no data.');
ELSE
LOOP
dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||' = ' ||
(CASE WHEN v_gtype IS NULL THEN 'NULL' ELSE to_char(v_gtype) END) ||
' Count(' || v_gtype_count || ')');
FETCH c_gtypes INTO v_gtype, v_gtype_count;
EXIT WHEN c_gtypes%NOTFOUND;
END LOOP;
END IF;
CLOSE c_gtypes;
exception
WHEN others THEN
dbms_output.put_line('Query (' || v_sql || ') threw error (' || SQLCODE ||')');
END;
END loop;
END;
/
-- Results
anonymous block completed
GEODPOINT2D.GEOM = 2001 COUNT(500)
GEODPOINT3D.GEOM = 3001 COUNT(500)
GEODPOLY2D.GEOM = 2003 COUNT(1)
GUTDATA.GEOM = 2001 COUNT(1000)
HAS_METADATA.GEOM = 2001 COUNT(500)
HAS_METADATA3D.GEOM = 3001 COUNT(500)
LOCALLINE2D.GEOM = 2002 COUNT(2)
LOCALPOINT2D.GEOM = 2001 COUNT(1)
LOCALPOLY2D.GEOM = 2007 COUNT(1)
LOCALPOLY2D.GEOM = 2003 COUNT(1)
LRS_ROUTES.ROUTE_GEOMETRY = 3302 COUNT(4)
MARK_A.GEOM = 2001 COUNT(25)
MARK_EMPTY.GEOM has no DATA.
MARK_L.GEOM = 3001 COUNT(4)
NO_METADATA.GEOM = 2001 COUNT(500)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2005 COUNT(4)
ORACLE_TEST_GEOMETRIES.GEOMETRY = NULL COUNT(1)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2112 COUNT(1)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2001 COUNT(3)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2006 COUNT(8)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 4002 COUNT(1)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2007 COUNT(8)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 3002 COUNT(1)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 3302 COUNT(1)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2003 COUNT(17)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2004 COUNT(2)
ORACLE_TEST_GEOMETRIES.GEOMETRY = 2002 COUNT(18)
ORIENTED_POINT.GEOM = 3001 COUNT(1533)
PROJ41014POLY2D.GEOM = 2003 COUNT(2)
PROJ41914POLY2D.GEOM = 2003 COUNT(1)
PROJCIRCLE2D.GEOM = 2003 COUNT(500)
PROJCOMPOUND2D.GEOM = 2004 COUNT(3)
PROJLINE2D.GEOM = 2002 COUNT(5)
PROJLINE3D.GEOM = 3002 COUNT(2)
PROJMULTILINE2D.GEOM = 2006 COUNT(500)
PROJMULTIPOINT2D.GEOM = 2005 COUNT(9)
PROJMULTIPOINT3D.GEOM = 3005 COUNT(1)
PROJMULTIPOLY2D.GEOM = 2007 COUNT(500)
PROJPOINT2D.GEOM = 2001 COUNT(500)
PROJPOINT3D.GEOM = 3001 COUNT(500)
PROJPOLY2D.GEOM = 2003 COUNT(8)
PROJPOLY3D.GEOM = 3003 COUNT(2)
TOOLSPOLY2D.CENTROID = 2001 COUNT(1)
TOOLSPOLY2D.GEOM = 2003 COUNT(2)
V_GEODPOINT2D.GEOM = 2001 COUNT(500)
V_GEODPOLY2D.GEOM = 2003 COUNT(1)
I hope this helps someone out there.