set serveroutput on DECLARE
TYPE type_id IS
    TABLE OF policies_tab.id%TYPE;
TYPE type_create_dat IS
    TABLE OF policies_tab.create_dat%TYPE;
t_id         type_id;
t_create_dat type_create_dat;
BEGIN

select id,create_dat  BULK COLLECT INTO t_id,t_create_dat from policies_tab where        substr(id,-1) in(6);

FORall i in t_id.first .. t_id.last
update policy_aggr set create_dat =t_create_dat(i).create_dat
where pol_id =t_id(i).id;
--dbms_output.put_line(t_id(i));
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('Error while updating record in policy_aggr table' ||   sqlerrm);
    ROLLBACK;
end;

When compiling getting error t_id and t_id(i).id must be declared even though I declared on top Could someone explain what I'm missing here and what is the cause of error

set serveroutput on DECLARE
TYPE type_id IS
    TABLE OF policies_tab.id%TYPE;
TYPE type_create_dat IS
    TABLE OF policies_tab.create_dat%TYPE;
t_id         type_id;
t_create_dat type_create_dat;
BEGIN

select id,create_dat  BULK COLLECT INTO t_id,t_create_dat from policies_tab where        substr(id,-1) in(6);

FORall i in t_id.first .. t_id.last
update policy_aggr set create_dat =t_create_dat(i).create_dat
where pol_id =t_id(i).id;
--dbms_output.put_line(t_id(i));
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('Error while updating record in policy_aggr table' ||   sqlerrm);
    ROLLBACK;
end;

When compiling getting error t_id and t_id(i).id must be declared even though I declared on top Could someone explain what I'm missing here and what is the cause of error

• sql
• oracle-database
• plsql
• plsql-package

2 Answers 2

You don't need a collection (or PL/SQL) and can do it in a single MERGE statement:

MERGE INTO policy_aggr dst
USING (
  SELECT id,
         create_dat
  FROM   policies_tab
  WHERE  SUBSTR(id,-1) = '6'
) src
ON (src.id = dst.pol_id)
WHEN MATCHED THEN
  UPDATE
  SET create_dat = src.create_dat;

Which, for the sample data:

CREATE TABLE policy_aggr(pol_id, create_dat) AS
SELECT '16', DATE '1900-01-01' FROM DUAL UNION ALL
SELECT '16', DATE '1900-01-01' FROM DUAL UNION ALL
SELECT '26', DATE '1900-01-01' FROM DUAL UNION ALL
SELECT '35', DATE '1900-01-01' FROM DUAL;

CREATE TABLE policies_tab(id, create_dat) AS
SELECT '16', DATE '2000-01-01' FROM DUAL UNION ALL
SELECT '26', DATE '2010-01-01' FROM DUAL UNION ALL
SELECT '35', DATE '2020-01-01' FROM DUAL;

Then, after the MERGE, policy_aggr contains:

If you want to fix your code then your collections are tables of scalar values (and not tables of records) so trying to use .column_name is not valid syntax.

DECLARE
  TYPE type_id         IS TABLE OF policies_tab.id%TYPE;
  TYPE type_create_dat IS TABLE OF policies_tab.create_dat%TYPE;
  t_id         type_id;
  t_create_dat type_create_dat;
BEGIN
  SELECT id,create_dat
  BULK COLLECT INTO t_id, t_create_dat
  FROM   policies_tab
  WHERE  SUBSTR(id,-1) = '6';

  FORALL i IN 1 .. t_id.COUNT
    UPDATE policy_aggr
    SET    create_dat = t_create_dat(i)
    WHERE  pol_id = t_id(i);
END;
/

fiddle

Please find more about BULK COLLECT and FORALL here.

--      S a m p l e    D a t a :
Create Table policies_tab AS
Select Cast( 6 as Number(6) ) as ID, To_Date('01.11.2024', 'dd.mm.yyyy') as CREATE_DAT From Dual Union All
Select 16, To_Date('05.11.2024', 'dd.mm.yyyy') From Dual Union All
Select 21, To_Date('18.11.2024', 'dd.mm.yyyy') From Dual ;

Create Table policy_aggr AS
Select Cast( 6 as Number(6) ) as POL_ID, To_Date('01.10.2024', 'dd.mm.yyyy') as CREATE_DAT From Dual Union All
Select 16, Null From Dual Union All
Select 21, Null From Dual ;

Select * From policies_tab;

Select * From policy_aggr;

Try it like here - your code slightly adjusted ...

Declare
    TYPE type_id IS TABLE OF policies_tab.id%TYPE
        INDEX BY PLS_INTEGER;
    t_id         type_id;
    --
    TYPE type_create_dat IS TABLE OF policies_tab.create_dat%TYPE
        INDEX BY PLS_INTEGER;
    t_create_dat type_create_dat;
Begin
    Select id, create_dat  BULK COLLECT INTO t_id, t_create_dat 
    From   policies_tab 
    Where  Substr(id, -1) IN( '6' );
    --
    FOR i in 1..t_id.count LOOP  
        Update policy_aggr SET CREATE_DAT = t_create_dat(i)
        Where pol_id = t_id(i);
    END LOOP;
    --
EXCEPTION
WHEN OTHERS THEN
    dbms_output.put_line('Error while updating record in policy_aggr table' ||   sqlerrm);
    ROLLBACK;
End;
/

Select * From policy_aggr;

fiddle