This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 14, 2012 12:38 AM by BillyVerreynne RSS

function compiled with error

ricard888 Newbie
Currently Being Moderated
can someone please assist
 
CREATE OR REPLACE FUNCTION HR.is_leap_year (   date_in IN DATE)
    RETURN NUMBER 
    AS    
    result NUMBER := 0;    
BEGIN
                
        SELECT 
                    CASE WHEN (
                                MOD(EXTRACT(YEAR FROM date_in), 4) = 0 AND MOD(EXTRACT(YEAR FROM  date_in) ,100) != 0)                                
                                    OR MOD(date_in, 400) = 0   THEN 1   ELSE 0  END AS bit
                    INTO result
         FROM DUAL;
          
            RETURN result;
        
END is_leap_year ;
/
  • 1. Re: function compiled with error
    jeneesh Guru
    Currently Being Moderated
    Why cant you at least post the error message?

    Niot tested
    CREATE OR REPLACE FUNCTION HR.is_leap_year (   date_in IN DATE)
        RETURN NUMBER 
        AS    
        result NUMBER := 0;    
    BEGIN
                    
           result :=  CASE WHEN 
                     ( MOD(
                        EXTRACT(YEAR FROM date_in), 
                         4) = 0 
                           AND 
                       MOD(
                        EXTRACT(YEAR FROM  date_in) ,
                         100) != 0
                      )                                
                                  OR 
                      MOD(date_in, 400) = 0   THEN 1   ELSE 0  END;
                RETURN result;
            
    END is_leap_year ;
    /
  • 2. Re: function compiled with error
    Manik Expert
    Currently Being Moderated
    CREATE OR REPLACE FUNCTION HR.is_leap_year (date_in IN DATE)
       RETURN NUMBER AS
       result   NUMBER := 0;
    BEGIN
       SELECT CASE
                 WHEN (MOD (EXTRACT (YEAR FROM date_in), 4) = 0
                       AND MOD (EXTRACT (YEAR FROM date_in), 100) != 0)
                      OR MOD (EXTRACT (YEAR FROM date_in), 400) = 0 THEN    ----    This is corrected.
                    1
                 ELSE
                    0
              END
                 AS bit
         INTO result
         FROM DUAL;
    
       RETURN result;
    END is_leap_year;
    /
    Cheers,
    Manik.

    Edited : schema name in the proc

    Edited by: Manik on Nov 14, 2012 10:17 AM
  • 3. Re: function compiled with error
    ricard888 Newbie
    Currently Being Moderated
    Hi Jeenesh, i would if i got errror message but all i got is "Warning: compiled but with compilation errors"
    and that is why i didn't posted
  • 4. Re: function compiled with error
    sb92075 Guru
    Currently Being Moderated
    ricard888 wrote:
    Hi Jeenesh, i would if i got errror message but all i got is "Warning: compiled but with compilation errors"
    and that is why i didn't posted
    SQL> SHOW ERROR
  • 5. Re: function compiled with error
    ricard888 Newbie
    Currently Being Moderated
    Warning: compiled but with compilation errors
    No errors.

    i am scratching my head.... i am currently using oracle 9 client running on 11g. is that a reason
  • 6. Re: function compiled with error
    Manik Expert
    Currently Being Moderated
    Do check my post, you were actually doing a mod between date and number... I corrected it.

    You might have got

    ORA-00932: inconsistent datatypes: expected NUMBER got DATE

    Cheers,
    Manik.
  • 7. Re: function compiled with error
    ricard888 Newbie
    Currently Being Moderated
    thanks Manik. i didn't see earlier.
  • 8. Re: function compiled with error
    sb92075 Guru
    Currently Being Moderated
    ricard888 wrote:
    Warning: compiled but with compilation errors
    No errors.

    i am scratching my head.... i am currently using oracle 9 client running on 11g. is that a reason
    I doubt client version matters.
    Sometimes SHOW ERROR does not work as desired; then do as below

    SELECT * FROM USER_ERRORS;
  • 9. Error and the solution
    970697 Newbie
    Currently Being Moderated
    Hi,

    Error throw is : <b> [Error] ORA-00932 (10: 45): PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got DATE </b> and is thrown at the MOD() where you are trying to input a date to the function instead of a number. Just try extracting the year and input it to the function
                                        OR MOD( EXTRACT(YEAR FROM date_in) , 400) = 0   THEN 1   ELSE 0  END AS bit
    --  OR MOD( date_in , 400) = 0   THEN 1   ELSE 0  END AS bit -- at line 10  
    Regards
  • 10. Re: function compiled with error
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Manik wrote:
    CREATE OR REPLACE FUNCTION HR.is_leap_year (date_in IN DATE)
    RETURN NUMBER AS
    result   NUMBER := 0;
    BEGIN
    SELECT CASE
    WHEN (MOD (EXTRACT (YEAR FROM date_in), 4) = 0
    AND MOD (EXTRACT (YEAR FROM date_in), 100) != 0)
    OR MOD (EXTRACT (YEAR FROM date_in), 400) = 0 THEN    ----    This is corrected.
    1
    ELSE
    0
    END
    AS bit
    INTO result
    FROM DUAL;
    
    RETURN result;
    END is_leap_year;
    /
    Why use the SQL engine to do the calculation, when your code is inside the PL/SQL engine that is perfectly capable of doing the calculation?
  • 11. Re: function compiled with error
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    ricard888 wrote:
    can someone please assist
    Do not use SQL when SQL is not needed - context switching from the PL/SQL engine to the SQL engine can be expensive.

    Do not code in ugly uppercase. There is NO programming standard in use today that states that reserved words need to be coded in uppercase. Have a look at Java standards, .Net standards, Pascal standards, C/C++ standards.

    The function should look as follows (assuming it is a wise decision to return a number when a boolean or Y/N flag value is expected) - as already shown previously in the thread:
    SQL> create or replace function isLeapYear( d date ) return integer is
      2  begin
      3          return(
      4                  case
      5                          when    ( mod(extract(year from d),4) = 0 and
      6                                    mod(extract(year from d),100) != 0
      7                                  )
      8                                  or
      9                                  mod(extract(year from d),400) = 0 then
     10                                  1
     11                  else
     12                          0
     13                  end
     14          );
     15  end;
     16  /
    
    Function created.
    
    SQL> 
    SQL> select isLeapYear(sysdate) as BIT from dual;
    
           BIT
    ----------
             1
    
    SQL> select isLeapYear(sysdate+300) as BIT from dual;
    
           BIT
    ----------
             0
    
    SQL> 
  • 12. Re: function compiled with error
    ricard888 Newbie
    Currently Being Moderated
    i suppose next step take logic from the function and combined for the requirement. for example below.
    
    (CASE WHEN Month (“Table Date”.”Date”) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
    
    WHEN Month (“Table Date”.”Date”) IN (4, 6, 9, 11) THEN 30
    
    ELSE (CASE WHEN (MOD (YEAR (“Table Date”.”Date”), 4) = 0 AND MOD (YEAR (“Table Date”.”Date”), 100)! = 0) OR (MOD (YEAR (“Table Date”.”Date”), 400) = 0) THEN 29
  • 13. Re: function compiled with error
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    ricard888 wrote:
    i suppose next step take logic from the function and combined for the requirement. for example below.
    
    (CASE WHEN Month (“Table Date”.”Date”) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
    
    WHEN Month (“Table Date”.”Date”) IN (4, 6, 9, 11) THEN 30
    
    ELSE (CASE WHEN (MOD (YEAR (“Table Date”.”Date”), 4) = 0 AND MOD (YEAR (“Table Date”.”Date”), 100)! = 0) OR (MOD (YEAR (“Table Date”.”Date”), 400) = 0) THEN 29
    Are you trying to get the last day of a month? If so, use the LAST_DAY() function.
  • 14. Re: function compiled with error
    stratmo Newbie
    Currently Being Moderated
    Hi Ricard888,

    could you please take a look at the following code
    create or replace function IS_LEAP_YEAR (nYr in number) return boolean is 
    v_day varchar2(2); 
    begin 
      select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual; 
      if v_day = '29' then -- if v_day = 29 then it must be a leap year, return TRUE 
        return TRUE; 
      else 
        return FALSE;  -- otherwise year is not a leap year, return false 
      end if; 
    end;
    This is taken from [url http://www.dba-oracle.com/t_detect_leap_year_function.htm] Burleson Consulting.
    It's another way to implement your function. But I think yours is faster.

    Looking a bit closer at your last posting you may just use the function "last_day" (argument date) and probably rephrase your function. or only use this built in function.


    Regards

    stratmo
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points