Oracle Database MLE JavaScript & SQL Developer Web

Transcript

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

   View Slide

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

   View 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 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 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 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 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 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 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 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 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 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 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 Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View 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 Slide

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

    View Slide

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

    View Slide

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

    View 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 Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View 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 Slide

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

    View Slide