Loop through record type var and insert values to target table using pl/sql

I have this table TABLE_BB (firstname, lastname, id).

I also have a record type:

EMPREC is record(fname varchar,lname varchar2,idty varchar2);
type emprectype is TABLE OF EMPREC INDEX BY BINARY_INTEGER;

I need to call a procedure and loop over the record type variable value and insert into target TABLE_BB:

CREATE OR REPLACE PACKAGE MYPKG IS
emprec is record(fname varchar,lname varchar2,idty varchar2);
type  is table of emprectype INDEX BY BINARY_INTEGER; 
PROCEDURE proc_aaa (    
p_emp      IN emprectype,
x_status_code   OUT VARCHAR2,
x_error_message OUT VARCHAR2
);
END MYPKG;

CREATE OR REPLACE PACKAGE body MYPKG as
PROCEDURE proc_aaa (    
p_emp      IN emprectype,
x_status_code   OUT VARCHAR2,
x_error_message OUT VARCHAR2
) IS

BEGIN
FOR i in 1 .. p_emp.count
LOOP
insert into table_BB values (p_emp(i).fname,p_emp(i).lname,p_emp(i).idty);
END LOOP;

END proc_aaa;
    

But the compilation states that p_emp is not a cursor.

How to iterate over this variable value?

You:

1. Are missing the TYPE keyword in the emprec declaration;
2. Can use %TYPE in the declaration of the record;
3. Have not got an identifier for the TABLE type; and
4. Should use FORALL (rather than a FOR loop) - assuming that your indexes start a 1 and are contiguous; if you can have a sparse collection then you should use START and NEXT to iterate over the collection:

Like this:

CREATE OR REPLACE PACKAGE MYPKG
AS
  TYPE emprec IS RECORD(
    fname TABLE_BB.FNAME%TYPE,
    lname TABLE_BB.LNAME%TYPE,
    idty  TABLE_BB.IDTY%TYPE
  );
  TYPE emprectype IS TABLE OF emprec INDEX BY BINARY_INTEGER;

  PROCEDURE proc_aaa (    
    p_emp      IN emprectype,
    x_status_code   OUT VARCHAR2,
    x_error_message OUT VARCHAR2
  );
END MYPKG;
/

CREATE OR REPLACE PACKAGE body MYPKG as
  PROCEDURE proc_aaa (    
    p_emp      IN emprectype,
    x_status_code   OUT VARCHAR2,
    x_error_message OUT VARCHAR2
  )
  IS
  BEGIN
    FORALL i IN 1 .. p_emp.COUNT
      insert into table_BB values (p_emp(i).fname,p_emp(i).lname,p_emp(i).idty);
  END proc_aaa;
END;
/

Then, if you have the table:

CREATE TABLE table_bb (
  fname VARCHAR2(255),
  lname VARCHAR2(255),
  idty VARCHAR2(255)
)

You can use:

DECLARE
  v_emps MYPKG.EMPRECTYPE;
  v_status VARCHAR2(4000);
  v_error  VARCHAR2(4000);
BEGIN
  v_emps(1).FNAME := 'Alice';
  v_emps(1).LNAME := 'Abbot';
  v_emps(1).IDTY  := 'AAAAA';
  v_emps(2).FNAME := 'Beryl';
  v_emps(2).LNAME := 'Baron';
  v_emps(2).IDTY  := 'BBBBB';
  mypkg.proc_aaa(v_emps, v_status, v_error);
END;
/ 

And table_bb will contain:

fiddle