0 Replies Latest reply on Mar 12, 2019 11:37 AM by Philipp Salvisberg

    How to require a module from a dbms_mle script (JavaScript)

    Philipp Salvisberg

      I have installed the validator example according https://oracle.github.io/oracle-db-mle/js/udf/ (changed username, password and dbinstance accordingly).


      The following query works (returns 1):


      select isemail('bla@bla.com') from dual;


      Now, I tried to use the validator within dbms_mle as follows:


      create or replace function isemail_dyn(in_email in varchar2) return number is
        l_script clob;
        l_handle dbms_mle.script_handle_t;
        l_result number;
        l_script := q'[mle.binds.result = require('validator').isEmail(mle.binds.email);]';
        l_handle := DBMS_MLE.CREATE_SCRIPT('JS', l_script);
        dbms_mle.bind_variable(l_handle, 'email', in_email);
        dbms_mle.variable_value(l_handle, 'result', l_result);
        return l_result;


      The query using this function fails:


      select isemail_dyn('bla@bla.com') from dual;


      The error stack looks like this:


      ORA-06550: line 1, column 20:
      dynamic-mle-script--1156712892.js: ReferenceError: require is not defined
      ORA-06512: at "SYS.DBMS_MLE_CAPI", line 97
      ORA-06512: at "SYS.DBMS_MLE", line 57
      ORA-06512: at "DEMO.ISEMAIL_DYN", line 9
      06550. 00000 -  "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.


      I've tried tried different variants for "require('validator')", but all with more or less the same result.


      Now the question. Is it possible to use previously loaded modules from dbms_mle? If yes, how?


      Thank you very much.