PL/SQL Cursor to Return One Row with Multiple Columns: Example Guide

declare
       cursor s is select employee_id,first_name,salary
       from emp ;
       v_id emp.employee_id%type;
       v_name emp.first_name%type;
       v_sal emp.salary%type;
begin
      open s;
   
      fetch s into v_id,v_name,v_sal;
      dbms_output.put_line(v_id||' '||v_name||' '||v_sal);
   
end;

OUTPUT:
     100 Steven 24000

👋 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 Anonymous Block to Return Multiple Columns: How-To Guide

declare
       v_id emp.employee_id%type:=&v_no;
       v_name emp.first_name%type;
       v_sal emp.salary%type;
begin
      select employee_id,first_name,salary
      into v_id,v_name,v_sal
      from emp
      where employee_id= v_id;
      dbms_output.put_line(v_id|| ' ' ||v_name||' '||v_sal);
end;

output :
 101 Neena 17000

👋 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 Program to Accept Two Numbers and Print the Largest

declare

       x number;
       y number;
begin
      x :=&x;
      y :=&y;
      if( x > y)then
       dbms_output.put_line('x is largest than y');
       else
          dbms_output.put_line('y is largest than x');
   
      end if;
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.

SQL String Concatenation: How to Combine Strings in Queries

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)||chr(10)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip  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;

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