Need info about exception handling in teradata

Tools & Utilities
Enthusiast

Need info about exception handling in teradata

I need to implement exception handling in bteq script.
Can it be done like its done in pl/sql in Oracle.
Actually single script contains about 20 different sql statements(create/insert/delete) if any one gives error and when script is ran again, it gives errors like table created already exists....I cannot go and write drop for tables there.....can I hande it by catching in some variable and handling errorcode or something like that...
4 REPLIES
Enthusiast

Re: Need info about exception handling in teradata

You need to combine individual pieces of SQL into a group of SQL to create a transaction and put inside BT ET statement. Do a normal .IF ERRORCODE checking after each statement, and quit if there is an error. Also you need to do ERRORCODE check after the ET statement.

Do something like :
BT;
CREATE Table A....;
.IF ERRORCODE <> 0 THEN .GOTO Bye_Bye;
Insert into A....;
.IF ERRORCODE <> 0 THEN .GOTO Bye_Bye;
Delete A ....;
.IF ERRORCODE <>0 THEN .GOTO Bye_Bye;
ET;
.IF ERRORCODE = 0 THEN .GOTO NextStep
.LABEL Bye_Bye
.QUIT ERRORCODE
Teradata Employee

Re: Need info about exception handling in teradata

You can also check based on the "severity" (ERRORLEVEL). That allows you to use .SET ERRORLEVEL to (temporarily) modify the severity of selected ERRORCODEs. For example:

* Change "object does not exist" error to informational only.
.SET ERRORLEVEL 3807 SEVERITY 0;
DROP TABLE MyDB.MyTable;
.IF ERRORLEVEL > 4 THEN .QUIT ERRORCODE;
.IF ERRORLEVEL > 0 THEN .REMARK '*** JUST A WARNING ***';
* If we get "object does not exist on some other statement later, we DO want an error.
.SET ERRORLEVEL 3807 SEVERITY 8;
...
Enthusiast

Re: Need info about exception handling in teradata

Thanks for help...

this will surely solve the problem i guess...can u recomend any bbok to study implementation of pl/sql in teradata...
Enthusiast

Re: Need info about exception handling in teradata

Hi Manish,

You can get all teradata related stuffs in www.info.teradata.com web page, check the pdfs under ‘Data Warehousing/Teradata Database’

Regards,
Balamurugan