distinct and order-by for result of regexp_substr

I got that sql that gives me the expected result:

select regexp_substr(longarg, '30G([^|]*)', 1, 1, '', 1) founded from dod;

If I try to distinct my result

select distinct regexp_substr(longarg, '30G([^|]*)', 1, 1, '', 1) founded from dod;

I get the message:

ORA-00932: 00932. 00000 -  "inconsistent datatypes: expected - got CLOB"

Another question is. How can I order the result set with order by? As you see I am not realy a oracle sql expert...

Thanks for your help

Stefan

Solution

Looks like your column is a CLOB, which unfortunately has some restrictions you are running into (can't use them in DISTINCT or ORDER BY, etc.. for full list see: http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_working.htm#ADLOB2010)

However, if what you are bringing back from the regexp_substr is less than 4000 characters, you can use to_char(), which will then allow you to use it in distinct/order by:

select distinct to_char(regexp_substr(longarg, '30G([^|]*)', 1, 1, '', 1)) founded
from dod
order by founded;