PL/SQL how do I select into table type local variable

Question

PROCEDURE "ARCHIVE_CASE_LIST"
(
    a_case_id_list IN INLISTNUMBERS
)
IS
  l_customers INLISTNUMBERS;

INLISTNUMBERS is Oracle table of numbers;

How do I prepare pre-calculated list of customers and store them in l_customers, so that I don't need to use that long select statement in other update/select statements?

  insert into table(l_customers) <-- fail
    select distinct case1.customer_id into l_customers from case case1
      where case1.case_id in (select column_value from table(a_case_id_list)) and
        not exists (select 0 from case case2 where case2.customer_id = case1.customer_id and
          case2.lifecycle_code not in (code_id('LIFECYCLE','A'), code_id('LIFECYCLE','D')));

  update customer set customer.lifecycle_code = code_id('LIFECYCLE','A')
    where customer.customer_id in (select column_value from table(l_customers));

  open l_persons for 
    select person_id from person where person.customer_id in
      (select column_value from table(l_customers));

Codo · Accepted Answer · 2011-04-10 12:08:24Z

11

Use a SELECT statement with BULK COLLECT:

select distinct case1.customer_id bulk collect into l_customers
from case case1
  where case1.case_id in (select column_value from table(a_case_id_list)) and
    not exists (select 0 from case case2 where case2.customer_id = case1.customer_id and
      case2.lifecycle_code not in (code_id('LIFECYCLE','A'), code_id('LIFECYCLE','D')));

answered Apr 10, 2011 at 12:08

Codo

79.7k18 gold badges176 silver badges218 bronze badges

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

Collectives™ on Stack Overflow

PL/SQL how do I select into table type local variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related