3

My company uses Oracle 19 and we have many thousands of tables which have two columns called CRBY and MODBY in them, which are varchar2(200). These columns get populated by a script called on an INSERT, UPDATE or DELETE trigger. The script records client_identifier information so we can relate individual row changes back to users.

Recently, I began work on enhancing the script as we now have more requirements for session information than what client_identifier alone gives us. The result of this is that the text strings being generated by the script are now in certain cases exceeding 200 characters.

I therefore need a way to alter the varchar(200) in the many tables that have the columns so that they become varchar2(255) instead.

I would like to get some recommendations on what the best approach to this is please? I have read that doing this dynamically can be a bad idea, but given I have 4500 tables to work through, I'm not sure how else I can do it.

Thanks

Improve this question

1 Answer 1

Reset to default
4

Use a query like the following to generate your DDL, then run the output as a script:

select 'alter table ' || table_name || ' modify ( CRBY VARCHAR2(255), MODBY VARCHAR2(255) );' from user_tables;

Modify the select statement or add where conditions as necessary for your situation.

Improve this answer
3
  • I didn't think I could modify a column that had data in it? Commented Aug 10, 2023 at 12:02
  • 2
    You can't shorten it to value which is smaller than the longest value stored in that VARCHAR2 column, but - up to that - yes, you can. If you want to make it larger, no problem. Commented Aug 10, 2023 at 12:56
  • 1
    @SeanGaff Making things in Oracle bigger is easy. Making things smaller - that's hard. Commented Aug 10, 2023 at 13:11

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.