All Forums Tools
29 Nov 2007
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...

Someshnr 53 posts Joined 06/07
29 Nov 2007

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

Fred 1096 posts Joined 08/04
29 Nov 2007

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;...

30 Nov 2007

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

Balamurugan B 81 posts Joined 09/07
30 Nov 2007

Hi Manish,You can get all teradata related stuffs in web page, check the pdfs under ‘Data Warehousing/Teradata Database’Regards,Balamurugan


You must sign in to leave a comment.