PL/SQL Package Procedure returning PL/SQL Record

PL/SQL Package Procedure returning PL/SQL Record 

PL/SQL Record Type hold one record at a time.

Syntax: PL/SQL Record Type

Type [typename]  is Record(type1,type2, ...);

 

for ex:  Emp Table Record

 Type empRecType is Record(p_empno number(4),p_ename varchar2(20));

This tutorial explains how to return Record Type from a stored procedure.

Create a Package Specification

which has Record Type and one Procedure returning Record Type as Output Parameter.

create or replace package pkgRec
is
 

type empRecType is record(p_empno number(4),p_ename varchar2(20));
 

procedure sp_get_empRecord(p_empno in number,p_emprec out empRecType);
 

end pkgRec;

Create a Package Body

create or replace package body pkgRec
is
 

procedure sp_get_empRecord(p_empno in number,

p_emprec out empRecType)
as
 

begin
select empno,ename into p_emprec from  emp where empno=p_empno;
end;
 

end pkgRec;

Consuming  a PL/SQL Package Procedure

SQL>declare

v_empRec pkgRec.empRecType;

begin

dbms_output.put_line('Package procedure returing Record Type');

pkgRec.sp_get_empRecord(&empno,v_empRec);

dbms_output.put_line(v_empRec.p_empno||' '||v_empRec.p_ename);

Exception

when no_data_found then

dbms_output.put_line(sqlerrm||' '||sqlcode);

end;

Note: before executing 

SQL>set serveroutput on

SQL>/

Enter value for empno: 7900

Enter value for empno: 7900
old   5: pkgRec.sp_get_empRecord(&empno,v_empRec);
new   5: pkgRec.sp_get_empRecord(7900,v_empRec);
Package procedure returing Record Type
7900 JAMES

PL/SQL procedure successfully completed.

Tags:PL/SQL Package Procedure returning PL/SQL Record , PL/SQL Record Type,using PL/SQL Record in Stored Procedures, using PL/SQL Record type in Packages.Consuming PL/SQL package procedures in Anonymous blocks.