If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

A strange requirement for no PL/SQL, but here's a SQL-only solution, though you will need a SQL collection type. What I've done is to determine the number of delimited elements in each string and then generate a row per element "on the fly". Using a combination of INSTR, SUBSTR and the element number, it is possible to parse out the individual values.

Note that in the example, I've commented out a REGEXP_REPLACE function ( 10g ). Including this was bombing my session. YMMV.

If you look at my example, I have a table "T" with more than one string of different sizes, as specified by the OP. You need to consider how you would replace your :x bind variable with a SELECT from the table of strings. It's less simple when you need to extract the elements from more than one string.

Edit: I realized later, that I used dba_objects as a matter of habit. Replace that with all_objects. In your case all_objects, or a custom-made table with sufficient rows would suffice. From 9i on, dual with a connect by is a safe replacement.

Last edited by Alien; 07-13-2005 at 02:21 PM.
Reason: Creatures of habit

The query posted above was tested on 10.1.0.3 only, but it is all 9i syntax. I was getting the ORA-3113 when I included the REGEXP_REPLACE. But, I have an 8i version that I also wrote and forgot to post ( the OP asked for all versions ). You could perhaps test this:-