CODEWITHSUNDEEP
oracle-databaseplsql
user466534
user466534

Reputation:

select each employeere's expire date

Suppose that expire date of employees is defined as one year after hire_date. I have created a function which is trying to do such task:

 create  or   replace   expiredate(empno  in number) return date is
    hiredate  employees.hire_date%type;
begin
    select  add_months(e.hire_date,12) into hiredate
    from employees e
    where empno is not null;
    return   hiredate;
    end  expiredate;

But it shows many errors, like this:

Error starting at line 1 in command:
create  or   replace   expiredate(empno  in number) return date is
hiredate  employees.hire_date%type
Error at Command Line:1 Column:23
Error report:
SQL Error: ORA-00922: missing or invalid option
00922. 00000 -  "missing or invalid option"
*Cause:    
*Action:

Error starting at line 3 in command:
begin
select  add_months(e.hire_date,12) into hiredate
from employees e
where empno is not null;
return   hiredate;
end  expiredate;
Error report:
ORA-06550: line 4, column 7:
PL/SQL: ORA-00904: "EMPNO": invalid identifier
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 1:
PLS-00372: In a procedure, RETURN statement cannot contain an expression
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

I think that main idea of error is too many rows mistake, but how can I correct this code?

Upvotes: 0

Views: 826

Answers (1)

Mat
Mat

Reputation: 206689

There are syntax errors in your create statement, and the select you put doesn't make sense.

Try the following:

create or replace function expiredate(empno  in number)
  return date is
  hiredate  employees.hire_date%type;
begin
    select  add_months(e.hire_date,12) into hiredate
    from employees e
    where e.empno = empno;
    return   hiredate;
end  expiredate;
/

Result:

SQL> create table employees (hire_date date); 

Table created.
SQL> create or replace function expiredate(empno  in number)
return date is
hiredate  employees.hire_date%type;
begin
    select  add_months(e.hire_date,12) into hiredate
    from employees e
    where e.empno = empno;
    return   hiredate;
end  expiredate;
/  2    3    4    5    6    7    8    9   10  

Function created.

SQL> insert into employees values (1, SYSDATE);

1 row created.

SQL> select * from employees;

     EMPNO HIRE_DATE
---------- ------------------
     1 25-FEB-12

SQL> select expiredate(1) from dual;

EXPIREDATE(1)
------------------
25-FEB-13

Upvotes: 1

Related Questions