Menu
I have a simple need to increase the size of a VARCHAR2 column in an Oracle DB from VARCHAR2(20 CHAR) to VARCHAR2(35 CHAR).
The database is used by an existing application with production data though I will start with DEV first (of course).
597
Oracle allows you to perform many actions but the following are the main ones: Modify the column's visibility. Allow or not allow null values. Shorten or widen the size of the column.
The maximum length for VARCHAR2 is 32672 BYTE or 8168 CHAR which is the same as the maximum length for VARCHAR of 32672 OCTETS or 8168 CODEUNITS32. Similarly, when the NVARCHAR2 data type is explicitly encountered in SQL statements, it is implicitly mapped following the same rules as the NVARCHAR data type.
TO_CHAR (number) converts n to a value of VARCHAR2 datatype, using the optional number format fmt . The value n can be of type NUMBER , BINARY_FLOAT , or BINARY_DOUBLE . If you omit fmt , then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
Oracle VARCHAR2 max length Since Oracle Database 12c, you can specify the maximum size of 32767 for the VARCHAR2 data type. Oracle uses the MAX_STRING_SIZE parameter for controlling the maximum size. If the MAX_STRING_SIZE is STANDARD , then the maximum size for VARCHAR2 is 4000 bytes.
You want to use the Liquibase refactoring "modifyDataType", which is documented here: http://www.liquibase.org/documentation/changes/modify_data_type.html
Here is an example:
<changeSet author="liquibase-docs" id="modifyDataType-example">
<modifyDataType
columnName="CODE"
newDataType="varchar2(35 char)"
tableName="PCF_PROGRAMME"/>
</changeSet>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
