Forum Stats

  • 3,852,732 Users
  • 2,264,130 Discussions
  • 7,905,127 Comments

Discussions

Issues with procedure!

2»

Answers

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

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited Jan 19, 2015 11:44AM

    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
    Solomon Yakobson Member Posts: 19,728 Red Diamond

    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
    James Su Member Posts: 1,164 Gold Trophy

    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
    2842297 Member Posts: 3
    edited Jan 19, 2015 2:02PM

    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
    BluShadow Member, Moderator Posts: 42,319 Red Diamond
    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; 
    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; 
    / 
    
    Boneist
This discussion has been closed.