CODEWITHSUNDEEP
db2db2-luw
Sathya sarma
Sathya sarma

Reputation: 1

Error: DB2 Stored procedure with the error A value is not compatible with the data type of its assignment target. Target name is "C1"

While creating the stored procedure I am getting the error

"A value is not compatible with the data type of its assignment target. Target name is "C1".. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.19.56"

Help me to create a stored procedure with cursor variable

CREATE OR REPLACE TYPE DE_ROW_T as row ( C1 INTEGER,C2 VARCHAR(100),C2 CHAR(1));

CREATE OR REPLACE TYPE C_ID_CURSOR_T as DE_ROW_T CURSOR;

CREATE OR REPLACE PROCEDURE  "SP_CURTEST" 
LANGUAGE SQL 
BEGIN
    
DECLARE V_ROW DE_ROW_T;
DECLARE C1  C_ID_CURSOR_T;

SET C1 = CURSOR FOR 
        SELECT DISTINCT 
           COL1,COL2,COL3
          FROM    
            MYTABLE
          WHERE COL1=101;

OPEN C1;
FETCH C1 INTO V_ROW;

END;

Upvotes: 0

Views: 2145

Answers (1)

mao
mao

Reputation: 12267

Your table DDL must match the row type in datatype and lengths, otherwise you will get exceptions trying to fit the wrong data into your rowtype variable.

The following script shows how you can see the stored-procedure operating with Db2-LUW v11.5.4.0 on Linux, via printing out the progress as debugging tool. You can also debug stored procedures with other GUI tools, including IBM Data Studio.

--#SET TERMINATOR ;

CREATE OR REPLACE TYPE DE_ROW_T as row ( C1 INTEGER,C2 VARCHAR(100),C3 CHAR(1));

CREATE OR REPLACE TYPE C_ID_CURSOR_T as DE_ROW_T CURSOR;

drop table if exists mytable;

create table mytable(col1 integer, col2 varchar(100), col3 char(1));
insert into mytable(col1,col2,col3) values(101,'abcd','a');


--#SET TERMINATOR @

set serveroutput on@

CREATE OR REPLACE PROCEDURE  "SP_CURTEST" 
LANGUAGE SQL 
specific curtest
BEGIN
    DECLARE V_ROW DE_ROW_T;
    DECLARE C1  C_ID_CURSOR_T;

    SET C1 = CURSOR FOR SELECT DISTINCT COL1,COL2,COL3 FROM MYTABLE WHERE COL1=101;

    call dbms_output.put_line('opening c1');
    OPEN C1;
    call dbms_output.put_line('opened c1');
    FETCH C1 INTO V_ROW;
    call dbms_output.put_line('fetched c1: '||varchar(v_row.c1)||' , '||trim(v_row.c2)||' , '||v_row.c3);
END
@

call sp_curtest()@

IF you run the above script via the db2 CLP at the command line, it will show the output below:

    $ db2 -tvf sp_so_8.sql

CREATE OR REPLACE TYPE DE_ROW_T as row ( C1 INTEGER,C2 VARCHAR(100),C3 CHAR(1))
DB20000I  The SQL command completed successfully.

CREATE OR REPLACE TYPE C_ID_CURSOR_T as DE_ROW_T CURSOR
DB20000I  The SQL command completed successfully.

drop table if exists mytable
DB20000I  The SQL command completed successfully.

create table mytable(col1 integer, col2 varchar(100), col3 char(1))
DB20000I  The SQL command completed successfully.

insert into mytable(col1,col2,col3) values(101,'abcd','a')
DB20000I  The SQL command completed successfully.

set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.

CREATE OR REPLACE PROCEDURE  "SP_CURTEST" 
LANGUAGE SQL 
specific curtest
BEGIN
    DECLARE V_ROW DE_ROW_T;
    DECLARE C1  C_ID_CURSOR_T;

    SET C1 = CURSOR FOR SELECT DISTINCT COL1,COL2,COL3 FROM MYTABLE WHERE COL1=101;

    call dbms_output.put_line('opening c1');
    OPEN C1;
    call dbms_output.put_line('opened c1');
    FETCH C1 INTO V_ROW;
    call dbms_output.put_line('fetched c1: '||varchar(v_row.c1)||' , '||trim(v_row.c2)||' , '||v_row.c3);
END

DB20000I  The SQL command completed successfully.

call sp_curtest()

  Return Status = 0

opening c1
opened c1
fetched c1: 101 , abcd , a

$

Upvotes: 0

Related Questions