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 27, 2006 05:58PM

I had already posted the script for creating the table and its corrosponding dummy datas. Please find it below once again.


Step-1. Table Defination

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

Step-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');

Step - 3) 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 - 4) 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 - 5) 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-6) Running the procedure in MySQL - WORKS FINE PRODUCES THE DESIRED RESULT


call Prc_11('p001'); //


Step- 7) 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;
//

I hope now u can run it .
Thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Cannot insert value in the Temporary Table when accessed from a function via a procedure.
6662
August 27, 2006 05:58PM
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.