Creating unlimited Nested categories via Stored Procedure

I am trying to create a loop in stored procedure to forumulate a parent/chil
d
( unlimited relationships ). I do not know how to go about doing this in a
stored procedure. I can do it on the frontend but I rather not do it this
way. The following is a sample of the database table:
Categories
---
| CATID | NAME | ParentId |
---
1 Electronics 0
2 Computers 1
3 Cameras 1
4 Sony Cameras 3
5 Clothing 0
6 White TShirt 5
I would like the output to like the following
Electronics
Electronics -> Computers
Electronics -> Cameras
Electronics -> Cameras -> Sony Cameras
Clothing
Clothing -> White TShirt
How can i get this to work via a stored procedure?Something like this would work...
Drop Table #temp
Create Table #temp
(
CatID int,
[Name] varchar(20),
ParentID int
)
insert into #temp values (1, 'Electronics', 0)
insert into #temp values (2, 'Computers', 1)
insert into #temp values (3, 'Cameras', 1)
insert into #temp values (4, 'Sony Cameras', 3)
insert into #temp values (5, 'Clothing', 0)
insert into #temp values (6, 'White TShirt', 5)
Drop Table #temp2
Create Table #temp2
(
CatID int,
[Name] varchar(255),
ParentID int
)
Insert Into #temp2
Select CatID, [Name], ParentID
>From #temp
While @.@.Rowcount > 0
Update t
Set [Name] = t1.[Name] + ' -> ' + t.[Name],
ParentID = t1.ParentID
From #temp2 t Inner Join #temp t1
On t.ParentID = t1.CatID
Select * From #temp2
HTH
Jason|||Procedure nesting is limited to 32 levels in SQL Server (the same goes for
triggers). If your requirements exceed this limit, you'll have to create the
hierarchy in your application.
ML|||Loops!!' Procedural code' !! God! How non-relational!
Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested sets
model.

Creating report using a loop

Hello everybody

Maybe some of you had faced such kind of problem:
I have to out query results into html page.
Ok. It's more than simple using a cursor. BUT it becomes a problem, when tables have 100 or 200 or more columns.
I mean this:

cursor cur is
select * from table_name;
cur_record table_name%rowtype;

and after that

OPEN cur;
LOOP
FETCH cur INTO cur_record;
EXIT WHEN cur%notfound;
HTP.P('columns: '||cur_record.col1||','||cur_record.col2||'...... .'||cur_record.col259||'...');
END LOOP;
CLOSE cur;

Becomes uncomfortable. And, I think, it should be possible to put into loop. But i cannot find a decision how to realize such thing like
cur_record.col(i)

The other decision could be

TYPE RES_ARRAY AS VARRAY (300) OF NUMBER;

RESULTS RES_ARRAY;
BEGIN
SELECT * INTO RESULTS FROM table_name WHERE condition;

In that table are ONLY numbers.

BUT I receive an error
ORA-00932: inconsistent datatypes: expected UDT got NUMBER

As I understand, UDT is User Defined Type...... And all tutorials and Oracle documentations show only examples with UDT.

IS IT POSSIBLE SOME KIND OF SOLUTIONS?

Thanks very much.You would need to look at the DBMS_SQL package. This may get you started:

declare
v_cursor integer := dbms_sql.open_cursor;
v_status integer;
v_desc_tab dbms_sql.desc_tab;
v_numcols number;
v_value varchar2(4000);
v_record varchar2(32767);
v_query varchar2(32767) := 'SELECT * FROM emp';
begin

dbms_sql.parse( v_cursor, v_query, dbms_sql.native );
dbms_sql.describe_columns( v_cursor, v_numcols, v_desc_tab );

for i in 1 .. v_numcols loop
dbms_sql.define_column(v_cursor, i, v_value, 4000);
end loop;

v_status := dbms_sql.execute(v_cursor);

while ( dbms_sql.fetch_rows(v_cursor) > 0 ) loop
v_record := 'columns: ';
for i in 1 .. v_numcols loop
dbms_sql.column_value( v_cursor, i, v_value );
v_record := v_record || ',' || v_value;
end loop;
dbms_output.put_line(v_record);
end loop;
end;

/

columns: ,7369,SMITH,CLERK,7902,17-DEC-1980,800,,20,42
columns: ,7499,ALLEN,SALESMAN,7698,20-FEB-1981,1600,300,30,42
columns: ,7521,WARD,SALESMAN,7698,22-FEB-1981,1250,500,30,42
columns: ,7566,JONES,MANAGER,7839,02-APR-1981,2975,,20,42
columns: ,7654,MARTIN,SALESMAN,7698,28-SEP-1981,1250,1400,30,42
columns: ,7698,BLAKE,MANAGER,7839,01-MAY-1981,2850,,30,42
columns: ,7782,CLARK,MANAGER,7839,09-JUN-1981,2450,,10,42
columns: ,7788,SCOTT,ANALYST,7566,19-APR-2087,3000,,20,42
columns: ,7839,KING,PRESIDENT,,17-NOV-1981,5000,,10,42
columns: ,7844,TURNER,SALESMAN,7698,08-SEP-1981,1500,0,30,42
columns: ,7876,ADAMS,CLERK,7788,23-MAY-2087,1100,,20,42
columns: ,7900,JAMES,CLERK,7698,03-DEC-1981,950,,30,43
columns: ,7902,FORD,ANALYST,7566,03-DEC-1981,3000,,20,42
columns: ,7934,MILLER,CLERK,7782,23-JAN-1982,1300,,10,42

PL/SQL procedure successfully completed.