Facing error while executing function inside the package in PL/SQL

Question

Hi everyone hope you're doing well. I'm new to oracle and I'm reading about the concept of 'Package' and 'Package body'. I have a function as you can see below

CREATE OR REPLACE Function Show_description(i_Course_No 
   Course.Course_No%Type)
 Return Varchar2 AS
V_Description Varchar2(50);

 Begin

     Select description
     into v_Description
     From Course
     Where Course_No = I_Course_No;
        Return V_Description;

  Exception
     When No_Data_Found Then
        Return('The Course Is not in the DataBase');
     When Others Then
        Return('Error in running Show_Description');

  End;

and I have a package containing only this function :

CREATE OR REPLACE PACKAGE MY_First_Package is

   function Show_description(i_Course_No Course.Course_No%Type) Return 
   Varchar2;

   end ;

and I have a package body as you can see here :

create or replace package body my_first_package AS

  Function Show_description(i_Course_No Course.Course_No%Type)
  Return Varchar2 AS
  V_Description Varchar2(50);

  Begin
    Select description
    into v_Description
    From Course
    Where Course_No = I_Course_No;
        Return V_Description;

    Exception
      When No_Data_Found Then
          Return('The Course Is not in the DataBase');
      When Others Then
          Return('Error in running Show_Description');

    End;
       End  my_first_package;

and everything seems fine without any error . When I want to execute this package as :

exec my_first_package.Show_description(12);

I face an error which is : ORA-00900 :Invalid sql statement

what is the problem??

Answer 1

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> create or replace package my_first_package as
  2    function show_Description(par_deptno in varchar2) return varchar2;
  3  end;
  4  /

Package created.

SQL> 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> var l_ret varchar2(20);
SQL> exec :l_ret := my_first_package.show_description(10);

PL/SQL procedure successfully completed.

SQL> print l_ret;

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

SQL>

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;

Answer 2

OK I've found where the problem is. I'm calling the function within the package SO I should not use the EXECUTE syntax I have to use it with a select statement like this :

select my_first_package.Show_description(12) 
 from dual;