Results 1 to 3 of 3

Thread: Ora-01401

  1. #1
    Join Date
    Jun 2007
    Posts
    17

    Unanswered: Ora-01401

    This code is a part of my procedure:
    CREATE OR REPLACE PROCEDURE PROC_CONFIG_REQ(.........)
    as
    id number;
    NOW VARCHAR2(30);
    begin

    SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI: SS') INTO NOW FROM DUAL;
    select config_req_seq.nextval

    into request_id
    from dual;



    insert into CONFIG_REQUEST(
    RELEASE_NAME , PRODUCT_NAME , SUB_CATEGORY ,

    DEVELOPER_NAME , EMAIL_ID , SRC_CODE_DEPENDENCE ,

    DESCRIPTION , STATUS , PRIMARY_ASSIGNEE ,

    REASSIGN_TO , CCRID , STATUS_UPDATE_TIME

    )
    values( release_name , product_name , sub_category ,

    developer_name , email_id , src_code_dependence ,

    description , status , primary_assignee ,

    primary_assignee , request_id , NOW

    );


    end;

    It compiles fine but on executing gives the error
    ORA-01401: inserted value too large for column

    The length of status_update_time in my table is also varchar2(30)

    What could be going wrong?

    -Sajita

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What could be going wrong?
    ORA-01401: inserted value too large for column
    You don't provide enough details to conclude exactly what's wrong
    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
    Aug 2004
    Location
    France
    Posts
    754
    How are you sure the culprit is status_update_time ?

    BTW you are doing things wrong with storing a date/time as a string : NEVER EVER DO SO ! This is NOT an option. The only option is : which date/time type will you use for your column ? DATE if a precision to the second is sufficient, TIMESTAMP if it's not the case, and consider TIMESTAMP WITH TIMEZONE if you want to store date and times for different timezones in this column. You may also consider INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH datatypes if you want to store durations, intervals between two dates, not dates.

    Drawbacks of storing dates as strings include : less control over what is inserted, more space used to store data (a DATE field takes only 7 bytes, TIMESTAMP 11 bytes, not sure about TIMESTAMP WITH TIMEZONE but I think it is 14 bytes), the string format chosen to store data is locale-dependent (ie date formats are different from country to country), and queries are bound to be less efficient, especially for range scans (even if some formats, which does NOT include yours may still be efficient).

    Really, always store data for what they are : numbers as numbers, strings as strings, dates as dates... and you will easily avoid many problems.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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