Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    49

    Unanswered: When a date is null?

    Hi everybody,
    i have to insert data with a stored procedure in a table some fields of which are DATE type. it might happen i have no value to insert in the DATE field, but trying to insert a NULL value into the DATE field i get an error...
    how can i set to NULL a DATE field???

    One more question: i have to catch an exception if the format of DATE i'm trying to put into the table is not correct. for instance if i try to put 23545/jd the insert fails and i want to catch the exception. what kind of exception i have to look for?

    Thank you,

    Matte
    Last edited by matte2111; 08-06-03 at 06:11.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can insert null providing the table doesnt have a not null constraint on the date column.

    As for the exception just put in an 'exception when others then' and look at the values of sqlcode & sqlerrm when you use an incorrect format.

    Alan

  3. #3
    Join Date
    Aug 2003
    Posts
    49

    thank you, but...

    Thank you very much Alan,
    the thing is i would like to get an unique exception when the date format is not correct. If i look for the sqlcode, i get different error codes depending on the kind of format error (one if the year is wrong, anther if the month is out of bounds, etc...); that means i have to control a lot of different condition.
    doesn't exis a kind of "date_error" just like there is "value_error" or "no_data_found"?

    Matte

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: thank you, but...

    Originally posted by matte2111
    Thank you very much Alan,
    the thing is i would like to get an unique exception when the date format is not correct. If i look for the sqlcode, i get different error codes depending on the kind of format error (one if the year is wrong, anther if the month is out of bounds, etc...); that means i have to control a lot of different condition.
    doesn't exis a kind of "date_error" just like there is "value_error" or "no_data_found"?

    Matte
    Why not either (a) just let the Oracle error occur unhandled, or (b) use WHEN OTHERS and then raise an error using SQLERRM? Trying to explicitly handle every error that might ever occur is a fruitless task, and makes your programs horribly unwieldy. For example you could do:

    Code:
    SQL> begin
      2     insert into emp(empno,hiredate) values (111,'99');
      3  exception
      4     when DUP_VAL_ON_INDEX then
      5        raise_application_error( -20001, 'Employee ID already exists' );
      6     when others then
      7        raise_application_error( -20001, 'Oracle error: '||SQLERRM);
      8  end;
      9  /
    begin
    *
    ERROR at line 1:
    ORA-20001: Oracle error: ORA-01847: day of month must be between 1 and last day
    of month
    ORA-06512: at line 7
    Of course, your code might be logging the error to a file or whatever you do. The important thing is that you must raise an exception of some kind, not allow code to carry on oblivious of the error.

  5. #5
    Join Date
    Aug 2003
    Posts
    49

    Re: thank you, but...

    Originally posted by andrewst
    Why not either (a) just let the Oracle error occur unhandled, or (b) use WHEN OTHERS and then raise an error using SQLERRM? Trying to explicitly handle every error that might ever occur is a fruitless task, and makes your programs horribly unwieldy. For example you could do:

    Code:
    SQL> begin
      2     insert into emp(empno,hiredate) values (111,'99');
      3  exception
      4     when DUP_VAL_ON_INDEX then
      5        raise_application_error( -20001, 'Employee ID already exists' );
      6     when others then
      7        raise_application_error( -20001, 'Oracle error: '||SQLERRM);
      8  end;
      9  /
    begin
    *
    ERROR at line 1:
    ORA-20001: Oracle error: ORA-01847: day of month must be between 1 and last day
    of month
    ORA-06512: at line 7
    Of course, your code might be logging the error to a file or whatever you do. The important thing is that you must raise an exception of some kind, not allow code to carry on oblivious of the error.

    The thing is i have to log into another table (in a column error_type) the reason why my insert failed: it might be either because the date is not present (error_type=1) or because the format is wrong (error_type=2) or because nodoby knows (error_type=3). that's why, when i try my insert i'd like to catch the generic exception of "DATE FORMAT ERROR" or something like that.
    Using WHEN OTHERS i can't distinguish between error_type=2 and error_type=3.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: thank you, but...

    Originally posted by matte2111
    The thing is i have to log into another table (in a column error_type) the reason why my insert failed: it might be either because the date is not present (error_type=1) or because the format is wrong (error_type=2) or because nodoby knows (error_type=3). that's why, when i try my insert i'd like to catch the generic exception of "DATE FORMAT ERROR" or something like that.
    Using WHEN OTHERS i can't distinguish between error_type=2 and error_type=3.
    I see. Well there are a lot of date errors, but I believe they are all between -1800 and -1899. You could therefore trap them like this:

    EXCEPTION
    ...
    WHEN OTHERS THEN
    log_error( SQLCODE );
    END;

    In the procedure log_error you could search for specific errors like this:

    PROCEDURE log_error( p_sql_code IN NUMBER )
    IS
    ...
    BEGIN
    IF p_sql_code IN (-1800, -1801, -1847, -1892, ... ) THEN
    error_type := 2;
    ELSE
    error_type := 3;
    END IF;
    ...
    END;

  7. #7
    Join Date
    Aug 2003
    Posts
    49

    Re: thank you, but...

    Originally posted by andrewst
    I see. Well there are a lot of date errors, but I believe they are all between -1800 and -1899. You could therefore trap them like this:

    EXCEPTION
    ...
    WHEN OTHERS THEN
    log_error( SQLCODE );
    END;

    In the procedure log_error you could search for specific errors like this:

    PROCEDURE log_error( p_sql_code IN NUMBER )
    IS
    ...
    BEGIN
    IF p_sql_code IN (-1800, -1801, -1847, -1892, ... ) THEN
    error_type := 2;
    ELSE
    error_type := 3;
    END IF;
    ...
    END;

    Yes, this is the way i was using, but i hoped there was a more "comfortable" way.
    Please, do you know where i can find a list of SQL_CODE to check which ones are referred to DATE format?

    Thankx a lot!

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: thank you, but...

    Originally posted by matte2111
    Yes, this is the way i was using, but i hoped there was a more "comfortable" way.
    Please, do you know where i can find a list of SQL_CODE to check which ones are referred to DATE format?

    Thankx a lot!
    The Error Messages manual:

    http://technet.oracle.com/docs/produ...a96525/toc.htm

    If you look at the ORA-1500 to ORA-02099 section, you will see that all errors in range 01800-01899 are date related, whereas 01799 is about outer joins and 01900 is about LOGFILE. However, I can't swear that there aren't other date-related errors elsewhere (but I don't think there are). Still, if for all other errors you log error_type as 3 (unknown) and record the ORA number somewhere, you can always review later and add further codes to your error_type 2 list.

    If you do come across a simpler solution, please let us know!

Posting Permissions

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