CREATE OR REPLACE PACKAGE_BODY ORGANIZATION_PACKAGE

FUNCTION get_organization_data(pin_parm)
  RETURN organization_tbl PIPELINED
IS
  key_data loan%rowtype;
  v_out_data organization_rec;
BEGIN

   SELECT * INTO key_data FROM LOANTBL WHERE key = pin_parm;
   
   SELECT NAME INTO v_out_data.NAME FROM SCHEMA1.ORGTBL WHERE ORG = key_data.org; -- I get data from another schema
   
   v_out_data.ADDRESS := address_pkg.address_data@<SERVER>@<SCHEMA>(key_data.org); -- I get data through DBLINK
   
   PIPE ROW(v_out_data);
   
END get_organization_data; 

END ORGANIZATION_PACKAGE;
-----------------------------------------------
FUNCTION test_func_main(pin_parm IN VARCHAR2)
 RETURN boolean
IS
  v_name varchar2(100);
  v_address varchar2(100);
BEGIN
  dbms_output.put_line('--- TESTING MAIN FUNCTION --- (' || pin_parm || ')');
  
  dbms_output.put_line('--------------------------------------------------------');
  dbms_output.put_line('--- GET DATA ABOUT ORGANIZATION ---');
 
  SELECT NAME, ADDRESS
    INTO v_name, v_address
    FROM TABLE (ORGANIZATION_PACKAGE.get_organization_data(pin_parm));
 
  RETURN(TRUE);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('NO DATA FOR ^^^^^^^^^^');
    RETURN(TRUE);
  WHEN OTHERS THEN
    dbms_output.put_line('ERROR-> ' || SQLERRM);
    RETURN(FALSE);

END test_func_main;