sql - 与 CLOB 一起使用的类似 UTL_MATCH 的函数

Question

我的问题是:有没有 UTL_MATCH类似于函数，它使用 CLOB 而不是 VARCHAR2？

我的具体问题是:我使用的是 Oracle 数据库。我有一堆预先编写的查询，它们与 Domo CenterView 接口(interface)。查询中包含由 ${variableName} 定义的变量。我需要重写这些查询。我没有编写原文，因此我不想弄清楚变量的最佳值应该是多少，而是想使用应用程序运行查询并从 V$SQL 获取查询内容。 .

所以我的解决方案是:对其中包含变量内容的查询和V$SQL.SQL_FULLTEXT执行UTL_MATCH。但是，UTL_MATCH 仅限于 VARCHAR2，并且 V$SQL.SQL_FULLTEXT 的数据类型为 CLOB。因此，这就是为什么我正在寻找类似 UTL_MATCH 的函数，该函数可与 CLOB 数据类型一起使用。

欢迎提供有关如何实现此目的的任何其他提示。谢谢!

编辑有关提示。如果您对如何执行此操作有更好的了解，请让我告诉您我掌握的一些信息。我有大约 100 个查询，它们都在 Excel 电子表格中(其中带有 ${variableName} 的查询)。所以我可以很容易地使用 Excel 为我编写查询。我希望将所有这些查询合并在一起并将输出复制到另一张纸上。不管怎样，如果您认为有更好的方法来做到这一点，这也许会有所帮助。

示例: 假设我有来自 Domo 的以下查询:

select department.dept_name
from department
where department.id = '${selectedDepartmentId}'
;

我想这样调用:

select v.sql_fulltext
from v$sql v
where utl_match.jaro_winkler_similarity(v.sql_fulltext,
'select department.dept_name
from department
where department.id = ''${selectedDepartmentId}''') > 90
;

并得到这样的返回:

SQL_FULLTEXT
------------------------------------------
select department.dept_name
from department
where department.id = '154'

我尝试过的:

我尝试对 clob 进行子字符串化并将其转换为 varchar。我真的希望这会起作用，但它给了我一个错误。代码如下:

select v.sql_fulltext
from v$sql v
where  utl_match.jaro_winkler_similarity( cast( substr (v.sql_fulltext, 0, 4000) as varchar2 (4000)),
'select department.dept_name
from department
where department.id = ''${selectedDepartmentId}''') > 90
;

这是错误:

ORA-22835:缓冲区太小，无法进行 CLOB 到 CHAR 或 BLOB 到 RAW 的转换(实际:8000，最大:4000)

但是，如果我运行它，它工作正常:

select cast(substr(v.sql_fulltext, 0, 4000) as varchar2 (4000))
from v$sql v
;

所以我不确定转换子字符串有什么问题......

Answer 1

UTL_MATCH 是一个用于比较字符串的包装，用于检查两个字符串的相似程度。它的函数评估字符串并返回分数。因此，您将得到的只是一个数字，指示(例如)您需要进行多少次编辑才能将 ${variableName} 转换为“Farmville”或“StackOveflow”。

您不会得到的是实际差异:这两个文本字符串是相同的，除了在偏移量 123 处将 ${variableName} 替换为“Farmville”。

这样说就表明了另一种方法。使用INSTR()和 SUBSTR()在 Domo CenterView 查询中定位 ${variableName} 实例，并使用这些偏移量来识别 v$sql.fulltext 等效项中的不同文本。您可以使用 PL/SQL 中的 CLOB 来执行此操作 DBMS_LOB package .