How can I pass a list of pairs to an Oracle stored procedure?

Question

I want to write an Oracle PL/SQL stored procedure that takes as a parameter a list of pairs of some other type, say varchar2(32). Is this possible? What's the best way to do it?

Answer 1

It sounds like you just want to pass in a collection, i.e.

SQL> create type point as object (
  2    x_coordinate number,
  3    y_coordinate number );
  4  /

Type created.

SQL> create type point_array
  2  is
  3  table of point;
  4  /

Type created.

SQL> create procedure interpolate( points IN point_array )
  2  as
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

SQL> declare
  2    points point_array := point_array( point(0,1), point(1,1) );
  3  begin
  4    interpolate( points );
  5  end;
  6  /

PL/SQL procedure successfully completed.

Obviously, in reality, the function would do something with the array that was passed in, but that's the general idea.