Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Recursive procedure returning recordset

samolygNov 8 2007 — edited Nov 9 2007
Hello!
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 7 2007
Added on Nov 8 2007
3 comments
431 views