How to populate nested object table in pl/sql block?

Question

I struggle a problem, which, i think, is rather simple.

I have a type T_OPERATION_TAG in a database which is created as:

CREATE OR REPLACE TYPE t_operation_tag AS OBJECT(
  tag_name  VARCHAR2(30),
  tag_value VARCHAR2(30),
  CONSTRUCTOR FUNCTION t_operation_tag RETURN SELF AS RESULT
)

I also have another type T_OPERATION_TAGS, which is defined as follows

CREATE OR REPLACE TYPE t_operation_tags AS TABLE OF t_operation_tag;

Then in my pl/sql block i have the following code

DECLARE
   p_op_tags     t_operation_tags;
BEGIN

   p_op_tags := t_operation_tags();
   FOR i IN (SELECT tag_name, tag_value 
              FROM op_tags_table
             WHERE some_condition)
   LOOP

   --How to append new lines to p_op_tags ?

   END LOOP;

END;

So, if the SELECT-query in the FOR LOOP returns,e.g., five lines then how can I populate my P_OP_TAGS object table with these five lines?

neshkeev · Accepted Answer · 2014-08-05 12:07:44Z

Like this:

DECLARE
   p_op_tags t_operation_tags;
   p_cursor sys_refcursor;
   p_limit number := 5;
BEGIN
 open p_cursor for
     SELECT t_operation_tag(tag_name, tag_value)
       FROM op_tags_table
  ;
  fetch p_cursor bulk collect into p_op_tags limit p_limit;
  DBMS_OUTPUT.put_line(p_op_tags(4).tag_name);
  close p_cursor;
END;

Or if you prefer the loop clause:

DECLARE
  p_op_tag t_operation_tag;
  p_op_tags t_operation_tags;
  p_limit number := 5;
BEGIN
  p_op_tags := t_operation_tags();
  for i in (SELECT tag_name, tag_value 
          FROM op_tags_table
         WHERE some_condition
           and rownum < p_limit + 1)
  loop
    p_op_tag := t_operation_tag(i.tag_name, i.tag_value);
    p_op_tags.extend();
    p_op_tags(p_op_tags.COUNT) := p_op_tag;
  end loop;
  DBMS_OUTPUT.put_line(p_op_tags(4).tag_name);
END;
/

Thanks a lot. The second solution is what i was searching for.

Alex Poole · Accepted Answer · 2014-08-05 11:26:54Z

2

You don't really need a cursor or loop at all, if you're populating the collection entirely from your query; you can bulk collect straight into it:

DECLARE
   p_op_tags     t_operation_tags;
BEGIN
   SELECT t_operation_tag(tag_name, tag_value)
   BULK COLLECT INTO p_op_tags
   FROM op_tags_table
   WHERE some_condition;

   ...
END;
/

answered Aug 5, 2014 at 11:26

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Collectives™ on Stack Overflow

How to populate nested object table in pl/sql block?

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related