1

I would like to loop through the vertices in a SHAPE/GEOMETRY column in a user-defined data type (ESRI's version of ST_GEOMETRY). I've posted the details in a GIS SE post here: https://gis.stackexchange.com/questions/209894/update-sde-st-geometry-m-values-to-cumulative-distance-using-oracle-sql

I believe this would normally be done in a stored procedure (probably in conjunction with the sde.ST_GEOMETRY functions ST_NumPoints and ST_PointN) . However, I'm merely a data analyst, not a DBA, and do not have CREATE PROCEDURE permissions, so I can't make a stored procedure. Additionally, in my company/organization, a request for a procedure to be made would either be rejected or take years to be implemented.

So I'm wondering, as an alternative to creating a stored procedure, if I could loop through the vertices within an SQL update statement instead?

Oracle 12c (12.1.0.2.0)

2

You can put a pl/sql in an anonymous block. Something like this might help:

declare
  v_vertices  your_user_defined_data_type;

  cursor your_cursor_c
  is
    select vertices
    from   your_table
    where  your_column = 'A VALUE';

begin
    open your_cursor_c;
    loop
      fetch your_cursor_c
      into  v_vertices;
      exit when your_cursor_c%notfound;

      ST_NumPoints(v_vertices);
      ST_PointN(v_vertices);

      commit;
    end loop;
end;

Also with 12c there is some inline procedure/function stuff that you can do. Not sure if it is necessary but here is a link: https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1.

5
  • 1
    Essentially you can do almost anything with an anonymous PL/SQL block that you can do with a procedure or package. – Gandolf989 Sep 13 '16 at 19:28
  • @Nick Thanks again. I hate to be dramatic, but this has changed everything. – Wilson Nov 17 '16 at 0:28
  • Haha glad it helps! – Nick S Nov 17 '16 at 0:39
  • The link about declaring functions in the WITH clause is especially helpful. I can make functions without actually having the CREATE FUNCTION privilege. Which is awesome. All I have to do is replace the word CREATE with the word WITH. – Wilson Nov 23 '16 at 22:06
  • WITH clause video: Better PL/SQL From SQL – Wilson Feb 23 '17 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.