I have a table with column "foo" that its datatype is varchar2(100).
In this table I've got a row with some information in "foo" column. Let's say it is string "bar".
Then I have a procedure that makes copy of row, changes the data and insert it to the table. One of changes is to add string "xyz, " in "foo" column at the beginning and concatenate the rest. So when I run this procedure it makes row that has string "xyz, bar" in "foo" column. The next time I run this procedure I would get string "abc, xyz, bar" in "foo" column, etc.
To not run into an error I write my code like that:
Insert into table (
foo
) values (
substr(variable_with_additional_string || variable_with_string_from_foo_column, 1, 100)
);
I always cut the last part if a string is too long.
But what if someone alters table and changes the datatype of column "foo" to varchar2(80)? My procedure will not work anymore.
So my question is: How to not hardcode number 100 in substr() function?
My only idea is to
SELECT DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'table'
AND COLUMN_NAME = 'foo';
and find number using regular expressions or other functions but I do not think that is the optimal way so solve this issue.
You can use a simple select query to get a data length of your column, for example:
SELECT DATA_LENGTH
INTO v_max_length
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'table'
AND COLUMN_NAME = 'foo'
AND OWNER= 'your_schema_name'-- thanks for a good point made by MT0 in comment section.
And then use that value in your insert's substr function:
Insert into table
(
foo
)
values
(
substr(variable_with_additional_string || variable_with_string_from_foo_column, 1, v_max_length)
);