CODEWITHSUNDEEP
sqloracle-databaseplsqldeveloper
and_apo
and_apo

Reputation: 1297

Impact of editing field length of a varchar2 column oracle

I am to edit the length of a varchar2 column in an Oracle table(from 50 to 200).

I am checking the following Oracle views to detect possible impact amongst underlying packages/tables that reference this specific column:

I 've also thought of checking some possible Oracle jobs that might use that field but I didn't find anything of relevance.

As I am new to Oracle do you think checking the above two views should suffice or am I missing something?

Upvotes: 1

Views: 458

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can check the DBA_DEPENDENCIES view as well:

DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.

Although it doesn't say so there, oddly, it includes package and trigger dependencies on tables, so if you query that with referenced_name = '<your table>' you'll see anything that would be invalidated by the table change. Some of those won't matter - if you have a procedure with an argument with a %type dependency it will be invalidated but will work the same after compilation.

But you could have your own variables defined as varchar2(50) instead of table.column%type, and those will be harder to find. The objects listed in that view will be a good starting point, but you might still need to check the source for them manually to look for issues like that, rather than waiting for a test case to fail.

Upvotes: 2

Related Questions