Unable to call output of my cursor for use in output

Question

I have an Oracle Apex page where I receive 4 variables from the page.

:P1_COL1
:P1_COL2
:P1_COL3
:P1_COL4

The problem here is that P1_COL4 can receive many values and they are separated by a colon. So what I did was that I declared a cursor with a regexp to delimit the rows by a colon.

For example,

Currently this is what the output is if I do:

select :P1_COL1,:P1_COL2,:P1_COL3,:P1_COL4 from dual;

BOB,JONES,M,BLUE:RED:GREEN:YELLOW:PURPLE

Whereas I'm looking for something like this...

Bob, Jones, M, BLUE
Bob, Jones, M, RED
Bob, Jones, M, GREEN
Bob, Jones, M, YELLOW
Bob, Jones, M, PURPLE

But currently my code is not working

DECLARE CURSOR a1
is
        WITH statement AS
    (
        select :P1_COL4 obj
        from dual
    )
SELECT REGEXP_SUBSTR(OBJ,'[^,:;]+',1,LEVEL) FROM dual CONNECT BY LEVEL<=LENGTH(REGEXP_REPLACE(OBJ,'[^,:;]+'))+1;

begin
for x in a1
loop
dbms_output.put_line(:P1_COL1 ||','|| :P1_Col2 ||','||
     :P1_Col3||','|| a1 || chr(13));
 end loop;
 end;

Answer 1

Split the colon delimited string separately and use cross join to include other columns.

SQL Fiddle

WITH data 
     AS (SELECT 'Bob'                          AS P1_COL1, 
                'Jones'                        AS P1_COL2, 
                'M'                            AS P1_COL3, 
                'BLUE:RED:GREEN:YELLOW:PURPLE' AS P1_COL4 
         FROM   dual) 
SELECT d.p1_col1, 
       d.p1_col2, 
       d.p1_col3, 
       s.col 
FROM   data d 
       cross join (SELECT REGEXP_SUBSTR(p1_col4, '[^:]+', 1, LEVEL) AS col 
                     FROM   data 
                   CONNECT BY LEVEL <= REGEXP_COUNT(p1_col4, '[^:]+')) s 

Results:

| P1_COL1 | P1_COL2 | P1_COL3 |    COL |
|---------|---------|---------|--------|
|     Bob |   Jones |       M |   BLUE |
|     Bob |   Jones |       M |    RED |
|     Bob |   Jones |       M |  GREEN |
|     Bob |   Jones |       M | YELLOW |
|     Bob |   Jones |       M | PURPLE |

You could also simplify your query without a with clause and selecting those variables directly from dual.

EDIT

I'm trying to use for loop

This is how you could do it.

SET SERVEROUTPUT ON

BEGIN
  FOR r IN
  (
   SELECT :p1_col1 as p1_col1
     ,:p1_col2 as p1_col2
     ,:p1_col3 as p1_col3
     ,s.p1_col4
FROM dual d
CROSS JOIN (
    SELECT REGEXP_SUBSTR(:p1_col4, '[^:]+', 1, LEVEL) AS p1_col4
    FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(:p1_col4, '[^:]+')
    ) s )
  LOOP
    DBMS_OUTPUT.PUT_LINE (r.p1_col1
    ||','
    || r.p1_col2
    ||','
    || r.p1_col3
    || r.p1_col4
    || CHR(13));
  END LOOP; 
 END;
 /