PL/SQL Procedure Basics: How to Create and Use Procedures

create or replace procedure proc1
is
  l_id integer;
begin
  select employee_id
  into l_id
  from employees
  where first_name = 'Suriya Parithy';
  dbms_output.put_line(l_id);
end;

exec proc1;

👋 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 SELECT Multiple Rows with Examples

declare
          type party_id_type is table of hz_parties.party_id%type;
          type party_name_type is table of hz_parties.party_name%type;
           party_id_tab   party_id_type;
           party_name_tab party_name_type;
begin
     SELECT party_id,party_name
     BULK COLLECT INTO party_id_tab, party_name_tab
     FROM hz_parties;
   
    FOR i IN party_id_tab.FIRST..party_id_tab.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE ('party_id: '||party_id_tab(i));
       DBMS_OUTPUT.PUT_LINE ('party_name: '||party_name_tab(i));
    END LOOP;
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: Selecting Multiple Columns from a Table Explained

DECLARE
      CURSOR  hzp_cur IS
        SELECT party_id, party_name
        FROM hz_parties;
BEGIN
     FOR i IN hzp_cur
     LOOP
       DBMS_OUTPUT.PUT_LINE ('party_id: '||i.party_id);
       DBMS_OUTPUT.PUT_LINE ('party_name: '||i.party_name);
     
     END LOOP;
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 Procedure to Select Multiple Columns: Step-by-Step Guide

create or replace procedure MUTL_GET_EMP_ADDRESS(p_segment1 in varchar2)

is

  p_address_line1 varchar2(300);

  p_city          varchar2(100);

  p_state         varchar2(300);

  p_zip           varchar2(300);

     

      CURSOR c_address IS

        SELECT   assa.address_line1,

                 assa.city,

                 assa.state,

                 assa.zip      

        FROM     ap_supplier_sites_all assa,

                ap_suppliers aps

      WHERE    assa.vendor_id = aps.vendor_id

      AND      aps.segment1 =p_segment1;

BEGIN

      OPEN c_address;

        FETCH c_address INTO p_address_line1,p_city,p_state,p_zip;

        DBMS_OUTPUT.put_line (  p_address_line1 || '-' || p_city || '-' || p_state || '-' || p_zip); 

     CLOSE c_address;

END;

--Package spec

 create or replace package  MUTL_GET_EMP_ADDRESS_PKG

Is

  procedure MUTL_GET_EMP_ADDRESS(p_segment1 in varchar2);

End MUTL_GET_EMP_ADDRESS_PKG;

--Package body

  create or replace package body MUTL_GET_EMP_ADDRESS_PKG

is

  procedure MUTL_GET_EMP_ADDRESS(p_segment1 in varchar2)

is

  p_address_line1 varchar2(300);

  p_city          varchar2(100);

  p_state         varchar2(300);

  p_zip           varchar2(300);

     

      CURSOR c_address IS

        SELECT   assa.address_line1,

                 assa.city,

                 assa.state,

                 assa.zip      

        FROM     ap_supplier_sites_all assa,

                ap_suppliers aps

      WHERE    assa.vendor_id = aps.vendor_id

      AND      aps.segment1 =p_segment1;

BEGIN

      OPEN c_address;

        FETCH c_address INTO p_address_line1,p_city,p_state,p_zip;

        DBMS_OUTPUT.put_line (  p_address_line1 || '-' || p_city || '-' || p_state || '-' || p_zip); 

     CLOSE c_address;

 END MUTL_GET_EMP_ADDRESS;

end MUTL_GET_EMP_ADDRESS_PKG;

👋 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.