written as UMD modules. It is the
most common pattern for writing modules and can be generated by a variety
of tools (like, e.g., webpack,
the simplest way to write a module that can be loaded by Oracle Database MLE is to
use Common-JS and to specify the functions that should
be callable from the Oracle Database as properties of
CREATE SOURCE statement.
For our helloworld example, we create a file
hello.sql with the following content:
We run this script in the
scott/tiger schema as follows:
sqlplus scott/tiger@ORCLPDB1 @hello.sql
ORCLPDB1 needs to be replaced if a container other than
ORCLPDB1 was choosen at container startup.
We now need provide a type declaration for the
hello function via
CREATE FUNCTION statement.
This specification is needed to bridge the gap between the statically-typed SQL
This call specification can be created via SQL*Plus as follows:
CREATE FUNCTION system privilege.
We can now run a query that calls the function
hello and pass a string as argument:
select hello('world') from dual;
The output is as follows:
HELLO('WORLD') -------------------------------------------------------------------------------- Hello world!
When writing scripts by hand we recommend using CommonJS's
to export all functions to the database. When using tools like webpack or writing the extension
directly in TypeScript, an easier way is to
instruct these tools to generate UMD modules directly.
recommend to use the
CREATE FUNCTION or
CREATE PROCEDURE statements.
types are number, string, object, Symbol, and undefined.
Dynamic Script Execution¶