asked on

How do I properly execute a trigger using cursor query

Please find attached table structure and the trigger developed to update the stage table (s_ais) when changes occur to the underlying table, ais. As we are only interested in a subset of records found in the ais table, I attempted to use a cursor to filter only on ais that are of interest to CDT.

My trigger is failing, ORA-06502; numeric or value error: char string buffer too small on line 40. Yet I fail to see the necessary action to be taken. I assume the cause is that I am trying to execute a statement that resulted in an arithmetic, numeric, string, conversion, or constraint error.

If I run the cursor solo, the resultant is as expected. A proper list of ais displays.

Any assistance is appreciated.

TableStructure.txt
S-U-AIS-ID-SEQ-TR-ExpHlp.txt

sathyagiri

Try changing the following declarations

ais_chng_dsc_tx_v varchar2(4000);

And in the insert statement change ais_chng_dsc_tx_v to substr(ais_chng_dsc_tx_v,1,499)

lulubell-b

ASKER

Hi!

I changed the declare statement to:
ais_chng_dsc_tx_v      varchar2(4000);
and on the insert statement changed as follows:

      :new.AMCAT_PRIM_TYP,
      'U',
      sysdate,
      'CDT',
     substr(ais_chng_dsc_tx_v,1,499),
      'N');

The script compiles with errors:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/40 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
type <an identifier> <a double-quoted delimited-identifier>
The symbol "type" was substituted for ";" to continue.

No doubt I did not follow instructions correctly, please advise and thank you

sathyagiri

Sounds like syntax error @ some place, can you paste the entire trigger text ?

lulubell-b

ASKER

I also attached in my initial post the trigger script file and table structures, thanks for your quick reply.

CREATE OR REPLACE TRIGGER S_U_AIS_ID_SEQ_TR 
after update on AIS
for each row 
declare
ais_chng_dsc_tx_v  	varchar2(4000);
aisu_cmnq_v cdb.ais_usage.cmnq_part_id%;
o_cmnq_v cdb.ais.cmnq_part_id%type;
cursor aisucmnqpartid_crsr is
select ais_usage.cmnq_part_id
FROM CDB.AIS_USAGE 
where cdb.ais_usage.ais_usg_nm = 'CDT';
begin 
open aisucmnqpartid_crsr; 
fetch aisucmnqpartid_crsr into aisu_cmnq_v;
loop
o_cmnq_v := aisu_cmnq_v;
if aisu_cmnq_v = o_cmnq_v then
while aisu_cmnq_v = o_cmnq_v
loop
  if updating ('CMNQ_PART_ID')
        or updating ('AIS_NM')
	or updating ('AIS_ACRYNM_TX')
	or updating ('AIS_CT_CD')
	or updating ('ORG_ID')
	or updating ('AIS_EXT_DSC_TX')
	or updating ('AIS_IOC_DT')
	or updating ('AIS_FOC_DT')
	or updating ('MSN_AR_TYP_CD')
	or updating ('MSN_AR_TYP_TX')
	or updating ('SCRTY_CLSFCTN_TX')
	or updating ('AIS_DSD_ID')
	or updating ('AIS_DSD_CD')
	or updating ('FAR_NM')
	or updating ('AIS_UPGRADE_RMK_TX')
	or updating ('AIS_RCRD_CRTN_ZZ')
	or updating ('AIS_RCD_ZZ')
	or updating ('AIS_DSC_TX')
  then	
		if nvl (:old.AIS_NM, 'null')!=nvl(:new.AIS_NM, 'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_NM, ';
		end if;
		if nvl (:old.AIS_ACRYNM_TX, 'null')!=nvl(:new.AIS_ACRYNM_TX, 'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_ACRYNM_TX, ';
		end if;
		if nvl (:old.AIS_CT_CD,'null')!=nvl(:new.AIS_CT_CD,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_CT_CD, ';
		end if;
		if nvl(to_char(:old.ORG_ID),'null')!=nvl(to_char(:new.ORG_ID), 'null') then
		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'ORG_ID, ';
		end if;
		if nvl (:old.AIS_EXT_DSC_TX,'null')!=nvl(:new.AIS_EXT_DSC_TX,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_EXT_DSC_TX, ';
		end if;
		if nvl (to_char(:old.AIS_IOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.AIS_IOC_DT,'MM/DD/YYYY'),'null') then
		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_IOC_DT, ';
		end if;
		if nvl (to_char(:old.AIS_FOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.AIS_FOC_DT,'MM/DD/YYYY'),'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_FOC_DT, ';
		end if;
		if nvl (:old.MSN_AR_TYP_CD,'null')!=nvl(:new.MSN_AR_TYP_CD,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'MSN_AR_TYP_CD, ';
		end if;
		if nvl (:old.MSN_AR_TYP_TX,'null')!=nvl(:new.MSN_AR_TYP_TX,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'MSN_AR_TYP_TX, ';
		end if;
		if nvl (:old.SCRTY_CLSFCTN_TX,'null')!=nvl(:new.SCRTY_CLSFCTN_TX,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'SCRTY_CLSFCTN_TX, ';
		end if;
		if nvl (:old.AIS_DSD_ID,'null')!=nvl(:new.AIS_DSD_ID,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_DSD_ID, ';
		end if;
		if nvl (:old.AIS_DSD_CD,'null')!=nvl(:new.AIS_DSD_CD,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_DSD_CD, ';
		end if;
		if nvl (:old.FAR_NM,'null')!=nvl(:new.FAR_NM,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'FAR_NM, ';
		end if;
		if nvl (:old.AIS_UPGRADE_RMK_TX,'null')!=nvl(:new.AIS_UPGRADE_RMK_TX,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_UPGRADE_RMK_TX, ';
		end if;
		if nvl (to_char(:old.AIS_RCRD_CRTN_ZZ,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.AIS_RCRD_CRTN_ZZ,'MM/DD/YYYY'),'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_RCRD_CRTN_ZZ, ';
		end if;
		if nvl (to_char(:old.AIS_RCD_ZZ,'MM/DD/YYYY'),'null')!=nvl (to_char(:new.AIS_RCD_ZZ,'MM/DD/YYYY'),'null') 			then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_RCD_ZZ, ';
		end if;
		if nvl (:old.AIS_DSC_TX,'null')!=nvl(:new.AIS_DSC_TX,'null') then
 		ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_DSC_TX, ';
		end if;
insert into s_ais 
	(S_AIS_ID,
	CMNQ_PART_ID,
 	AIS_NM,
	AIS_ACRYNM_TX,
	AIS_CT_CD,
	ORG_ID,
	AIS_EXT_DSC_TX,
	AIS_IOC_DT,
	AIS_FOC_DT,
	MSN_AR_TYP_CD,
	MSN_AR_TYP_TX,
	SCRTY_CLSFCTN_TX,
	AIS_DSD_ID,
	AIS_DSD_CD,
	FAR_NM,
	AIS_UPGRADE_RMK_TX,
	AIS_RCRD_CRTN_ZZ,
	AIS_RCD_ZZ,
	AIS_DSC_TX,
	AMCAT_ID,
	AMCAT_PRIM_TYP,
	ais_chng_cd,
  	ais_mod_c_dt,
	ais_d_src,
	ais_chng_dsc_tx,
	ais_cnfrm_rcpt_flg) 
	values 
	(S_AIS_ID_SEQ.NEXTVAL,
	:old.cmnq_part_id, 
	:new.AIS_NM,
	:new.AIS_ACRYNM_TX,
	:new.AIS_CT_CD,
	:new.ORG_ID,
	:new.AIS_EXT_DSC_TX,
	:new.AIS_IOC_DT,
	:new.AIS_FOC_DT,
	:new.MSN_AR_TYP_CD,
	:new.MSN_AR_TYP_TX,
	:new.SCRTY_CLSFCTN_TX,
	:new.AIS_DSD_ID,
	:new.AIS_DSD_CD,
	:new.FAR_NM,
	:new.AIS_UPGRADE_RMK_TX,
	:new.AIS_RCRD_CRTN_ZZ,
	:new.AIS_RCD_ZZ,
	:new.AIS_DSC_TX,
	:new.AMCAT_ID,
	:new.AMCAT_PRIM_TYP,
	'U',
	sysdate,
	'CDT',
 	substr(ais_chng_dsc_tx_v,1,499),
	'N');
end if;
fetch aisucmnqpartid_crsr into aisu_cmnq_v;
Exit when aisucmnqpartid_crsr%notfound;
end loop;
end if;
end loop;
close aisucmnqpartid_crsr;
END;
/

Open in new window

sathyagiri

aisu_cmnq_v cdb.ais_usage.cmnq_part_id%;

Shd that be aisu_cmnq_v cdb.ais_usage.cmnq_part_id%type;

lulubell-b

ASKER

good catch, thank you! Sadly, the same error is received, now on line 37.
35 or updating ('AIS_RCRD_CRTN_ZZ')
36 or updating ('AIS_RCD_ZZ')
37 or updating ('AIS_DSC_TX')
38 then
39 if nvl (:old.AIS_NM, 'null')!=nvl(:new.AIS_NM, 'null') then
40 ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_NM, ';
41 end if;

I'll be here till 2:00, hopefully we can continue to work this issue. My sincere thanks...

sathyagiri

Are you sure you are getting the same error. Would you mind posting the error message as well

lulubell-b

ASKER

Network is now up, but slow... Please see the code snippet. The id 999999997 is in the usage table and is designated as CDT.

Also there is only one trigger on the ais_usage table and that is...
CREATE OR REPLACE TRIGGER AIS_USG_ID_seq_tr
BEFORE INSERT ON AIS_USAGE
FOR EACH ROW
BEGIN
SELECT AIS_USG_ID_seq.nextval
INTO :New.AIS_USG_ID
FROM DUAL;
END;
/


  1  CREATE OR REPLACE TRIGGER S_U_AIS_ID_SEQ_TR
  2  after update on AIS
  3  for each row
  4  declare
  5  ais_chng_dsc_tx_v          varchar2(4000);
  6  aisu_cmnq_v cdb.ais_usage.cmnq_part_id%type;
  7  o_cmnq_v cdb.ais.cmnq_part_id%type;
  8  cursor aisucmnqpartid_crsr is
  9  select ais_usage.cmnq_part_id
 10  FROM CDB.AIS_USAGE
 11  where cdb.ais_usage.ais_usg_nm = 'CDT';
 12  begin
 13  open aisucmnqpartid_crsr;
 14  fetch aisucmnqpartid_crsr into aisu_cmnq_v;
 15  loop
 16  o_cmnq_v := aisu_cmnq_v;
 17  if aisu_cmnq_v = o_cmnq_v then
 18  while aisu_cmnq_v = o_cmnq_v
 19  loop
 20    if updating ('CMNQ_PART_ID')
 21          or updating ('AIS_NM')
 22     or updating ('AIS_ACRYNM_TX')
 23     or updating ('AIS_CT_CD')
 24     or updating ('ORG_ID')
 25     or updating ('AIS_EXT_DSC_TX')
 26     or updating ('AIS_IOC_DT')
 27     or updating ('AIS_FOC_DT')
 28     or updating ('MSN_AR_TYP_CD')
 29     or updating ('MSN_AR_TYP_TX')
 30     or updating ('SCRTY_CLSFCTN_TX')
 31     or updating ('AIS_DSD_ID')
 32     or updating ('AIS_DSD_CD')
 33     or updating ('FAR_NM')
 34     or updating ('AIS_UPGRADE_RMK_TX')
 35     or updating ('AIS_RCRD_CRTN_ZZ')
 36     or updating ('AIS_RCD_ZZ')
 37     or updating ('AIS_DSC_TX')
 38    then     
 39             if nvl (:old.AIS_NM, 'null')!=nvl(:new.AIS_NM, 'null') then
 40             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_NM, ';
 41             end if;
 42             if nvl (:old.AIS_ACRYNM_TX, 'null')!=nvl(:new.AIS_ACRYNM_TX, 'null') then
 43             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_ACRYNM_TX, ';
 44             end if;
 45             if nvl (:old.AIS_CT_CD,'null')!=nvl(:new.AIS_CT_CD,'null') then
 46             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_CT_CD, ';
 47             end if;
 48             if nvl(to_char(:old.ORG_ID),'null')!=nvl(to_char(:new.ORG_ID), 'null') then
 49             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'ORG_ID, ';
 50             end if;
 51             if nvl (:old.AIS_EXT_DSC_TX,'null')!=nvl(:new.AIS_EXT_DSC_TX,'null') then
 52             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_EXT_DSC_TX, ';
 53             end if;
 54             if nvl (to_char(:old.AIS_IOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.AIS_IOC_DT,'MM/DD/YYY
 55             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_IOC_DT, ';
 56             end if;
 57             if nvl (to_char(:old.AIS_FOC_DT,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.AIS_FOC_DT,'MM/DD/YYY
 58             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_FOC_DT, ';
 59             end if;
 60             if nvl (:old.MSN_AR_TYP_CD,'null')!=nvl(:new.MSN_AR_TYP_CD,'null') then
 61             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'MSN_AR_TYP_CD, ';
 62             end if;
 63             if nvl (:old.MSN_AR_TYP_TX,'null')!=nvl(:new.MSN_AR_TYP_TX,'null') then
 64             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'MSN_AR_TYP_TX, ';
 65             end if;
 66             if nvl (:old.SCRTY_CLSFCTN_TX,'null')!=nvl(:new.SCRTY_CLSFCTN_TX,'null') then
 67             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'SCRTY_CLSFCTN_TX, ';
 68             end if;
 69             if nvl (:old.AIS_DSD_ID,'null')!=nvl(:new.AIS_DSD_ID,'null') then
 70             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_DSD_ID, ';
 71             end if;
 72             if nvl (:old.AIS_DSD_CD,'null')!=nvl(:new.AIS_DSD_CD,'null') then
 73             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_DSD_CD, ';
 74             end if;
 75             if nvl (:old.FAR_NM,'null')!=nvl(:new.FAR_NM,'null') then
 76             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'FAR_NM, ';
 77             end if;
 78             if nvl (:old.AIS_UPGRADE_RMK_TX,'null')!=nvl(:new.AIS_UPGRADE_RMK_TX,'null') then
 79             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_UPGRADE_RMK_TX, ';
 80             end if;
 81             if nvl (to_char(:old.AIS_RCRD_CRTN_ZZ,'MM/DD/YYYY'),'null')!=nvl(to_char(:new.AIS_RCRD_CRTN_Z
 82             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_RCRD_CRTN_ZZ, ';
 83             end if;
 84             if nvl (to_char(:old.AIS_RCD_ZZ,'MM/DD/YYYY'),'null')!=nvl (to_char(:new.AIS_RCD_ZZ,'MM/DD/YY
 85             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_RCD_ZZ, ';
 86             end if;
 87             if nvl (:old.AIS_DSC_TX,'null')!=nvl(:new.AIS_DSC_TX,'null') then
 88             ais_chng_dsc_tx_v := ais_chng_dsc_tx_v || 'AIS_DSC_TX, ';
 89             end if;
 90  insert into s_ais
 91     (S_AIS_ID,
 92     CMNQ_PART_ID,
 93     AIS_NM,
 94     AIS_ACRYNM_TX,
 95     AIS_CT_CD,
 96     ORG_ID,
 97     AIS_EXT_DSC_TX,
 98     AIS_IOC_DT,
 99     AIS_FOC_DT,
100     MSN_AR_TYP_CD,
101     MSN_AR_TYP_TX,
102     SCRTY_CLSFCTN_TX,
103     AIS_DSD_ID,
104     AIS_DSD_CD,
105     FAR_NM,
106     AIS_UPGRADE_RMK_TX,
107     AIS_RCRD_CRTN_ZZ,
108     AIS_RCD_ZZ,
109     AIS_DSC_TX,
110     AMCAT_ID,
111     AMCAT_PRIM_TYP,
112     ais_chng_cd,
113     ais_mod_c_dt,
114     ais_d_src,
115     ais_chng_dsc_tx,
116     ais_cnfrm_rcpt_flg)
117     values
118     (S_AIS_ID_SEQ.NEXTVAL,
119     :old.cmnq_part_id,
120     :new.AIS_NM,
121     :new.AIS_ACRYNM_TX,
122     :new.AIS_CT_CD,
123     :new.ORG_ID,
124     :new.AIS_EXT_DSC_TX,
125     :new.AIS_IOC_DT,
126     :new.AIS_FOC_DT,
127     :new.MSN_AR_TYP_CD,
128     :new.MSN_AR_TYP_TX,
129     :new.SCRTY_CLSFCTN_TX,
130     :new.AIS_DSD_ID,
131     :new.AIS_DSD_CD,
132     :new.FAR_NM,
133     :new.AIS_UPGRADE_RMK_TX,
134     :new.AIS_RCRD_CRTN_ZZ,
135     :new.AIS_RCD_ZZ,
136     :new.AIS_DSC_TX,
137     :new.AMCAT_ID,
138     :new.AMCAT_PRIM_TYP,
139     'U',
140     sysdate,
141     'CDT',
142     substr(ais_chng_dsc_tx_v,1,499),
143     'N');
144  end if;
145  fetch aisucmnqpartid_crsr into aisu_cmnq_v;
146  Exit when aisucmnqpartid_crsr%notfound;
147  end loop;
148  end if;
149  end loop;
150  close aisucmnqpartid_crsr;
151* END;
SQL> /
 
Trigger created.
 
SQL> ed
Wrote file afiedt.buf
  1  UPDATE CDB.AIS SET CDB.AIS.AIS_NM = 'test trigger update'
  2* WHERE CDB.AIS.CMNQ_PART_ID=999999997
SQL> /
UPDATE CDB.AIS SET CDB.AIS.AIS_NM = 'test trigger update'
           *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "CDB.S_U_AIS_ID_SEQ_TR", line 37
ORA-04088: error during execution of trigger 'CDB.S_U_AIS_ID_SEQ_TR'
 
 
SQL>

Open in new window

ASKER CERTIFIED SOLUTION

lulubell-b

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer