sql - ORACLE SQL ORA-22814 属性或元素值大于指定类型

Question

我正在调用一个返回一种表类型的函数。

我的函数接收一个 varchar 类型的变量。因为我返回了一个我创建的类型，所以我必须制作 cast 和 multiset...
我的问题是，我的多重集内的选择在函数外没有问题地执行，但是当我执行我的函数时，它给我 ORA-22814 属性或元素值大于指定的类型错误。注意:我总是使用相同的变量值运行脚本

键入对象:

create or replace
TYPE Z_PEDIDO_SeB IS OBJECT(
Contrato varchar2(4000 BYTE),
DataObjectivo date,
DataObjectivoSpecified date,
Descricao varchar2(500 BYTE),
DireccaoRequerente varchar2(50 BYTE),
Empresa varchar2(50 BYTE),
Estado varchar2(50 BYTE),
GestorDDS varchar2(100 BYTE),
GestorEncomenda varchar2(30 BYTE),
Referencia varchar2(50 BYTE),
Link varchar2(500 BYTE),
ObsComite varchar2(4000 BYTE),
OutrosFornecedores varchar2(4000 BYTE),
OutrosSistAfectados varchar2(4000 BYTE),
PrincipalSistAfectado varchar2(4000 BYTE),
Prioridade varchar2(50 BYTE),
Rede varchar2(50 BYTE),
Requerente varchar2(100 BYTE),
TestesAceitacao varchar2(10 BYTE),
proj_id varchar2(50 BYTE));

输入表格:

create or replace
TYPE Z_TABLE_PEDIDO_SeB IS TABLE OF Z_PEDIDO_SeB;

功能:

create or replace
function Z_GetDadosCreateUpdate_SEB(
    proj_id in varchar2
)
return Z_TABLE_PEDIDO_SeB as
  t_dados   Z_TABLE_PEDIDO_SeB;
begin
  select 
  cast(
  multiset(
--STARTS HERE WHAT I RUN WITHOUT PROBLEM OUTSIDE THE FUNCTION
  select 
(SELECT line_text
from long_text 
where key2 = proj_id and key1 = 'contrato'
) Contrato,
NVL(SCHEDULE_FINISH,ACTUAL_FINISH) DataObjectivo,
NVL(SCHEDULE_FINISH,ACTUAL_FINISH) DataObjectivoSpecified,
pedidos.description as Descricao,
costum.direcaorequerente DireccaoRequerente,
costum.empresa Empresa,
estado.description Estado,
costum.gestordds GestorDDS,
(select recursos.description
from structure recursos,
resources,
workflow,
wf_team
where recursos.structure_code = resources.resource_code
and workflow.planning_code = projectos.structure_code
and wf_team.workflow_id = workflow.workflow_id
and wf_team.lifecycle_role_code = '868'
and wf_team.user_name = resources.LOGON_ID
and rownum = 1
) GestorEncomenda,
pedidos.structure_code ID,
(SELECT line_text
from long_text 
where key2 = proj_id and key1 = 'urlcadernoreq'
) Link,
(SELECT line_text
from long_text 
where key2 = proj_id and key1 = 'observacoescomite'
) ObsComite,
(SELECT line_text
from long_text 
where key2 = proj_id and key1 = 'outrosfornecedores'
) OutrosFornecedores,
(SELECT line_text
from long_text 
where key2 = proj_id and key1 = 'outrossistemas'
) OutrosSistAfectados,
(SELECT line_text
from long_text 
where key2 = proj_id and key1 = 'principalsistema'
) PrincipalSistAfectado,
costum.prioridade Prioridade,
(SELECT rede.description
from structure rede, planning_entity proj
where proj.code88 = rede.structure_code
and proj.planning_code = proj_id
) Rede,
costum.requerente Requerente,
(SELECT rede.description
from structure rede, planning_entity proj
where proj.code89 = rede.structure_code
and proj.planning_code = proj_id
) TestesAceitacao,
projectos. structure_code proj_id
from structure projectos,
planning_entity,
structure pedidos,
structure estado,
custom_data costum
where projectos.structure_code = planning_entity.planning_code
and planning_entity.planning_code = planning_entity.ppl_code
and pedidos.structure_code = planning_entity.code31
and estado.structure_code = planning_entity.code20
and projectos.structure_code = proj_id
and costum.planning_code = proj_id
-- HERE ENDS WHAT I RUN OUTSIDE THE FUNCTION WITHOUT A PROBLEM
    ) 
      as Z_TABLE_PEDIDO_SeB)
    into
      t_dados
    from 
      dual;

  return t_dados;

end Z_GetDadosCreateUpdate_SEB;

我执行的是:

SELECT * FROM table (PVDEV.Z_GetDadosCreateUpdate_SEB('184765'));

我得到的错误:

ORA-22814: valor do atributo ou elemento é superior ao especificado no tipo
ORA-06512: na "PVDEV.Z_GETDADOSCREATEUPDATE_SEB", linha 7
22814. 00000 -  "attribute or element value is larger than specified in type"
*Cause:    Value provided for the object type attribute or collection element
       exceeded the size specified in the type declaration.
*Action:   Choose another value and retry the operation.

注意:如果我尝试使用 18476 而不是 184765，它可以正常运行。所以？我是怎么设置这样的限制的？在哪里？
注意:现在我显示的是错误，真正的错误，我真的很抱歉错误

我真的很感激任何答案，因为其他人的工作正在等待我的部分:S。无论如何，提前感谢您提供任何信息。

Answer 1

如果我是你，我会将该函数声明为流水线函数，并通过管道行语句返回值。并且一定要摆脱 CAST/multiset。 For 循环语句是您的选择。我相信它会按照我的建议行得通。