Click here to Skip to main content
15,892,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to create a temporary table in stored procedure in db2?

Below is my stored procedure and i'm getting error while executing this Procedure.

SQL
DECLARE RTIMID_MATCH INTEGER;
DECLARE PARAMS_VALUE VARCHAR(2500);DECLARE tmp_param varchar(500); DECLARE tmp_seperated_param varchar(500); DECLARE count_val INTEGER DEFAULT 1;
CREATE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE_PARAMS( param_key varchar(500), param_value varchar(500));
DECLARE cursor2 CURSOR FOR select varchar(params) from RTIMD.V3_TICKET_LOG where RTIM_ID = IN_RTIM_ID or TICKET_ID = IN_RTIM_ID order by CREATED;
DECLARE cursor3 CURSOR FOR SELECT VARCHAR(elem, 500) FROM TABLE ( RTIMD.elements(PARAMS_VALUE,',') ) AS t(elem);
DECLARE cursor4 CURSOR FOR SELECT VARCHAR(elem, 500) FROM TABLE ( RTIMD.elements(tmp_param,'=') ) AS t(elem);
DECLARE cursor5 CURSOR FOR SELECT * FROM SESSION.TEMP_TABLE_PARAMS;


SQL
OPEN cursor2;
            FETCH FROM cursor2 INTO tmpname;
            WHILE(SQLSTATE = '00000')
                DO
                    SET PARAMS_VALUE = (select replace(replace(replace(replace(tmpname, '''', ''),'{',''), '}' , ''),'=>' , '=') from SYSIBM.SYSDUMMY1);
                    OPEN cursor3;
                        FETCH FROM cursor3 INTO tmp_param;
                        WHILE(SQLSTATE = '00000')
                            DO
                                OPEN cursor4;
                                FETCH FROM cursor4 INTO tmp_seperated_param;
                                WHILE(SQLSTATE = '00000')
                                    DO
                                    IF MOD(count_val,2) = 0 then
                                        update SESSION.TEMP_TABLE_PARAMS set param_value = tmp_seperated_param;
                                    ELSE
                                        insert into SESSION.TEMP_TABLE_PARAMS values (tmp_seperated_param,'');
                                    END IF;
                                    SET count_val = count_val + 1;
                                    fetch from cursor4 into tmp_seperated_param;
                                END WHILE;
                                CLOSE cursor4;
                            fetch from cursor3 into tmp_param;
                        END WHILE;
                    CLOSE cursor3;

                fetch from cursor2 into tmpname;
            END WHILE;
        CLOSE cursor2;

        OPEN cursor5;
            FETCH FROM cursor5 INTO tmpname;
            WHILE(SQLSTATE = '00000')
                DO
                IF mycount = 1 then
                    SET tmpval = tmpname;
                    SET mycount = 2;
                ELSE
                    SET tmpval = tmpval concat ',' concat tmpname;
                    SET mycount = mycount + 1;
                END IF;
                fetch from cursor5 into tmpname;
            END WHILE;
        CLOSE cursor5;
        SET tmpmsg = tmpval;
        DROP TABLE SESSION.TEMP_TABLE_PARAMS;



The error is,
XML
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "<cursor declaration>" was found following "".
Expected tokens may include:  "<SQL statement>".  LINE NUMBER=1.
SQLSTATE=42601




Please help. Thanks in advance.
Posted

1 solution

Look at the error - it's not even hinting that you are creating a temporary table never mind incorrectly, so what you are really asking is "how can I solve this error".

I haven't got DB2 (or Oracle) to hand at the moment so I am going to walk you through a method of solving the problem for yourself ...

1. Have a look at the line
SQL
SET PARAMS_VALUE = (select replace(replace(replace(replace(tmpname, '''', ''),'{',''), '}' , ''),'=>' , '=') from SYSIBM.SYSDUMMY1);
Try replacing that with a hard-coded value and run your SP. If it no longer errors then you need to carefully examine that line of code and correct the problem - I personally don't like escaping single quotes and prefer to use CHR(39).


2. If it doesn't error ... Edit your SP and comment out (or remove to a safe place) everything other than the DECLARE cursor lines. Run it. If it errors then carefully check the syntax of the code you have left.

2. If it didn't error then start adding bits of the code back in - keep the size of the chunks of code reasonably small to make it easier to locate the error when it does occur

Basically put bits back a piece at a time, run the SP each time. When it falls over you know it was the last thing that you put back that caused the problem.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900