Skip to main content

Oracle - VARCHAR2 - Characters or Bytes? What is the maximum length of VARCHAR2?

Today I came across an exception: ORA-01461, which as per Oracle is "can bind a LONG value only for insert into a LONG column".

Bytes or Characters for VARCHAR2?


To specify length, VARCHAR2 data type supports two methods:

1. In bytes: VARCHAR2(10 byte). This will support up to 10 bytes of data, which could be as few as two characters in a multibyte character set.

2. In characters: VARCHAR2(10 char). This will support to up 10 characters of data, which could be as much as 40 bytes of information.

Two issues people encounter frequently, are:
When using multibyte character sets, namely that a VARCHAR2(N) doesn't necessarily hold N characters, but rather N bytes.

The maximum length in bytes of a VARCHAR2 is 4,000.

You must specify a maximum length for a VARCHAR2 column. This maximum length must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes.


For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.

Also Read Setting Maximum Open Cursors In Oracle

Comments

  1. Valuable post. I did also faced this exception but is not aware why it occurs. From your article I got to know the actual reason and how to remove it. Thanks for clearing it so nicely.
    sap project

    ReplyDelete

Post a Comment

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to our feed and get articles like this delivered automatically to your feed reader? Like our Facebook Page.

Popular posts from this blog

HDFC Bank introduces Missed Call Service to know Account Balance

Missed call is a powerful business tool in developing countries like India where customers give a miss call to specific phone numbers for getting account details, providing feedback, voting etc. On receiving a missed call from a registered phone number, the underlying app performs a phone number lookup and sends the data to the caller via text message(SMS) or records the call details for future processing.HDFC Bank recently introduced missed call service for its retail customers which allows to retrieve bank account details, mini statement etc. by simply giving a miss call to their toll free numbers.Following services are now available: 1800 270 3333 - Account Balance 1800 270 3355 - Mini Statement 1800 270 3366 - Request for new Cheque Book 1800 270 3377 - Request for Bank account statement Also you can download HDFC Mobile Banking Application by giving a missed call to : 1800 270 3344. Other banks providing missed call serviceAxis bank(known as Axis Dial) - 09225892258Bank of India…

Induction Cooker Showing an Error Code? Induction Cooker Error Codes Explained

Are you searching for Induction Cook-top error codes? Here you can find the error codes of all popular induction cooktops and how to troubleshoot it.

These are for your reference only, do not try to open your cooktop without proper safety measures, we advise you to call the service person if any servicing is needed.

If you want to know how Induction Cook-top works, read our previous article titled What is Induction Cooker? How Induction Cooker Works?.

Whirlpool Induction Cooker Error CodesError CodeErrorSolutionF0An internal error was detected.Disconnect power. Wait 5 seconds before reconnecting power. If the symbol appears again, call for service.F2The surface cooking area is too hot and has turned off.Remove the pans from the surface cooking area. "F2" will disappear when the surface cooking area has cooled. If you turn the surface cooking area back on and "F2" reappears, the cooktop is still too hot. Turn off the surface cooking area and allow it to cool.F4The po…

LICHFL - Generating Home Loan Statements Online

Generating an online statement from LIC Housing Finance Ltd is very easy, simply follow the below steps to create an online account with LICHFL and generate statements online! You may use the online generated statement as a proof for principal paid for a housing loan(under section 80C) and interest paid(under section 24) while filing income tax returns.* Want to know how to save maximum income tax? Read our most read article how to save maximum income tax (opens in new tab).* Paying high interest to LICHFL? Learn how to reduce interest on your existing home loan (opens in new tab).
Before reading further, make sure you have the following information with you: Your Loan Account NumberSanctioned AmountStep 1 - Open LICHFL websiteVisit LICHFL website and click on the 'New Customers? Click Here' link(refer the below screenshot). Step 2 - Enter your loan account detailsFill in the following details: New Loan NumberSanctioned AmountDate of BirthSecurity Codeand click on the Submit b…