Iterate over table of objects in Oracle

Question

How should I iterate over a table of objects like this?

CREATE TYPE SOME_OBJECT AS OBJECT (
  ATTR1 VARCHAR2(20)
, ATTR2 VARCHAR2(30)
);
/

CREATE TYPE C_SOME_OBJECT AS TABLE OF SOME_OBJECT;
/

And... I want to use this as a parameter for a procedure. Have to initialize it in procedure?

PROCEDURE SOME_PROCEDURE(OBJECT IN C_SOME_OBJECT)
IS
BEGIN
  --Some code here iterating IN parameter
END;

Answer 1

-- NOTE #1
-- You cant use Object as name for your input variable because it is a type
-- (Types are reserved words)
PROCEDURE SOME_PROCEDURE(p_SOME_TABLE IN C_SOME_OBJECT)
IS
BEGIN
  FOR i IN p_SOME_TABLE.FIRST .. p_SOME_TABLE.LAST
  LOOP
     -- NOTE #2 Manage it like this
     -- Current iteration: p_SOME_TABLE(i)
     -- Access Example   : p_SOME_TABLE(i).ATTR1

     -- NOTE #3 Or you can assign current iteration to a variable
     -- then use that variable thought it is not by reference if you
     -- do it like this.
  END LOOP;
END;

UPDATE:

Also be careful not to confuse RECORDS with OBJECTS.

An OBJECT is an SQL type but RECORD on the other hand is a PL/SQL type, you could think of it more of a C/C++ like struct.

UPDATE_2:

Note that this wont raise any exception if the table is empty thus you need to handle if there are no data, for example you could use .COUNT like this:

IF p_SOME_TABLE.COUNT = 0 THEN
   RAISE my_exception;
END IF;

Answer 2

You can use a WHILE loop with FIRST, NEXT, LAST to handle sparse collections (where elements have been deleted) and you also need to check for uninitialised elements in the collection:

CREATE PROCEDURE SOME_PROCEDURE(
  I_OBJECT IN C_SOME_OBJECT
)
IS
  i PLS_INTEGER;
BEGIN
  -- Check that the collection is not NULL
  IF I_OBJECT IS NULL THEN
    RETURN;
  END IF;

  i := I_OBJECT.FIRST;
  WHILE i IS NOT NULL LOOP
    IF I_OBJECT(i) IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE( i || ' = (' || I_OBJECT(i).attr1 || ', ' || I_OBJECT(i).attr2 || ')' );
    ELSE
      DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
    END IF;
    i := I_OBJECT.NEXT(i);
  END LOOP;
END;
/

Then you can call it:

DECLARE
  o C_SOME_OBJECT := C_SOME_OBJECT(
                       SOME_OBJECT( '1.1', '1.2' ),
                       SOME_OBJECT( '2.1', '2.2' ),
                       NULL,
                       SOME_OBJECT( '4.1', '4.2' )
                     );
BEGIN
  o.DELETE(2);

  SOME_PROCEDURE( o );
END;
/

Outputs:

1 = (1.1, 1.2)
3 IS NULL
4 = (4.1, 4.2)

Issues with FOR ... LOOP:

Using FOR i IN 1 .. o.COUNT LOOP:

DECLARE
  o C_SOME_OBJECT := C_SOME_OBJECT(
                       SOME_OBJECT( '1.1', '1.2' ),
                       SOME_OBJECT( '2.1', '2.2' ),
                       NULL,
                       SOME_OBJECT( '4.1', '4.2' )
                     );
BEGIN
  o.DELETE(2);

  FOR i IN 1 .. o.COUNT LOOP
    IF o(i) IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE( i || ' = (' || o(i).attr1 || ', ' || o(i).attr2 || ')' );
    ELSE
      DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
    END IF;
  END LOOP;
END;
/

Does not run and raises the exception:

ORA-01403: no data found
ORA-06512: at line 12

Because it reaches the second item in the array and tries to read it but it has been deleted and no data is found.

If the exception is caught:

DECLARE
  o C_SOME_OBJECT := C_SOME_OBJECT(
                       SOME_OBJECT( '1.1', '1.2' ),
                       SOME_OBJECT( '2.1', '2.2' ),
                       NULL,
                       SOME_OBJECT( '4.1', '4.2' )
                     );
BEGIN
  o.DELETE(2);

  FOR i IN 1 .. o.COUNT LOOP
    BEGIN
      IF o(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE( i || ' = (' || o(i).attr1 || ', ' || o(i).attr2 || ')' );
      ELSE
        DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE( i || ' NOT FOUND' );
    END;
  END LOOP;
END;
/

Then the output is:

1 = (1.1, 1.2)
2 NOT FOUND
3 IS NULL

This outputs element 2, which does not exist, and does not output element 4, which does exist.

Using FOR i IN o.FIRST .. o.LAST LOOP:

DECLARE
  o C_SOME_OBJECT := C_SOME_OBJECT(
                       SOME_OBJECT( '1.1', '1.2' ),
                       SOME_OBJECT( '2.1', '2.2' ),
                       NULL,
                       SOME_OBJECT( '4.1', '4.2' )
                     );
BEGIN
  o.DELETE(2);

  FOR i IN o.FIRST .. o.LAST LOOP
    BEGIN
      IF o(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE( i || ' = (' || o(i).attr1 || ', ' || o(i).attr2 || ')' );
      ELSE
        DBMS_OUTPUT.PUT_LINE( i || ' IS NULL' );
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE( i || ' NOT FOUND' );
    END;
  END LOOP;
END;
/

Outputs:

1 = (1.1, 1.2)
2 NOT FOUND
3 IS NULL
4 = (4.1, 4.2)

This outputs 4 elements (including the deleted 2nd element) when o.COUNT is only 3; meaning that the code is doing extra, unnecessary processing.

db<>fiddle here