Facing error while executing function inside the package in PL/SQL
Well, yes - but not necessarily. As you wanted to use EXEC (which is SQL*Plus), then have a look how you could have done it; first, a package with a function that returns department name for a certain department number.

SQL&gt; create or replace package my_first_package as
  2    function show_Description(par_deptno in varchar2) return varchar2;
  3  end;
  4  /

Package created.

SQL&gt; create or replace package body my_first_package as
  2    function show_Description(par_deptno in varchar2)
  3      return varchar2
  4    is
  5      retval dept.dname%type;
  6    begin
  7      select dname
  8        into retval
  9        from dept
 10        where deptno = par_deptno;
 11      return retval;
 12    exception
 13      when no_data_found then
 14        return null;
 15    end;
 16  end;
 17  /

Package body created.


Now, how to use EXEC: declare a variable first, and then ...

SQL&gt; var l_ret varchar2(20);
SQL&gt; exec :l_ret := my_first_package.show_description(10);

PL/SQL procedure successfully completed.

SQL&gt; print l_ret;

L_RET
-------------------------------------------------------------
ACCOUNTING

SQL&gt;


If you'd want to use that function in PL/SQL, then you could do the same - declare a variable and return the function value into it, e.g.

declare
  l_ret varchar2(20);
begin
  some code here
  l_ret := my_first_package.show_description(10);
  ...
end;


Or, you could use it "directly" in, for example, WHERE clause:

begin
  select ...
  into ...
  from ...
  where description = my_first_package.show_description(10)
    and ...
end;