0

Please consider the following code (query.sql):

create or replace function age (dateOfBirth date)
return number
is

mAge number(5,2);

begin
   mAge:=(sysdate-dateOfBirth)/365.25;
   return mAge;
end;



SQL> @query.sql
13
14
15  /

Warning: Function created with compilation errors.

And when I click on Show error, I get the following:

Code:

SQL> show error
Errors for FUNCTION AGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/8      PL/SQL: Item ignored
5/15     PLS-00325: non-integral numeric literal 5.2 is inappropriate in
     this context

8/8      PL/SQL: Statement ignored
8/8      PLS-00320: the declaration of the type of this expression is
     incomplete or malformed

9/5      PL/SQL: Statement ignored
9/12     PLS-00320: the declaration of the type of this expression is
     incomplete or malformed

LINE/COL ERROR
-------- -----------------------------------------------------------------

I tried to do the following from : Oracle Procedure

1) SQL> set role none;

and 

2) SELECT ON DBA_TAB_COLUMNS;

But the second query above is throwing error : Missing expression.

Please let me know what's wrong with all of the above stuff.

Thanks

Community
  • 1
  • 1
Tan
  • 1,433
  • 5
  • 27
  • 47

1 Answers1

2

You're missing a BEGIN and your NUMBER variable should be declared with a comma not a period.

create or replace function age (
  pDateOfBirth date ) return number is

   l_age number(5,2);

begin    
   l_age := ( sysdate - pDateOfBirth ) / 365.25;
   return l_age;
end;
/

You've now edited the question to include the BEGIN but you haven't fixed your declaration of the variable. As your error message says:

PLS-00325: non-integral numeric literal 5.2 is inappropriate in this context

Personally, I believe you're calculating age incorrectly. There are 365 or 366 days in a year. I'd do this instead, which uses internal Oracle date functions:

function get_age (pDOB date) return number is
   /* Return the the number of full years between 
      the date given and sysdate.
      */    
begin    
   return floor(months_between(sysdate, pDOB)/12);    
end;

That is if you only want the number of full years.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Thanks for pointing out. I was pasting the code here and forgot BEGIN. Fixed it. I'm getting errors along with the presence of BEGIN.Anyways, I'll check what you have mentioned and let you know. – Tan May 05 '13 at 20:59
  • I just checked your first part of code and I'm getting "INSUFFICIENT PRIVILEGES " error. How to get rig of this? – Tan May 05 '13 at 21:01
  • Most likely you don't have the privileges to create the function @AaK; if that's the case you need to log on as SYS or another DBA user and `grant create procedure to ` where `` is the name of the schema you're trying to create the function in. See the documentation: http://docs.oracle.com/cd/B19306_01/network.102/b14266/authoriz.htm#i1009241 – Ben May 05 '13 at 21:05
  • Well, I tried to figure out privilege problem: Here are the steps I followed: In my Windows command prompt I logged in as follows: sqlplus / as sysdba I got the SQL> prompt over there and then I tried to grant privilige on my function "age" as follows: SQL> GRANT EXECUTE on AGE to SCOTT; I got the error ORA-0402: function body doesn't exist. How can I save my function body as I'm just running "Query.sql" file on SQL Plus. Please help ! – Tan May 05 '13 at 23:08
  • You can't grant execute on a function that doesn't exist. Grant create to Scott as I write above @aak – Ben May 06 '13 at 06:47