Skip to Main Content

creating a stored procedure from a select query which has a join

Daniel TaitMay 20 2015 — edited May 21 2015


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.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.

Post Details
Added on May 20 2015