Why does my code break with no changes and no invalid objects?


Recently I encountered an interesting situation where a job has been running successfully for months and then one day it suddenly started failing every time with an ORA-06502: PL/SQL: numeric or value error.  The same error can also manifest itself in bulk operations with ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind.

The more confusing part is that no changes were made to the code and there were no invalid objects.  Digging into the problem some more, the job called a procedure which read from a view, selecting from remote tables via a database link.

So, next step, obviously, I check on the remote side: but again, I find no invalid objects there and looking at the LAST_DDL_TIME in DBA_OBJECTS I see none of the tables had been modified in a couple months either.

Those old remote changes seemed innocuous; but it had been even longer since I had changed anything in the view or procedure within the local db.  I recompiled the view in the local db and the job started working again.  So, what changed and why did it only fail now and not a couple months ago when the remote tables changed?

For this I had to go to SYS and query DBA_TAB_COLS with flashback query to check the columns of my view and sure enough, one of the VARCHAR2 columns had grown.  The reason nothing failed was no new data came in right away using the new, bigger limit.

Once I saw the problem, replicating it was fairly easy.  Another interesting quirk between SQL and PL/SQL is the query within the procedure would run without error when executed as SQL because it didn’t have precompiled size limitations.  So it would adapt on its own.

The example objects and data to replicate the problem are fairly simple but it can be a little tricky following along since the session alternates between the local and remote database.  I’ve left the connect statements and altered the SQL> prompt to include the database name to help highlight where the action is taking place.

sds@REMOTE_DB> connect sds/pa55w0rd@remote_db
Connected.
sds@REMOTE_DB> create table sds.testtable(test_id integer, testtext varchar2(10));

Table created.

sds@REMOTE_DB> insert into sds.testtable(test_id,testtext) values (1,'abcd');

1 row created.

sds@REMOTE_DB> insert into sds.testtable(test_id,testtext) values (2,'efgh');

1 row created.

sds@REMOTE_DB> commit;

Commit complete.

sds@REMOTE_DB> connect sds/pa55w0rd@local_db
Connected.
sds@LOCAL_DB> CREATE DATABASE LINK remote_db_link CONNECT TO sds IDENTIFIED BY "pa55w0rd" USING 'remote_db';

Database link created.

sds@LOCAL_DB> CREATE OR REPLACE VIEW test_view
AS
SELECT * FROM sds.testtable@remote_db_link; 2 3

View created.

sds@LOCAL_DB> SELECT * FROM test_view;

TEST_ID TESTTEXT
---------- ----------
1 abcd
2 efgh

sds@LOCAL_DB> CREATE OR REPLACE PROCEDURE show_remote_data
IS
BEGIN
FOR x IN ( SELECT test_id, testtext
FROM test_view
ORDER BY test_id)
LOOP
DBMS_OUTPUT.put_line(x.test_id || ' ' || x.testtext);
END LOOP;
END; 2 3 4 5 6 7 8 9 10
11 /

Procedure created.

sds@LOCAL_DB> set serveroutput on
sds@LOCAL_DB> exec show_remote_data;
1 abcd
2 efgh

PL/SQL procedure successfully completed.

Up to here everything is working normally, now we’ll make the text column larger.
The procedure will still work correctly even though the local and remote sizes don’t match. The error doesn’t occur until new data shows up that exceeds the prior limit.

sds@LOCAL_DB> connect sds/pa55w0rd@remote_db
Connected.
sds@REMOTE_DB> alter table sds.testtable modify (testtext varchar2(30));

Table altered.

sds@REMOTE_DB> connect sds/pa55w0rd@local_db
Connected.
sds@LOCAL_DB> exec show_remote_data;

PL/SQL procedure successfully completed.

sds@LOCAL_DB> set serveroutput on
sds@LOCAL_DB> exec show_remote_data;
1 abcd
2 efgh

PL/SQL procedure successfully completed.

sds@LOCAL_DB> connect sds/pa55w0rd@remote_db
Connected.
sds@REMOTE_DB> insert into sds.testtable(test_id,testtext) values (3,'abcdefghijklmnopqrstuvwxyz');

1 row created.

sds@REMOTE_DB> commit;

Commit complete.

sds@REMOTE_DB> connect sds/pa55w0rd@local_db
Connected.
sds@LOCAL_DB> set serveroutput on
sds@LOCAL_DB> exec show_remote_data;
1 abcd
2 efgh
BEGIN show_remote_data; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SDS.SHOW_REMOTE_DATA", line 4
ORA-06512: at line 1

sds@LOCAL_DB>
Advertisements

Questions and Comments always welcome

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: