PL/SQL Package Function returning PL/SQL Record

PL/SQL Package Function 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));
function get_empRecord(p_empno in number) return empRecType;
end pkgRec;

Create a Package Body

create or replace package body pkgRec
is
 

create or replace package body pkgRec
is
function get_empRecord(p_empno in number) return empRecType
as
v_empRec empRecType;
begin
select empno,ename into v_empRec from  emp where empno=p_empno ;
return v_empRec ;
end;
end pkgRec;
 

Consuming  a PL/SQL Package Procedure

SQL>declare
v_empRec pkgRec.empRecType;
begin
dbms_output.put_line('Package function returing Record Type');
v_empRec:=pkgRec.get_empRecord(&empno);
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.get_empRecord(&empno,v_empRec);
new   5: pkgRec.get_empRecord(7900,v_empRec);
Package function returing Record Type
7900 JAMES

PL/SQL procedure successfully completed.

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