select inside loop oracle

Question

I have written a stored procedure with a query inside a loop. This query sets the records into a custom data type of the type RECORD something like

TYPE finalrecord
IS
  RECORD
  (
    corh           VARCHAR2(1),
    myspissueid    NUMBER(10),
    mypkey         VARCHAR2(10),
    mycreated      DATE,
    myprevstepname VARCHAR2(10),
    mystepname     VARCHAR2(10),
    mystorypoints  NUMBER(2) );

  myfinalrecord finalrecord;

The for loop goes like

for vh in (select * from table1 where abc=3)
loop

select steps.current_or_history,
    steps.issueid,
    steps.pkey,
    steps.created,
    steps.prev_step_name,
    steps.step_name,
    steps.story_points

from steps where column1 = 'xyz' and column2=vh.column2;


end loop;

Every time the inner loop is executed, the SELECT statement would return more than one record. I want to add this record to a main variable (as a collection..but varray or nested table or associative array) and return that variable as a output of the stored procedure.

Any idea?

CanFil · Accepted Answer · 2014-05-02 12:32:06Z

1

declare
  type t is table of finalrecord;
  my_table t;
begin
  for vh in (select * from table1 where abc = 3) loop

    execute immediate 'select finalrecord(steps.current_or_history,
        steps.issueid,
        steps.pkey,
        steps.created,
        steps.prev_step_name,
        steps.step_name,
        steps.story_points)

    from steps where column1 = ''xyz'' and column2=vh.column2' bulk
                      collect
      into my_table;

  end loop;
end;

you can try this if it works you can also create procedure...

answered May 2, 2014 at 12:32

CanFil

3252 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kannan Sundararaj Over a year ago

How do i return this "my_table"? i mean the return type of this procedure should be this "my_table"

Collectives™ on Stack Overflow

select inside loop oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related