Increasing the size of Varchar2() in oracle

Question

Is it possible to increment the size of a column (say varchar2(25)) by 50? To be precise, I am not looking for something like this:

ALTER TABLE <Table_name> modify <Column_name> varchar2(75);

Rather, I am inquisitive about something that will increase the size by 50 or some other integer constant without the explicit calculation on the programmer part.

PS: Please comment if I am not clear.

Answer 1

Just to be clear, it appears you're asking for a way to add a fixed value to the column size without knowing what the original size is (hence asking how to add 50 and disallowing setting it directly to 75, which would require knowing it was 25 to start with).

Most databases provide system tables or views which give you the metadata about various objects. For example, DB2/z has sysibm.syscolumns and Oracle has all_tab_columns as shown in this link.

If you wanted to expand the column by 50 without knowing in advance what the size was, you could simply consult the metadata to get the current size and just add 50, constructing a statement to do it for you.

In other words, use something like:

select char_length from all_tab_columns
    where owner = '<Table_owner>'
    and table_name = '<Table_name>'
    and column_name = '<Column_name>'

then extract that number from the recordset, add 50, and use that to dynamically construct and execute an alter table statement, similar to the one in your question that assumes you already know the length you want.

You can also use the user_tab_columns view if you're only concerned with your own tables rather than all those you can see. In that case, you don't need to concern yourself with the where owner = clause.

---

Although this sample code is specific to the DB2/z metadata, it wouldn't take much to convert it to the corresponding Oracle version:

import java.io.*;
import java.util.*;
import java.sql.*;

class chgcolsz {
    public void chgcolsz() {}

    public static void main (String args[]) {
        Connection conn;
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
            conn = DriverManager.getConnection(
                "jdbc:db2://MyBox:9999/MyInstance", "Me", "MyPassword");
            conn.setAutoCommit (true);
        } catch (Exception e) {
            System.out.println ("** Error: DB connect: " + e);
            e.printStackTrace();
            return;
        }

        String cmd =
            "select length from sysibm.syscolumns" +
            " where tbcreator = 'PAX'" +
            " and tbname = 'XYZZY'" +
            " and name = 'COLUMN1'";
        ResultSet rs;
        try {
            Statement sttmnt = conn.createStatement();
            rs = sttmnt.executeQuery (cmd);
        } catch (Exception e) {
            rs = null;
            System.out.println ("** Warning: rowset create: '" +
                cmd + "': " + e);
            e.printStackTrace();
        }

        int sz = -1;
        if (rs != null) {
            try {
                rs.next();
                sz = rs.getInt(1);
                rs.close();
            } catch (Exception e) {
                System.out.println ("** Warning: rowset close: " + e);
                e.printStackTrace();
            };
        }
        if (sz != -1) {
            System.out.println ("Current size is " + sz);
            cmd = "alter table pax.xyzzy" +
                " alter column column1" +
                " set data type" +
                " varchar(" + (sz + 50) + ")";
            System.out.println ("Executing: " + cmd);
            try {
                Statement sttmnt = conn.createStatement();
                sttmnt.execute (cmd);
            } catch (Exception e) {
                System.out.println ("** Warning: table alter: '" +
                    cmd + "': " + e);
                e.printStackTrace();
            }
        }

        try {
            conn.close();
        } catch (Exception e) {
            System.out.println ("** Warning: DB close: " + e);
            e.printStackTrace();
        };
    }
}

You can see from subsequent runs of this program that it's increasing the column width by 50 each time:

pax> java chgcolsz
Current size is 50
Executing: alter table pax.xyzzy alter column column1 set data type varchar(100)

pax> java chgcolsz
Current size is 100
Executing: alter table pax.xyzzy alter column column1 set data type varchar(150)

pax> java chgcolsz
Current size is 150
Executing: alter table pax.xyzzy alter column column1 set data type varchar(200)