Home » SQL & PL/SQL » SQL & PL/SQL » Mutating Table Help (10g)
Mutating Table Help [message #299344] Mon, 11 February 2008 10:13 Go to next message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Hello All
I am trying to place a validation on a table to ensure that there are no overlaps on the same table.
Due to mutating error, I created 3 triggers to populate the pl-sql table and use the values in validating the
values from being entered. The problem I am having is that the way the cursor handles the Date values.
The Start and Stoptime are being passed in as MM-DD-YY format making it look like 01-JAN-03 (meaning 0003 instead of 2003)
I tried formatting but still doesnt work.

can anyone help to fix this problem. I need the date passed in the cursor to validate as a regular DD-MON-YYYY format.

Please see the code below:

Thanks
Leonard



PL_SqL Table declaration

TYPE ExtBillRecType IS RECORD(
SiteID VARCHAR2(13),
StartTime DATE,
StopTime DATE);

TYPE T_ExtBill IS TABLE OF ExtBillRecType INDEX BY BINARY_INTEGER;

vt_ExtBill T_ExtBill;
vn_ExtBillCnt INTEGER := 0;



CREATE OR REPLACE TRIGGER TR_EPCEXTERNALBIL_BIUDS
BEFORE INSERT OR UPDATE ON TEMPTABLE
BEGIN
dbms_output.put_line('cleaning up table');
vt_ExtBill.DELETE ;
END;


CREATE OR REPLACE TRIGGER TR_TEMPTABLE_AIUDR
AFTER DELETE OR INSERT OR UPDATE ON PWRLINE.TEMPTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
vn_ExtBillCnt := vn_ExtBillCnt + 1;
vt_ExtBill(vn_ExtBillCnt).SITEID := :NEW.SITEID;
vt_ExtBill(vn_ExtBillCnt).StartTime := :NEW.STARTTIME;
vt_ExtBill(vn_ExtBillCnt).StopTime := :NEW.STOPTIME;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END;


CREATE OR REPLACE TRIGGER TR_TEMPTABLE_AIUDS
AFTER DELETE OR INSERT OR UPDATE ON PWRLINE.TEMPTABLE
REFERENCING NEW AS NEW OLD AS OLD
DECLARE

CURSOR c_Overlap(in_SiteID IN TEMPTABLE.SITEID%TYPE, in_StopTime IN DATE, in_StartTime IN DATE) IS
SELECT COUNT(*)
FROM TEMPTABLE
WHERE siteid = trim(in_SiteID)
AND STARTTIME <= in_StopTime
AND STOPTIME >= in_StartTime;

v_count NUMBER := 0;
v_starttime date ;
v_stoptime date ;
BEGIN

IF vn_ExtBillCnt >= 1 THEN

FOR v_LoopIndx IN 1 .. vn_ExtBillCnt LOOP
IF INSERTING OR UPDATING THEN
v_count := 0;

v_starttime := vt_ExtBill(vn_ExtBillCnt).StartTime;
v_stoptime := vt_ExtBill(vn_ExtBillCnt).StopTime ;

OPEN c_Overlap(trim(vt_ExtBill(v_LoopINdx).siteid),
v_stoptime,
v_starttime);
FETCH c_Overlap
INTO v_count;
CLOSE c_overlap;

IF v_count > 0 THEN
vn_ExtBillCnt := 0;
vt_ExtBill.DELETE;
RAISE_APPLICATION_ERROR(-20023,
'Cannot Insert a valid record to overlap another valid record');
END IF;

END IF;
END LOOP;
-- Clear the global variables.
vn_ExtBillCnt := 0;
vt_ExtBill.DELETE;

END IF;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END TR_TEMPTABLE_AIUDS;
Re: Mutating Table Help [message #299348 is a reply to message #299344] Mon, 11 February 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Code is easier to read if it is formatted.
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

2/ Think what will happen if 2 or more processes execute an insert or update statement at the same time.

3/ Consider putting all the "when others" blocks in comment during your tests and until you know what to do in these ones.

Regards
Michel
Re: Mutating Table Help [message #299352 is a reply to message #299344] Mon, 11 February 2008 10:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
I am not sure that the problem is what you think it is. If the temptable.starttime and temptable.stoptime are date data types as they should be, then there should not be a problem. Even if they are varchar2, 03 should be 2003. So, I suspect that either that is not the problem or your data is getting corrupted before it is entered into the table. It would help to have a full example. In the meanwhile, here is a link to a general example on how to avoid overlapping values of any data type:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:474221407101
Re: Mutating Table Help [message #299616] Tue, 12 February 2008 13:30 Go to previous message
Leonard Martin
Messages: 45
Registered: May 2002
Location: Canada
Member
Thanks
I followed the example by Tom and it worked.1
Much appreciated

Leonard
Previous Topic: FILE CONVERSION
Next Topic: Schedule to execute SP's...ist possible?
Goto Forum:
  


Current Time: Tue Dec 06 12:31:20 CST 2016

Total time taken to generate the page: 0.15792 seconds