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??
Thanks in advance
Best Answer
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.Now, how to use
EXEC
: declare a variable first, and then ...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.
Or, you could use it "directly" in, for example,
WHERE
clause: