life span of temp table

database, informix, sql, temp-tables

Solution

I finally used a variation of Jonathan's and RET's solution:

CREATE PROCEDURE foo ()
    ON EXCEPTION IN (-206)
    END EXCEPTION WITH RESUME;

    DROP TABLE tempTable;    

    SELECT * FROM fooBar INTO TEMP tempTable;

    -- do something with tempTable here

    DROP TABLE tempTable;
END PROCEDURE;

Problem

I have the following procedure: ``` CREATE PROCEDURE foo () SELECT * FROM fooBar INTO TEMP tempTable; -- do something with tempTable here DROP TABLE tempTable; END PROCEDURE; ``` What happens if there is an exception before the DROP TABLE is called? Will tempTable still be around after foo exits? If so, foo could fail the next time it is called, because tempTable would already exist. How should that be handled. EDIT: I am using informix 11.5

Original source