使用 Oracle OLE DB 提供程序时如何解决 SQL 查询参数映射问题?

2021-12-30 00:00:00 oracle ssis

尝试使用 Oracle OLE DB 提供程序输入带参数的 SQL 查询时，出现以下错误:

When trying to enter a SQL query with parameters using the Oracle OLE DB provider I get the following error:

无法从 SQL 命令中提取参数.提供程序可能无法帮助解析命令中的参数信息.在这种情况下，请使用来自变量的 SQL 命令"访问模式，将整个 SQL 命令存储在一个变量中.
附加信息:
Provider 无法导出参数信息，并且尚未调用 SetParameterInfo.(Microsoft OLE DB Provider for Oracle)

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
ADDITIONAL INFORMATION:
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)

我尝试遵循此处的建议，但不太明白需要什么:针对 Oracle 的参数化查询

I have tried following the suggestion here but don't quite understand what is required:Parameterized queries against Oracle

有什么想法吗?

推荐答案

扩展问题中给出的链接:

To expand on the link given in the question:

  1. 创建包变量
  2. 双击包变量名称.(这允许您访问变量的属性)
  3. 将属性EvaluateAsExpression"设置为 true
  4. 在表达式构建器中输入查询.
  5. 将 OLE DB 源查询设置为来自变量的 SQL 命令

表达式构建器可以使用变量动态创建表达式以创建参数化查询".
所以下面的正常"查询:

The expression builder can dynamically create expressions using variable to create 'parametised queries'.
So the following 'normal' query:

select * from book where book.BOOK_ID = ?

可以在表达式生成器中写成:

Can be written in the expression builder as:

"select * from book where book.BOOK_ID = " + @[User::BookID]

然后您可以使用表达式构建器进行空值处理和数据转换.

You can then do null handling and data conversion using the expression builder.

相关文章