JavaScript, in the Oracle Database ?? Is that even a real programming language? Jeff Smith Distinguished Product Manager Oracle Database Tools

• product manager • @Oracle since 2012 • database tools • GUIs, CLIs, Web Apps, & REST APIs whoami 2

Copyright © 2023, Oracle and/or its affiliates 3 Martin Bach Loic Lefevre Lucas Victor Braun MLE JS Database PM Developer Relations PM Oracle Research GraalVM Dev Lead @martinDBA @Loic__Lefevre @lucasbraun87 MUST FOLLOWS for MLE JS in Oracle Database 21-23c

Copyright © 2023, Oracle and/or its affiliates 4 The Oracle Database adds support for ‘JavaScript snippets’ to be executed. The JavaScript is powered by the GraalVM GraalVM provides an ECMAScript-compliant runtime to execute JavaScript and Node.js applications. The Multilingual Engine, or MLE. Our story starts in 21c

Copyright © 2023, Oracle and/or its affiliates 5 Why should we care about JS and the Oracle Database? MLE JS means these popular libraries are available in YOUR Oracle Database! • Request/Fetch • Validator (9m+ weekly downloads!) • Moccha • Mustache

Copyright © 2023, Oracle and/or its affiliates 6 DBMS_MLE: PL/SQL package for evaluating JavaScript in your Oracle database session How does it work? console.log DBMS_OUTPUT oracle.db module execute(sql)

Copyright © 2023, Oracle and/or its affiliates 7 PRIVS Required SQL> grant EXECUTE ON javascript TO hr; Grant succeeded. SQL> grant EXECUTE dynamic mle TO hr; Grant succeeded.

Copyright © 2023, Oracle and/or its affiliates 8 Using MLE JS in Oracle SQL Developer Web Oracle Database 21c & JS Snippets SQL Worksheet Code your ANON block with DBMS_MLE – wrap JS with q’~ … ~’; Execute as Script

Copyright © 2023, Oracle and/or its affiliates 9 MLE JavaScript support in SQL Developer Web 21c: Snippets & DBMS_MLE Code Editor Open a new JavaScript editor Write and Run your JS Get JS Editor Intellisense

Copyright © 2023, Oracle and/or its affiliates 10 MLE JavaScript support in SQL Developer Web 21c: Snippets & DBMS_MLE Code Editor SQL Developer auto-magically wraps the JS with the required DBMS_MLE bits

Oracle Database 23c Oracle Database 23c accelerates Oracle’s mission to make it simple to develop and run all data-driven apps App Simple Copyright © 2023, Oracle and/or its affiliates 300+ New Features Now Available! Production: OCI Base Database, EE Free Download & Use: 23c Free

Copyright © 2023, Oracle and/or its affiliates 12 In addition to Snippets, you now can save your JavaScript libraries into the database. MODULES: “unit of MLE's language code stored in the database as a schema object” ENVIRONMENTS: define the runtime with directives, configuration scripts and provides name resolution of module imports CALL SPECIFICATIONS: PL/SQL program where the body is replaced with reference to JS module and function 23c Brings MODULES

Copyright © 2023, Oracle and/or its affiliates 13 Included in the 23c database distribution are: We include some powerful modules to help you gets started, Day 0! https://oracle-samples.github.io/mle-modules/

Copyright © 2023, Oracle and/or its affiliates 14 mle-js-fetch

Copyright © 2023, Oracle and/or its affiliates 15 MLE-FETCH Docs

Copyright © 2023, Oracle and/or its affiliates 16 Doing the same via PL/SQL in 11gR2 – 19c

Copyright © 2023, Oracle and/or its affiliates 17 Default Libraries Insufficient? Time to code or IMPORT!

Copyright © 2023, Oracle and/or its affiliates 18 Upload/importing multiple libraries is easy

Copyright © 2023, Oracle and/or its affiliates 19 Managing Environments Define dependencies for runtime (including debugging)

Copyright © 2023, Oracle and/or its affiliates 20 Set/Define your environment My new module utilizes routines from our MATHS environment

Copyright © 2023, Oracle and/or its affiliates 21 Example: Using JS to generate random, but useful, strings MIT License

Copyright © 2023, Oracle and/or its affiliates 22 We can import CHANCE as a Module and then Create one or more Call Specs, MAX FLEXIBILITY OR WE can simply create an INLINE Call Specification where the JS is defined alongside a single PL/SQL Spec, SIMPLICITY CHANCE.js has DOZENS of functions. For flexibility we may want to implement multiple different PL/SQL wrappers, so use Module + Call Spec Modules with Call Specs or In-Line Modules

Copyright © 2023, Oracle and/or its affiliates 23

Copyright © 2023, Oracle and/or its affiliates 24

Copyright © 2023, Oracle and/or its affiliates 25 Chance Examples: Generating URLs, Twitter Handles, Credit Card #s, Dates & More

Copyright © 2023, Oracle and/or its affiliates 26 • Dependencies Diagramming • Code Completion / Documentation Insight • MarkDown Documentation Generation • Code from Local File, Straight to Database or Both SQL Developer Web’s JavaScript Editor Features

Copyright © 2023, Oracle and/or its affiliates 27 Dependency Diagrams make many imports easy to visualize

Copyright © 2023, Oracle and/or its affiliates 28 JavaScript Completion & Insight

Copyright © 2023, Oracle and/or its affiliates 29 Save work locally (files/browser and/or to the Database)

Copyright © 2023, Oracle and/or its affiliates 30 Using a function exported from another module?

Copyright © 2023, Oracle and/or its affiliates 31 Markdown Java{Script}Doc: A Right-Click Away!

Copyright © 2023, Oracle and/or its affiliates 32 Post Execution Debugging: attach debug profile & Run!

Copyright © 2023, Oracle and/or its affiliates 33 Debugging: Seeing What’s What

Copyright © 2023, Oracle and/or its affiliates 34 Debugging: SQL Developer Web Makes this EASY

Copyright © 2023, Oracle and/or its affiliates 35 1. I have REST API for a resource in a DIFFERENT Oracle Database 2. I have a JavaScript routine to FETCH that HTTPS resource 3. I want to call this from a simple SQL SELECT ONE LAST EXAMPLE

Copyright © 2023, Oracle and/or its affiliates 36 Let’s GET a Beer!