MySQL Forums
Forum List  »  Oracle

Cannot insert value in the Temporary Table when accessed from a function via a procedure.
Posted by: Rajarshi Biswas
Date: August 21, 2006 02:11AM

Hi All,

I am quite confused about a problem which i am facing now, i dont know whether i should put it as a bug or not.
The problem is as follows
Cannot insert in the Temporary Table when accessed from a function via a procedure. Whereas, when the procedure is called alone, the desired result is obtained.
to be more elaborate.

i have a procedure which creates a temporary table and calls another procedure which inserts some value in that temporary table created in prc_1.

CREATE PROCEDURE prc_1()

BEGIN

CREATE TEMPORARY TABLE Temporary_Table
(id VARCHAR(32), kind INT(5));
-- calls the 2nd procedure here
call prc_2();
END;
//


CREATE PROCEDURE prc_2()

BEGIN
DECLARE v_id VARCHAR(32);
DECLARE v_kind INT(3);
DECLARE c_level CURSOR FOR SELECT prodname,prodprice FROM product limit 1;
OPEN c_level;

FETCH c_level INTO v_id,v_kind;
CLOSE c_level;
INSERT INTO Temporary_Table VALUES (v_id, v_kind);

END;
//


When i say -> call prc_1() then it produces the desired result .

But when i call prc_1 () from a function, it says Temporary_Table not found.

eg:

CREATE FUNCTION func_1()
returns VARCHAR(32)
BEGIN

DECLARE ret VARCHAR(32);

SET ret= ' return some value ';
CALL prc_1();
return ret;

END;

//
it gives an error message saying databasename.Temporary_Table is not found.


Please help with some suggestion or tips on this.

Thanks in Advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Cannot insert value in the Temporary Table when accessed from a function via a procedure.
7008
August 21, 2006 02:11AM
8021
September 12, 2007 09:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.