1: package body ALR_DBTRIGGER as -- package body
2: /* $Header: ALREDBTB.pls 120.5.12010000.2 2008/11/14 21:35:51 jwsmith ship $ */
3:
4:
5: --
77: sqlstmt VARCHAR2(2000);
78: begin
79:
80: -- Only do debug logging if Diagnostics is enabled
81: if alr_dbtrigger.DIAGNOSTICS = 'Y' then
82:
83: DEBUG_SEQ := DEBUG_SEQ + 1;
84:
85: sqlstmt := 'insert into alr_dbtrigger_debug'||
81: if alr_dbtrigger.DIAGNOSTICS = 'Y' then
82:
83: DEBUG_SEQ := DEBUG_SEQ + 1;
84:
85: sqlstmt := 'insert into alr_dbtrigger_debug'||
86: '(type,creation_date,stmt) values('''||
87: DEBUG_SEQ||''',sysdate,'''||TXT||''')';
88:
89: c := dbms_sql.open_cursor;
109: begin
110: c := dbms_sql.open_cursor;
111:
112: begin
113: dbms_sql.parse(c, 'delete alr_dbtrigger_debug', dbms_sql.native);
114: rows_processed := dbms_sql.execute(c);
115: exception
116: when others then
117: NULL; -- do nothing
132:
133: --
134: -- Fetch the APPLSYS schema, only once.
135: --
136: if alr_dbtrigger.APPLSYS_SCHEMA is NULL then
137: begin
138:
139: select fou.oracle_username
140: into t_schema
147: -- but this code is shared with aluddt.sql, called by
148: -- AutoInstall before FND_INSTALLATION gets recreated
149: -- during upgrade.
150:
151: alr_dbtrigger.APPLSYS_SCHEMA := t_schema;
152:
153: exception
154: when others then
155: -- Need to provide an error message here.
160:
161: --
162: -- Fetch the appl shortname for the passed appl ID
163: --
164: if (alr_dbtrigger.TARGET_APPL_ID is NULL) or
165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then
166: begin
167:
168: select application_short_name
161: --
162: -- Fetch the appl shortname for the passed appl ID
163: --
164: if (alr_dbtrigger.TARGET_APPL_ID is NULL) or
165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then
166: begin
167:
168: select application_short_name
169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME
165: (alr_dbtrigger.TARGET_APPL_ID <> APPL_ID) then
166: begin
167:
168: select application_short_name
169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME
170: from fnd_application
171: where application_id = APPL_ID;
172:
173: alr_dbtrigger.TARGET_APPL_ID := APPL_ID;
169: into alr_dbtrigger.TARGET_APPL_SHORT_NAME
170: from fnd_application
171: where application_id = APPL_ID;
172:
173: alr_dbtrigger.TARGET_APPL_ID := APPL_ID;
174:
175: exception
176: when others then
177: -- Need to provide an error message here.
182:
183: --
184: -- Fetch the multi-org flag
185: --
186: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
187: begin
188:
189: select multi_org_flag
190: into alr_dbtrigger.MULTI_ORG_FLAG
186: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
187: begin
188:
189: select multi_org_flag
190: into alr_dbtrigger.MULTI_ORG_FLAG
191: from fnd_product_groups
192: where rownum=1; -- there should only be one row, but just in case
193:
194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
190: into alr_dbtrigger.MULTI_ORG_FLAG
191: from fnd_product_groups
192: where rownum=1; -- there should only be one row, but just in case
193:
194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
195: alr_dbtrigger.MULTI_ORG_FLAG := 'N'; -- default
196: end if;
197:
198: exception
191: from fnd_product_groups
192: where rownum=1; -- there should only be one row, but just in case
193:
194: if alr_dbtrigger.MULTI_ORG_FLAG is NULL then
195: alr_dbtrigger.MULTI_ORG_FLAG := 'N'; -- default
196: end if;
197:
198: exception
199: when others then
205:
206: --
207: -- Fetch the Diagnostics flag
208: --
209: if alr_dbtrigger.DIAGNOSTICS is NULL then
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
207: -- Fetch the Diagnostics flag
208: --
209: if alr_dbtrigger.DIAGNOSTICS is NULL then
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215: end if;
209: if alr_dbtrigger.DIAGNOSTICS is NULL then
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215: end if;
216:
217: -- Try to delete the old debug records
210: begin
211: alr_dbtrigger.DIAGNOSTICS := FND_PROFILE.VALUE('DIAGNOSTICS');
212:
213: if alr_dbtrigger.DIAGNOSTICS is NULL then
214: alr_dbtrigger.DIAGNOSTICS := 'N'; -- default
215: end if;
216:
217: -- Try to delete the old debug records
218: ALR_DEBUG_CLEAN;
218: ALR_DEBUG_CLEAN;
219:
220: exception
221: when others then
222: alr_dbtrigger.DIAGNOSTICS := 'N';
223:
224: end;
225: end if;
226:
318:
319: for CREC in C loop -- fetch all ORACLE IDs
320:
321: if crec.ORG_ID is not NULL and
322: alr_dbtrigger.MULTI_ORG_FLAG = 'Y' then
323: oracle_id_str := crec.ORACLE_USERNAME || crec.ORG_ID;
324: else
325: oracle_id_str := crec.ORACLE_USERNAME;
326: end if;
381: 1) Create the debug table under the APPLSYS account as follows:
382:
383: connect applsys/apps
384:
385: create table alr_dbtrigger_debug
386: (creation_date date,
387: applsys varchar2(30),
388: appsname varchar2(30),
389: target varchar2(30),
392:
393: Grant access to the APPS account and other APPS accounts
394: to be debugged:
395:
396: grant all privileges on alr_dbtrigger_debug to APPS;
397:
398: Create a synonym under the APPS account and other APPS accounts
399: to be debugged:
400:
399: to be debugged:
400:
401: connect apps/apps
402:
403: create synonym alr_dbtrigger_debug for APPLSYS.alr_dbtrigger_debug;
404:
405: 2) Uncomment the below INSERT statement and save this file. */
406:
407: /*
404:
405: 2) Uncomment the below INSERT statement and save this file. */
406:
407: /*
408: insert into alr_dbtrigger_debug values
409: (Sysdate,
410: alr_dbtrigger.APPLSYS_SCHEMA,
411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
412: TARGET_SCHEMA,
406:
407: /*
408: insert into alr_dbtrigger_debug values
409: (Sysdate,
410: alr_dbtrigger.APPLSYS_SCHEMA,
411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
412: TARGET_SCHEMA,
413: DEBUG_SEQ,
414: SQLSTMT);
407: /*
408: insert into alr_dbtrigger_debug values
409: (Sysdate,
410: alr_dbtrigger.APPLSYS_SCHEMA,
411: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
412: TARGET_SCHEMA,
413: DEBUG_SEQ,
414: SQLSTMT);
415: commit;
414: SQLSTMT);
415: commit;
416: */
417:
418: /* 3) Recreate ALR_DBTRIGGER package defined by this file:
419:
420: sqlplus apps/apps @ALREDBTB.pls
421: : (and in any other APPS accounts)
422:
424: problemed alert definition by simply disabling and then
425: re-enabling it. This will attempt to recreate the underlying
426: database trigger(s). If ORA-4068 occurs, try this step once
427: more. (This is not the original error and is caused by the
428: regeneration of the ALR_DBTRIGGER package.)
429:
430: When the error is reproduced, there should be one or more records
431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute
427: more. (This is not the original error and is caused by the
428: regeneration of the ALR_DBTRIGGER package.)
429:
430: When the error is reproduced, there should be one or more records
431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute
433: the statement directly in sqlplus while connected to the APPS
434: account to determine the exact cause.
435:
428: regeneration of the ALR_DBTRIGGER package.)
429:
430: When the error is reproduced, there should be one or more records
431: inserted in the ALR_DBTRIGGER_DEBUG table. Select the records and
432: examine the SQL stmt stored in ALR_DBTRIGGER_DEBUG.STMT. Execute
433: the statement directly in sqlplus while connected to the APPS
434: account to determine the exact cause.
435:
436: Once the issue is resolved, please undo the debugging setup by
441:
442: if ROOT_STMT = 'create' then
443: AD_DDL.create_trigger_in_schema(TARGET_SCHEMA, SQLSTMT);
444: else
445: AD_DDL.do_ddl (alr_dbtrigger.APPLSYS_SCHEMA,
446: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
447: STMT_TYPE,
448: SQLSTMT,
449: OBJECT_NAME);
442: if ROOT_STMT = 'create' then
443: AD_DDL.create_trigger_in_schema(TARGET_SCHEMA, SQLSTMT);
444: else
445: AD_DDL.do_ddl (alr_dbtrigger.APPLSYS_SCHEMA,
446: alr_dbtrigger.TARGET_APPL_SHORT_NAME,
447: STMT_TYPE,
448: SQLSTMT,
449: OBJECT_NAME);
450:
914: OID, ONAME, TBL_NAME, EVENT_MODE, TRGNAM);
915:
916: if TRGNAM is not NULL then
917: -- Trigger created successfully (enabled)
918: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
919: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
920: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
921: TRGNAM;
922: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
915:
916: if TRGNAM is not NULL then
917: -- Trigger created successfully (enabled)
918: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
919: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
920: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
921: TRGNAM;
922: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: ONAME;
916: if TRGNAM is not NULL then
917: -- Trigger created successfully (enabled)
918: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
919: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
920: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
921: TRGNAM;
922: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: ONAME;
924: end if;
918: alr_dbtrigger.NUMBER_OF_TRIGGERS :=
919: alr_dbtrigger.NUMBER_OF_TRIGGERS + 1;
920: alr_dbtrigger.CREATED_TRIGGERS(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
921: TRGNAM;
922: alr_dbtrigger.CREATED_TRIG_ONAME(alr_dbtrigger.NUMBER_OF_TRIGGERS) :=
923: ONAME;
924: end if;
925:
926: if ENABLED_FLAG = 'N' then
975: ALR_DEBUG('--->> Entering CREATE_EVENT_DB_TRIGGER');
976:
977: INIT_GLOBALS (TBL_APPLID);
978:
979: alr_dbtrigger.NUMBER_OF_TRIGGERS:=0;
980: alr_dbtrigger.BAD_ORACLE_USERNAMES:='';
981:
982: for CREC in C loop -- process all ORACLE IDs
983:
976:
977: INIT_GLOBALS (TBL_APPLID);
978:
979: alr_dbtrigger.NUMBER_OF_TRIGGERS:=0;
980: alr_dbtrigger.BAD_ORACLE_USERNAMES:='';
981:
982: for CREC in C loop -- process all ORACLE IDs
983:
984: if IS_ENABLE = 'N' or crec.ENABLED_INST = 'N' then
1010:
1011: when NONEXISTENT_TABLE or PLSQL_UNCOMPILED then
1012:
1013: -- Save ORACLE ID to display to user
1014: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1015: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1016: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1017: end if;
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1011: when NONEXISTENT_TABLE or PLSQL_UNCOMPILED then
1012:
1013: -- Save ORACLE ID to display to user
1014: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1015: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1016: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1017: end if;
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1012:
1013: -- Save ORACLE ID to display to user
1014: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1015: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1016: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1017: end if;
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1020:
1014: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1015: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1016: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1017: end if;
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1020:
1021: when others then
1022:
1015: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1016: alr_dbtrigger.BAD_ORACLE_USERNAMES||', ';
1017: end if;
1018: alr_dbtrigger.BAD_ORACLE_USERNAMES :=
1019: alr_dbtrigger.BAD_ORACLE_USERNAMES || crec.ORACLE_USERNAME;
1020:
1021: when others then
1022:
1023: raise ERROR_ROLLBACK;
1024: end;
1025:
1026: end loop;
1027:
1028: if alr_dbtrigger.BAD_ORACLE_USERNAMES is NOT NULL then
1029:
1030: raise NONEXISTENT_TABLE;
1031: end if;
1032:
1037: FND_MESSAGE.SET_NAME('ALR', 'TRIGGER-NO TABLE IN ACCOUNT');
1038: -- this message is uppercased in fnd_new_messages and fnd_messages
1039: FND_MESSAGE.SET_TOKEN('TABLE_NAME', TBL_NAME);
1040: FND_MESSAGE.SET_TOKEN('ORACLE_USERNAME',
1041: alr_dbtrigger.BAD_ORACLE_USERNAMES, FALSE);
1042: APP_EXCEPTION.RAISE_EXCEPTION;
1043:
1044: when ERROR_ROLLBACK then
1045:
1050: FND_MESSAGE.SET_TOKEN(
1051: 'ROUTINE', 'CREATE_EVENT_DB_TRIGGER', FALSE);
1052:
1053: -- "Rollback"
1054: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1055: DROP_EVENT_DB_TRIGGER
1056: (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1057: alr_dbtrigger.CREATED_TRIGGERS(I));
1058: -- Note that table appl is the same even when
1052:
1053: -- "Rollback"
1054: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1055: DROP_EVENT_DB_TRIGGER
1056: (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1057: alr_dbtrigger.CREATED_TRIGGERS(I));
1058: -- Note that table appl is the same even when
1059: -- dropping trigger off different accounts.
1060: end loop;
1053: -- "Rollback"
1054: for I in 1 .. alr_dbtrigger.NUMBER_OF_TRIGGERS loop
1055: DROP_EVENT_DB_TRIGGER
1056: (alr_dbtrigger.CREATED_TRIG_ONAME(I),
1057: alr_dbtrigger.CREATED_TRIGGERS(I));
1058: -- Note that table appl is the same even when
1059: -- dropping trigger off different accounts.
1060: end loop;
1061:
1262: begin
1263:
1264: ALR_DEBUG_CLEAN;
1265:
1266: end ALR_DBTRIGGER;