为并行 DML 涉及的表提供复杂的默认值
我有许多表参与并行运行的合并语句。合并输出到通过 DBMS_ERRLOG.CREATE_ERROR_LOG 函数生成的错误表。该语句工作正常，并且合并工作。
然后，我向错误表添加了一个附加列，它继续正常工作，没有问题。
我现在希望使该附加列具有基于系统中其他位置的值的默认值（本质上是批处理日期，无论如何与当前日期时间不同） - 而且我每次都会遇到限制转动。
- 我尝试使用触发器填充该值，但这会在并行 DML 涉及的表上放置一个触发器，这是不允许的，并且在合并期间会引发错误。 （这确实会出错）
- 我尝试添加基于 pl/sql 函数的默认值 - 这是不允许的。
- 我尝试根据 sql 语句添加默认值 - 这又是不允许的。
我可以实现额外的更新后处理来填充列，但是这必须对我合并的每个表进行，这确实有点麻烦 - 随着系统扩展，必须为每个错误表记住这一点。填充默认值是首选方法。
如何解决 Oracle PDML 对触发器的限制，以有效地基于 select 语句填充此值。
I have a number of tables involved in a merge statement that is running parallel. The merge outputs to an error table which is generated via the DBMS_ERRLOG.CREATE_ERROR_LOG function. The statement works fine, and the merge works.
I then added an additional column to the error table, and it continues to work without issue.
I now wish to make that additional column have a default value based on a value elsewhere in the system (in essence the batch processing date, which is not the same as the current datetime in anyway) - and I am tripping up over restrictions at every turn.
- I tried populating the value using a trigger - but that then places a trigger on a table involved in parallel DML which is not permitted, and and error is thrown during the merge. (This does error annoyingly)
- I tried adding a default value based on a pl/sql function - this is not permitted.
- I tried adding a default value based on a sql statement - this is again not permitted.
I could implement an additional update post processing to populate the column, but this would then have to be done for every table that I merge and really is a bit of a hack - as the system expands this would have to be remembered for every error table. Populating the default value is the preferred approach.
How do I work around Oracles PDML restriction on the trigger to populate this value based on effectively a select statement.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于该值听起来相当静态，因此请查看 SYS_CONTEXT。
例如，我有一个日志表，其中存储当前正在运行的模块（使用 DBMS_APPLICATION_INFO 设置）。
column_name VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','MODULE'),
不确定是否为会话或进程存储了常规上下文值。如果它不适用于并行处理，请查看 全局应用程序上下文，您可以将其应用于给定用户名的所有会话
Since the value sounds fairly static, look at SYS_CONTEXT.
For example, I have a logging table where I store the currently running module (set with DBMS_APPLICATION_INFO).
column_name VARCHAR2(256) DEFAULT SYS_CONTEXT('USERENV','MODULE'),
Not sure whether a conventional context value is stored for the session or process. If it doesn't work for parallel processing, check out the GLOBAL APPLICATION CONTEXT which you can apply across all sessions for a given username
这似乎有点超出了 DML 错误表本机要做的事情的范围，然后是的 - 并行 DML 规则将限制您使用其他选项。解决此问题的最简单方法是将批处理日期添加到原始表中，以便它也反映在错误表中。花费你一点存储空间，但是如果你无论如何都需要批量识别行......
或者，正如你提到的，有一个后处理解决方案。最后，是否有一种方法可以将该信息分层到一个视图中，以便在您查看错误时覆盖错误表？或者批次日期以后无法计算？
This seems a little bit outside the scope of what DML error tables are intended to do natively, and then yes - the parallel DML rules are going to retrict you from other options. The easiest way to resolve this would be to add that batch processing date to the original table so that it also is reflected in the error table. Costs you a bit of storage, but if you need to identify rows by batch anyway....
Or, as you mention, there is a post-process solution. Finally, is there perhaps a way to layer that information into a view that overlays the error table when you go to look at the errors? Or is the batch date impossible to calculate at a later time?