CODEWITHSUNDEEP
sqldatabaseoracle-database
westman379
westman379

Reputation: 713

Extend the length of the column in table - what can it break in the database?

I have executed such code in Oracle database:

ALTER TABLE TEST_TABLE MODIFY (example_column VARCHAR2(300));

And I want to ask you if extending column length from for example 30 to 300 can be dangerous when it comes to databse stability? After executing this statement I had to recompile all invalid objects like views and packages that were using that table and I'm just wondering if this can break something else like maybe variables and functions in Oracle forms and Oracle reports? Or maybe some other objects in the database? Are there any good practices for administrators to perform after such change?

Upvotes: 2

Views: 2111

Answers (3)

Jon Heller
Jon Heller

Reputation: 36808

Increasing the column length could fail because it would make an index too large. It's a rare problem though - I've only ever seen this happen once in the wild.

SQL> create table test_table(a varchar2(4000), b varchar2(2300), example_column varchar2(30));

Table created.

SQL> create index test_table_idx on test_table(a, b, example_column);

Index created.

SQL> alter table test_table modify (example_column varchar2(300));
alter table test_table modify (example_column varchar2(300))
*
ERROR at line 1:
ORA-01404: ALTER COLUMN will make an index too large

This should be an easy problem to catch immediately, unless your database environments do not match. If your lower environments have different indexes, byte length semantics, or block sizes, you may not see this error until you run in production.

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

One thing which comes immediately to mind is the possible effects on PL/SQL code which is written using hard-coded length values on character variables.

Let's say you have the following code:

CREATE TABLE SOME_TABLE
  (SOME_FIELD    VARCHAR2(30),
   WHATEVAH      NUMBER);

CREATE OR REPLACE PROCEDURE SOME_PROC(pinWhatevah IN NUMBER)
IS
  strSome_field  VARCHAR2(30);
BEGIN
  SELECT SOME_FIELD
    INTO strSome_field
    FROM SOME_TABLE
    WHERE whatevah = pinWhatevah;

  DBMS_OUTPUT.PUT_LINE('pinWhatevah=' || pinWhatevah ||
                         '  SOME_FIELD=' || '''' || strSome_field || '''');
END SOME_PROC;

Here someone has hard-coded the length of strSome_field instead of using SOME_TABLE.SOME_FIELD%TYPE. This can cause problems.

Now, if you execute

ALTER TABLE SOME_TABLE
  MODIFY (SOME_FIELD  VARCHAR2(300));

and add rows where SOME_FIELD contains a value longer than 30 characters, your procedure will fail with a CHARACTER STRING BUFFER TOO SMALL error.

dbfiddle here

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231651

The most likely source of problems will be storing data in non-anchored types. If all your PL/SQL code declares anchored types

declare
  l_example_column_val test_table%example_column.type;
  ...

rather than

declare
  l_example_column_val varchar2(30);
  ...

then when your code gets recompiled the local variables will implicitly change to allow 300 bytes of data. If your local variables use explicit varchar2(30) data types, as soon as your code tries to populate that variable with a newly allowed longer value, you'll get errors.

The next most likely source of problems will come for display and validation issues. A front end probably wants a different control for a field that can only be 30 characters. For 30 characters, a simple one-line text field is probably sufficient. For 300 characters, you probably want a text area since you aren't going to be able to (easily) display 300 characters on a single line. Reports may need to be formatted differently if you're allowing 300 characters of data-- you probably don't want that all on a single line of a report, you'd want it to wrap. And front-end validations also likely have to change to ensure that a 300 character string is now valid.

Upvotes: 5

Related Questions