CODEWITHSUNDEEP
sqlerror-handlingdb2
Abruzzo Forte e Gentile
Abruzzo Forte e Gentile

Reputation: 14869

raise_application_error in db2 functions

I am trying to use raise_application_error within a db2 function like below.

When I try to create that function db2 tells me the following

   An unexpected token "raise_application_error" was found following 
   "id is null then ".  Expected tokens may include:  "RETURN".  LINE

Is it allowed to use it in a function or is it meant only for stored procedures?

What is a valid substitute for functions?

     create or replace function get_stuff(id integer)
     returns varchar(10)
     language sql reads sql data
     begin    
             declare toreturn   varchar(10);

             if id Is null then
                   raise_application_error(-20000,'Id Missing)
             end if;
             --set (toreturn)=select ...///
             return toreturn;
     end@

Upvotes: 0

Views: 2119

Answers (2)

WarrenT
WarrenT

Reputation: 4542

The syntax error you are getting is a result of a missing apostrophe to terminate the string 'Id Missing'.

However, see AngocA's answer for the proper way to raise the condition. His syntax is portable across DB2 platforms.

Upvotes: 0

AngocA
AngocA

Reputation: 7693

If you are writing a IBM SQL PL you should define the text for a signal

SIGNAL SQLSTATE VALUE '20000'
 SET MESSAGE_TEXT = 'Id Missing';

If you qre writing a PL/SQL in DB2, you should be sure that the compatibility vector is set to ORA : http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html

Upvotes: 2

Related Questions