Случайное получение ORA-22814: значение атрибута или элемента больше, чем указано в типе, с массовым сбором в

У меня есть код, который случайно попадает "ORA-22814: attribute or element value is larger than specified in type" при массовом сборе.

Я подозреваю, что это проблема с данными, поскольку она случайна, но PL / SQL - не моя сильная сторона, но я поддерживаю приложение, в котором много всего используется в логике обработки.

Это база данных Oracle 12c.

Любая помощь будет оценена по достоинству!

Это блок кода:

  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;

Тип определяется следующим образом:

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)

)

Пожалуйста, опубликуйте, если вам нужна дополнительная информация. Я не был уверен, что может быть полезно.

обновление 1 18.07.17 Я изменил запрос с помощью substr, но не смог решить проблему:

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