I have code that is randomly getting "ORA-22814: attribute or element value is larger than specified in type" with a bulk collect into.
I suspect this is a data issue since it's random but PL/SQL is not my strong suit but I maintain an application that has a great deal of it in processing logic.
This is an Oracle 12c database.
Any help would be appreciated!
This is the block of code:
SELECT mxpv_activityupdate_O (
projectid,
task_id,
task_code,
pvtask_code,
task_name,
CASE
WHEN wbs_t0_id IS NOT NULL AND scgemergent_flag = 'Y'
THEN
wbs_t0_id
ELSE
wbs_pv_id
END,
act_start_date,
CASE
WHEN act_end_date IS NOT NULL AND (invalidactfinish_flag = 'Y')
THEN
NULL
ELSE
act_end_date
END,
NULL,
CASE WHEN invalidactuals_flag = 'Y' THEN NULL ELSE act_work_qty END,
MAX (oid),
scgemergent_flag,
invalidactuals_flag,
invalidactfinish_flag)
BULK COLLECT INTO u_all_tab
FROM (SELECT projectorganization,
projecttype,
projectid,
task_id,
task_code,
pvtask_code,
pvact_start_date,
task_name,
act_start_date,
act_end_date,
CASE
WHEN pvact_start_date IS NULL
AND act_start_date IS NULL
AND NVL (act_work_qty, 0) <> 0
THEN
'Y'
ELSE
'N'
END
invalidactuals_flag,
act_work_qty,
oid,
wbs_t0_id,
wbs_pv_id,
CASE
WHEN pvact_start_date IS NULL
AND (act_start_date IS NOT NULL --or nvl(act_work_qty,0)<>0
)
AND projectorganization = 'SCG'
AND projecttype = 'Daily'
AND pvtarget_start_date > v_t0_start_midnight + 7
THEN
'Y'
ELSE
'N'
END
scgemergent_flag,
CASE
WHEN act_end_date IS NOT NULL
AND NVL (act_start_date, pvact_start_date) IS NOT NULL
AND act_end_date <
NVL (act_start_date, pvact_start_date)
THEN
'Y'
ELSE
'N'
END
invalidactfinish_flag
FROM (SELECT oid,
projecttype,
wbs_t0.wbs_id wbs_t0_id,
--emergentwork,
projectorganization,
projectid,
task_id,
pvwbs_id wbs_pv_id,
activityid task_code,
task_code pvtask_code,
pvact_start_date,
pvtarget_start_date,
CASE
WHEN pvact_end_date IS NULL THEN activityname
ELSE NULL
END
task_name,
CASE
WHEN actualstart IS NOT NULL
AND ( pvact_start_date IS NULL
OR actualfinish IS NOT NULL)
THEN
actualstart
ELSE
NULL
END
act_start_date,
--actualstart act_start_date,
CASE
WHEN pvact_end_date IS NULL
AND ( pvact_start_date IS NOT NULL
OR actualstart IS NOT NULL)
THEN
actualfinish
ELSE
NULL
END
act_end_date,
NULL target_start_date,
CASE WHEN mxactualunits.proj_catg_short_name = 'Y' --and nvl(pvact_work_qty, 0) <> nvl(actuallaborhours,0)
THEN NVL (actuallaborhours, 0) ELSE NULL END
act_work_qty
FROM --1) activityid and proj id matches
(SELECT oid,
pv.task_id,
mx.proj_id,
mx.projectid,
pv.task_code,
pv.task_name,
pv.wbs_id pvwbs_id,
pv.act_start_date pvact_start_date,
pv.act_end_date pvact_end_date,
pv.target_start_date pvtarget_start_date,
mx.activityid,
mx.activityname,
mx.actualstart,
mx.actualfinish,
pv.act_work_qty pvact_work_qty,
mx.actuallaborhours,
mx.projectorganization,
mx.projecttype,
mx.process_start_ts --,
FROM mxpv_activity_tmp mx, mxpv_task_tmp pv
WHERE mx.task_id = pv.task_id
UNION
--3) PM Number/Job Plan number matches
SELECT oid,
jp.task_id,
jp.proj_id,
jp.proj_short_name,
jp.task_code,
jp.task_name,
jp.wbs_id,
jp.act_start_date pvtarget_start_date,
jp.act_end_date pvtarget_end_date,
jp.target_start_date pvtarget_start_date,
jp.activityid,
jp.activityname,
jp.actualstart,
jp.actualfinish,
jp.act_work_qty pvact_work_qty,
actuallaborhours,
projectorganization,
projecttype,
p_process_start_ts --,
FROM --3a) PM Number matches
(SELECT * FROM TABLE (pm_tab)
UNION
--3a) Job Plan Number matches
SELECT * FROM TABLE (jp_tab)) jp)
update_activity,
(SELECT projpcat.PROJ_ID, pcval.PROJ_CATG_SHORT_NAME
FROM privuser_PROJPCAT projpcat,
privuser_PCATTYPE pctype,
privuser_PCATVAL pcval
WHERE projpcat.PROJ_CATG_ID = pcval.PROJ_CATG_ID
AND pcval.PROJ_CATG_TYPE_ID =
pctype.PROJ_CATG_TYPE_ID
AND pctype.PROJ_CATG_TYPE = 'MX Actual Units')
mxactualunits,
(SELECT w.wbs_id, w.proj_id
FROM privuser_projwbs w, privuser_phase ph
WHERE w.phase_id = ph.phase_id
AND ph.phase_name = '0') wbs_t0
WHERE --update_activity.proj_id = actstrtoffset.proj_id (+)
update_activity.proj_id = wbs_t0.proj_id(+)
AND update_activity.proj_id = mxactualunits.proj_id(+)
AND update_activity.process_start_ts =
p_process_start_ts--and delete_ts is null
))
GROUP BY projectid,
task_id,
task_code,
pvtask_code,
task_name,
wbs_t0_id,
wbs_pv_id,
act_start_date,
act_end_date,
act_work_qty,
invalidactuals_flag,
invalidactfinish_flag,
scgemergent_flag,
pvact_start_date;
The type is defined as follows:
TYPE PV_APP_DATA.MXPV_ACTIVITYUPDATE_O AS OBJECT(
proj_short_name VARCHAR2 (100),
task_id NUMBER,
task_code VARCHAR2 (120 CHAR),
pvtask_code VARCHAR2 (120 CHAR),
task_name VARCHAR2 (120 CHAR),
wbs_id NUMBER,
act_start_date DATE,
act_end_date DATE,
target_start_date DATE,
act_work_qty NUMBER,
oid NUMBER,
scgemergent_flag VARCHAR2 (10),
invalidactuals_flag VARCHAR2 (100),
invalidactfinish_flag VARCHAR2 (100)
)
Please post if you need more information I wasn't sure what could be useful.
update 1 7/18/17 I modified the query with substr with no luck in fixing the problem:
select mxpv_activityupdate_O(
substr(projectid,0,99),
task_id,
substr(task_code,0,119),
substr(pvtask_code,0,119),
substr(task_name,0,119),
case when wbs_t0_id is not null and scgemergent_flag='Y' then wbs_t0_id
else wbs_pv_id end,
act_start_date,
case when act_end_date is not null and
(invalidactfinish_flag='Y'
)
then null else act_end_date end,
null,
case when invalidactuals_flag = 'Y' then null else act_work_qty end,
max(oid),
substr(scgemergent_flag,0,9),
substr(invalidactuals_flag,0,99),
substr(invalidactfinish_flag,0,99)
)
bulk collect into u_all_tab
from
(SELECT
projectorganization,
projecttype,
substr(projectid,0,99) as projectid,
task_id,
substr(task_code,0,119) as task_code,
substr(pvtask_code,0,119) as pvtask_code,
pvact_start_date,
substr(task_name,0,119) as task_name,
act_start_date,
act_end_date,
case when pvact_start_date is null and act_start_date is null
and nvl(act_work_qty, 0) <> 0 then 'Y' else 'N' end invalidactuals_flag,
act_work_qty,
oid,
wbs_t0_id,
wbs_pv_id,
case when pvact_start_date is null and
(act_start_date is not null --or nvl(act_work_qty,0)<>0
)
and projectorganization='SCG' and projecttype='Daily'
and pvtarget_start_date>v_t0_start_midnight+7
then 'Y' else 'N' end scgemergent_flag,
case when act_end_date is not null and nvl(act_start_date, pvact_start_date) is not null
and act_end_date<nvl(act_start_date, pvact_start_date) then 'Y' else 'N' end
invalidactfinish_flag
FROM
"if I want to force to correct length what's the best approach?"
You need to compare the length of the Tables' columns with the length of the Type's target attributes, and see which attributes are shorter than the columns you're trying to fit into them. Use the data dictionary.
For the Type Attributes use USER_TYPE_ATTRS (or ALL_TYPE_ATTRS if it's a different schema):
select attribute_name, attr_type_name, length, scale, precision
from user_type_attrs
where type_name = 'MXPV_ACTIVITY_UPDATE_O'
/
For the Table Columns it will be a similar query based on USER_TAB_COLS.
Once you have established the errant columns you will need to alter the type to enlarge the attributes. Alternatively apply SUBSTR() function to the columns in the query projection, to cut them down to size. Which is actually better depends on the circumstances of your application, although usually we would prefer the approach which doesn't lose data.