we have Oracle 11G
and i'm trying to move data from one table to another using bulk collect
. Problem is when I tried to evaluate if one field from origin is empty my package got invalidated. What I have:
Declaration:
CREATE OR REPLACE PACKAGE MYSCHEMA.MYPKG AS
CURSOR CUR_MYDATA IS
SELECT
o.name,
o.last_name,
o.id,
o.socnum
FROM
origin o
WHERE
1=1
AND o.name like upper ('a%');
TYPE t_name IS TABLE OF origin.name%TYPE;
TYPE t_lastname IS TABLE OF origin.last_name%TYPE;
TYPE t_id IS TABLE OF origin.id%TYPE;
TYPE t_socnum IS TABLE OF origin.socnum%TYPE;
l_name t_name;
l_lastname t_lastname;
l_id t_id;
l_socnum t_socnum;
PROCEDURE MYPROCEDURE;
END MYPKG;
Body:
CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS
PROCEDURE MYPROCEDURE IS
BEGIN
OPEN CUR_MYDATA;
LOOP
FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
forall i IN 1 .. l_name.COUNT
IF ( l_socnum(i) IS NULL)
THEN (select oo.socnum from other_origin where oo.id=l_id(i))
END IF;
INSERT INTO destiny (
d_name,
d_lastname,
d_id,
d_socnum)
VALUES (
l_name(i),
l_lastname(i),
l_id(i),
l_socnum(i),
EXIT WHEN l_name.count = 0;
END LOOP;
END MYPROCEDURE;
END MYPKG;
but when I check body status it is INVALID
any thoughs?
FORALL is not a loop construct: it cannot be split from its DML statement.
when I tried to evaluate if one field from origin is empty
You need to loop round the populated collection and fix that before executing the FORALL ... INSERT.
CREATE OR REPLACE PACKAGE BODY MYSCHEMA.MYPKG AS
PROCEDURE MYPROCEDURE IS
BEGIN
OPEN CUR_MYDATA;
LOOP
FETCH CUR_MYDATA BULK COLLECT INTO l_name,l_lastname,l_id,l_socnum;
EXIT WHEN l_name.count = 0;
for idx in 1 .. l_socnum.count() loop
IF l_socnum(idx) IS NULL THEN
select oo.socnum
into l_socnum(idx)
from other_origin
where oo.id = l_id(idx);
END IF;
end loop;
forall i IN 1 .. l_name.COUNT
INSERT INTO destiny (
d_name,
d_lastname,
d_id,
d_socnum)
VALUES (
l_name(i),
l_lastname(i),
l_id(i),
l_socnum(i));
END LOOP;
END MYPROCEDURE;
END MYPKG;
Other notes.
%rowtype
: this is simpler than defining and handling multiple collections based on columns.Also, your real code may be way more complicated than what you posted here, but if you have a large amount of data to shift there is a lot of performance gain in using pure SQL rather than a procedure:
INSERT INTO DESTINY (
D_NAME,
D_LASTNAME,
D_ID,
D_SOCNUM
)
SELECT
o.name,
o.last_name,
o.id,
coalesce(o.socnum, oo.socnum)
FROM
origin o
left outer join other_origin oo
on oo.id = o.id
WHERE
1=1
AND o.name like upper ('a%');