Hi Community,
I am trying to figure out how I can summarize data from a Collection (Nested Table) for insert into a target table. I've been searching around quite a bit, but have struggled to find a solution and would be truly appreciative for any help pointing out the error(s) of my ways.
At a high-level, I'm trying to accomplish:
1) Read detail data into a collection
2) Iterate through the collection to perform numerous data updates
3) Summarize the data at a higher level, for insert into a target table.
An example business scenario:
1) Read in detail GL Journal lines of payroll cost data,
2) Perform updates to that data to transform the data to create payroll accrual records,
3) Create the new Payroll Accrual GL Journal lines at a summarized level, higher than the original detail lines.
I know how to use some other methods to accomplish this (e.g. to use a global temp table instead of a collection), but I'm trying to get it done within the single PL/SQL package without having to create another GT table. I'm trying to use a collection, because I think it allows for easy layout and maintenance of the various update logic, along with other benefits that using collections provide.
I've attached some DDL (table, data, package) for a basic example of what I'm trying to do (my actual scenarios have a lot more going on).
My problem is around the following piece ( l_accrual_lines is my collection name), which throws the below errors:
INSERT INTO gl_lines
SELECT
je_cat,
dept,
org,
account,
exp_type,
SUM(debit),
SUM(credit)
FROM
TABLE ( l_accrual_lines )
GROUP BY
je_cat,
dept,
org,
account,
exp_type;
Error(32,5): PL/SQL: SQL Statement ignored
Error(34,10): PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Error(34,16): PLS-00642: local collection types not allowed in SQL statements
Thanks so much!!
Larry