当前位置： 文江博客话题详情

ORA-06502 创建视图时出错

发布于 2024-11-28 11:56:05 字数 6014 浏览 0 评论 0原文

我试图在我的生产服务器上创建一个无法编译的视图——但是，该视图已经存在于我们的测试服务器上，没有错误。

我收到此错误：

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 27

但是，非常令人困惑的是错误本身指向视图列定义。我不太确定如何解决这个问题，再次考虑到我们的测试环境中存在相同的例程，没有任何问题。

这是视图，并警告说它相当大：

CREATE OR REPLACE FORCE VIEW V2RPT.VW_DM
(
   ACCESSION_LABEL, 
   RECEIVED_DATE,
   ACCESSIONING_TS,
   TTR_TUMOR_TYPE_INFO,
   SPEC_TYPE,
   TUMOR_TYPE,
   TUMOR_SUBTYPE,
   PRIM_MET,
   TUMOR_SITE,
   EXTRACT_DATE,
   CELL_SOURCE_STATUS,
   MIXTURE_STATUS,
   PLATE_STATUS,
   CS_TERM_REASON,
   ASSAYS_ORDERED,
   ASSAYS_REPORTED,
   AOF_GEN_TS,
   AOF_TRANS_TS,
   AOF_PROC_TS,
   FIRST_TERM_REPORT_TRANS_TS,
   COMPLETE_WITHOUT_REPORT_TS,
   RELATED_CELL_SOURCE,
   FACILITY,
   RELEASED_DATE,
   IHC_DATE, --Error is happening here
   T,
   M,
   N,
   TUMOR_STAGE,
   TUMOR_GRADE,
   TUMOR_DIFF,
   I_COUNT,
   R_COUNT,
   N_COUNT,
   MIXTURE_ID,
   PLATED_TS,
   TREATED_TS,
   FIXED_STAINED_TS,
   SCANNED_TS,
   COUNTED_TS,
   ASSAYS_PLATED,
   CELL_YIELD,
   EXPLANTED_BY,
   REPLATE,
   AVG_CONTROL,
   CELL_SUSPENSION_CREATOR,
   CELL_SUSPENSION_TS,
   IN_CULTURE_TS,
   ENOUGH_CELLS_TS,
   MIXTURE_ON_PLATE_TS,
   TERM_MIXTURE_TS,
   MIXTURE_TERM_REASON,
   COMPLETE_TS,
   VALIDATED_TS,
   SALES_REP,
   DRUG_SEL_TS,
   DRUGS_ON_PANEL,
   FINAL_PATH_COMP_TS,
   PHYSICIAN,
   CELL_SOURCE_TYPE_ID,
   LXWXH,
   PCT_SOLID,
   PCT_FATTY,
   PCT_FIBROUS,
   PCT_BLOODY,
   PCT_NECROTIC,
   PCT_OTHER,
   LESS_THAN_100MG,
   OVERSIZED,
   UNDERSIZED,
   FLASK_TYPE,
   VITROGEN_COATED,
   AB_WASH_REQUIRED,
   DIM_X,
   DIM_Y,
   DIM_Z,
   MANUAL_PLATE,
   LAB_SITE_ABBR,
   REGION_TERRITORY,
   MEDIUM_TYPE,
   TUMOR_TYPE_FP,
   TUMOR_TYPE_PP,
   CR_ENTRY_CREATOR,
   CR_ENTRY_CREATED_TS,
   ICC_ENTRY_CREATOR,
   ICC_ENTRY_CREATED_TS,
   AOF_TRANS_CREATOR
)
AS
SELECT cs.accession_label,
          cs.received_date,
          cs.created_timestamp AS ACCESSIONING_TS,
          PW.TTR_TUMOR_TYPE_INFO,
          VW_CS.SPEC_TYPE,
          VW_CS.TUMOR_TYPE,
          VW_CS.TUMOR_SUBTYPE,
          VW_CS.PRIM_MET,
          VW_CS.TUMOR_SITE,
          VW_CS.EXTRACT_DATE,
          VW_CS.CELL_SOURCE_STATUS,
          VW_CS.MIXTURE_STATUS,
          VW_CS.PLATE_STATUS,
          VW_CS.CS_TERM_REASON,
          VW_CS.ASSAYS_ORDERED,
          VW_CS.ASSAYS_REPORTED,
          VW_CS.AOF_GEN_TS,
          VW_CS.AOF_TRANS_TS,
          VW_CS.AOF_PROC_TS,
          VW_CS.FIRST_TERM_REPORT_TRANS_TS,
          VW_CS.COMPLETE_WITHOUT_REPORT_TS,
          VW_CS.RELATED_CELL_SOURCE,
          VW_CS.VW_CS.FACILITY,
          VW_CS.RELEASED_DATE,
          VW_CS.IHC_DATE,
          VW_CS.T,
          VW_CS.M,
          VW_CS.N,
          VW_CS.TUMOR_STAGE,
          VW_CS.TUMOR_GRADE,
          VW_CS.TUMOR_DIFF,
          VW_CS.I_COUNT,
          VW_CS.R_COUNT,
          VW_CS.N_COUNT,
          MIXTURE.TERM_MC_MIXTURE_ID AS Mixture_ID,
          MIXTURE.PLATED_TS,
          MIXTURE.TREATED_TS,
          MIXTURE.FIXED_STAINED_TS,
          MIXTURE.SCANNED_TS,
          MIXTURE.COUNTED_TS,
          Mixture.Assays_Plated,
          Mixture.CELL_YIELD,
          Mixture.EXPLANTED_BY,
          NVL (Mixture.REPLATE, 0) Replate,
          Mixture.AVG_CONTROL,
          Mixture.CELL_SUSPENSION_CREATOR,
          Mixture.CELL_SUSPENSION_TS,
          Mixture.IN_CULTURE_TS,
          Mixture.ENOUGH_CELLS_TS,
          Mixture.MIXTURE_ON_PLATE_TS,
          Mixture.TERM_MIXTURE_TS,
          Mixture.MIXTURE_TERM_REASON,
          Mixture.COMPLETE_TS,
          Mixture.VALIDATED_TS,
          PW.SALES_REP,
          PW.DRUG_SEL_TS,
          PW.DRUGS_ON_PANEL,
          PW.FINAL_PATH_COMP_TS,
          PW.PHYSICIAN,
          CS.CELL_SOURCE_TYPE_ID,
          VW_CS.LXWXH,
          VW_CS.PCT_SOLID,
          VW_CS.PCT_FATTY,
          VW_CS.PCT_FIBROUS,
          VW_CS.PCT_BLOODY,
          VW_CS.PCT_NECROTIC,
          VW_CS.PCT_OTHER,
          VW_CS.LESS_THAN_100MG,
          VW_CS.OVERSIZED,
          VW_CS.UNDERSIZED,
          MIXTURE.FLASK_TYPE,
          MIXTURE.VITROGEN_COATED,
          VW_CS.AB_WASH_REQUIRED,
          VW_CS.DIM_X,
          VW_CS.DIM_Y,
          VW_CS.DIM_Z,
          MIXTURE.MANUAL_PLATE,
          CS.LAB_SITE_ABBR,
          V2.CRM_ACCOUNT_ADDRESS.STATE REGION_TERRITORY,
          MIXTURE.MEDIUM_TYPE,
          VW_CS.TUMOR_TYPE_FP,
          VW_CS.TUMOR_TYPE_PP,
          MIXTURE.CR_ENTRY_CREATOR,
          MIXTURE.CR_ENTRY_CREATED_TS,
          MIXTURE.ICC_ENTRY_CREATOR,
          MIXTURE.ICC_ENTRY_CREATED_TS,
          VW_CS.AOF_TRANS_CREATOR
     FROM V2.cell_Source cs
          INNER JOIN V2RPT.TEMP_DM_CS_TYPE
             ON CS.CELL_SOURCE_TYPE_ID =
                   V2RPT.TEMP_DM_CS_TYPE.CELL_SOURCE_TYPE_ID
          LEFT OUTER JOIN V2RPT.TEMP_DM_CS VW_CS
             ON CS.CELL_SOURCE_ID = VW_CS.CELL_SOURCE_ID
          LEFT OUTER JOIN V2RPT.TEMP_DM_MIXTURE MIXTURE
             ON MIXTURE.cell_Source_id = CS.CELL_SOURCE_ID
          LEFT OUTER JOIN V2RPT.VW_DM_PW PW
             ON PW.cell_source_ID = cs.cell_source_ID
          LEFT OUTER JOIN V2.CRM_ACCOUNT
             ON V2.CRM_ACCOUNT.CRM_ACCOUNT_ID = CS.CRM_ACCOUNT_ID
          LEFT OUTER JOIN (  SELECT MAX (
                                       V2.CRM_ACCOUNT_ADDRESS.
                                       CRM_ACCOUNT_ADDRESS_ID)
                                       Max_AA_ID,
                                    V2.CRM_ACCOUNT_ADDRESS.CRM_ACCOUNT_ID
                               FROM V2.CRM_ACCOUNT_ADDRESS
                           GROUP BY V2.CRM_ACCOUNT_ADDRESS.CRM_ACCOUNT_ID) Max_AA
             ON Max_AA.CRM_ACCOUNT_ID = V2.CRM_ACCOUNT.CRM_ACCOUNT_ID
          LEFT OUTER JOIN V2.CRM_ACCOUNT_ADDRESS
             ON V2.CRM_ACCOUNT_ADDRESS.CRM_ACCOUNT_ADDRESS_ID =
                   max_aa.Max_AA_ID;

我有点迷失了——有什么想法吗？我的意思是，甚至没有任何变量可以赋值，所以我不知道“字符串缓冲区可能太小”，

感谢帮助。

原文

I am trying to create a view on my production server which will not compile -- however, the view already exists on our test server with no error.

I am getting this error:

ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 27

However, whats very confusing is the fact that the error itself points to the view column definition. I'm not really sure how to approach this, again considering the fact that the same routine exists in our test environment with no issues.

Here is the view, with a warning that it is pretty large:

CREATE OR REPLACE FORCE VIEW V2RPT.VW_DM
(
   ACCESSION_LABEL, 
   RECEIVED_DATE,
   ACCESSIONING_TS,
   TTR_TUMOR_TYPE_INFO,
   SPEC_TYPE,
   TUMOR_TYPE,
   TUMOR_SUBTYPE,
   PRIM_MET,
   TUMOR_SITE,
   EXTRACT_DATE,
   CELL_SOURCE_STATUS,
   MIXTURE_STATUS,
   PLATE_STATUS,
   CS_TERM_REASON,
   ASSAYS_ORDERED,
   ASSAYS_REPORTED,
   AOF_GEN_TS,
   AOF_TRANS_TS,
   AOF_PROC_TS,
   FIRST_TERM_REPORT_TRANS_TS,
   COMPLETE_WITHOUT_REPORT_TS,
   RELATED_CELL_SOURCE,
   FACILITY,
   RELEASED_DATE,
   IHC_DATE, --Error is happening here
   T,
   M,
   N,
   TUMOR_STAGE,
   TUMOR_GRADE,
   TUMOR_DIFF,
   I_COUNT,
   R_COUNT,
   N_COUNT,
   MIXTURE_ID,
   PLATED_TS,
   TREATED_TS,
   FIXED_STAINED_TS,
   SCANNED_TS,
   COUNTED_TS,
   ASSAYS_PLATED,
   CELL_YIELD,
   EXPLANTED_BY,
   REPLATE,
   AVG_CONTROL,
   CELL_SUSPENSION_CREATOR,
   CELL_SUSPENSION_TS,
   IN_CULTURE_TS,
   ENOUGH_CELLS_TS,
   MIXTURE_ON_PLATE_TS,
   TERM_MIXTURE_TS,
   MIXTURE_TERM_REASON,
   COMPLETE_TS,
   VALIDATED_TS,
   SALES_REP,
   DRUG_SEL_TS,
   DRUGS_ON_PANEL,
   FINAL_PATH_COMP_TS,
   PHYSICIAN,
   CELL_SOURCE_TYPE_ID,
   LXWXH,
   PCT_SOLID,
   PCT_FATTY,
   PCT_FIBROUS,
   PCT_BLOODY,
   PCT_NECROTIC,
   PCT_OTHER,
   LESS_THAN_100MG,
   OVERSIZED,
   UNDERSIZED,
   FLASK_TYPE,
   VITROGEN_COATED,
   AB_WASH_REQUIRED,
   DIM_X,
   DIM_Y,
   DIM_Z,
   MANUAL_PLATE,
   LAB_SITE_ABBR,
   REGION_TERRITORY,
   MEDIUM_TYPE,
   TUMOR_TYPE_FP,
   TUMOR_TYPE_PP,
   CR_ENTRY_CREATOR,
   CR_ENTRY_CREATED_TS,
   ICC_ENTRY_CREATOR,
   ICC_ENTRY_CREATED_TS,
   AOF_TRANS_CREATOR
)
AS
SELECT cs.accession_label,
          cs.received_date,
          cs.created_timestamp AS ACCESSIONING_TS,
          PW.TTR_TUMOR_TYPE_INFO,
          VW_CS.SPEC_TYPE,
          VW_CS.TUMOR_TYPE,
          VW_CS.TUMOR_SUBTYPE,
          VW_CS.PRIM_MET,
          VW_CS.TUMOR_SITE,
          VW_CS.EXTRACT_DATE,
          VW_CS.CELL_SOURCE_STATUS,
          VW_CS.MIXTURE_STATUS,
          VW_CS.PLATE_STATUS,
          VW_CS.CS_TERM_REASON,
          VW_CS.ASSAYS_ORDERED,
          VW_CS.ASSAYS_REPORTED,
          VW_CS.AOF_GEN_TS,
          VW_CS.AOF_TRANS_TS,
          VW_CS.AOF_PROC_TS,
          VW_CS.FIRST_TERM_REPORT_TRANS_TS,
          VW_CS.COMPLETE_WITHOUT_REPORT_TS,
          VW_CS.RELATED_CELL_SOURCE,
          VW_CS.VW_CS.FACILITY,
          VW_CS.RELEASED_DATE,
          VW_CS.IHC_DATE,
          VW_CS.T,
          VW_CS.M,
          VW_CS.N,
          VW_CS.TUMOR_STAGE,
          VW_CS.TUMOR_GRADE,
          VW_CS.TUMOR_DIFF,
          VW_CS.I_COUNT,
          VW_CS.R_COUNT,
          VW_CS.N_COUNT,
          MIXTURE.TERM_MC_MIXTURE_ID AS Mixture_ID,
          MIXTURE.PLATED_TS,
          MIXTURE.TREATED_TS,
          MIXTURE.FIXED_STAINED_TS,
          MIXTURE.SCANNED_TS,
          MIXTURE.COUNTED_TS,
          Mixture.Assays_Plated,
          Mixture.CELL_YIELD,
          Mixture.EXPLANTED_BY,
          NVL (Mixture.REPLATE, 0) Replate,
          Mixture.AVG_CONTROL,
          Mixture.CELL_SUSPENSION_CREATOR,
          Mixture.CELL_SUSPENSION_TS,
          Mixture.IN_CULTURE_TS,
          Mixture.ENOUGH_CELLS_TS,
          Mixture.MIXTURE_ON_PLATE_TS,
          Mixture.TERM_MIXTURE_TS,
          Mixture.MIXTURE_TERM_REASON,
          Mixture.COMPLETE_TS,
          Mixture.VALIDATED_TS,
          PW.SALES_REP,
          PW.DRUG_SEL_TS,
          PW.DRUGS_ON_PANEL,
          PW.FINAL_PATH_COMP_TS,
          PW.PHYSICIAN,
          CS.CELL_SOURCE_TYPE_ID,
          VW_CS.LXWXH,
          VW_CS.PCT_SOLID,
          VW_CS.PCT_FATTY,
          VW_CS.PCT_FIBROUS,
          VW_CS.PCT_BLOODY,
          VW_CS.PCT_NECROTIC,
          VW_CS.PCT_OTHER,
          VW_CS.LESS_THAN_100MG,
          VW_CS.OVERSIZED,
          VW_CS.UNDERSIZED,
          MIXTURE.FLASK_TYPE,
          MIXTURE.VITROGEN_COATED,
          VW_CS.AB_WASH_REQUIRED,
          VW_CS.DIM_X,
          VW_CS.DIM_Y,
          VW_CS.DIM_Z,
          MIXTURE.MANUAL_PLATE,
          CS.LAB_SITE_ABBR,
          V2.CRM_ACCOUNT_ADDRESS.STATE REGION_TERRITORY,
          MIXTURE.MEDIUM_TYPE,
          VW_CS.TUMOR_TYPE_FP,
          VW_CS.TUMOR_TYPE_PP,
          MIXTURE.CR_ENTRY_CREATOR,
          MIXTURE.CR_ENTRY_CREATED_TS,
          MIXTURE.ICC_ENTRY_CREATOR,
          MIXTURE.ICC_ENTRY_CREATED_TS,
          VW_CS.AOF_TRANS_CREATOR
     FROM V2.cell_Source cs
          INNER JOIN V2RPT.TEMP_DM_CS_TYPE
             ON CS.CELL_SOURCE_TYPE_ID =
                   V2RPT.TEMP_DM_CS_TYPE.CELL_SOURCE_TYPE_ID
          LEFT OUTER JOIN V2RPT.TEMP_DM_CS VW_CS
             ON CS.CELL_SOURCE_ID = VW_CS.CELL_SOURCE_ID
          LEFT OUTER JOIN V2RPT.TEMP_DM_MIXTURE MIXTURE
             ON MIXTURE.cell_Source_id = CS.CELL_SOURCE_ID
          LEFT OUTER JOIN V2RPT.VW_DM_PW PW
             ON PW.cell_source_ID = cs.cell_source_ID
          LEFT OUTER JOIN V2.CRM_ACCOUNT
             ON V2.CRM_ACCOUNT.CRM_ACCOUNT_ID = CS.CRM_ACCOUNT_ID
          LEFT OUTER JOIN (  SELECT MAX (
                                       V2.CRM_ACCOUNT_ADDRESS.
                                       CRM_ACCOUNT_ADDRESS_ID)
                                       Max_AA_ID,
                                    V2.CRM_ACCOUNT_ADDRESS.CRM_ACCOUNT_ID
                               FROM V2.CRM_ACCOUNT_ADDRESS
                           GROUP BY V2.CRM_ACCOUNT_ADDRESS.CRM_ACCOUNT_ID) Max_AA
             ON Max_AA.CRM_ACCOUNT_ID = V2.CRM_ACCOUNT.CRM_ACCOUNT_ID
          LEFT OUTER JOIN V2.CRM_ACCOUNT_ADDRESS
             ON V2.CRM_ACCOUNT_ADDRESS.CRM_ACCOUNT_ADDRESS_ID =
                   max_aa.Max_AA_ID;

I'm a tad lost -- any ideas? I mean, there aren't even any variables to assign values to, so I don't know how the 'string buffer can be too small'

Appreciate the help.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

原来分手还会想你 2024-12-05 11:56:05

创建视图时数据库上是否有任何触发器会触发？对我来说，error generated at recursive SQL level 1 行表明触发器中存在错误。也许有某种审核触发器正在审核正在创建的对象，并且您创建的视图对于该触发器来说太大而无法处理？

以下演示了如何在创建视图时生成与您的错误类似的错误。首先，我们创建一个触发器，当您尝试创建某些内容时，该触发器会导致出现字符串缓冲区太小错误。很可能导致问题的触发器并不那么愚蠢：

SQL> create or replace trigger error_trigger
  2    before create on database
  3  declare
  4    a varchar(1);
  5  begin
  6    a := '12';
  7  end;
  8  /

Trigger created.

现在，当我们尝试创建视图时，我们收到错误：

SQL> create view some_view as select * from dual;
create view some_view as select * from dual
                                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

此视图与 PL/SQL 或字符串缓冲区无关，也不与 PL/SQL 或字符串缓冲区有关。它甚至有第 4 行吗，但由于我们创建的触发器，我们仍然收到上述错误。对于此触发器，第 1 行 (declare) 是触发器本身的第一行，而不是创建触发器的 create触发器 语句，因此第 4 行是行 <代码>a := '12';。

为了追踪这个触发器，我们可以查询dba_triggers数据字典视图。在下面的示例中，它出现在底行：

SQL> select trigger_name, trigger_type, owner from dba_triggers
  2   where trigger_type in ('BEFORE EVENT', 'AFTER EVENT');

TRIGGER_NAME                   TRIGGER_TYPE     OWNER
------------------------------ ---------------- ------------------------------
AW_DROP_TRG                    AFTER EVENT      SYS
AW_TRUNC_TRG                   AFTER EVENT      SYS
AW_REN_TRG                     AFTER EVENT      SYS
XDB_PI_TRIG                    BEFORE EVENT     SYS
SDO_DROP_USER                  AFTER EVENT      MDSYS
SDO_ST_SYN_CREATE              BEFORE EVENT     MDSYS
SDO_TOPO_DROP_FTBL             BEFORE EVENT     MDSYS
ERROR_TRIGGER                  BEFORE EVENT     LUKE

Have you got any triggers on the database that are firing when the view is created? To me, the line error occurred at recursive SQL level 1 suggests an error in a trigger. Perhaps there's some kind of auditing trigger that is auditing objects being created and the view you created was too large for this trigger to cope with?

Here's a demonstration of how to generate an error similar to yours when creating a view. First, we create a trigger that causes a character string buffer too small error when you attempt to create something. Chances are that the trigger that appears to be causing your problem isn't quite this stupid:

SQL> create or replace trigger error_trigger
  2    before create on database
  3  declare
  4    a varchar(1);
  5  begin
  6    a := '12';
  7  end;
  8  /

Trigger created.

Now, when we attempt to create a view, we get an error:

SQL> create view some_view as select * from dual;
create view some_view as select * from dual
                                       *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

This view has nothing to do with PL/SQL or character string buffers, nor does it even have a line 4, but we still get the above error because of the trigger we created. With this trigger, line 1 (declare) is the first line of the trigger itself, as opposed to the create trigger statement that creates it, so line 4 is the line a := '12';.

To track down this trigger, we can query the dba_triggers data dictionary view. In the example below, it turns up in the bottom row:

SQL> select trigger_name, trigger_type, owner from dba_triggers
  2   where trigger_type in ('BEFORE EVENT', 'AFTER EVENT');

TRIGGER_NAME                   TRIGGER_TYPE     OWNER
------------------------------ ---------------- ------------------------------
AW_DROP_TRG                    AFTER EVENT      SYS
AW_TRUNC_TRG                   AFTER EVENT      SYS
AW_REN_TRG                     AFTER EVENT      SYS
XDB_PI_TRIG                    BEFORE EVENT     SYS
SDO_DROP_USER                  AFTER EVENT      MDSYS
SDO_ST_SYN_CREATE              BEFORE EVENT     MDSYS
SDO_TOPO_DROP_FTBL             BEFORE EVENT     MDSYS
ERROR_TRIGGER                  BEFORE EVENT     LUKE
回复 原文
~没有更多了~

关于作者

早乙女

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

梦里南柯

文章 0 评论 0

不将就、

文章 0 评论 0

alipaysp_ZRaVhH1Dn

文章 0 评论 0

青衫儰鉨ミ守葔

文章 0 评论 0

故事未完

文章 0 评论 0

梦晓ヶ微光ヅ倾城

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文