Home » SQL & PL/SQL » SQL & PL/SQL » Exception handling
Exception handling [message #363193] Fri, 05 December 2008 06:01 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
My code is like this -

IF RECORD_COUNT > O THEN
UPDATE NPPI_PURGADO_S SET FECHA_FIN = SYSDATE ' AND ESTADO = 'F' WHERE 'IDENTIFICADOR = ------ AND PASO =
ELSE
WHILE (RECORD_COUNT> REGISTROS_BLOQUE)
EXECUTE IMMEDIATE 'DELETE FROM' || TABLA_MAESTRA || WHERE TABLA_MAESTRA.FEC_ALTA < (SELECT ADD_MONTHS(SYSDATE, -6) FROM DUAL) ;
REGISTROS:= REGISTROS+1;
RECORD_COUNT:= RECORD_COUNT - 1;
RECORDS:=RECORDS + 1 ;
END WHILE;
UPDATE NPPI_PURGADO_S SET FECHA_FIN = SYSDATE , REGISTROS_INICIALES = RECORD_COUNT , REGISTROS = $REGISTROS , STATE = (IF RECORD_COUNT > REGISTROS_MAX SET ESTADO 'F' ) WHERE PASO = $PASO ;


END IF;

I want to throw an error if any problem occurs in the highlighted portion of code. Whatever error is thrown has to be caught in a variable which i need to print in EXCEPTION block later. While printing , i need to update a table also.



Re: Exception handling [message #363201 is a reply to message #363193] Fri, 05 December 2008 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If there is an error you have nothing to do to throw an exception, Oracle will do it for you.

I doubt this code even compile.

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 (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Exception handling [message #363204 is a reply to message #363193] Fri, 05 December 2008 06:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to catch any exceptions raised, then wrap the higlighted section of code in a BEGIN...ECXCEPTION..END block.
In the Exception section, use a WHEN OTHERs to trap all errors, and set any variables that you want.
Re: Exception handling [message #363205 is a reply to message #363193] Fri, 05 December 2008 06:30 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
IF RECORD_COUNT > O THEN
UPDATE NPPI_PURGADO_S SET FECHA_FIN = SYSDATE ' AND ESTADO = 'F' WHERE 'IDENTIFICADOR = ID AND PASO = $PASO
ELSE
WHILE (RECORD_COUNT> REGISTROS_BLOQUE)
EXECUTE IMMEDIATE 'DELETE FROM' || TABLA_MAESTRA || WHERE TABLA_MAESTRA.FEC_ALTA < (SELECT ADD_MONTHS(SYSDATE, -6) FROM DUAL) ;
REGISTROS:= REGISTROS+1;
RECORD_COUNT:= RECORD_COUNT - 1;
RECORDS:=RECORDS + 1 ;
END WHILE;
UPDATE NPPI_PURGADO_S SET FECHA_FIN = SYSDATE , REGISTROS_INICIALES = RECORD_COUNT , REGISTROS = $REGISTROS , STATE = (IF RECORD_COUNT > REGISTROS_MAX SET ESTADO 'F' ) WHERE PASO = $PASO ;

END IF;


Michel what is the problem in this code ? Also let me know how can i go ahead for my concerns ,

I want to throw an error if any problem occurs in the highlighted portion of code. Whatever error is thrown has to be caught in a variable which i need to print in EXCEPTION block later. While printing , i need to update a table also.

Please suggest me..
Re: Exception handling [message #363212 is a reply to message #363205] Fri, 05 December 2008 06:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is repeating your post meant to do?

The only way a 'problem' can occurr is if one of the commands you execute raises an exception.
If you catch this in a local exception handler then you can record which exception it was, and report on it later.

It's possible that you don't want to catch the exception locally, and are actually trying to ask (in a very circumlocutious fashion where the ame of the last exception is stored.
If that is the case, you want to look at SQLERRM and SQLCODE
Re: Exception handling [message #363214 is a reply to message #363193] Fri, 05 December 2008 06:45 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi Jrow..

Actually there was a slight change in the code so only i posted the new correct code.

Could you please explain again as i didn't get what you said.

Hope my question is clear . I will again try to clear my question, If a error occurs in the highlighted portion of the code than an error should be generated , this error should be printed too and after that need to run an update query on one of the table.
Re: Exception handling [message #363215 is a reply to message #363205] Fri, 05 December 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Michel what is the problem in this code ? Also let me know how can i go ahead for my concerns ,

1/ It is not formatted
2/ It will not compile
3/
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Exception handling [message #363231 is a reply to message #363193] Fri, 05 December 2008 07:48 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I see an extra single quote.
Re: Exception handling [message #363233 is a reply to message #363193] Fri, 05 December 2008 08:02 Go to previous message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Thanks Jrow , Michel and joy_division

Actually my concern was mainly about how the exception to be thrown. i was aware code was having few things missing.

Well , now i got the answer i was looking after , some research work.

Thanks,
Shaksing
Previous Topic: Calling external interface procedure
Next Topic: if or case within a select
Goto Forum:
  


Current Time: Sun Dec 11 02:06:48 CST 2016

Total time taken to generate the page: 0.08141 seconds