PROJECT NAME   R1   R2   R3   Y/N
---------------------------------
example 1      1    0    0     N
esample 2      1    1    1     Y
projectttt     0    1    1     N

SELECT
  p.project_name,
  p.date,
  (SELECT pr.is_approved
    FROM project_review pr
    WHERE pr.project_id = p.project_id
    AND role = 'R1'
  ) R1,
  (SELECT pr.is_approved
    FROM project_review pr
    WHERE pr.project_id = p.project_id
    AND role = 'R2'
  ) R2,
  (SELECT pr.is_approved
    FROM project_review pr
    WHERE pr.project_id = p.project_id
    AND role = 'R3'
  ) R3
  FROM project p
where p.project_id = :P2_PROJECT_ID

declare
    myquery varchar2(1000);
begin
    myquery := 'SELECT ';
    for i in 1..:P3_NUM_COLS loop
        myquery := myquery || i || ' ' || ' mycol_name_' || i;
        if i <> :P3_NUM_COLS then
            myquery := myquery || ', ';
        end if;
    end loop;
    
    myquery := myquery || ' from dual';
    return myquery;
end;

SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(', (select pmr.IS_APPROVED
    from PMO_PROJECT_MILESTONE_REVIEW pmr
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID
    and  = '||rr.ROLE_ID||' '||rr.ROLE_CODE , '*')), '*', '') conc
FROM
  (SELECT rr.ROLE_ID,
    rr.ROLE_CODE,
    ROW_NUMBER() OVER (ORDER BY rr.ROLE_ID) R
  FROM PMO_ROLE rr
  ) rr
  START WITH r       =1
  CONNECT BY PRIOR r = r-1;

, (select pmr.IS_APPROVED 
    from PMO_PROJECT_MILESTONE_REVIEW pmr 
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID 
    and  = 1 R1, (select pmr.IS_APPROVED 
    from PMO_PROJECT_MILESTONE_REVIEW pmr 
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID 
    and  = 2 R2, (select pmr.IS_APPROVED 
    from PMO_PROJECT_MILESTONE_REVIEW pmr 
    where pmr.PROJECT_MILESTONE_ID = pm.PROJECT_MILESTONE_ID 
    and  = 3 R3

DECLARE
  v_query varchar2(1000);
BEGIN
  IF :P3_QUERY is not null THEN
    v_query := 'select pm.PROJECT_MILESTONE_ID, cp.CHECKPOINT_NAME, pm.MILESTONE_DATE' || 
:P3_QUERY ||' from PMO_PROJECT_MILESTONE pm, PMO_CHECKPOINT cp where pm.PROJECT_ID = ' || 
:P3_PROJECT_ID ||' and cp.checkpoint_code = pm.checkpoint_code';
  ELSE
  v_query := 'SELECT 1 FROM dual WHERE 1=0';
  END IF;
  return(v_query);
END;

where pm.PROJECT_ID = ' || 
:P3_PROJECT_ID ||' and cp.checkpoint_code = pm.checkpoint_code'

where pm.PROJECT_ID = :P3_PROJECT_ID and cp.checkpoint_code = pm.checkpoint_code'

ORA-01489: result of string concatenation is too long
 Error ERR-1019 Error computing item default value: page=1 name=P1_QUERY.