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>