1 2 Previous Next 15 Replies Latest reply: Nov 14, 2012 2:38 AM by Billy~Verreynne RSS

    function compiled with error

    ricard888
      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
          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
            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
              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
                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
                  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
                    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
                      thanks Manik. i didn't see earlier.
                      • 8. Re: function compiled with error
                        sb92075
                        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
                          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
                            Billy~Verreynne
                            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
                              Billy~Verreynne
                              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
                                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
                                  Billy~Verreynne
                                  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
                                    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