CODEWITHSUNDEEP
oracle-databaseplsql
S1eth
S1eth

Reputation: 23

Bulk collect into specific columns of collection

TYPE t_project_financial_table IS TABLE OF project_financial%ROWTYPE;
g_project_financial_table t_project_financial_table;

The table project_financial has 15 columns. The select statement returns 2 columns. Can I bulk collect into 2 specific columns of the collection g_project_financial_table and leave the other columns null or do I have to bulk collect into 2 varrays (for each column) and loop over those to get the values of the 2 columns into the collection g_project_financial_table.

Something like BULK COLLECT INTO g_project_financial_table.column3, g_project_financial_table.column8 ?

SELECT k.tag, 
  (SELECT pa.available
   FROM pers_account pa
   WHERE pa.valid_from = 
     (SELECT MAX(pa2.valid_from)
      FROM pers_account pa2
      WHERE pa2.valid_from <= k.tag)) AS available

BULK COLLECT INTO g_project_financial_table ??????? 

FROM kalender k
WHERE k.tag BETWEEN to_date('20120430','YYYYMMDD')
                AND to_date('20120504','YYYYMMDD')
  AND k.ist_werktag = 1
ORDER BY k.tag;

Upvotes: 2

Views: 16853

Answers (2)

winkbrace
winkbrace

Reputation: 2711

You know that for large date ranges the performance of this query is becoming an issue, because you execute 2 extra queries for each row, right?

Anyway, my solution would be to collect into 2 collections if you want to bulk insert. But honoustly, this doesn't look like you are going to insert huge numbers of rows, so it might be easiest to just use a normal insert. Here's the bulk insert anyway.

create or replace procedure add_days_to_financial(p_date_from in date, p_date_to in date)
as
    cursor cur_kalender
    is
    SELECT k.tag, 
      (SELECT pa.available
       FROM pers_account pa
       WHERE pa.valid_from = 
         (SELECT MAX(pa2.valid_from)
          FROM pers_account pa2
          WHERE pa2.valid_from <= k.tag)) AS available
    FROM kalender k
    WHERE k.tag BETWEEN p_date_from and p_date_to
      AND k.ist_werktag = 1
    ORDER BY k.tag;

    type t_tag is table of kalender.tag%type;
    type t_available is table of kalender.available%type;

    arr_tag t_tag;
    arr_available t_available;
begin
    open cur_kalender;
    loop
        fetch cur_kalender bulk collect into arr_tag, arr_available limit 500;

        forall i in arr_tag.first .. arr_tag.last
            insert into project_financial
            (tag, available)
            values
            (arr_tag(i), arr_available(i));

        commit;
        exit when cur_kalender%notfound;
    end loop;

    close cur_kalender;

    commit;
exception
    when others then 
        -- log? 
        raise;
end;

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231771

You cannot collect data into just 2 columns of a 15 column record. You could, however, add 13 additional NULL columns in the appropriate positions to your SELECT statement. Or, as you suggested, you could bulk collect the data into two different collections.

Upvotes: 0

Related Questions