Recursive procedure returning recordset
samolygNov 8 2007 — edited Nov 9 2007Hello!
I'm new to oracle. I have a working MSSQL procedure, and I want do the same thing in my oracle procedure. I read many discussions about using temp tables in oracle, but i still haven't any solution. This is a recursive procedure, returning a recordset with forum posts. The replies should be returned too, so the recordset shold contain the main items and the sub items too. (like menu hierarchy, menus and sub menus).
The working sql procedure is the following:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ShowHierarchyForumand OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ShowHierarchyForum]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROC dbo.ShowHierarchyForum
(
@Root int,
@ArticleId int
)
AS
BEGIN
if not exists (select name from [tempdb].[dbo].[sysobjects] where name like '#YourLocalTempTable%')
create table #YourLocalTempTable (Id int, ParentId int,ArticleId int, Title nVarchar(250),username nvarchar(50),UserEmail nvarchar(50),Description nvarchar(2000),Indent int,DateAdded datetime,UserProfile nvarchar(100), CommentType tinyint)
SET NOCOUNT ON
DECLARE @CID int, @PID int, @Title varchar(250)
insert into #YourLocalTempTable SELECT protrack.CP_FORUM_Comments.Id , ParentId ,ArticleId , Title,username ,UserEmail ,Description ,Indent ,DateAdded ,UserProfile, CommentType from protrack.CP_FORUM_Comments WHERE ID = @Root and ArticleId = @ArticleId
SET @CID = (SELECT MAX(ID) FROM protrack.CP_FORUM_Comments WHERE ParentID = @Root)
WHILE @CID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchyForum @CID, @ArticleId
SET @CID = (SELECT MAX(ID) FROM protrack.CP_FORUM_Comments WHERE ParentID = @Root AND ID < @CID and ArticleId = @ArticleId)
END
END
if @@NESTLEVEL =1
select * from #YourLocalTempTable
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My question:
How can I do this in oracle? May I use global temp table for storing the rows? Or should I use own %rowtype?
Thanks