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 06:30PM

Here is the complete code with the table definition and the dummy data.


1. Table Defination

CREATE TABLE recurse2(
category_id VARCHAR(20) ,
name VARCHAR(20) NOT NULL,
parent VARCHAR(20));

2. Insert-Data

INSERT INTO recurse2
VALUES('p001','ELECTRONICS',NULL),('p0010','2 WAY RADIOS','p006'),('p008','FLASH','p007'),
('p004','LCD','p002'),('p006','PORTABLE ELECTRONICS','p001'),('p005','PLASMA','p002'),
('p007','MP3 PLAYERS','p006'),('p003','TUBE','p002'),
('p002','TELEVISIONS','p001'),('p009','CD PLAYERS','p006');


4. MySQL Hierarchical query procedures

i ) Procedure 1 - Dynamically setting the depth as byDefault it is set to ZERO

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


ii) Procedure 2

CREATE PROCEDURE hLevel1 (start_with VARCHAR(32))
proc:
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), level INT);

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,v_level);

CALL sDepth();

CALL hLevel2(v_catagory_id);
END IF;

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

SELECT catagory_id,name,parent,level FROM Temporary_Table;

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


iii) Procedure 3 - This is the reqd. RECURSIVE procedure

CREATE PROCEDURE hLevel2 (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 c CURSOR FOR SELECT category_id, name, parent
FROM Recurse2 WHERE parent = start_with;

DECLARE c_level CURSOR FOR SELECT level FROM Temporary_Table WHERE catagory_id = start_with;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET error = TRUE;

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

REPEAT

SET v_catagory_id=NULL;

FETCH c INTO v_catagory_id, v_name, v_parent;

IF done=FALSE THEN

IF v_catagory_id IS NOT NULL THEN

OPEN c_level;
FETCH c_level INTO v_level;
CLOSE c_level;

SET v_level := v_level +1;

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

CALL hLevel2(v_catagory_id);

END IF;
END IF;
UNTIL done = TRUE
END REPEAT;
CLOSE c;
END;
//

5 ) Running the procedures in MySQL


call hLevel1('p001'); //

Importantly, it works fine when the procedure call is made.


6) Make a function - Calling the same procedure from a function cause the error mentioned.

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


select fnc_dummy3 (); //

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


Please let me know whether you could recreate the error or not .

Answer awaited.

Thanks

Options: ReplyQuote


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