Issues with procedure!

user782973-Oracle

Thanks Frank! Whats the right way of finding difference between two dates?

Solomon Yakobson

The right way is to use 4 digit year, use RR format or better use date literals. For example:

DATE '1980-01-01'

And BETWEEN works left to right, therefore start value must be less or equal to end value, therefore

WHERE HIREDATE BETWEEN TO_DATE('01-01-85','DD-MM-YY') AND TO_DATE('01-01-80','DD-MM-YY'); 

will never work. Use:

WHERE HIREDATE BETWEEN DATE '1980-01-01' AND DATE '1985-01-01'; 

And keep in mind, the above includes January 1, 1985. Somehow I have a feeling you want:

WHERE HIREDATE BETWEEN DATE '1980-01-01' AND DATE '1984-12-31';

SY.

Solomon Yakobson

And one more thing. Why do you have literals in where clause? It sounds you should use procedure parameters:

WHERE HIREDATE BETWEEN START_DATE AND END_DATE;

SY.

James Su

There's nothing between line 12 and 13. You need to have some code between IF and END IF

if c1%notfound then  

endif; 

BTW why don't you do a simple INERT...SELECT ?

2842297

Hi,

This will solve your problem

------------------------------------

CREATE OR REPLACE procedure VALID_PROC( START_DATE IN DATE, END_DATE IN DATE) 

IS 

cursor c1 is  

SELECT * FROM EMP 

WHERE HIREDATE BETWEEN TO_DATE(START_DATE,'DD-MM-YYYY') AND TO_DATE(END_DATE,'DD-MM-YYYY'); 

c_row c1%rowtype; 

BEGIN 

OPEN C1;

loop 

fetch c1 into c_row; 

exit when  c1%notfound;    

INSERT INTO emp_back(empno,ename,job,mgr,hiredate,sal,comm,deptno) 

VALUES(c_row.empno,c_row.ename,c_row.job,c_row.mgr,c_row.hiredate,c_row.sal,c_row.comm,c_row.deptno); 

end loop;

close c1; 

END; 

--------------------------

mistakes : missed MGR value

                didn't use loop

when Procedure/Function return compilation error...please try 

           SHOW ERROR PROCEDURE procedure_name;

it will give cause of error.

Thanks

Rajesh

BluShadow

user782973-Oracle wrote:

Hi, Can someone help me understand whats wrong with my procedure.

1. CREATE OR REPLACE procedure VALID_PROC( START_DATE IN DATE, END_DATE IN DATE) 
2. IS 
3.  
4.  
5. cursor c1 is 
6. SELECT * FROM EMP 
7. WHERE HIREDATE BETWEEN TO_DATE('01-01-85','DD-MM-YY') AND TO_DATE('01-01-80','DD-MM-YY'); 
8. c_row c1%rowtype; 
9. BEGIN 
10. OPEN C1; 
11. fetch c1 into c_row; 
12. if c1%notfound then 
13. endif; 
14.  
15.  
16. INSERT INTO emp_back(empno,ename,job,mgr,hiredate,sal,comm,deptno) 
17. VALUES(c_row.empno,c_row.ename,c_row.job,c_row.hiredate,c_row.sal,c_row.comm,c_row.deptno); 
18.  
19.  
20. commit; 
21. close c1; 
22. END; 
23. / 

CREATE OR REPLACE procedure VALID_PROC( START_DATE IN DATE, END_DATE IN DATE)
IS


cursor c1 is 
SELECT * FROM EMP
WHERE HIREDATE BETWEEN TO_DATE('01-01-85','DD-MM-YY') AND TO_DATE('01-01-80','DD-MM-YY');
c_row c1%rowtype;
BEGIN
OPEN C1;
fetch c1 into c_row;
if c1%notfound then 
endif;


INSERT INTO emp_back(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(c_row.empno,c_row.ename,c_row.job,c_row.hiredate,c_row.sal,c_row.comm,c_row.deptno);


commit;
close c1;
END;
/

What's right with it is the question.... the answer being... "not much".

The whole thing can be simplified to just:

create or replace procedure valid_proc(start_date in date, end_date in date) as
begin
  insert into emp_back(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    select empno, ename, job, mgr, hiredate, sal, comm, deptno
    from  emp
    where  hiredate between start_date and end_date;
  commit; -- if appropriate to business/transaction logic
end; 
/