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:
TYPE
keyword in the emprec
declaration;%TYPE
in the declaration of the record;TABLE
type; andFORALL
(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 |