All Forums Database
nshen 6 posts Joined 10/07
15 Oct 2007
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

drenugopal 8 posts Joined 10/07
16 Oct 2007

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 handlerDECLARE CONTINUE/EXIT HANDLERFOR 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.

nshen 6 posts Joined 10/07
16 Oct 2007

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

joedsilva 505 posts Joined 07/05
17 Oct 2007

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

abhisiue 7 posts Joined 01/09
08 Jan 2009

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#includevoid 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;}}--compileREPLACE PROCEDURE XSP_TEST (IN inputString VARCHAR(512) CHARACTER SET LATIN)LANGUAGE CNO SQLPARAMETER STYLE TD_GENERALEXTERNAL NAME 'CS!sam!C:\cxsp\example.cpp!F!setExc_xsp'--runcall XSP_TEST('');--output*** Failure 7502 A system trap was caused by UDF/XSP/UDM XSP_TEST for EXCEPTION_ACCESS_VIOLATIONI don't get the user defined exception message. Where am i going wrong?--UDFvoid 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;}}--compilecreate function find_error(searchStr varchar(20))returns charlanguage cno sqlparameter style td_generalexternal name 'CS!pat!C:\abhi\cxsp\udf.cpp!F!Str_UDF';--runselect find_error('');--output*** Failure 7502 A system trap was caused by UDF/XSP/UDM find_error for EXCEPTION_ACCESS_VIOLATIONI know i am doing a common mistake in both ways. Any suggestions to resolve please!!

joedsilva 505 posts Joined 07/05
08 Jan 2009

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 106 posts Joined 02/07
08 Jan 2009

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.

abhisiue 7 posts Joined 01/09
08 Jan 2009

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;}} --compilereplace procedure XSP_TEST(INOUT inputString varchar(50))LANGUAGE CNO SQLEXTERNAL NAME 'CS!sam!C:\abhi\cxsp\example.cpp!F!setExc_xsp'PARAMETER STYLE sql;--runcall XSP_TEST('');--output *** Failure 7504 in UDF/XSP/UDM tduser.XSP_TEST: SQLSTATE U0001: User defin ed exception!!

You must sign in to leave a comment.