1

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??

2

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;
  • Thanks . What if I want to execute this in pl/sql developer environment ?? Is the syntax the same?? – Pantea Tourang Jun 2 at 4:05
  • 1
    You're welcome. I edited my message by adding the PL/SQL part; have a look, please. – Littlefoot Jun 2 at 7:29
  • You're welcome. I edited my message by adding the PL/SQL part; have a look, please. – Littlefoot Jun 2 at 7:29
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.