This discussion is archived
9 Replies Latest reply: Jul 29, 2013 10:56 AM by 800849 RSS

Help in data type

800849 Newbie
Currently Being Moderated

Hi,

 

I have a table and one of the column is of varchar2(4000). When input data are submitted by the user from the UI then the input data are getting inserted into the table and based on the input we are fetching the data and collecting the data and send it through mail to the user.

 

Issue -> So when its inserting the data more than 4000 then its not showing any request_id to the user due to size of the column.

 

How can we avoid this without the use of CLOB data type.

 

Note: When we are using the datatype as CLOB then the fetching of data is not happening due to some issue.

 

so please help me or any other way can we handle this.

 

Regards

Das

  • 1. Re: Help in data type
    EdStevens Guru
    Currently Being Moderated

    800849 wrote:

     

    Hi,

     

    I have a table and one of the column is of varchar2(4000). When input data are submitted by the user from the UI then the input data are getting inserted into the table and based on the input we are fetching the data and collecting the data and send it through mail to the user.

     

    Issue -> So when its inserting the data more than 4000 then its not showing any request_id to the user due to size of the column.

     

    How can we avoid this without the use of CLOB data type.

     

    Note: When we are using the datatype as CLOB then the fetching of data is not happening due to some issue.

     

    so please help me or any other way can we handle this.

     

    Regards

    Das

    if the column is defined as varchar2(4000), then your statement " its inserting the data more than 4000"  cannot be true.

     

    As for the rest of your problem, you seem to be describing a problem that exists with your un-named and un-described method of "fetching the data and collecting the data and send it through mail to the user."

  • 2. Re: Help in data type
    michaelrozar17 Pro
    Currently Being Moderated

    If you business requirement says that the max column limit to be inserted must be of 4000 bytes then trim out the extra characters using function SUBSTR. If not you may have to use CLOB data type with the "some issue" fixed.

  • 3. Re: Help in data type
    800849 Newbie
    Currently Being Moderated

    Hi,

     

    Thanks for the info.

     

    But we cannot trim it the extra character as this is biz requirement.

     

    Regards

    Das

  • 4. Re: Help in data type
    rp0428 Guru
    Currently Being Moderated

    800849 wrote:

     

    Hi,

     

    Thanks for the info.

     

    But we cannot trim it the extra character as this is biz requirement.

     

    Regards

    Das

    Then you need to change the 'biz requirement'.

     

    A requirement of 'must allow more than 4000 bytes in a VARCHAR2(4000) column' is invalid.

     

    And remember - the limit of 4000 is BYTES, not characters. If you are using a multi-byte character set your limit could be 1000 'characters'.

  • 5. Re: Help in data type
    Oracle Maniac Explorer
    Currently Being Moderated

    I don't this there is any alternative to this . IN sql , the max size that can be fitted into a table without using lobs is 4000 BYTES. And the fetch error you are getting could  be due to the fact that we cannot fetch clob data over database links. Its apparent that you are fetching your data over a dblink.

     

    The only option you have is to make the column of clob datatype .You may get a number of workaround for this while fetching  the data .

     

     

    Thanks

  • 6. Re: Help in data type
    9876564 Newbie
    Currently Being Moderated

    Cant we use "LONG"  there ......Please pardon me if i am breaking the discussion ?

  • 7. Re: Help in data type
    Oracle Maniac Explorer
    Currently Being Moderated

    You are always welcome to  join the discussion .

     

    Long is a deprecated data type and a number of restrictions are associated with it .So as a new design strategy , I would suggest to avoid long data types.

  • 8. Re: Help in data type
    800849 Newbie
    Currently Being Moderated

    Hi,

     

    Let me try and we will update you once fixed.

     

    Regards

    Das

  • 9. Re: Help in data type
    800849 Newbie
    Currently Being Moderated

    Yes you are right.We should avoid use of Long data type.

     

    Regards

    Das

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points