CREATE OR REPLACE TYPE xx_note_typ force AS OBJECT 
( 
  note_id      NUMBER, 
  note_scope   NUMBER, 
  note_value   VARCHAR2(4000), 
  note_date    VARCHAR2(100), 
  CONSTRUCTOR FUNCTION xx_note_typ RETURN SELF AS RESULT 
) 

CREATE OR REPLACE TYPE xx_note_arr_typ FORCE AS TABLE OF xx_note_typ; 

create table xx_CUSTOMER_NOTE_ALL 
( 
  id         NUMBER not null, 
  agent_id   NUMBER, 
  bb_id number,
  aa_id varchar2(10),
  note_value VARCHAR2(4000), 
  note_scope NUMBER, 
  reg_date   TIMESTAMP(6) WITH TIME ZONE not null, 
  system_id  NUMBER not null, 
  ts         TIMESTAMP(6) WITH TIME ZONE not null 
) 
compress 
nologging

DECLARE 
  l_result xx_note_arr_typ; 
BEGIN 
  SELECT xx_note_typ(note_id => NULL, note_scope => NULL, note_value => NULL, note_date => NULL) 
    BULK COLLECT 
    INTO l_result 
    FROM xx_CUSTOMER_NOTE_ALL icn 
   WHERE (icn.note_scope = 0 AND icn.bb_id = 42) 
      OR (icn.note_scope = 1 AND icn.aa_id = '42'); 
END;