SQL & PL/SQL

My original query got sorted, but additional regexp_substr and count help is required further on down!

****************************************************
Hi,

I have a table on a 10.2.0.3 database which contains a clob field (sql_stmt), with contents that look something like:

SELECT <COB_DATE>, col2, .... coln
FROM   tab1, tab2, ...., tabn
WHERE tab1.run_id = <RUNID>
AND    tab2.other_col = '<OTHER TAG>'

(That's a highly simplified sql_stmt example, of course - if they were all that small we'd not be needing a clob field!).

I wanted to extract all the tags from the sql_stmt field for a given row, so I can get my (well not "mine" - I'd never have designed something like this, but hey, it works, sorta, and I'm improving it as and where I can!) pl/sql to replace the tags with the correct values. A tag is anything that's in triangular brackets (eg. <RUNID> from the above example)

So, I did this:

SELECT     SUBSTR (sql_stmt,
                   INSTR (sql_stmt, '<', 1, LEVEL),
                   INSTR (substr(sql_stmt, INSTR (sql_stmt, '<', 1, LEVEL)), '>', 1, 1)
                   ) tag
FROM       export_jobs
WHERE      exp_id =  p_exp_id
CONNECT BY LEVEL <= (LENGTH (sql_stmt) - LENGTH (REPLACE (sql_stmt, '<')))

Which I thought would be fine (having tested it on a text column). However, it runs very poorly against a clob column, for some reason (probably doesn't like the substr, instr, etc on the clob, at a guess) - the waits show "direct path read".

When I cast the sql_stmt as a varchar2 like so:

with my_tab as (select cast(substr(sql_stmt, instr(sql_stmt, '<', 1), instr(sql_stmt, '>', -1) - instr(sql_stmt, '<', 1) + 1) as varchar2(4000)) sql_stmt
                from export_jobs
                WHERE      exp_id = p_exp_id)
SELECT     SUBSTR (sql_stmt,
                   INSTR (sql_stmt, '<', 1, LEVEL),
                   INSTR (substr(sql_stmt, INSTR (sql_stmt, '<', 1, LEVEL)), '>', 1, 1)
                   ) tag
FROM       my_tab
CONNECT BY LEVEL <= (LENGTH (sql_stmt) - LENGTH (REPLACE (sql_stmt, '<')))

it runs blisteringly fast in comparison, except when the substr'd sql_stmt is over 4000 chars, of course! Using dbms_lob instr and substr etc doesn't help either.

So, I thought maybe I could find an xml related method, and from this link:2513869 , I tried:

select t.column_value.getrootelement() node
  from (select sql_stmt xml from export_jobs where exp_id = 28) xml, 
 table (xmlsequence(xml.xml.extract('//*'))) t

But I get this error: ORA-22806: not an object or REF. (It might not be the way to go after all, as it's not proper xml, being as there are no corresponding close tags, but I was trying to think outside the box. I've not needed to use xml stuff before, so I'm a bit clueless about it, really!)

I tried casting sql_stmt into an xmltype, but I got: ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

Is anyone able to suggest a better method of trying to extract my tags from the clob column, please?

Message was edited by:
Boneist