ORA-06502 occurs when trying to update a column with random data
I am trying to "strip" some sensitive data from a table by creating a random row with the same number of characters as the column. DML follows:
UPDATE PROD.SUBMISSION
SET DATA01 = CAST(dbms_random.string('X', LENGTH(DATA01)) AS VARCHAR2(2000 BYTE))
WHERE DATA01 IS NOT NULL AND LENGTH(DATA01) > 0
When I run this, ORA-06502 is created.
The DATA01 column is NARCH VARCHAR2 (2000 BYTE). I do not understand what the problem is. There are 100K + rows in the table, so if anyone can suggest any troubleshooting suggestions or detailing the record (s) that would cause a bigger problem.
Hello
source to share
Trying to replicate the problem and I haven't found anything yet that is throwing the error.
Oracle 11g R2 schema setup :
CREATE TABLE SUBMISSION (
DATA01 VARCHAR2(2000)
);
INSERT INTO SUBMISSION VALUES ( 'X' );
INSERT INTO SUBMISSION VALUES ( 'TEST' );
INSERT INTO SUBMISSION VALUES ( NULL );
INSERT INTO SUBMISSION VALUES ( 'Something longer.' );
INSERT INTO SUBMISSION VALUES ( '!"£$%^&*()_+-={}[];:''@~#,<.>/?\|`¬' );
INSERT INTO SUBMISSION VALUES ( DBMS_RANDOM.STRING('X',2000) );
UPDATE SUBMISSION
SET DATA01 = DBMS_RANDOM.STRING('X', LENGTH(DATA01))
WHERE DATA01 IS NOT NULL;
Request 1 :
SELECT SUBSTR(DATA01,1,60) FROM SUBMISSION
Results :
| SUBSTR(DATA01,1,60) |
|--------------------------------------------------------------|
| U |
| WSB8 |
| (null) |
| QNKHK0FVM8A7BO50H |
| V3NF2NJQLL5TLHD4HCW1NWDXZPSBVS0OBH |
| CUZSH86NDMX9QQN4DC1DEVTFRXGKEW3INKAVCNZANL53NMU5OW5FJ5X4SFDW |
Edit
You can run this:
UPDATE PROD.SUBMISSION
SET DATA01 = DBMS_RANDOM.STRING('X', LENGTH(DATA01))
WHERE DATA01 IS NOT NULL
AND ROWID IN ( SELECT ROWID
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY NULL ) AS RN
FROM SUBMISSION )
WHERE RN BETWEEN 1 AND 1000 );
If you change the values in the inter clause in the final line, you can try to narrow down which lines are causing the problem.
source to share
Try logging your errors in a separate log table.
Create table and log:
19:26:22 (15)HR@sandbox> create table errlog_test (key int primary key, value int not null);
Table created.
Elapsed: 00:00:00.06
19:26:49 (15)HR@sandbox> insert into errlog_test values (1,1);
1 row created.
Elapsed: 00:00:00.03
19:26:56 (15)HR@sandbox> exec dbms_errlog.create_error_log('errlog_test');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Follow the instructions:
19:32:05 (15)HR@sandbox> l
1 update errlog_test set value = null where key = 1
2* log errors into err$_errlog_test reject limit unlimited
19:32:06 (15)HR@sandbox> /
0 rows updated.
Elapsed: 00:00:00.04
Note. 0 lines updated from 1. And check the log with the entry that caused the error:
19:32:07 (15)HR@sandbox> select key, value, ora_err_mesg$ from err$_errlog_test;
KEY VALUE ORA_ERR_MESG$
--- ----- --------------------------------------------------------------------------------
1 ORA-01407: cannot update ("HR"."ERRLOG_TEST"."VALUE") to NULL
Elapsed: 00:00:00.01
source to share
The only thing I can think of if this might be correct is that the length argument to DBMS_RANDOM refers to the length in characters, and the generated string can contain multibyte characters. This would mean that if you traverse 2000 in length, you could return a string that is 2000 characters, but more than 2000 bytes, and that won't fit in a VARCHAR2 (2000 BYTE) column.
You can try using a different first parameter for DBMS_RANDOM - you might just be lucky with a more limited character set.
Another idea to narrow the problem was to update each possible length separately, for example:
UPDATE PROD.SUBMISSION
SET DATA01 = dbms_random.string('X', LENGTH(DATA01)
WHERE LENGTH(DATA01) = 1
And then repeat for 2,3, etc.
If my hypothesis above is correct, you are likely to be successful for shorter lengths and then more likely to run into errors as you get closer to 2000.
source to share