提交前更改Oracle SQL查询文本 [英] Change Oracle SQL Query Text before submission

问题描述

在将SQL查询提交给oracle之前，是否有高级的oracle功能来修改SQL Query?还是可能会修改结果集?基于一定条件?可能会根据提交查询的过程的名称来确定?还是基于查询文字?

这种情况是一个进程(用C ++编写)正在运行查询，而我们没有办法更改C ++代码.在该过程中，特定的业务验证失败了，为了使验证不失败，我想要一种在将返回的数据集提交给oracle引擎之前对其进行操作的方法.

C ++代码正在运行此确切查询

从A_HDR WHERE ENT_NBR =''中选择PL_ID

我想对其进行更改，以便ORACLE始终看到并执行以下查询

在A_HDR WHERE ENT_NBR =''中选择NULL

请指导

解决方案

获取流程并查询文本SYS_CONTEXT .例如，sys_context('userenv', 'current_sql')和sys_context('userenv', 'module').您可能还需要使用GV$SESSION来获取更多信息.

但是，我认为您应该尽可能避免使用VPD.极端地在后台静默转换所有SQL语句，这非常令人困惑.这会使开发和故障排除非常困难.

---

更新

其中的 SQL翻译框架功能Oracle 12c可能是解决此问题的最佳解决方案.

Is there a advanced oracle feature to modify the SQL Query just before it is submitted to oracle? Or may be modify the result set? Based on certain condition? Like may be based on the name of the process that submits the query? Or based on the query text?

The scenario is that a process (written in C++) is running a query and we don't have a way to change the C++ code. A particular business validation within that process is failing and in order to make the validation not fail, I want a way to manipulate the returned data set or manipulate the select query just before it is submitted to oracle engine.

The C++ code is running this exact query

SELECT PL_ID FROM A_HDR WHERE ENT_NBR=''

I want to change it such that ORACLE always sees and executes the following query instead

SELECT NULL FROM A_HDR WHERE ENT_NBR=''

Please guide

解决方案

Oracle Virtual Private Database may be what you're looking for. From the manual:

Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

You may be able to get the process and query text from SYS_CONTEXT. For example, sys_context('userenv', 'current_sql') and sys_context('userenv', 'module'). You may also need to use GV$SESSION to get more information.

However, I think you should try to avoid VPD if possible. It is extremely confusing to have all your SQL statements silently converted in the background. It can make development and troubleshooting very difficult.

---

Update

The SQL Translation Framework feature in Oracle 12c would probably be the best solution for this problem.

这篇关于提交前更改Oracle SQL查询文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！