Skip to Main Content
  • Questions
  • Looping for Nested dimensional objects in an array

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 26, 2017 - 9:33 pm UTC

Last updated: January 27, 2017 - 8:32 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I have

CLIENT_ORDER_OBJ as OBJECT
(order_id number,
Order_type varchar2(25));

CLIENT_CITY_OBJ as OBJECT
(city VARCHAR2(25))


TEST_OBJ is OBJECT with below attributes
(client_id VARCHAR2(25),
client_name VARCHAR2(25),
client_order CLIENT_ORDER_OBJ,
client_city CLIENT_CITY_OBJ);

TEST_ARRAY which is a VARRAY of TEST_OBJ

----
create or replace PROCEDURE test_nested_object(
p_test_array IN TEST_ARRAY)
AS
BEGIN
FOR i IN p_test_array.FIRST..p_test_array.LAST
LOOP
FOR j in p_test_array(i).client_order.FIRST..p_test_array(i).client_order.LAST
LOOP
FOR k in p_test_array(i).client_city.FIRST..p_test_array(i).client_city.LAST
LOOP
dbms_output.put_line(k.city);
END LOOP;
END LOOP;
END LOOP;

END;
/

I have to run a loop for CLIENT_CITY_OBJ within CLIENT_ORDER_OBJ Loop within TEST_OBJ and then do business logic. I have tried the below procedure but getting error in "FOR j in.." section. How do I loop through the nested client_order OBJECT and client_city OBJECTS?

and Chris said...

CLIENT_CITY_OBJ isn't within CLIENT_ORDER_OBJ and neither are collections. So you can't loop through them!

You just reference them directly:

create or replace procedure test_nested_object (
    p_test_array in test_array )
as
begin
  for i in p_test_array.first..p_test_array.last
  loop
    dbms_output.put_line ( p_test_array ( i ).client_order.order_id );
    dbms_output.put_line ( p_test_array ( i ).client_city.city ) ;
  end loop;
end;
/


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library