oracleloopsplsqltypesrecord

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?


Solution

  • 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:

    FNAME LNAME IDTY
    Alice Abbot AAAAA
    Beryl Baron BBBBB

    fiddle