Re: PL/SQL Procedure Need Help seriously

> this is my procedure whereby you input a date value into the procedure 
to calculate ur age.

> variable g_age number
> create or replace procedure cal_age
> ( mybirthdate in date 
>   myage out number )
> is
> begin
> myage := (sysdate - mybirthdate)/365
> :g_age := myage;
> end;
> print g_age

> Here is my problem
> how do i input a date value into my procedure..

> i tried
> execute cal_age('14-NOV-84');
> but it return an error and i know it wrong as well.

While this would be better written as a function, as others have said, 
that is not the problem you are having.  There are a few syntax errors in 
your procedure, which when corrected looks like this:

create or replace procedure cal_age
( mybirthdate in date
, myage out number )
is
begin
  myage := (sysdate - mybirthdate)/365;
end;

Now you can't call this procedure, which has 2 required parameters, with 
just 1 argument like you did:

SQL> exec cal_age('14-nov-84')
begin cal_age('14-nov-84'); end;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CAL_AGE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

You need to provide the 2nd OUT argument, e.g.

SQL> VARIABLE g_age NUMBER
SQL> exec cal_age('14-nov-84',:g_age)

> but i cant embed a to_date function within the parenthesis.

Yes you can, and you SHOULD because otherwise you are relying on the 
default date format mask being DD-MON-RR, which it may not be:

SQL> alter session set nls_date_format='MM/DD/YY';

Session altered.

SQL> exec cal_age('14-nov-84',:g_age)
begin cal_age('14-nov-84',:g_age); end;

*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1

SQL> exec cal_age(to_date('14-nov-84','dd-mon-rr'),:g_age)

PL/SQL procedure successfully completed.

SQL> print g_age

     G_AGE
----------
18.4532254
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].