Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle Database MLE JavaScript & SQL Developer Web

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

thatjeffsmith

October 07, 2023
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

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 19 Managing Environments

    Define dependencies for runtime (including debugging)
  15. Copyright © 2023, Oracle and/or its affiliates 20 Set/Define your

    environment My new module utilizes routines from our MATHS environment
  16. Copyright © 2023, Oracle and/or its affiliates 21 Example: Using

    JS to generate random, but useful, strings MIT License
  17. 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
  18. Copyright © 2023, Oracle and/or its affiliates 25 Chance Examples:

    Generating URLs, Twitter Handles, Credit Card #s, Dates & More
  19. 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
  20. Copyright © 2023, Oracle and/or its affiliates 29 Save work

    locally (files/browser and/or to the Database)
  21. Copyright © 2023, Oracle and/or its affiliates 30 Using a

    function exported from another module?
  22. 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