Reputation: 1248
ORA-06502 Error when creating a view
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.
Upvotes: 1
Views: 2226
Answers (1)
Reputation: 64959
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
Upvotes: 4
Related Questions
- Oracle Error When Creating View - ORA-01720
- ora-00904 invalid identifier on creating a view
- ORA-00942 error is generating while creating a view
- error on statement execution when creating a view
- Cannot create View
- creating a view SQL, always getting error
- ORA-01031 when creating a view
- Creating views in SQL and receiving error
- Ora-00904 - Error with creating a View
- Oracle Throwing SQL Error when creating a View