Repeating a query and storing the results until no more records.

Hi (and help).

I've been using Mysql and am in the process of migrating an intranet to Oracle. In the Mysql pages, we've created a number of pages which use temporary tables, which aren't available in Oracle.

Most occurances I can rethink and improve, but I have one particular page which I can't fathom out a solution for.

The ASP page carries out a Mulit-level Bill Of Material explosion. A parent part is entered, then the component parts for this part are read. These get stored in a temporary table and a master temporary table. The second read, uses the records from the first temporry table as the 'next-level' parent parts, and reads all of their component parts. These records replace the data from in the first temporary table, and get appended to the master temporary table.This continues through all levels of the B.O.M, until no records get returned. The looping is controlled by server-side scripting.

The mastertemporary table is then used to create the data displayed to the user, after going back to the database to read other related data.

The problem is the number of levels is not pre-defined. It varies according to the part being 'exploded'.

Please can someone advise where I should looking. Is it Global Temporary Tables??

Many thanks in advance. Received on Sun Jan 29 2006 - 11:42:17 CST