Is it possible to have a loop in SQL without creating a stored procedure?

Question

I want 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: Update sde.st_geometry M values to cumulative distance (geometric length)

I think 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 don't have CREATE PROCEDURE permissions, so I can't make a stored procedure.

As an alternative to creating a stored procedure, is there a way to loop through the vertices using an SQL statement instead?

Oracle 12c (12.1.0.2.0)

• user953: The model clause has iterations in it. Not sure about the gis part but if you can use the model clause that would performance wise almost certainly be a winner. If readability and maintainability counts .... It is pretty advanced use of sql
• User1974: @ik_zelf : I wonder if the recursive WITH clause could be used.
• user953: I have my doubts. Recursion can be very elegant when used in their type of problems. The model clause is aimed at working with spreadsheet style organized data. Iterations are an important control in there and not a side effect.

Answer 1

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.

• Gandolf989: Essentially you can do almost anything with an anonymous PL/SQL block that you can do with a procedure or package.
• User1974: 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.