Saturday, January 30, 2010

Null in trigger source issue for expdp

Ran into a nasty little bug while doing a full database import using datapump.


ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calling DBMS_METADATA.CONVERT [TRIGGER:"SCHEMANAME"."SOME_TRIGGER_NAME"]
ORA-06502: PL/SQL: numeric or value error
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6313

Found a number of Metalink DocID's that fit the bill, but the best suspect is 3897122.8. The issue is actually a bug in expdp where source with null lines causes expdp to basically create a corrupted export file. Fantastic.

Here's a query we used to identify the bad source code to be fixed:

select * from dba_source where text like '%'||CHR(0)||'%';

Found two triggers with null lines, probably put there by some kind of SQL dev tool. Rebuilt the triggers without null lines, and can now create an export file that can actually be imported.