Home » SQL & PL/SQL » SQL & PL/SQL » Transaction Management Oracle (Oracle 10g, Windows Server 2003 Service Pack 2, 32 bit operating system)
Transaction Management Oracle [message #353369] Mon, 13 October 2008 11:22 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hi,

I want to do transaction management, for the bellow procedure.

how do i do it, i have gone through the oracle 10g complete reference but did not get what i wanted.

Bellow is the procedure which will delete some records from three table based on some date criteria.

CREATE OR REPLACE PROCEDURE PURGE_DATA
(
    	CutOffDate  IN   TIMESTAMP DEFAULT ADD_MONTHS(SYSDATE,-6),
	StartDate   OUT timestamp,
   Now    OUT timestamp
)
AS
BEGIN
 SELECT min(ENTRYTIME) into Now FROM outbound_sms order by entrytime;
 StartDate := Now;
SELECT MIN(RECEIVETIME) INTO NOW FROM REPORTINCOMINGSMS;
 IF (Now IS NOT NULL) AND (Now < StartDate) THEN
  StartDate := Now;
 END IF;
 SELECT MIN(SENTTIME) INTO NOW FROM REPORTSMSRESPONSE;
 IF (Now IS NOT NULL) AND (Now < StartDate) THEN
  StartDate := Now;
 END IF;
 NOW := TO_DATE(StartDate,'DD-MON-YY');
	WHILE NOW <= CutOffDate LOOP
		SAVEPOINT MYSAVEPOINT;
		DELETE OUTBOUND_SMS WHERE ENTRYTIME < NOW;		
		DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < NOW;
		DELETE REPORTSMSRESPONSE WHERE SENTTIME < NOW;
		COMMIT;
	END LOOP;
END PURGE_DATA;


Now i want to do transaction management for delete operation, Like is something goes wrong while deleting rollback <savepoint-name>. But i don't know what kind of error database might throw while performing delete operation.

please help me for the same.

Thanks,
Jigar Naik
Re: Transaction Management Oracle [message #353374 is a reply to message #353369] Mon, 13 October 2008 11:55 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Not sure on this?

WHILE NOW <= CutOffDate LOOP

won't that create a infinite loop? What exactly are you trying to do?
Re: Transaction Management Oracle [message #353375 is a reply to message #353369] Mon, 13 October 2008 12:04 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hey, thanks

you are right, there should be if condition.

CREATE OR REPLACE PROCEDURE PURGE_DATA
(
    	CutOffDate  IN   TIMESTAMP DEFAULT ADD_MONTHS(SYSDATE,-6),
	StartDate   OUT timestamp,
   Now    OUT timestamp
)
AS
BEGIN
 SELECT min(ENTRYTIME) into Now FROM outbound_sms order by entrytime;
 StartDate := Now;
SELECT MIN(RECEIVETIME) INTO NOW FROM REPORTINCOMINGSMS;
 IF (Now IS NOT NULL) AND (Now < StartDate) THEN
  StartDate := Now;
 END IF;
 SELECT MIN(SENTTIME) INTO NOW FROM REPORTSMSRESPONSE;
 IF (Now IS NOT NULL) AND (Now < StartDate) THEN
  StartDate := Now;
 END IF;
 NOW := TO_DATE(StartDate,'DD-MON-YY');
	IF (NOW <= CutOffDate) THEN
		SAVEPOINT MYSAVEPOINT;
		DELETE OUTBOUND_SMS WHERE ENTRYTIME < NOW;		
		DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < NOW;
		DELETE REPORTSMSRESPONSE WHERE SENTTIME < NOW;
		COMMIT;
	END IF;
END PURGE_DATA;


Well i want to delete records which are older then 6 months from the data user gives as an input parameter, if user there is no date as an input parameter then the current date will be considered.

Re: Transaction Management Oracle [message #353380 is a reply to message #353375] Mon, 13 October 2008 12:44 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
1. What is the purpose of the ORDER BY clause?
2. Do not use TO_DATE on a DATE column or variable.
Re: Transaction Management Oracle [message #353385 is a reply to message #353369] Mon, 13 October 2008 12:55 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

ya the order by clause is not required.

and is there any particular reason we should not use TO_DATE FUNCTION ON DATE ? in my procedure i wanted to change the format of date and wanted to assign to another date type variable, what is the alternative ?
Re: Transaction Management Oracle [message #353386 is a reply to message #353385] Mon, 13 October 2008 13:06 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Because it's already a DATE I think. Also not sure if this is possible:

TO_DATE(StartDate,'DD-MON-YY');

Since 'StartDate' is timestamp? Did you compile your code?
Re: Transaction Management Oracle [message #353387 is a reply to message #353369] Mon, 13 October 2008 13:10 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

ya it's working fine i have compiled the code.

but i am keen to know about exception handling or can say transaction management, if something goes wrong while delete operation i need to rollback the transaction.

So how do i know something has gone wrong while delete operation ?
Re: Transaction Management Oracle [message #353392 is a reply to message #353387] Mon, 13 October 2008 13:31 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I'm no expert. But I can't think of any exception you might encounter in a delete statement as long as the syntax is valid. The fact is that it's either you deleted some or none of the records. Which will have an error on the business side in case you deleted the *wrong* records.

You can look on implicit cursor attributes if you want to check the number of records deleted.

SAVEPOINT MYSAVEPOINT;
DELETE OUTBOUND_SMS WHERE ENTRYTIME < NOW;		
DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < NOW;
DELETE REPORTSMSRESPONSE WHERE SENTTIME < NOW;
COMMIT;


Here, it depends on your requirements, if its ok for you to continue the process of deletion even if you *might* encounter an error on some of the deletes you have. You might do something like

savepoint a
begin
delete from ...
exception
...
rollback to savepoint a...
end;

savepoint b

begin
delete..
exception
rollback to b...
end;

In this way you can preserve I think the previous deletes you had. I'm sure the experts here in this forum would give something much better.

[Updated on: Mon, 13 October 2008 13:43]

Report message to a moderator

Re: Transaction Management Oracle [message #353394 is a reply to message #353392] Mon, 13 October 2008 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
An example of error:
SQL> create table p (pid integer primary key);

Table created.

SQL> create table c (cid integer primary key, pid integer references p);

Table created.

SQL> insert into p values (1);

1 row created.

SQL> insert into c values (1,1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete p;
delete p
*
ERROR at line 1:
ORA-02292: integrity constraint (MICHEL.SYS_C005740) violated - child record found

Regards
Michel
Re: Transaction Management Oracle [message #353396 is a reply to message #353394] Mon, 13 October 2008 13:46 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Thank you very much for that Michel.
Re: Transaction Management Oracle [message #353398 is a reply to message #353369] Mon, 13 October 2008 13:57 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hey Michel,

Thanks and i got your point,

But is there any super type of all the exception ?

Regards,
Jigar Naik.
Re: Transaction Management Oracle [message #353400 is a reply to message #353398] Mon, 13 October 2008 14:03 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I think 'OTHERS' could be considered the super-class 'Exception'.
Re: Transaction Management Oracle [message #353405 is a reply to message #353369] Mon, 13 October 2008 14:22 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

ok, i added others for handling exception,

The following code doesn't compile, getting the following error

  19    NOW := TO_CHAR(StartDate,'DD-MON-YY');
 20    IF (NOW <= CutOffDate) THEN
 21     SAVEPOINT MYSAVEPOINT;
 22     DELETE OUTBOUND_SMS WHERE ENTRYTIME < NOW;
 23     DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < NOW;
 24     DELETE REPORTSMSRESPONSE WHERE SENTTIME < NOW;
 25     COMMIT;
 26    END IF;
 27   EXCEPTION
 28    WHEN OTHERS
 29    THEN ROLLBACK mysavepoint;
 30*  END PURGE_DATA;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show error;
Errors for PROCEDURE PURGE_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
29/8     PL/SQL: SQL Statement ignored
29/17    PL/SQL: ORA-02181: invalid option to ROLLBACK WORK

Where as the bellow code words fine.
  IF (NOW <= CutOffDate) THEN
   SAVEPOINT MYSAVEPOINT;
   DELETE OUTBOUND_SMS WHERE ENTRYTIME < NOW;
   DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < NOW;
   DELETE REPORTSMSRESPONSE WHERE SENTTIME < NOW;
   COMMIT;
  END IF;
 EXCEPTION
  WHEN OTHERS 
  THEN ROLLBACK;
 END PURGE_DATA;


What is the reason ?
Re: Transaction Management Oracle [message #353407 is a reply to message #353405] Mon, 13 October 2008 14:25 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Here. And here.

[Updated on: Mon, 13 October 2008 14:26]

Report message to a moderator

icon7.gif  Re: Transaction Management Oracle [message #353408 is a reply to message #353369] Mon, 13 October 2008 14:38 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

thanks,

so bellow is my final stored procedure. looks like fine. Smile

CREATE OR REPLACE PROCEDURE PURGE_DATA
(
     CutOffDate  IN   TIMESTAMP DEFAULT ADD_MONTHS(SYSDATE,-6),
 StartDate   OUT timestamp,
   Now    OUT timestamp
)
AS
BEGIN
 SELECT min(ENTRYTIME) into Now FROM outbound_sms;
 StartDate := Now;
SELECT MIN(RECEIVETIME) INTO NOW FROM REPORTINCOMINGSMS;
 IF (Now IS NOT NULL) AND (Now < StartDate) THEN
  StartDate := Now;
 END IF;
 SELECT MIN(SENTTIME) INTO NOW FROM REPORTSMSRESPONSE;
 IF (Now IS NOT NULL) AND (Now < StartDate) THEN
  StartDate := Now;
 END IF;
 NOW := TO_CHAR(StartDate,'DD-MON-YY');
 IF (NOW <= CutOffDate) THEN
  SAVEPOINT SP_BEFORE_DELETE;
  DELETE OUTBOUND_SMS WHERE ENTRYTIME < NOW;
  DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < NOW;
  DELETE REPORTSMSRESPONSE WHERE SENTTIME < NOW;
  COMMIT;
 END IF;
EXCEPTION
 WHEN OTHERS
 THEN ROLLBACK TO SAVEPOINT SP_BEFORE_DELETE;
END PURGE_DATA;
Re: Transaction Management Oracle [message #353410 is a reply to message #353408] Mon, 13 October 2008 14:50 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Just a note. Why did you set the Startdate and Now to OUT parameters? Will you use them from the calling module?
Re: Transaction Management Oracle [message #353411 is a reply to message #353369] Mon, 13 October 2008 14:56 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

nopes it will be used internally by procedure.
the parameter CutOffDate will be passed by the calling application, and if there is no CutOffDate then the default is (current date - 6 months)
Re: Transaction Management Oracle [message #353412 is a reply to message #353411] Mon, 13 October 2008 14:59 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
I think you could have just declared those as a local variables, as to avoid confusion on the usage of the other parameters.
Re: Transaction Management Oracle [message #353414 is a reply to message #353408] Mon, 13 October 2008 15:20 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
naikjigar wrote on Mon, 13 October 2008 15:38
thanks,

so bellow is my final stored procedure. looks like fine. Smile



no, it is not.
Quote:

CREATE OR REPLACE PROCEDURE PURGE_DATA
(
     CutOffDate  IN   TIMESTAMP DEFAULT ADD_MONTHS(SYSDATE,-6),
 StartDate   OUT timestamp,
   Now    OUT timestamp
)
.
.
.
 NOW := TO_CHAR(StartDate,'DD-MON-YY');
 IF (NOW <= CutOffDate) THEN



You are setting a DATE variable to a character string , then you are comparing a character string to a DATE. '01-JAN-08' is less than '02-MAR-05.' Why do you insist on ignoring proper use of datatypes?
Re: Transaction Management Oracle [message #353485 is a reply to message #353408] Tue, 14 October 2008 00:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe I'm not fully awake yet, maybe I should have taken a coffee first, but in my opinion this procedure will not delete anything.
First you select the minimum date from three tables and then you are deleting every record in those three tables that have a date < the absolute minimum..
icon14.gif  Re: Transaction Management Oracle [message #353508 is a reply to message #353369] Tue, 14 October 2008 01:18 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Yes frank. you are right.
where ... < CutOffDate should be there.

Thanks !!
Re: Transaction Management Oracle [message #353510 is a reply to message #353508] Tue, 14 October 2008 01:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Then what's all the preliminary fetching of those min dates for?
Re: Transaction Management Oracle [message #353511 is a reply to message #353369] Tue, 14 October 2008 01:26 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

yes frank you are right, the delete statement should be
delete form ... where .. <CutOfdate;

well i am trying to execute procedure but getting following error.

SQL> SELECT TO_TIMESTAMP(SYSDATE) FROM DUAL;

TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
14-OCT-08 12.00.00 AM

SQL> EXECUTE PURGE_DATA('14-OCT-08 12.00.00 AM');
BEGIN PURGE_DATA('14-OCT-08 12.00.00 AM'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PURGE_DATA'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> 
Re: Transaction Management Oracle [message #353514 is a reply to message #353511] Tue, 14 October 2008 01:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As you can tell by the surrounding quotes, '14-OCT-08 12.00.00 AM' is a string, not a timestamp.
Re: Transaction Management Oracle [message #353518 is a reply to message #353369] Tue, 14 October 2008 01:46 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

but than how do i pass timestamp as an in parameter ?
Re: Transaction Management Oracle [message #353520 is a reply to message #353518] Tue, 14 October 2008 01:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Google really is a great way to find stuff like this.
Oracle convert string timestamp

Does that yield any results?
Re: Transaction Management Oracle [message #353535 is a reply to message #353369] Tue, 14 October 2008 02:26 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Oh there was just a space missing after procedure name.

SQL> execute purge_data ('14-OCT-08 12.00.00.000000000 AM');

PL/SQL procedure successfully completed.

SQL>

Re: Transaction Management Oracle [message #353553 is a reply to message #353535] Tue, 14 October 2008 03:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Same error. Still a string, while a timestamp was expected.
Previous Topic: Query not execute for the first time
Next Topic: help in query
Goto Forum:
  


Current Time: Sat Dec 10 20:44:33 CST 2016

Total time taken to generate the page: 0.11831 seconds