select id, name, NVL(address, '***') address
from some_table
where ...

CREATE OR REPLACE PROCEDURE AIC_OP_CD (
    p_SYS      IN       NUMBER,
    p_field6   OUT      VARCHAR2,
    p_field7   OUT      VARCHAR2,
    p_field8   OUT      VARCHAR2,
    p_field9   OUT      varchar2
 )
 IS
    CURSOR c1
    IS
       SELECT
               CSO_OPER_CODE , CSO_OP_STAGE_NO
 FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
 where csh_sys_id = csd_csh_sys_id
 and  csd_sys_id = cso_csd_sys_id
 and csd_sys_id = P_SYS
 ORDER BY CSO_OP_STAGE_NO;
   cursor c2
   is
       SELECT COUNT(*) FROM
 OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
 where csh_sys_id = csd_csh_sys_id
 and  csd_sys_id = cso_csd_sys_id
 and csd_sys_id = P_SYS
 ORDER BY CSO_OP_STAGE_NO   ;
 M_OPER VARCHAR2(20);
 M_STG NUMBER;
 M_NO NUMBER;
 BEGIN
    OPEN c1;
    FETCH c1
     INTO M_OPER,M_STG;
    CLOSE c1;
    OPEN C2;
    FETCH C2 INTO M_NO;
    CLOSE C2;
  FOR I IN C2
   LOOP
    IF M_NO = 1 AND M_STG=1
    THEN
       p_field6 := M_OPER;
       p_field7 := 'X';
       P_FIELD8 := 'X';
       P_FIELD9 := 'X';
    ELSIF  M_NO = 2 AND M_STG=1
    THEN
       P_field6 := M_OPER;
       p_field7 := 'X';
       p_field8 := 'X';
       P_FIELD9 := 'X';
   ELSIF  M_NO = 2 AND M_STG=2
    THEN
       P_field6 := 'X';
       p_field7 := M_OPER;
       p_field8 := 'X';
       P_FIELD9 := 'X';
   ELSIF  M_NO = 3 AND M_STG=1
    THEN
       P_field6 := M_OPER;
       p_field7 := 'X';
       p_field8 := 'X';
       P_FIELD9 := 'X';
   ELSIF  M_NO = 3 AND M_STG=2
    THEN
       P_field6 := 'X';
       p_field7 := M_OPER;
       p_field8 := 'X';
       P_FIELD9 := 'X';
    ELSE
       P_field6 := 'X';
       p_field7 := 'X';
       p_field8 := M_OPER;
       P_FIELD9 := 'X';
    END IF;
 END LOOP;
  END;

CREATE OR REPLACE FUNCTION ORION2007.getoper1 (p_sys  in  

ot_cutting_sheet_detail.csd_sys_id%TYPE)
   RETURN VARCHAR2
 IS
   l_text  VARCHAR2(32767) := NULL;
    CURSOR c1
     IS
        SELECT
              cso_csd_sys_id, CSO_OP_STAGE_NO
  FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  ORDER BY CSO_OP_STAGE_NO;
  cursor c2
    is
        SELECT COUNT(*) FROM
  OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  ORDER BY CSO_OP_STAGE_NO   ;
  cursor c3 (p_stg number) is
    select   CSO_oper_code
  FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  and cso_op_stage_no = p_stg
  ORDER BY CSO_OP_STAGE_NO;
 p_field6 VARCHAR2(20);
 p_field7 varchar2(20);
 p_field8 varchar2(20);
 p_field9 varchar2(20);
  m_sys number;
  m_oper varchar2(20);
  M_STG NUMBER;
  M_NO NUMBER;
  BEGIN
     OPEN c1;
     FETCH c1
      INTO M_sys,M_STG;
     CLOSE c1;
     OPEN C2;
     FETCH C2 INTO M_NO;
     CLOSE C2;
     open c3(m_stg);
     fetch c3 into m_oper;
     close c3;
   FOR j IN C2
    LOOP
     IF M_NO = 1
       THEN
    for i in c1
      loop
      for k in c3(i.cso_op_stage_no)
       loop
        p_field6 := M_OPER;
        p_field7 := 'X';
        P_FIELD8 := 'X';
        P_FIELD9 := 'X';
    l_text := (p_field6||''||p_field7||''||p_field8||''||p_field9);
         end loop;
      end loop;
     ELSIF  M_NO = 2
     THEN
     for i in c1
      loop
      for k in c3(i.cso_op_stage_no)
       loop
       if i.cso_op_stage_no = 1
        then
        P_field6 := M_OPER;
        p_field7 := 'X';
        p_field8 := 'X';
        P_FIELD9 := 'X';
        else
        P_field6 := 'X';
        p_field7 := M_OPER;
        p_field8 := 'X';
        P_FIELD9 := 'X';
      l_text := (p_field6||''||p_field7||''||p_field8||''||p_field9);
    END IF;
        END LOOP;
       END LOOP;
   END IF;
  RETURN  l_text;
   END;

CREATE TABLE ot_cutting_sheet_head (csh_sys_id VARCHAR2(10),csh_dt DATE);

INSERT INTO OT_CUTTING_SHEET_HEAD ( CSH_SYS_ID, CSH_DT ) VALUES ( 
'50',  TO_Date( '01/10/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
COMMIT;

CREATE TABLE  ot_cutting_sheet_detail (csd_csh_sys_id VARCHAR2(10),csd_sys_id VARCHAR2(10),csd_pos VARCHAR2(100))

INSERT INTO OT_CUTTING_SHEET_DETAIL ( CSD_CSH_SYS_ID, CSD_SYS_ID,
CSD_POS ) VALUES ( 
'100', '50', '101'); 
INSERT INTO OT_CUTTING_SHEET_DETAIL ( CSD_CSH_SYS_ID, CSD_SYS_ID,
CSD_POS ) VALUES ( 
'100', '50', '102'); 
INSERT INTO OT_CUTTING_SHEET_DETAIL ( CSD_CSH_SYS_ID, CSD_SYS_ID,
CSD_POS ) VALUES ( 
'100', '50', '103'); 
COMMIT;

CREATE TABLE ot_cutting_sheet_operation (cso_csd_sys_id VARCHAR2(10),csd_sys_id VARCHAR2(10), cso_oper_code VARCHAR2(100))

INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'cut'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'weld'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'paint'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'blast'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'102', '50', 'cut'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'102', '50', 'weld'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'103', '50', 'cut'); 
COMMIT;

  SELECT  csh_dt, csd_pos, MAX (cut) cut, MAX (weld) weld, MAX (paint) paint,
         MAX (blast) blast
    FROM (SELECT csh_dt, csd_pos, DECODE (seqno, 1, cso_oper_code, 'X') cut,
                 DECODE (seqno, 2, cso_oper_code, 'X') weld,
                 DECODE (seqno, 3, cso_oper_code, 'X') paint,
                 DECODE (seqno, 4, cso_oper_code, 'X') blast
            FROM (SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos)
                                                                        seqno,
                           csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos))
GROUP BY csh_dt,csd_pos
order by csh_dt,csd_pos

1) --: get a sequential number value for each operation per stage:

SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos) seqno,
                           csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos

2)--- use this sequential number in a decode/case construction.
  ---this allows to break the values in 4 different columns.
 


SELECT csh_dt, csd_pos, DECODE (seqno, 1, cso_oper_code, 'X') cut,
                 DECODE (seqno, 2, cso_oper_code, 'X') weld,
                 DECODE (seqno, 3, cso_oper_code, 'X') paint,
                 DECODE (seqno, 4, cso_oper_code, 'X') blast
            FROM (SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos)
                                                                        seqno,
                           csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos)


3) --In above we distributed the values over 4 columns
   --Now need to aggregate the values using a MAX()... GROUP BY:

SELECT  csh_dt, csd_pos, MAX (cut) cut, MAX (weld) weld, MAX (paint) paint,
         MAX (blast) blast
    FROM (SELECT csh_dt, csd_pos, DECODE (seqno, 1, cso_oper_code, 'X') cut, ---2)  
                 DECODE (seqno, 2, cso_oper_code, 'X') weld,
                 DECODE (seqno, 3, cso_oper_code, 'X') paint,
                 DECODE (seqno, 4, cso_oper_code, 'X') blast
            FROM (SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos) --1)  
                        seqno,csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos))
GROUP BY csh_dt,csd_pos
order by csh_dt,csd_pos