How to minimize the relationship / dependency between physical DDL changes and PL / SQL changes?

Question

We encountered a specific problem with our Oracle table definition (DDL) and in one of our PL / SQL scripts.

The problem is that there were changes in the table that changed from varchar(20)to varchar(30), however, this change was not diligently reflected in one of our PL / SQL scripts, consuming data that was still there varchar(20), causing an error ORA-06502: PL/SQL: numeric or value errorduring one of our regression tests.

I would like to get advice from Oracle experts and databases here, whether you have encountered such scenarios in the past, as a result of which changes were made to the DDL table and were not reflected in PL / SQL and how you deal with this gap,

I know that one simple way could be some form of enforcement or paperwork, but could there be a more beautiful or elegant solution, that is, the way foreign keys work to avoid inserting / updating / deleting an anomaly?

With thanks!

Answer 1

First, you should always declare your variables as TYPE based on the column definitions in your tables:

Ie, not:

dept_name  VARCHAR2(50);

Using:

dept_name  dept.dept_name%TYPE;

That way, when your base table changes, your ad remains valid.

You can also declare your procedural parameters as types:

PROCEDURE proc(p1 IN dept.dept_name%TYPE)