CREATE OR REPLACE TYPE xx_note_typ force AS OBJECT 
( 
  note_id      NUMBER, 
  note_scope   NUMBER, 
  note_value   VARCHAR2(4000), 
  note_date    VARCHAR2(100)
) ;
/

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, 
  debtor_id  VARCHAR2(10), 
  serving_id NUMBER, 
  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 
) ;


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.serving_id = 42) 
      OR (icn.note_scope = 1 AND icn.debtor_id = '42');    
END; 
/

PL/SQL procedure successfully completed.