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;
      begin
        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.execute_script(l_handle); 
        dbms_mle.variable_value(l_handle, 'result', l_result);
        dbms_mle.drop_script(l_handle);
        return l_result;
      end;
      /
      

       

      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.
      *Action:
      

       

      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.