For the moment this does the trick ,but Id appreciate a heads up for a better solution.
The inner regexp_replace matches each line = '^.*$'
Replace each line with the '1' character.
Beginning character = 1
First occurance = 0
Tell oracle its a multiline string = 'm'
The outer regexp_replace removes the new lines left over.
Counting the remaining characters gives you the line count.
Ill have to look into a better solution.
Heres a much much more performant solution
LENGTH(TEXTVALUE) - LENGTH(replace(TEXTVALUE,chr(10))))
replacing all CHR(10) (linefeed char) with nothing. Then compare with original length.
A query which took me 40 minutes with the first query, now takes under a second.
much faster than Oracle 11g, regexp_count as well.