类似UTL_MATCH的函数可与CLOB一起使用 [英] UTL_MATCH-like function to work with CLOB

问题描述

我的问题是:是否有 UTL_MATCH 之类的函数，它与CLOB而不是VARCHAR2一起使用?

My question is: Is there a UTL_MATCH-like function which works with a CLOB rather than a VARCHAR2?

我的具体问题是:我在Oracle数据库上.我有一堆与Domo CenterView 交互的预先编写的查询.查询中的变量由${variableName}定义.我需要重写这些查询.我没有写原始文档，因此不是想弄清楚变量的好值是什么，而是要使用应用程序运行查询并从

My specific problem is: I'm on an Oracle database. I have a bunch of pre-written queries which interface with Domo CenterView. The queries have variables in them defined by ${variableName}. I need to rewrite these queries. I didn't write the original so instead of figuring out what a good value for the variables should be I want to run the queries with the application and get what the query was from V$SQL.

所以我的解决方案是:对其中包含变量东西和V$SQL.SQL_FULLTEXT的查询执行UTL_MATCH.但是，UTL_MATCH被限制为VARCHAR2，并且V$SQL.SQL_FULLTEXT的数据类型是CLOB.因此，这就是为什么我要寻找一种与CLOB数据类型一起使用的类似UTL_MATCH的函数.

So my solution is: Do a UTL_MATCH on the queries with the variable stuff in it and V$SQL.SQL_FULLTEXT. However, UTL_MATCH is limited to VARCHAR2 and the datatype of V$SQL.SQL_FULLTEXT is CLOB. So, this is why I'm looking for a UTL_MATCH-like function which works with a CLOB datatype.

欢迎提供其他有关如何完成此操作的提示.谢谢！

编辑有关提示的信息..如果您对操作方法有更好的了解，请告诉我一些我可以使用的信息.我大约有100个查询，它们都在excel电子表格中(其中包含${variableName}的查询).因此，我可以很容易地使用excel为我编写查询.我希望将所有这些查询合并在一起，然后将输出复制到另一张纸上.无论如何，如果您认为有更好的方法可以这样做，那可能会有所帮助.

Edit, about the tips. If you have a better idea of how to do this, let me just tell you some information I've got at my disposal. I have about 100 queries, they're all in an excel spreadsheet (the ones with the ${variableName} in them). So I could pretty easily use excel to write a query for me. I'm hoping to just union all those queries together and copy the output to another sheet. Anyway, maybe that's helpful if you're thinking there's a better way to do this.

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

An example: Let's say I have the following query from 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.我真的很希望这能够奏效，但是这给了我一个错误.这是代码:

I tried substringing the clob and casting it to a varchar. I was really hopeful this would work, but it gives me an error. Here's the code:

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: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 8000, maximum: 4000)

但是，如果我运行此程序，它将运行良好:

However, if I run this it works fine:

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

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

So I'm not sure what the problem is with casting the substring...

推荐答案

我最终为其创建了一个自定义函数.这是代码:

I ended up creating a custom function for it. Here's the code:

CREATE OR REPLACE function match_clob(clob_1 clob, clob_2 clob) return number as

similar number := 0;
sec_similar number := 0;
sections number := 0;
max_length number := 3949;
length_1 number;
length_2 number;
vchar_1 varchar2 (3950);
vchar_2 varchar2 (3950);

begin
  length_1 := length(clob_1);
  length_2 := length(clob_2);
  --dbms_output.put_line('length_1: '||length_1);
  --dbms_output.put_line('length_2: '||length_2);
  IF length_1 > max_length or length_2 > max_length THEN

    FOR x IN 1 .. ceil(length_1 / max_length) LOOP

      --dbms_output.put_line('((x-1)*max_length) + 1'||(x-1)||' * '||max_length||' = '||(((x-1)*max_length) + 1));

      vchar_1 := substr(clob_1, ((x-1)*max_length) + 1, max_length);
      vchar_2 := substr(clob_2, ((x-1)*max_length) + 1, max_length);

--      dbms_output.put_line('Section '||sections||' vchar_1: '||vchar_1||' ==> vchar_2: '||vchar_2);

      sec_similar := UTL_MATCH.JARO_WINKLER_SIMILARITY(vchar_1, vchar_2);

      --dbms_output.put_line('sec_similar: '||sec_similar);

      similar := similar + sec_similar;
      sections := sections + 1;

    END LOOP;

    --dbms_output.put_line('Similar: '||similar||' ==> Sections: '||sections);
    similar := similar / sections;

  ELSE
    similar := UTL_MATCH.JARO_WINKLER_SIMILARITY(clob_1,clob_2);
  END IF;
  --dbms_output.put_line('Overall Similar: '||similar);
   return(similar);
end;
/

这篇关于类似UTL_MATCH的函数可与CLOB一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！