Table:
| REFERRING_PAGE | SEARCH_TERM |
|---|
| TOYOTA.COM | 4RUNER;HIGHLANDER;COROLA;CAMRY; |
| HONDA.COM | ACCORD;CIVIC;PILOT |
| FORD.COM | EXPLORER;ESCAPE SUV;MUSTANG;TAURUS |
Display Output:
| REFERRING_PAGE | Search_term | WORDS |
|---|
| TOYOTA.COM | 4RUNNER;HIGHLANDER;COROLA;CAMRY; | 4RUNNER; |
| | HIGHLANDER; |
| | COROLA; |
| | CAMRY; |
| HONDA | ACCORD;CIVIC;PILOT | ACCORD; |
| | CIVIC; |
| | PILOT |
| FORD.COM | EXPLORER; | EXPLORER |
| | ESCAPE SUV; |
| | MUSTANG; |
| | TAURUS |
Dear OTN forum;
how can I construct this EXECUTE IMMEDIATE REGEXP_SUBSTR function in PL/SQL. Thanks in advance.
SET SERVEROUTPUT ON SIZE 100000;
DECLARE
v_referring_page dealers.referring_page%type;
v_search_term dealers.search_term%type;
CURSOR myCursor IS
SELECT referring_page
,search_term
FROM DEALERS
WHERE search_term IS NOT NULL
AND TRUNC(log_date) = TO_DATE('30-JAN-14','DD-MON-YY');
iRec myCursor%ROWTYPE;
BEGIN
OPEN myCursor;
LOOP
FETCH myCursor INTO iRec;
EXIT WHEN myCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(iRec.referring_page||' '||iRec.search_term);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE( EXECUTE IMMEDIATE 'SELECT REGEXP_SUBSTR(iRec.search_term,''[^;]+'', 1, level) FROM DUAL
CONNECT BY REGEXP_SUBSTR(iRec.search_term,''[^;]+, 1, level) IS NOT NULL');
END LOOP;
CLOSE myCursor;
END;