PL/SQL Function to Return Multiple Columns: Step-by-Step Guide

create or replace function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
  return varchar2
  as
   
  l_ret_address  VARCHAR2(32000);

begin
      select address_line1 ||','||chr(10)|| nvl2(address_line2, address_line2||','||chr(10),NULL)||city||','||nvl2(state, state||','||chr(10),NULL)||
       nvl2(province, province||','||chr(10),NULL)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip
       INTO  l_ret_address
    from    ap_supplier_sites_all assa,ap_suppliers aps , fnd_territories_tl ftt
      where assa.vendor_id = aps.vendor_id   
      and  ftt.territory_code = assa.country
      and aps.vendor_id = p_vendor_id
      and assa.vendor_site_id = p_vendor_site_id ;
   
      return l_ret_address;
  end get_emp_addr;


PACKAGE SPEC :
  CREATE OR REPLACE package APPS.mutl_get_emp_address_Pkg
  is
  function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
  return varchar2;
  End ;

PACKAGE BODY :
   CREATE OR REPLACE package body APPS.mutl_get_emp_address_Pkg
  is
  function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
  return varchar2
  as
   
  l_ret_address  VARCHAR2(32000);

begin
      select address_line1 ||','||chr(10)|| nvl2(address_line2, address_line2||','||chr(10),NULL)||city||','||nvl2(state, state||','||chr(10),NULL)||
       nvl2(province, province||','||chr(10),NULL)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip
       INTO  l_ret_address
    from    ap_supplier_sites_all assa,ap_suppliers aps , fnd_territories_tl ftt
      where assa.vendor_id = aps.vendor_id   
      and  ftt.territory_code (+) = assa.country
      and aps.vendor_id = p_vendor_id
      and assa.vendor_site_id = p_vendor_site_id ;
   
      return l_ret_address;
  end get_emp_addr;
end mutl_get_emp_address_pkg;

RUN the package :
   DECLARE
  RetVal varchar2(32000);
  P_VENDOR_ID NUMBER;
  P_VENDOR_SITE_ID NUMBER;
BEGIN
  P_VENDOR_ID := 1887660;
  p_vendor_site_id :=127676;

  RetVal := APPS.GET_EMP_ADDRESS_PKG.GET_EMP_LIST ( P_VENDOR_ID, p_vendor_site_id );
  dbms_output.put_line(substr(retval,1,255));
  COMMIT;
END;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL: How to Return Multiple Values from a Function

CREATE OBJECT-TYPE

   CREATE TYPE HZ_PARTY_OBJ IS OBJECT (PARTY_ID NUMBER,PARTY_NUMBER VARCHAR2(30),PARTY_NAME VARCHAR2(360));
 

TABLE TYPE

   CREATE TYPE HZ_PARTY_TAB IS TABLE OF HZ_PARTIES_OBJ;
 
CREATE FUNCTION

CREATE OR REPLACE FUNCTION GET_PARTIES
  RETURN HZ_PARTY_TAB
   IS
   L_HZ_PARTY_TAB  := HZ_PARTY_TAB();
    N INTEGER := 0;
BEGIN 
   FOR R IN (SELECT PARTY_ID,PARTY_NUMBER,PARTY_NAME FROM HZ_PARTIES)
   LOOP
      L_HZ_PARTY_TAB.EXTEND;
      N := N +1 ;
      L_HZ_PARTY_TAB(N) := HZ_PARTY_OBJ(R.PARTY_ID,R.PARTY_NUMBER,R.PARTY_NAME);
      END LOOP;
      RETURN L_HZ_PARTY_TAB;
END;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.

PL/SQL: How to Return Multiple Columns from a Function

create or replace type emp_type_o as object

(address_line1 varchar2(240),

   city          varchar2(60),

   state         varchar2(150),

   zip           varchar2(60)

   );

create type emp_type_t is table of emp_type_o;

PL/SQL FUNCTION:

  create or replace function get_emp_list(p_segment1 varchar2)

     return emp_type_t

 as

     v_address_line_1 emp_type_t;

 begin

      select emp_type_o(address_line1,city,state,zip)

      bulk collect into v_address_line_1

      from ap_supplier_sites_all assa,ap_suppliers aps

      where assa.vendor_id = aps.vendor_id

      and   aps.segment1 = p_segment1;

      

      return v_address_line_1;

  end;

PL/SQL FUNCTION RUN OUTPUT:

select * from table(get_emp_list(16896));

Package Spec:

 Create Or Replace Package Get_emp_address_Pkg

    Is

    Function get_emp_list(p_segment1 varchar2)

    Return emp_type_t;

 End ;

Package Body:

 create or replace package body Get_emp_address_Pkg

  is

  function get_emp_list(p_segment1 varchar2)

  return emp_type_t

  as

     v_address_line_1 emp_type_t;

begin

      select emp_type_o(address_line1,city,state,zip)

      bulk collect into v_address_line_1

      from ap_supplier_sites_all assa,ap_suppliers aps

      where assa.vendor_id = aps.vendor_id

      and   aps.segment1 = p_segment1;

      return v_address_line_1;

   end get_emp_list;

end get_emp_address_pkg;

Run the Package:

     select get_emp_address_pkg.get_emp_list(16896) from dual;

👋 Hi, I'm Suriya — QA Engineer with 4+ years of experience in manual, API & automation testing.

📬 Contact Me | LinkedIn | GitHub

📌 Follow for: Real-Time Test Cases, Bug Reports, Selenium Frameworks.