I got en error when attempting to use a BULK COLLECT
statement ORA-00947: not enough values
for table of objects.
Error occurs at the line from (select jta.nobject_id,
CREATE OR REPLACE TYPE "T_PPW_WORK" as object
(
nObjectKey number,
cJobType varchar2(500),
dPlanStart date,
dPlanEnd date,
cExecutor varchar2(500),
cComment varchar2(4000)
)
CREATE OR REPLACE TYPE "T_PPW_WORK_TABLE" as table of T_PPW_WORK;
function getPlannedOverdueJobs(in_nPlanKey number) return T_PPW_WORK_TABLE is
l_oWorks T_PPW_WORK_TABLE;
l_oWork T_PPW_WORK;
begin
select * bulk collect
into l_oWorks
from (select jta.nobject_id,
jt.cjobtype_name,
jta.dactual_start,
jta.dactual_finish,
st.familiya,
jta.ccomment
from ppw_jobtype_assign jta
left join pgts_sotrudnik st
on jta.nworkerid = st.npgts_sotrudnikkey
join ppw_jobtype jt
on jta.njobtype_id = jt.njobtype_key);
return l_oWorks;
end getPlannedOverdueJobs;
what is the reason?
select * bulk collect
into l_oWorks
from (select t_ppw_work(jta.nobject_id,
jt.cjobtype_name,
jta.dactual_start,
jta.dactual_finish,
st.familiya,
jta.ccomment)
from ppw_jobtype_assign jta
left join pgts_sotrudnik st
on jta.nworkerid = st.npgts_sotrudnikkey
join ppw_jobtype jt
on jta.njobtype_id = jt.njobtype_key);
You need to convert your result set using your defined object type (t_ppw_work) first.