Geek asks and answers

UTL_MATCH-like function for working with CLOB

My question is: Is there a function UTL_MATCHthat works with CLOBrather than a VARCHAR2?

My specific problem: I am in an Oracle database. I have a bunch of pre-written queries that interact with Domo CenterView . Requests have variables defined in them ${variableName}. I need to rewrite these queries. I did not write the original, so instead of understanding what a good value for the variables should be, I want to run queries with the application and get what was requested from V$SQL.

So, my solution: Do UTL_MATCHin queries with variables in it and V$SQL.SQL_FULLTEXT. However UTL_MATCHlimited VARCHAR2, and the data type is V$SQL.SQL_FULLTEXTequal CLOB. So this is why I am looking for a function UTL_MATCHthat works with a type CLOB.

Any other tips on how to do this are welcome. Thank!

Change about tips. If you have a better idea of ​​how to do this, let me tell you some information that I have at my disposal. I have about 100 queries, all of them are in the Excel table (the ones that have ${variableName}them). Therefore, I could easily use excel to write a query for me. I hope to combine all these queries together and copy the output to another sheet. Anyway, maybe this is useful if you think the best way to do this.

Example . Let's say I have the following query from Domo:

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

I want to call something like this:

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
;

And we get something like this in response:

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

What I tried:

I tried to tune clob and drop it on varchar. I really hoped this would work, but it gives me an error. Here is 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
;

And here is the error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 8000, maximum: 4000)

, , :

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

, , ...

+3
3

. :

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;
/
+1

UTL_MATCH - , , . . , , , - , (), , ${variableName} "Farmville" "StackOveflow".

, , : , 123, ${variableName} "Farmville".

, . INSTR() SUBSTR(), ${variableName} Domo CenterView v$sql.fulltext. CLOB PL/SQL DBMS_LOB.

+1

If the text you want to find is & lt; = 32767, then you can simply convert CLOBto VARCHAR2using DBMS_LOB.SUBSTR:

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

More articles:


All Articles