Home » SQL & PL/SQL » SQL & PL/SQL » while executing procedure : Savepoint never established (Oracle 10g, Windows Server 2003 Service Pack 2, 32 bit operating system)
while executing procedure : Savepoint never established [message #353708] Tue, 14 October 2008 15:58 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Hi I am trying to execute the following procedure which has got compiled successfully. but getting following error while executing.

SQL> CREATE OR REPLACE PROCEDURE PURGE_DATA
  2  (
  3   CutOffDate   IN   TIMESTAMP DEFAULT ADD_MONTHS(SYSDATE,-6)
  4  )
  5  IS
  6   StartDate    TIMESTAMP;
  7      Now      TIMESTAMP; 
  8  BEGIN
  9   SELECT min(ENTRYTIME) into Now FROM outbound_sms;
 10   StartDate := Now;
 11  SELECT MIN(RECEIVETIME) INTO NOW FROM REPORTINCOMINGSMS;
 12   IF (Now IS NOT NULL) AND (Now < StartDate) THEN
 13    StartDate := Now;
 14   END IF;
 15   SELECT MIN(SENTTIME) INTO NOW FROM REPORTSMSRESPONSE;
 16   IF (Now IS NOT NULL) AND (Now < StartDate) THEN
 17    StartDate := Now;
 18   END IF;
 19   NOW := TO_DATE(StartDate,'DD-MON-YY');
 20   WHILE NOW <= CutOffDate
 21   LOOP
 22    SAVEPOINT SP_BEFORE_DELETE;
 23    DELETE OUTBOUND_SMS WHERE ENTRYTIME < Now;
 24    DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < Now;
 25    DELETE REPORTSMSRESPONSE WHERE SENTTIME < Now;
 26    COMMIT;
 27    Now := Now + 1;
 28   END LOOP;
 29  EXCEPTION
 30   WHEN OTHERS
 31   THEN ROLLBACK TO SAVEPOINT SP_BEFORE_DELETE;
 32  END PURGE_DATA;
 33  /

Procedure created.

SQL> EXECUTE PURGE_DATA;
BEGIN PURGE_DATA; END;

*
ERROR at line 1:
ORA-01086: savepoint 'SP_BEFORE_DELETE' never established
ORA-06512: at "SMSCONNECT.PURGE_DATA", line 31
ORA-01830: date format picture ends before converting entire input string
ORA-06512: at line 1


Why the save point is not established ?
Re: while executing procedure : Savepoint never established [message #353733 is a reply to message #353708] Tue, 14 October 2008 22:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member




Make the exception block specifically for the following section .


20   WHILE NOW <= CutOffDate
 21   LOOP
 22    SAVEPOINT SP_BEFORE_DELETE;
 23    DELETE OUTBOUND_SMS WHERE ENTRYTIME < Now;
 24    DELETE REPORTINCOMINGSMS WHERE RECEIVETIME < Now;
 25    DELETE REPORTSMSRESPONSE WHERE SENTTIME < Now;
 26    COMMIT;
 27    Now := Now + 1;
 28   END LOOP;


This error is because exception is raised before it reaches the the SAVEPOINT declaration.

Thumbs Up
Rajuvan.
Re: while executing procedure : Savepoint never established [message #353752 is a reply to message #353708] Wed, 15 October 2008 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Again this weird construct.
Why don't you simply delete where date < cutoffdate?!
Re: while executing procedure : Savepoint never established [message #353787 is a reply to message #353708] Wed, 15 October 2008 02:05 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

Thanks,

Frank i have no explanation about that, and i know you are right. i gotta do it that way only because i was told to do so. Confused
Re: while executing procedure : Savepoint never established [message #353788 is a reply to message #353787] Wed, 15 October 2008 02:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What do you mean? How can someone tell you how to do your code?
Why doesn't that person himself do the coding?
Ask him why you need to do it like this. Explain him why you think it is not necessary.
It looks to me like either of you two misunderstood, so it might be best to get it clarified now. Otherwise you will end up with either the wrong code (he was right and you misunderstood) or ridiculous code (he was wrong).
Previous Topic: Problem in database trigger
Next Topic: Login to UNIX from Oracle stored procedure
Goto Forum:
  


Current Time: Mon Dec 05 04:46:24 CST 2016

Total time taken to generate the page: 0.13730 seconds