How do I extract a pattern from a table in Oracle 11g?

I want to extract text from a column using regular expressions in Oracle 11g. I have 2 queries that do the job but I'm looking for a (cleaner/nicer) way to do it. Maybe combining the queries into one or a new equivalent query. Here they are:

Query 1: identify rows that match a pattern:

select column1 from table1 where regexp_like(column1, pattern);

Query 2: extract all matched text from a matching row.

select regexp_substr(matching_row, pattern, 1, level) 
from dual
connect by开发者_开发百科 level < regexp_count(matching_row, pattern);

I use PL/SQL to glue these 2 queries together, but it's messy and clumsy. How can I combine them into 1 query. Thank you.

UPDATE: sample data for pattern 'BC':

row 1: ABCD
row 2: BCFBC
row 3: HIJ
row 4: GBC

Expected result is a table of 4 rows of 'BC'.

You can also do it in one query, functions/procedures/packages not required:

WITH t1 AS (
SELECT 'ABCD' c1 FROM dual
UNION
SELECT 'BCFBC' FROM dual
UNION
SELECT 'HIJ' FROM dual
UNION
SELECT 'GBC' FROM dual
)
SELECT c1, regexp_substr(c1, 'BC', 1, d.l, 'i') thePattern, d.l occurrence
  FROM t1 CROSS JOIN (SELECT LEVEL l FROM dual CONNECT BY LEVEL < 200) d
WHERE regexp_like(c1,'BC','i')
   AND d.l <= regexp_count(c1,'BC');

C1    THEPATTERN           OCCURRENCE
----- -------------------- ----------
ABCD  BC                            1
BCFBC BC                            1
BCFBC BC                            2
GBC   BC                            1

SQL>

I've arbitrarily limited the number of occurrences to search for at 200, YMMV.

Actually there is an elegant way to do this in one query, if you do not mind to run some extra miles. Please note that this is just a sketch, I have not run it, you'll probably have to correct a few typos in it.

create or replace package yo_package is
  type word_t  is record (word varchar2(4000));
  type words_t is table of word_t;
end;
/

create or replace package body yo_package is

  function table_function(in_cur in sys_refcursor, pattern in varchar2) 
  return words_t
    pipelined parallel_enable (partition in_cur by any)
  is
    next varchar2(4000);
    match varchar2(4000);
    word_rec word_t;
  begin
    word_rec.word = null;

    loop

    fetch in_cur into next;
    exit when in_cur%notfound;

    --this you inner loop where you loop through the matches within next
    --you have to implement this 
    loop
        --TODO get the next match from next     
        word_rec.word := match;
        pipe row (word_rec);    
    end loop;

    end loop;

  end table_function;

end;
/


select  *
from table(
    yo_package.table_function(
        cursor(
            --this is your first select
            select column1 from table1 where regexp_like(column1, pattern)
        )
    )