MySQL Forums
Forum List  »  Oracle

Re: Cannot insert value in the Temporary Table when accessed from a function via a procedure.
Posted by: Rajarshi Biswas
Date: August 23, 2006 10:19PM

hi !

There are some changes in the code ,as there was a nesting (BEGIN...END)problem ,
please find below the runnable code.

i am using MySQL command prompt and copy/pasting the code
please use
-> delimiter // command before pasting the code.

STEP - 1) Procedure 1 - setting the recursion depth

CREATE PROCEDURE sDepth()
BEGIN
SET @sql ="SET max_sp_recursion_depth=255";
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
//


STEP - 2) Procedure 2 - Parent Procedure

CREATE PROCEDURE Prc_11 (start_with VARCHAR(32))

BEGIN
DECLARE temporary_table_exists BOOLEAN;
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
DROP TABLE IF EXISTS Temporary_Table;
END;
BEGIN
DECLARE v_catagory_id,v_parent,v_name VARCHAR(32);
DECLARE v_level INT;

CREATE TEMPORARY TABLE Temporary_Table
(catagory_id VARCHAR(32), name VARCHAR(32), parent VARCHAR(32));

SET temporary_table_exists=TRUE;
SELECT category_id, name
INTO v_catagory_id, v_name FROM recurse2
WHERE category_id = start_with limit 1;

IF v_catagory_id IS NOT NULL THEN

SET v_level := 1;

INSERT INTO Temporary_Table VALUES
(v_catagory_id, v_name, v_parent);

CALL sDepth();

CALL Prc_22(v_catagory_id);
END IF;

-- This line is given to view the resultant Temporary Table

SELECT catagory_id,name,parent FROM Temporary_Table;

IF temporary_table_exists THEN
DROP TEMPORARY TABLE Temporary_Table;
END IF;
END;
END;
//


STEP - 3) Procedure 3 - procedure with a recursive call

CREATE PROCEDURE Prc_22 (start_with VARCHAR(32))
proc:
BEGIN
DECLARE v_catagory_id,v_parent,v_name VARCHAR(32);
DECLARE v_level INT;

DECLARE done, error BOOLEAN DEFAULT FALSE;

DECLARE cs CURSOR FOR SELECT category_id, name, parent
FROM Recurse2 WHERE parent = start_with;


DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET error = TRUE;

OPEN cs;
IF error THEN
SELECT 'OPEN failed'; LEAVE proc; END IF;

REPEAT

SET v_catagory_id=NULL;

FETCH cs INTO v_catagory_id, v_name, v_parent;

IF done=FALSE THEN

IF v_catagory_id IS NOT NULL THEN

INSERT INTO Temporary_Table VALUES
(v_catagory_id, v_name, v_parent);
CALL Prc_22(v_catagory_id);
END IF;
END IF;
UNTIL done = TRUE
END REPEAT;
CLOSE cs;
END;
//

STEP - 4) Running the procedure in MySQL - WORKS FINE PRODUCES THE DESIRED RESULT


call Prc_11('p001'); //


STEP - 5) Make a function - Calling the procedure from a function - CAUSE ERROR while running

CREATE FUNCTION fnc_dummy4 ()
returns VARCHAR(32)
BEGIN
DECLARE ret VARCHAR(32);
call Prc_11('p001');
return ret;
END;
//

select fnc_dummy4 (); //

ERROR 1146 (42S02): Table '< database-name >.temporary_table' doesn't exist

Its a compiled code in my PC, please let me know incase there any problem/difficulty to compile or run the code .
I wonder whether i have to report it as a bug or not.
Your suggestions/reply is keenly awaited.

Thanks in advance



Edited 1 time(s). Last edit at 08/24/2006 07:49PM by Rajarshi Biswas.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Cannot insert value in the Temporary Table when accessed from a function via a procedure.
3629
August 23, 2006 10:19PM
7957
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.