Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Unanswered: Request for guide line

    Hello,

    I have created a function in oracle database but it is giving me error... Both codes and its error i am writing down please help me that what should i have to do...


    CODES is::

    CREATE FUNCTION DATEDIFF (
    DATEPART IN VARCHAR2
    , DATE1 IN DATE
    , DATE2 IN DATE
    ) RETURN NUMBER IS
    BEGIN
    if DATEPART = 'Day' Then
    RETURN Date2 Date1;
    End if;
    if DATEPART = 'Week' Then
    Return Trunc((date1 - Date2) / 7);
    End if;
    if DATEPART = 'Month' Then
    RETURN TRUNC((Date1 - DATE2)/30);
    End if;
    END;



    The error is >>>>>>>>>>

    Warning: Function created with compilation errors.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Read & FOLLOW posting guidelines as stated here:
    http://www.dbforums.com/showthread.php?t=1031644
    Use CUT & PASTE for whole SQL*Plus session using 'code tags'
    After getting "Warning: Function created with compilation errors." do:
    SQL> SHOW ERROR --- AND POST RESULTS
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that there's a garbage character within the "Day" IF construct; try to rewrite "Date2 - Date1" part as Oracle objects to the '-' sign.

    Also, show some respect to our tired eyes and format code next time using the [code] tags
    Code:
    SQL> CREATE OR REPLACE FUNCTION Datediff (
      2     datepart   IN   VARCHAR2,
      3     date1      IN   DATE,
      4     date2      IN   DATE
      5  )
      6     RETURN NUMBER
      7  IS
      8  BEGIN
      9     IF datepart = 'Day'
     10     THEN
     11        RETURN date2 - date1;
     12     END IF;
     13
     14     IF datepart = 'Week'
     15     THEN
     16        RETURN TRUNC ((date1 - date2) / 7);
     17     END IF;
     18
     19     IF datepart = 'Month'
     20     THEN
     21        RETURN TRUNC ((date1 - date2) / 30);
     22     END IF;
     23  END;
     24  /
    
    Function created.
    
    SQL> select datediff('Month', sysdate, to_date('05.04.2007', 'dd.mm.yyyy'))
      2
    SQL>
    SQL> select
      2    datediff('Day', sysdate, to_date('05.04.2007', 'dd.mm.yyyy')) r_day,
      3    datediff('Week', sysdate, to_date('05.04.2007', 'dd.mm.yyyy')) r_week,
      4    datediff('Month', sysdate, to_date('05.04.2007', 'dd.mm.yyyy')) r_month
      5  from dual;
    
         R_DAY     R_WEEK    R_MONTH
    ---------- ---------- ----------
    -123.77771         17          4
    
    SQL>

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    While that will repair the error, your code is wrong. Dividing by 30 does NOT give you the number of months. What about months with 28, 29, and 31 days? Use the months_between function.


    select months_between(sysdate,sysdate - 354) from dual;

    MONTHS_BETWEEN(SYSDATE,SYSDATE-354)
    -----------------------------------
    11.6451613
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, the question was how to fix
    Quote Originally Posted by OP
    Warning: Function created with compilation errors
    Nobody said anything about February. So far, that is

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Littlefoot, I wasn't saying your code was wrong. Just that the original code by fezsupper0.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Aug 2007
    Posts
    4

    thanks for reply

    Hello all,
    thanks for reply.. Truly speaking i am really new with DBA.. i got assignment from my school, which is like

    date format and conversion


    What date of the week I was born on?
    in this assignment i have to mention that in what date of the week i was born?? and even i have to tell that How many days old I am?
    and how many years i will be old in 2032 and other thing even i have mention that what is Another Important date in my life ????;


    for those i wrote those kind of [codes] which are given below....
    ================================================== ======
    [codes]
    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++
    column "Jose's weekday of Birth" format a12
    select to_char(to_date('1979 07 18', 'YYYY MM DD'),'Day')
    "WeekDay of Birth"
    from dual;

    DROP FUNCTION DATEDIFF;

    CREATE FUNCTION DATEDIFF (
    DATEPART IN VARCHAR2
    , DATE1 IN DATE
    , DATE2 IN DATE
    ) RETURN NUMBER IS
    BEGIN
    if DATEPART = 'Day' Then
    RETURN Date2 Date1;
    End if;
    if DATEPART = 'Week' Then
    Return Trunc((date1 - Date2) / 7);
    End if;
    if DATEPART = 'Month' Then
    RETURN TRUNC((Date1 - DATE2)/30);
    End if;
    END;



    SELECT DATEDIFF('Day', to_char(sysdate) ,TO_DATE('18-JUL-1979 ', 'DD-MON-YYYY')) "Days old" FROM DUAL;


    SELECT TO_DATE('18-JUL-1979', 'DD-MON-YYYY')+25000 "25K-days-old" FROM DUAL;
    COMMIT;


    select to_char(to_date('1979 07 18', 'YYYY MM DD'),'Day')
    "WeekDay of Mariage"
    from dual;


    ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++


    those all codes i wrote but i got the errors which like Warning: Function created with compilation errors. I dont know where am falling or doing something wrong... please help me and correct my mistakes .... I will be thankful for your kind help...

    waiting for your kind reply

  8. #8
    Join Date
    Aug 2007
    Posts
    4
    hello there,
    thanks for guideline ... i tried your way too but still same error i dont know why please guide me ... i hope you wont mind..

  9. #9
    Join Date
    Aug 2007
    Posts
    4
    Quote Originally Posted by Littlefoot
    It seems that there's a garbage character within the "Day" IF construct; try to rewrite "Date2 - Date1" part as Oracle objects to the '-' sign.

    Also, show some respect to our tired eyes and format code next time using the [code] tags
    Code:
    SQL> CREATE OR REPLACE FUNCTION Datediff (
      2     datepart   IN   VARCHAR2,
      3     date1      IN   DATE,
      4     date2      IN   DATE
      5  )
      6     RETURN NUMBER
      7  IS
      8  BEGIN
      9     IF datepart = 'Day'
     10     THEN
     11        RETURN date2 - date1;
     12     END IF;
     13
     14     IF datepart = 'Week'
     15     THEN
     16        RETURN TRUNC ((date1 - date2) / 7);
     17     END IF;
     18
     19     IF datepart = 'Month'
     20     THEN
     21        RETURN TRUNC ((date1 - date2) / 30);
     22     END IF;
     23  END;
     24  /
    
    Function created.
    
    SQL> select datediff('Month', sysdate, to_date('05.04.2007', 'dd.mm.yyyy'))
      2
    SQL>
    SQL> select
      2    datediff('Day', sysdate, to_date('05.04.2007', 'dd.mm.yyyy')) r_day,
      3    datediff('Week', sysdate, to_date('05.04.2007', 'dd.mm.yyyy')) r_week,
      4    datediff('Month', sysdate, to_date('05.04.2007', 'dd.mm.yyyy')) r_month
      5  from dual;
    
         R_DAY     R_WEEK    R_MONTH
    ---------- ---------- ----------
    -123.77771         17          4
    
    SQL>
    ================================================== ======


    i tried with these codes but still i get same error i dont know why ???? and even i dont know what is wrong with it??? please help me... Waiting for positive reply..

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    When you are going to recreate a stored procedure, it is easier to use CREATE OR REPLACE instead of two separate DROP and CREATE statements. Not that it makes a big difference (the result is the same), but you save your nerves.

    What might be wrong with your code? I don't know. As you can see, the same code works on my 10g database (but I'm sure it would work on much older versions too). So I believe this isn't a reason.

    Anacedent already told you what to do: as soon as you see a warning message, issue SHOW ERRORS at the SQL*Plus prompt and you'll see the feedback (what was wrong and in what line(s)). Then you'll know what to do to fix those errors.

    Perhaps you should do that using baby-steps: don't calculate all at once, but one at the time. That way it would be easier to see what went wrong, why, and you won't do that again.

    OK, you are new at it. But it has nothing to do with DBA business.

    First and last query you wrote (birthday and wedding) seem to be OK; but, if you are going to format columns, make sure you spell their names correctly (you didn't do that).

    DATEDIFF function: fix it first, then it will return some value. You used "to_char(sysdate)" which doesn't make sense. IN parameters are DATES, so why are you converting a date (returned by the SYSDATE function) into a character?

    I didn't understand the "25 kdays" query; also, what are you committing? There's nothing to commit here.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    When you are going to recreate a stored procedure, it is easier to use CREATE OR REPLACE instead of two separate DROP and CREATE statements. Not that it makes a big difference (the result is the same), but you save your nerves.
    There is a small (but important) difference:
    With DROP & CREATE you lose all grants on the procedure. So you will have to issue any needed GRANT as well.
    With CREATE OR REPLACE the grants are preserved.

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    True. Thank you.

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    And as I noted before, if you want your function to be correct for the months, write it as follows.

    Code:
    CREATE OR REPLACE FUNCTION Datediff (datepart IN VARCHAR2,
                                         date1    IN   DATE,
                                         date2    IN   DATE)
    RETURN NUMBER IS
    BEGIN
      IF upper(datepart) = 'DAY' THEN
         RETURN TRUNC(date2 - date1);
      ELSIF UPPER(datepart) = 'WEEK' THEN
         RETURN TRUNC((date1 - date2) / 7);
      ELSIF UPPER(datepart) = 'MONTH' THEN
         RETURN TRUNC(MONTHS_BETWEEN(date1,date2));
      ELSE
         RETURN NULL;
      END IF;
      END;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •