Archives of the TeradataForum

Message Posted: Wed, 10 Dec 2008 @ 16:25:16 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello Sridhar:

First of all, if I were you I would spend a bit of time to see if I could eliminate the stored procedure and replace it with straight SQL. Teradata SQL allows you to do MANY things that Oracle SQL does not allow. Plus, you get maximum parallel processing with SQL and that is not necessarily the case with a stored procedure. It might be time well spent now and for the future. After all, I assume you are now using Teradata because Oracle could no longer handle your workload. Why put the same constraints on Teradata that Oracle required? Does this make sense?

With that stated, you will need to build a HANDLER routinue. They come in two types: CONTINUE and EXIT. Then you will user either the Oracle type of error indicator called SQLSTATE (char(5) or the Teradata one called SQLCODE (numeric)to check for specific errors and then "handle them." Otherwise you can reference SQLEXCEPTION which would be any exception not specifically handled via one of the above error indicators.

Example:

     DECLARE continue HANDLER
     FOR SQLSTATE '41020', SQLSTATE '52100'
          insert into my_err_table values (:SQLSTATE,date, time, 'Continue
     processing', my_value1, my_value2, ...);
     DECLARE exit HANDLER
     FOR SQLEXCEPTION
          insert into my_err_table values (:SQLSTATE,date, time, 'Exiting
     processing', my_value1, my_value2, ...);

Of course these are merely VERY generic examples I wrote and you would need to store/record any important information for debugging/evaluating/whatever. Reading the manual or a GOOD book on the topic might also be a great place for you to start the process.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>