Table of contents
Oracle server side programming has a long history of available languages and the obvious de facto one is PL/SQL. When I have started to work on Oracle more than 25 years I have also written some code in Pro*C/C++ where you have a precompiler and finally a C/C++ compiler to create OS executable. Pro*Cobol is also available but I feel it has less been used…
Server side programming can also be a Java program that will be executed on your database server. Java has all the tools and method to access an Oracle database easily. This could also be (not tested) a CREATE OR REPLACE JAVA [CLASS | SOURCE | RESOURCE] with Java code directly embedded inside the database. The natural way of using Java with an Oracle database will normally be an application written in java running on an application server and accessing remotely the database through JDBC or OCI.
Never (yet) tested but you can also create functions in PL/SQL that will directly call a C or a Java program:
CREATE FUNCTION function01 RETURN VARCHAR2 AS [LANGUAGE JAVA NAME '...' | LANGUAGE C NAME '...'];
Testing has been done using Oracle database Enterprise Edition 21c (21.7) running on a 12 cores and 64GB Red Hat Enterprise Linux Server release 7.9 (Maipo) server.
Multilingual Engine (MLE) testing
To access somme dummy table I have created this test table with top 5 polluters (source):
CREATE TABLE fossil_carbon_dioxide_emissions ( id NUMBER, country VARCHAR2(30), YEAR NUMBER, total_mt NUMBER, CONSTRAINT fossil_carbon_dioxide_emissions_pk PRIMARY KEY (id) enable ); INSERT INTO fossil_carbon_dioxide_emissions VALUES(1,'China',2017, 10877.218); INSERT INTO fossil_carbon_dioxide_emissions VALUES(2,'United States',2017, 5107.393); INSERT INTO fossil_carbon_dioxide_emissions VALUES(3,'India',2017, 2454.774); INSERT INTO fossil_carbon_dioxide_emissions VALUES(4,'Russia',2017, 1764.866); INSERT INTO fossil_carbon_dioxide_emissions VALUES(5,'Japan',2017, 1320.776); COMMIT;
I have granted those two privileges to my personal account:
I firstly wanted to see how many languages are supported as I had the feeling that the door is open to other languages (might be in future):
The first script is connecting onto the database (mle-js-oracledb) and accessing/displaying my dummy figures:
I’m hitting the number weird behavior that can be anyway easily corrected…
The second script is still connecting to the database but I take two arguments (year and country) and export a sentence containing the expected result (mle-js-bindings). This could also be encapsulated in a PL/SQL procedure or function:
Multilingual Engine (MLE) advanced testing
I wanted to build a more advanced test case by directly building a pie chart representing my dummy figures. I was about to use Apache ECharts to generate this pie chart but at the time of writing this post nothing is available to import an external library into your code like you would do with NPM.
I might come back to this feature if things improved in future…