Oracle Database MLE JavaScript & SQL Developer Web

See how our web IDE makes it trivial to work with JavaScript in your Oracle 21 or 23c Database. Debuggers, doc generation, intellisense, and more.

Powered by GraalVM, the Multi-Lingual Engine puts JavaScript directly into the Oracle Database kernel, meaning JavaScript code can now live and RUN inside your Oracle database.

Links mentioned in Slides
Oracle Database 23c: https://www.oracle.com/database/23c/

Loic's Blog for Random JS: https://blogs.oracle.com/developers/post/how-to-import-javascript-es-modules-in-23c-free-and-use-them-in-sql-queries

Martin's Blog for Debugging JS: https://blogs.oracle.com/developers/post/an-introduction-to-post-execution-debugging-in-oracle-23c-free-developer-release

Transcript

1. JavaScript, in the Oracle Database ?? Is that even a real programming language? Jeff Smith Distinguished Product Manager Oracle Database Tools
2. • product manager • @Oracle since 2012 • database tools • GUIs, CLIs, Web Apps, & REST APIs whoami 2
3. 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
4. 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
5. 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
6. 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)
7. 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.
8. 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
9. 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
10. 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
11. 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
12. 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
13. 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/
14. Copyright © 2023, Oracle and/or its affiliates 14 mle-js-fetch
15. Copyright © 2023, Oracle and/or its affiliates 15 MLE-FETCH Docs
16. Copyright © 2023, Oracle and/or its affiliates 16 Doing the same via PL/SQL in 11gR2 – 19c
17. Copyright © 2023, Oracle and/or its affiliates 17 Default Libraries Insufficient? Time to code or IMPORT!
18. Copyright © 2023, Oracle and/or its affiliates 18 Upload/importing multiple libraries is easy
19. Copyright © 2023, Oracle and/or its affiliates 19 Managing Environments Define dependencies for runtime (including debugging)
20. Copyright © 2023, Oracle and/or its affiliates 20 Set/Define your environment My new module utilizes routines from our MATHS environment
21. Copyright © 2023, Oracle and/or its affiliates 21 Example: Using JS to generate random, but useful, strings MIT License
22. 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
23. Copyright © 2023, Oracle and/or its affiliates 23
24. Copyright © 2023, Oracle and/or its affiliates 24
25. Copyright © 2023, Oracle and/or its affiliates 25 Chance Examples: Generating URLs, Twitter Handles, Credit Card #s, Dates & More
26. 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
27. Copyright © 2023, Oracle and/or its affiliates 27 Dependency Diagrams make many imports easy to visualize
28. Copyright © 2023, Oracle and/or its affiliates 28 JavaScript Completion & Insight
29. Copyright © 2023, Oracle and/or its affiliates 29 Save work locally (files/browser and/or to the Database)
30. Copyright © 2023, Oracle and/or its affiliates 30 Using a function exported from another module?
31. Copyright © 2023, Oracle and/or its affiliates 31 Markdown Java{Script}Doc: A Right-Click Away!
32. Copyright © 2023, Oracle and/or its affiliates 32 Post Execution Debugging: attach debug profile & Run!
33. Copyright © 2023, Oracle and/or its affiliates 33 Debugging: Seeing What’s What
34. Copyright © 2023, Oracle and/or its affiliates 34 Debugging: SQL Developer Web Makes this EASY
35. 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
36. Copyright © 2023, Oracle and/or its affiliates 36 Let’s GET a Beer!