Re: Recursive Queries

Hi Paul.

What you are looking for is Oracle's SELECT ... CONNECT BY PRIOR.. START WITH syntax.
Currently, however, there is a limitation you may run into that requires that the use of the select connect by NOT contain a join in it. Thus, you might not be able to work with your specific case. To use the select... connect by, you might need to rebuild your table in one table that represents only objects, and does not distinguish between a file and a directory (after all, a directory and a file in your context are only differentiated by the number of child nodes. thus, you might try this:

CREATE TABLE FILES (
File_name VARCHAR2(200),
Parent_name VARCHAR2(200) );

SELECT * from files
CONNECT BY prior parent_name = file_name;

If you really need to maintain the separate file and directory structure like you have it, you might need to resort to a stored procedure that uses a recursive call to return the data that you want. If you need to access the data from SQL, write the stored procedure to return a string and call it from sql, or if you want to get really fancy you could create a user object type that is a table of strings, then return it from the stored procedure and then flatten the object with the THE keyword.

Hope that helped!
Dave

Paul Schrum wrote in message <37261D84.19D7AF13_at_geopak.com>...
>I have a data structure which is a hierarchical tree represented in a
>relationional database. Without going into the specifics of my
>application, a directory/file structure is an exact cognate for it.
>
>Imagine something like File Manager or Windows Explorer in which there
>is a hierarchy of directories, and files under the directories. A given
>directory may have any number of files and any number of
>sub-directories. To represent this I have two tables: DIRECTORY and
>FILE. They appear as follows:
>
>=======================
>DESC DIRECTORY
>
>DIRECTORY_ID NUMBER(38) PRIMARY KEY
>NAME VARCHAR2(256)
>PARENT_DIR_ID NUMBER(38) FOREIGN KEY IS DIRECTORY.DIRECTORY_ID
>=======================
>
>DESC FILE
>
>FILE_ID NUMBER(38) PRIMARY KEY
>NAME VARCHAR2(256)
>PARENT_DIRID NUMBER(38) FOREIGN KEY IS DIRECTORY.DIRECTORY_ID
>=======================
>
>A sample of the data is as follows:
>
>
>Root
>|--config.sys
>|--Program Files
>| |--OraNT
>| | |--Oracle.exe
>| |--Games
>| | |--Freecell
>| | |--freecell.exe
>| | |--freecell.ini
>| |--Microsoft
>| |--word.exe
>|--autoexec.bat
>
>
>(Assume that any item with a file extension is a file. Else it is a
>directoy)
>Any directory can have an unlimited number of files and directories
>underneath it. There is no limit to the depth of directory nesting.
>
>SELECT * FROM DIRECTORY
> returns
>DIRECTORY_ID NAME PARENT_DIR_ID
>------------ -------------- --------------
>1 Root NULL
>2 Program Files 1
>3 OraNT 2
>4 Games 2
>5 Microsoft 2
>6 Freecell 4
>
>
>SELECT * FROM FILE
> returns
>FILE_ID NAME PARENT_DIRID
>-------- ------------------- -------------
>1 config.sys 1
>2 Oracle.exe 3
>3 freecell.exe 6
>4 freecell.ini 6
>5 word.exe 5
>6 autoexec.bat 1
>
>The Question:
>How do I compose a query which, given a DIRECTORY.NAME, returns all
>files underneath that directory at all depths? I want only the files.
>I do not need the subdirectories in the final resutl.
>
>As I understand it, a simple subquery is not adequate because of the
>constraints I set above -- no limit to nesting depth or number of
>subitems.
>
>BTW, when I populate these tables on the client side in C, I use a
>recursive function call. This question, then, could be viewed as asking
>whether there is an sql syntax for a recursive query, and what would
>that be.
>
>To all who have made it this far in my post, I thank you. If you are
>able to respond, either here on usenet or directly to me via email is
>ok.
>
>-Paul Schrum
>GEOPAK Corp.
>
>paul_at_geopak.com
>
>
Received on Tue Apr 27 1999 - 23:15:38 CDT