Skip to content

Instantly share code, notes, and snippets.

@PhilippSalvisberg
Created May 20, 2024 12:14
Show Gist options
  • Save PhilippSalvisberg/e0564e763d45ecf1c42d436f92d4f1a4 to your computer and use it in GitHub Desktop.
Save PhilippSalvisberg/e0564e763d45ecf1c42d436f92d4f1a4 to your computer and use it in GitHub Desktop.
-- MLE inline call spec in 23.3 (does not work in 24.4) anymore
/*
create or replace function find_terms (
"in_terms" in varchar2,
"in_text" in varchar2
) return json is mle language javascript q'[
var found_terms = [];
var terms_array = in_terms.split(',');
for (var i = 0; i < terms_array.length; i++) {
var term = terms_array[i].trim().toLowerCase();
if (in_text.toLowerCase().includes(term)) {
found_terms.push(term);
}
}
return {"terms": found_terms, "hits": found_terms.length};
]';
/
*/
-- MLE module and call spec (wrapper) in 23.4 as workaround (works also in 23.3)
create or replace mle module find_terms_mod language javascript as
export function findTerms(terms, text) {
var found_terms = [];
var terms_array = terms.split(',');
for (var i = 0; i < terms_array.length; i++) {
var term = terms_array[i].trim().toLowerCase();
if (text.toLowerCase().includes(term)) {
found_terms.push(term);
}
}
return {"terms": found_terms, "hits": found_terms.length};
}
/
create or replace function find_terms(in_terms in varchar2, in_text in varchar2)
return json is
mle module find_terms_mod
signature 'findTerms';
/
-- format output for SQL*Plus or SQLcl
set linesize 200
set pagesize 100
column hits format 999
column terms format a44
column txt format a100
-- bug in 23.3, 23.4: throws ORA-00904: "HITS": invalid identifier
/*
with
base as (
select id,
find_terms('create,database,privilege,select,drop,view,table', txt) as found_terms,
txt
from t
)
select id,
j.found_terms.terms,
to_number(j.found_terms.hits) as hits,
txt
from base j
where j.found_terms.hits > 3
order by hits desc;
*/
-- Workaround for bug in 23.3 and 23.4: call json_value once (does not matter for what)
with
base as (
select id,
find_terms('create,database,privilege,select,drop,view,table', txt) as found_terms,
txt
from t
)
select id,
j.found_terms.terms,
to_number(json_value(j.found_terms, '$.hits')) as hits,
txt
from base j
where j.found_terms.hits > 3
order by hits desc;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment