Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

Database
N/A

Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

Hi! I'm in the process of porting Oracle stored procedures into Teradata.
With Oracle, one can use RAISE_APPLICATION_ERROR to issue an user defined error message text(or RAISERROR on MS SQL Server).
What is the equivalent on Teradata? I'm using Teradata V2R6.2 on Windows Server 2003.

Is there is Oracle to Teradata Porting Guide? I only saw customer case studies on Teradata website which is for marketing folks, I need one that is for application developers.

Thank you.
Regards, Nancy

7 REPLIES

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

Hi,
I think you may need to create SP in Teradata from Oracle SP using 'DECLARE HANDLER' with Exit/Continue for any Specific SQLSTATE.
Otherwise you can use 'SQLEXCEPTION' for any Generic Exception and warnings.

The Syntax for handler

DECLARE CONTINUE/EXIT HANDLER
FOR SQLSTATE 'SQLSTATE_NO'

where,
SQLSTATE_NO - can be any valid Teradata SQLSTATE.

i hope it will be useful for your Oracle stored procedure to Teradata migration.

N/A

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

Yes, I am using exit and continue handler from Teradata for generic error processing.
But there are places where I need to raise an error with my own error message; like Oracle's RAISE_APPLICATION_ERROR, DB2's SIGNAL and SQL Server's RAISERROR. I'm looking for the equivalent on Teradata.

Thank you.
Regards, Nancy

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

I don't recall if there was a straight forward way to do that other than doing some weird trick like doing a divide by zero or so (in which case anyhow you won't pass a sensible message upstream).

But if you are willing to take an extra step, you can use an external SP to raise an error condition the XSP takes an error code and message string as argument and set's the sqlstate to the code and the msg to whatever string that was passed to it.

So in your store procedure you would just do a

CALL setsqlstate('ZZ333', 'The fridge is empty !!');

This technique is explained in detail in the stored procedure user's guide orange book, in the error handling and diagnostics section.

Do remember that you would need a C compiler in one of the PE nodes if you are planning to install an XSP src.

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

Anyone came up with a solution for this discussion?

I am trying for an equivalent of raise_application_error in teradata.
I have tried an external stored procedure and a function to do that
--XSP
#define SQL_TEXT Latin_Text
#include
#include
void setExc_xsp(VARCHAR_LATIN *inputString,char sqlstate[6],SQL_TEXT error_message[257])
{
if (strlen((char *)inputString) == 0) {
strcpy(sqlstate, "U0001");
strcpy((char *)error_message, "User defined exception!!");
return;
}
}

--compile
REPLACE PROCEDURE XSP_TEST (IN inputString VARCHAR(512) CHARACTER SET LATIN)
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'CS!sam!C:\cxsp\example.cpp!F!setExc_xsp'

--run
call XSP_TEST('');

--output
*** Failure 7502 A system trap was caused by UDF/XSP/UDM XSP_TEST for EXCEPTION_ACCESS_VIOLATION


I don't get the user defined exception message. Where am i going wrong?

--UDF
void Str_UDF(VARCHAR_LATIN *inputString,CHARACTER_LATIN *result,char sqlstate[6],SQL_TEXT error_message[257])
{
if (strlen((char *)inputString) == 0) {
strcpy(sqlstate, "01H01");
*result = '0';
/* Set the error message return value */
strcpy((char *)error_message, "Zero length input string");
return;
}
}

--compile
create function find_error(searchStr varchar(20))
returns char
language c
no sql
parameter style td_general
external name 'CS!pat!C:\abhi\cxsp\udf.cpp!F!Str_UDF';

--run
select find_error('');

--output
*** Failure 7502 A system trap was caused by UDF/XSP/UDM find_error for EXCEPTION_ACCESS_VIOLATION

I know i am doing a common mistake in both ways. Any suggestions to resolve please!!

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

I think you are missing a few arguments ?

void setExc_xsp(
VARCHAR_LATIN *inputString
,char sqlstate[6]

SQL_TEXT fncname[129],
SQL_TEXT sfncname[129],

,SQL_TEXT error_message[257]
)
rgs
N/A

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

In addition to what Joe posted, TD_GENERAL parameter passing style does not have an “error_message” return argument. Look at the documentation again. Your C prototype must match what the documentation says for the parameter passing style you choose. Your error message is overwriting who knows where in memory, hence the access violation. You need to specify SQL parameter passing style if you want to return error text.

Re: Teradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?

Thanks guys for your valuable suggestions. I made the changes u said and i got the output.

--XSP
#define SQL_TEXT Latin_Text
#include
#include
void setExc_xsp(VARCHAR_LATIN *inputString,int *inputString_isNull, char sqlstate[6],SQL_TEXT setExc_xsp[129],SQL_TEXT XSP_TEST[129], SQL_TEXT error_message[257])
{
if (strlen((char *)inputString) == 0) {

strcpy(sqlstate, "U0001");
strcpy((char *)error_message, "User defined exception!!");
return;
}
}

--compile
replace procedure XSP_TEST(INOUT inputString varchar(50))
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!sam!C:\abhi\cxsp\example.cpp!F!setExc_xsp'
PARAMETER STYLE sql;

--run
call XSP_TEST('');

--output
*** Failure 7504 in UDF/XSP/UDM tduser.XSP_TEST: SQLSTATE U0001: User defin
ed exception!!