We have a problem with a long varchar-field: Although we expected it to be (almost: 2Gb) unlimited in size, we get a "Size too large" on the field - even for smaller texts. Is there a default length for fields of type LONG, and if so, how can we change that? We are using Oracle 8.1.7, and the error appears on both a Win2k and a Solaris box. We get the error in both constellations: When using SQL-Plus and when access the DB using a type-4-jdbc-driver.
What are you trying to do when you get the error? A LONG datatype can hold up to 2GB of variable-length character data and can be used as a column in a SELECT list, the SET clause of an UPDATE statement and the VALUES clause of an INSERT statement. There is no minimum, it simply holds whatever you put into it up to the 2GB limit. A problem it could possibly have is if you are attempting to put non-character data into the column, i.e. some kind of control or binary character. If that is the case you need to look at using LONG RAW.
Actually, when using 8.1.7, Oracle recommends that you use CLOB and BLOB datatypes instead of LONG and LONG RAW, as they are the newer, more robust datatypes supporting large objects. Oracle has deprecated the use of the LONG datatype, meaning that eventually the LONG datatype will be going away. I'd opt to use the CLOB/BLOB datatypes now especially if you're having trouble with using LONG. CLOB/BLOB have many less restrictions and will actually hold up to 4GB of data.
For More Information
- What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.