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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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)

    View full-size slide

  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.

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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/

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  23. Copyright © 2023, Oracle and/or its affiliates
    23

    View full-size slide

  24. Copyright © 2023, Oracle and/or its affiliates
    24

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide