CODEWITHSUNDEEP
db2
Raman
Raman

Reputation: 205

Stored procedure in db2 with cursor return type

I am developing a stored procedure (SP) in db2 which will return some data in the form of output cursor but field lengths for different field may vary. I am facing issues as I am not able to make SP compile for this requirement. Below is the code for reference

 create table employee(id bigint,first_name varchar(128),last_name varchar(128));
 create table employee_designation(id bigint, emp_id bigint, 
designation varchar(128));
 create type empRowType as row(first_name varchar(128),last_name varchar(128),
designation varchar(128));
 create type empCursorType as empRowType cursor;

 create procedure emp_designation_lookup(in p_last_name varchar(128), out emp_rec empCursorType) 
 result sets 0
 language SQL
 begin
    set emp_rec = cursor for select a.first_name,a.last_name,b.designation
                        from employee a, employee_designation b
                        where a.id=b.EMP_ID
                        and a.LAST_NAME = p_last_name;
 end;

the above SP compiles and return the result as intended. However if I change the row definition as below

create type empRowType as row(first_name varchar(120),last_name varchar(128),
designation varchar(128));

On recompiling the SP, I get the following error

BMS sample -- [IBM][CLI Driver][DB2/NT64] SQL0408N  A value is not compatible with the 
data type of its assignment target. Target name is "EMP_REC".  LINE NUMBER=5.  SQLSTATE=42821

The error is coming as first_name defined in cursor is not of same length in the table employee(cursor has 120 whereas table has 128)

However for my actual work, I need the return values computed based on some logic and hence the length specified in the cursor will be different from what is there in the table. Also I have some new columns in the cursor which are not related with table's column (for example determining the bonus amount or should the employee be promoted etc).

I want to know if there is indeed some solution to such scenario specifically to db2. I am new to db2 and am using version 10.5.7. I also explored multiple articles in IBM docs but not able to find the exact resolution. Any help of pointers will be of great help.

Upvotes: 0

Views: 1372

Answers (1)

mao
mao

Reputation: 12267

When you use a strongly typed cursor, then any assignment involving that cursor must exactly match the relevant type. Otherwise the compiler will throw an exception, which is your symptom.

Db2 SQL PL also supports weak cursors, and an SQL PL procedure output parameter type can be a weak cursor. This means that a stored procedure declaration can use ...OUT p_cur CURSOR (so there is no preassigned user defined type linked to that cursor) , and then assign that output parameter from different queries ( set p_cur = CURSOR FOR SELECT ... ) . In my case the caller is always SQL (not jdbc), but you might experiment with jdbc as IBM gives an example in the Db2-LUW v11.5 documentation.

Most people use simple result-sets (instead of returned cursors) to harvest the output from queries in stored procedures. These result-sets are consumable by all kinds of client applications (jdbc, odbc, cli) and languages that uses those interfaces (java, .net, python, php, perl, javascript, command-line/scripting etc.). So the simple result sets offer more general purpose usability that returned cursor parameters.

IBM publishes various Db2 samples in different places (on github, in the samples directory-tree of your Db2 server instance directory, in the Knowledge Center etc.).

Upvotes: 1

Related Questions