Inspect data and prepare catalog with PL/SQL … (Oracle case)

keywords: pl/sql, cursor, oracle, geoserver, sld

Sometimes happens that you have plenty of tables that you have to publish as layers on Geoserver and you have to produce SLD... it could be some other GIS tool or engine ...anyway, without some specification or catalog you will get a lot of overhead work.

Easy way to produce catalog that contains some basic elements such as geometry type and attribute values needed for symbology can be done using PL/SQL. In this particular case values of attribute VRSTA are important for symbology... of course, as it is always the case, the geometry type was also needed (point, line, polygon).

Using presented anonymous block you will get all distinct values in the field VRSTA (category) and geometry used for visualisation (GTYPE) as well as geometry field name for every table in the PPSV shema. To cover some different case that may happen you can modify cursors in the declare part of procedure and variables related to cursor.

This procedure is also good example how to create cursor that has table name as parameter (c_vrsta).

To make it more clear...
PPSV: database user/schema with tables
VRSTA(eng. category): column name in tables that is used for categorisation (by convention it is named VRSTA)


declare
 cursor c_tablice is select distinct(table_name) t_name from  all_tab_cols where owner = 'PPSV' and column_name = 'VRSTA' and table_name not like '%$%';
 cursor c_gtables is select table_name t_name, column_name c_name from  all_tab_cols where owner = 'PPSV' and  table_name not like '%$%' and data_type = 'SDO_GEOMETRY';

 type vrsta_type is ref cursor;
 c_vrsta vrsta_type;
 vrsta varchar2(1000);
 v_sql varchar2(1000);
begin
for  r_tablice in c_tablice
loop
  dbms_output.put_line('> TABLE: '||r_tablice.t_name);
      open c_vrsta for 'select distinct(VRSTA) d_vrsta from '||r_tablice.t_name;
          loop
       fetch c_vrsta into vrsta ;
             exit when c_vrsta%NOTFOUND;
             dbms_output.put_line('  VRSTA: '|| vrsta);
          end loop;
      dbms_output.put_line('');
      close  c_vrsta;
end loop;    

for  r_tablice in c_gtables
loop
  dbms_output.put_line('> TABLE: '||r_tablice.t_name || '   GEOMETRY FIELD: ' || r_tablice.c_name);
  v_sql := 'select distinct(tb.' || r_tablice.c_name || '.sdo_gtype) g_vrsta from '|| r_tablice.t_name || '  tb';
  --dbms_output.put_line(v_sql);
      open c_vrsta for v_sql;
          loop
       fetch c_vrsta into vrsta ;
             exit when c_vrsta%NOTFOUND;
             dbms_output.put_line('  GTYPE: '|| vrsta);
          end loop;
      dbms_output.put_line('');
      close  c_vrsta;
end loop;
end;
/