Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    13

    Lightbulb Unanswered: Create constraint on checking column width

    We have a problem with a vendor application that creates a lots of issues. The vendor's software is very buggy. When you enter data into a character column that is longer than the width of the column, it accepts the data but then creates other problems that are a lot worse.

    I would like to create a constraint on a character field in a table that will check the column width of that particular column and if the entered value is greater than the column width, I would like the constraint to raise an error.

    Problem is, I don't have access to the vendor's code, so I don't know if the application would even recognize the error that I would create, but if this could be done, I think it would be worth a test. Or if you have any better ideas on implementing something like this that would be great.


    I don't know if this should be a constraint or a trigger or something totattly different.

  2. #2
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    1. If you don't have access to vendor code, how do you intend to modify to throw error?

    2. What is the database? Oracle does throw error when data length is more than col width.

    PHP Code:
    SQLdesc a
     Name                                      Null
    ?    Type
     
    ----------------------------------------- -------- ----------------------------
     
    COL1                                               VARCHAR2(10)

    SQLinsert into a values ('12345678901');
    insert into a values ('12345678901')
                          *
    ERROR at line 1:
    ORA-01401inserted value too large for column


    SQL
    drop table a
      2  
    ;

    Table dropped.

    SQLcreate table a (col1 char(3));

    Table created.

    SQLinsert into a values ('1234');
    insert into a values ('1234')
                          *
    ERROR at line 1:
    ORA-01401inserted value too large for column 
    If software is accepting more data and what you see in database is truncated data, this truncation is probably done by the software.
    Oracle can do wonders !

  3. #3
    Join Date
    Dec 2003
    Posts
    13
    Thank you! I wasn't sure if the appplication would reconize the error. I was just testing this concept. In fact, there is already the error message you have listed so it is a mute point now.

    By the way, the application ignores the error.

    Thanks for your help!

    Originally posted by cmasharma
    1. If you don't have access to vendor code, how do you intend to modify to throw error?

    2. What is the database? Oracle does throw error when data length is more than col width.

    PHP Code:
    SQLdesc a
     Name                                      Null
    ?    Type
     
    ----------------------------------------- -------- ----------------------------
     
    COL1                                               VARCHAR2(10)

    SQLinsert into a values ('12345678901');
    insert into a values ('12345678901')
                          *
    ERROR at line 1:
    ORA-01401inserted value too large for column


    SQL
    drop table a
      2  
    ;

    Table dropped.

    SQLcreate table a (col1 char(3));

    Table created.

    SQLinsert into a values ('1234');
    insert into a values ('1234')
                          *
    ERROR at line 1:
    ORA-01401inserted value too large for column 
    If software is accepting more data and what you see in database is truncated data, this truncation is probably done by the software.

Posting Permissions

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