Teradata Stored Procedures & User Defined Exceptions

Database
Enthusiast

Teradata Stored Procedures & User Defined Exceptions

Hi all,

we are running on Teradata 13.10.01.05, on Suse Linux. The session transaction mode we are using is ANSI.

I am investigating how to use user defined exceptions within Teradata stored procedures.

I have created the following stored procedures:

replace procedure pr_test1()
begin
declare test_condition condition for sqlstate value '22012';

declare x int default 1;

if x = 1 then
signal test_condition;
end if;

end;

replace procedure pr_test2()
begin

call pr_test1();

end;

Procedure pr_test1() declares a condition, and associates the SQLState value 22012 to it. Procedure pr_test2() invokes pr_test1().

When I run this through BTEQ, this happily works.

Now, I'd like to specify a SQLState value for a user defined exception. The documentation (Teradata Database - SQL Stored Procedures and Embedded SQL guide, release 13.10 , page 455) states that SQLStates whose first 2 characters are 'U0' indicate a user defined exception. So, I recoded procedure pr_test1() as follows:

replace procedure pr_test1()
begin
declare test_condition condition for sqlstate value 'U0000';

declare x int default 1;

if x = 1 then
signal test_condition;
end if;

end;

Now, when I execute pr_test2() through BTEQ, I am presented with the following message:

call pr_test2();
*** Error 7600 PR_TEST2:Internal error in RTS subsystem.
*** Total elapsed time was 5 seconds.

Which looks to me like either: a problem with our installation or a bug in Teradata?

So my questions are:

1) Is my interpretation of the Teradata documentation correct - i.e. am I correct to specify SQLStates prefixed with 'U0' to define a user defined exception?

2) If my interpretation is not correct, can anyone provide a valid example of user defined exception in a Teradata stored procedure.

3) Does anyone know whether the message stating 'Internal error in RTS subsystem' is a known problem?

Thanks,

Andrew. 

2 REPLIES
Teradata Employee

Re: Teradata Stored Procedures & User Defined Exceptions

Hi Andrew,

I am facing the same problem you mentioned above.

Were you able to sort the issue out? If yes, please help.

Thanks,

Umar

Re: Teradata Stored Procedures & User Defined Exceptions

Andrew/Umar,

I encountered the same problem.

The resolution is to use a 'T' rather than 'U' for the user defined exception, for example use SQLstate value 'T0075' instead of 'U0075'.

The fix for this problem will be in version 13.10.04.x and above.

Peter.