handling runtime exception for sql functions

Question

I use db2 9.7.6 full edition for windows. I need develop functions which can be use on sql select expression. Functions must contain modifying sql data and handling runtime exceptions. There are two variants, but I have problems of implementations all requirments in ever variants. The first variant is implementation of sql table function, e.g.

 CREATE FUNCTION func1 (val CHAR(20))
    RETURNS table(result varchar(1000))
    LANGUAGE SQL
    MODIFIES SQL DATA
   BEGIN atomic
    insert into a values(val);
        return (select result from a);
    END

It works, but I can't implement handling exception how in sql procedures. When I tried to use block "declare exit handler", I got a syntax errors. The second variant is implementation of pl/sql function, e.g.

CREATE OR REPLACE FUNCTION bb
RETURN varchar2
MODIFIES SQL DATA
AS
BEGIN
    insert into st values ('a');
    return 0;
END bb;

But when I tried to execute this function, I got error "SQLCODE=-740, SQLSTATE=51034 is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked". Help me please. best regards, Turkin Andrew.

Why do you use the plus sign (+) surrounding the lines?

AngocA
– AngocA

2012-10-21 02:23:57 +00:00
Commented Oct 21, 2012 at 2:23 — AngocA
– AngocA, Commented Oct 21, 2012 at 2:23

AngocA · Accepted Answer · 2012-10-21 06:07:28Z

1

There are many differences between inlined SQL compound and compiled compound. I wrote the following script, and it runs ok in db2 10.1 for LUW.

Script.sql

CREATE or replace FUNCTION func1a (val CHAR(20))
RETURNS varchar(20)
LANGUAGE SQL
READS SQL DATA
BEGIN
declare ret varchar(20);
declare exit handler for sqlstate '02000' resignal sqlstate '08888';
select C1 into ret from T1 fetch first 1 row only;
return ret;
END@

CREATE or replace FUNCTION func1b (val CHAR(20))
RETURNS table(result varchar(20))
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN atomic
insert into T1 values(val);
return (select C1 from T1);
END@

The execution (In Windows client, but it does not matter)

db2 CREATE TABLE T1 (C1 CHAR(20))
db2 -td@ -vf Script.sql
db2 "values func1a('s')"
db2 "SELECT * FROM TABLE (FUNC1b('A'))"

As you can see there are many difference in DB2 10.1 for inlined and compiled SQL: - Single value return vs table or row return. - Reads vs modified data. - Condition handler vs nothing.

Due to the function definition, the calling method is different, in one case it is a scalar value, in the other is a function table.

answered Oct 21, 2012 at 6:07

AngocA

7,7046 gold badges42 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AngocA Over a year ago

I had a similar problem for row retuning from a function: ibm.com/developerworks/forums/thread.jspa?messageID=14898269

typik89 Over a year ago

Thanks for reply. But I think that you understand me wrong. I don't care what kind functions, scalar or table. I need implement all requirements: 1. It must be function 2. It can be use in sql select 3. there are handling runtime exception and modifying sql data In first variant I don't know how catch exceptions. In second variant I don't know how create pl/sql function whith "modify sql data". In this case I get error when execute this function.

Udo Held · Accepted Answer · 2012-10-22 09:31:40Z

0

It just doesn't work.

List of maximum access levels

Routine type    Default SQL access level    Maximum allowed SQL access level
SQL procedures  MODIFIES SQL DATA   MODIFIES SQL DATA
SQL functions (scalar functions)    READS SQL DATA  READS SQL DATA
SQL functions (table functions)     READS SQL DATA  MODIFIES SQL DATA
External procedures     MODIFIES SQL DATA   MODIFIES SQL DATA
External functions (scalar functions)   READS SQL DATA  READS SQL DATA
External functions (table functions)    READS SQL DATA  READS SQL DATA

For scalar functions the maximum level is "READS SQL DATA". There is probably now way around that, so you have to reconsider your coding.

answered Oct 22, 2012 at 9:31

Udo Held

12.6k11 gold badges73 silver badges94 bronze badges

1 Comment

typik89 Over a year ago

Thanks for reply. I understood that pl/sql doesn't help me to implement required functionalities. Are there ways for handling runtime exceptions in atomic sql functions? E.g. I need return concrete string if I catch duplicate exception.

Collectives™ on Stack Overflow

handling runtime exception for sql functions

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related