AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of lulubell-b
lulubell-b
 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
Oracle DatabaseSQL
Avatar of undefined
Last Comment
lulubell-b
8/22/2022 - Mon
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 ?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent