大容量收集到对象的表类型
大容量收集到对象的表类型
提问于 2014-05-26 10:28:04
当我试图对对象表使用BULK COLLECT语句ORA-00947: not enough values时，我遇到了错误。
在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;原因何在？
回答 2
Stack Overflow用户
发布于 2014-05-26 12:40:02
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);您需要首先使用定义的对象类型(t_ppw_work)转换结果集。
Stack Overflow用户
发布于 2018-07-11 02:03:29
你可以像下面这样做。
CREATE OR REPLACE EDITIONABLE TYPE "F_OBJ" AS OBJECT (
Employee_name VARCHAR2(100),
Employee_id VARCHAR2 ( 100 ))
CREATE OR REPLACE EDITIONABLE TYPE "F_TAB" as table of F_OBJ
create or replace function "fname"
return f_tab
is
l_f_tab f_tab;
begin
SELECT f_obj(employee_name, employee_id) bulk collect into f_tab from employee_table;
return f_tab;
end;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23861927
复制相关文章
相似问题
腾讯云开发者
