问题描述
我正在将数据从旧系统迁移到我们的新应用程序(在Oracle数据库10gR2上运行)。作为迁移的一部分,我正在使用将数据插入到应用程序使用的表中的脚本。导入的数据行数为数千,源数据不干净(NOT NULL列中的意外空值等)。因此,在通过脚本插入数据时,每当发生这种异常时,脚本会突然结束,并且整个事务将回滚。
有没有办法,我可以继续插入行干净的数据?
使用 NVL()
或 COALESCE()
不是一个选项,因为我想记录行导致错误,以便数据可以纠正下一次传递。
编辑:我当前的过程有一个异常处理程序,我记录的第一行导致错误。是否可以插入继续而不终止,因为现在在第一个处理的异常,该过程终止执行。
使用PLSQL,您可以在自己的事务中执行每个插入(在每个事务之后执行COMMIT),并记录或忽略继续进行的异常处理程序的错误。
I'm working on migration of data from a legacy system into our new app(running on Oracle Database, 10gR2). As part of the migration, I'm working on a script which inserts the data into tables that are used by the app.
The number of rows of data that are imported runs into thousands, and the source data is not clean (unexpected nulls in NOT NULL columns, etc). So while inserting data through the scripts, whenever such an exception occurs, the script ends abruptly, and the whole transaction is rolled back.
Is there a way, by which I can continue inserts of data for which the rows are clean?Using NVL()
or COALESCE()
is not an option, as I'd like to log the rows causing the errors so that the data can be corrected for the next pass.
EDIT: My current procedure has an exception handler, I am logging the first row which causes the error. Would it be possible for inserts to continue without termination, because right now on the first handled exception, the procedure terminates execution.
Using PLSQL you can perform each insert in its own transaction (COMMIT after each) and log or ignore errors with an exception handler that keeps going.
这篇关于在异常引发时,继续插入Oracle的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!