DB2 UDF create temporary table

[mpramodk]

Please find the code for creating a temporary table and access it in UDF functions.
While we do this we obtain a compile time error stating "A declared temporary table cannot be used in the given context."

CODE:

DECLARE GLOBAL TEMPORARY TABLE 
        SESSION.TEMP (id INT, data VARCHAR(10))
        ON COMMIT PRESERVE ROWS;

 ----------------------------- Commands Entered ------------------------------

CREATE FUNCTION fn_DocumentCount2 (v_GroupID CHAR(16) FOR BIT DATA )
RETURNS INTEGER

LANGUAGE SQL

BEGIN ATOMIC

    DECLARE v_documentcount INTEGER;   

    delete from  SESSION.TEMP;

    RETURN 10;

END!

ERROR: 

SQL0526N  The requested function does not apply to declared temporary tables.

Explanation: 

A declared temporary table cannot be used in the given context.

Can we create a temporary table in udf ?

[docdiesel]

Hi,

please use code tags when posting code. In your sample you're creating a function which is a permanent object, while the global temp table will vanish after disconnect. So if the function is getting called sometime it cannot be sure if a new global temp table SESSION.TEMP has been created during the session or not, while the latter case would lead to an error. That's why DB2 acts somewhat foresighted and won't let you mix this.

Regards,

Bernd