I keep the two queries separate to avoid excessive complexity. I compose the SQL code using PHP. Right now, the second query comes unsorted from Oracle: I use PHP to sort it at a later stage in my application (my PHP skills are better than my SQL ones). Would it be possible to use the ID list to sort the second query inside Oracle?

Server runs Oracle9i Enterprise Edition Release 9.2.0.1.0

No need to make this too difficult. If you were on 10g, you could so some fancy things with regexp_substr. A simple example which should work on 9i and below:
Create a testing table for this demonstration named T1, think of this as your FOO table:

Note in the INSTR, the sequence of the numbers must end in a comma, and we tell INSTR to locate the number in the list with a comma appended to the end of the value of C1.
Your SQL statement would look like this:

SELECT .......
FROM FOO
LEFT JOIN BAR .......
WHERE FOO_ID IN (1, 98, 12, 33)
ORDER BY
INSTR(',1,98,12,33,' , ','||TO_CHAR(FOO_ID)||',');

Or:

SELECT .......
FROM
FOO,
BAR
WHERE
FOO.FOO_ID=BAR.FOO_ID(+)
AND FOO_ID IN (1, 98, 12, 33)
ORDER BY
INSTR(',1,98,12,33,' , ','||TO_CHAR(FOO_ID)||',');

Before deciding to use the above technique, determine if there is a better way to do *everything* in a single SQL statement. You might be able to do this by wrapping your complicated SQL statement into an inline view, and joining to that just as if it were a regular table:

Table dir_size stores the mbytes of storage used in a given directory. Table directories stores various directory names which may or may not exist in table dir_size.
For every directory in table directories, report the cumulative storage in that directory and all its subdirectories. This solution uses a cartesian join. I imagine it will not scale well.

Now, if we performed the same process for all of the rows in the DIR_SIZE table, grouping on DIR_NAME2, we might be able to find the SUM of the MBYTES column.

(Note that I did not provide an exact/final answer to the original poster – my post was intended to push the OP in the right direction of a solution.)

The OP followed up with this comment:

Thanks for the suggestion. I suspect the best way will involve some kind of recursive processing. The tricky bit is the matching of the rows in the directories table to the rows in the dir_size table. We need to do a “like” (which we can’t, of course) which is why I thought of the instr.

The LIKE keyword is not necessary.

Notice how closely the output of the following SQL statement:

SELECT
'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
100 MBYTES
FROM
DUAL;

Matches the row created by one of your insert statements:

insert into dir_size values ('c:\aaa\bbb\ccc\ddd', 100);

You might try replacing in the above examples:

SELECT
'c:\aaa\bbb\ccc\ddd\' DIR_NAME,
100 MBYTES
FROM
DUAL;

With a SQL statement that selects all of the rows from your DIR_SIZE table – the results might surprise you IF each of the DIR_NAME values end with a “\”.
You really need more variety in the insert statements to see what is happening, for example:

The first of the above SQL statements will increase the calculated SUM in the c:\ directory by 800, and the second insert statement will increase the SUM in both of the c:\ and c:\ddd\ directories by 300 if you modify my original example to use the DIR_SIZE table rather than the DUAL table.
The final part that I did not provide to the OP is below:

Is there way to force the randomize within a set of number in Oracle?
Say I have a set of integers ( 2,8,6,5) and I want to force randomize function to randomly pick within that set only. I know about the DBMS_RANDOM package, sample and seed clause but none help what I need to do here.

any thoughts?

It was not clear if the integers will be present in table rows, supplied in a comma separated list, or through another method. Additionally, it was not clear if the solution should be provided in SQL, PL/SQL, or another programming language. A couple people offered potential solutions.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Vladimir Zakharychev suggested the following:

Straightforward way: just generate random numbers between 0 and 1, divide the 0..1 range into 4 subranges and then pick a number from
your list depending on which subrange your generated random value belongs, like this:

with rnd as (select dbms_random.value val from dual)
select
case
when val < 0.25 then 2
when val >= 0.25 and val < 0.5 then 8
when val >= 0.5 and val < 0.75 then 6
when val >= 0.75 then 5
end x
from rnd

(note that this query does not work correctly in 9.2.0.8 for some reason – returns wrong results. In 10.2.0.4 it returns expected results.) Obviously, you can partition 0..1 range into as many subranges as there are numbers in your list and apply the same technique. A function that will do this automatically given an array of possible return values is not too hard to write.

CK, instead of using a fixed range as Vladimir had in his example I was thinking I might use a MOD division of the random number returned from dbms_random to generate the index key into my table of values. This would make the code flexible for variable length lists. If you have a fixed number of entries then Vlad’s solution is simple and easy to understand. If your number of variables varies then I think this would fit the bill.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I offered the following:

Here is another way to do it, if you do not know how many elements will be in the list.

First, we return a row from DUAL with the rows of interest with a comma appended at the start and end:

Finally, we push the above SQL statement into an inline view, search for the specified number of commas according to SEL_ITEM column to determine the starting position of the element, and then search for the next comma to determine the ending position of the element:

If we now use a FIRST_VALUE analytic function, we could just retrieve
the first PERCENT value and use that in a WHERE clause (the PERCENT_RANK function was the start of another approach which was never developed):

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: