Hi,
Please bare with me - I'm new to PL/SQL.
I have written a query which I want turn into a stored procedure which returns one or more rows.
The query is:
WITH temp as
(
SELECT id, sdo_geom.validate_geometry(a.geometry, 0.005) as ORACLE_ERROR_NUMBER
FROM land_protection a
WHERE sdo_geom.validate_geometry(a.geometry, 0.005) != 'TRUE')
SELECT t.id, t.oracle_error_number, e.error_message
FROM temp t JOIN GIS_ANALYSIS.oracle_errors e
ON (t.oracle_error_number = e.error_number);
In this query the geometry column (geometry) in table 'land_protection' is being validated (using the sdo_geom.validate_geometry function) which returns the ora message number. This is then joined to a table which has the error message for each ora error number (table 'ORACLE_ERRORS').
What I would like is a stored procedure where table_name, primary_key column and geometry_column are arguments in the stored procedure.
For example, something like EXEC GEOMVAL (LAND_PROTECTION, ID, GEOMETRY)
I have no idea how this could be done. Any help would be much appreciated.