Slide 1
Slide 1 text
1
Copyright @ 2022 Oracle and/or its affliates
Oracle SQL Developer Web
Database Actions
Jeff Smith
Distinguished Product Manager
[email protected]
Blog: https://www.thatjeffsmith.com
Twitter: @thatjeffsmith
Slide 2
Slide 2 text
2
SQL Developer Web
The power of SQL Developer…in your browser
• SQL Worksheet – SQL, PLSQL, MLE/JS
• Charts & Dashboarding
• Schema Browser
• Performance & Instance Management
• Diagramming/Dictionary Reports
• JSON Document Management
• REST Workshop
• User Management
• Data Loading / Data Pump
• DBMS Scheduler
• APEX Workspace Management
Copyright @ 2022 Oracle and/or its affliates
Slide 3
Slide 3 text
3
How It Works
Standalone or
Java Servlet (Tomcat or WLS)
DB User Authentication for
REST Enabled Schemas
ORDS Served REST APIs
Oracle JET Single Page Web App
Copyright @ 2022 Oracle and/or its affliates
Slide 4
Slide 4 text
4
Installation & Configuration
You can use SQL Developer anywhere you can run ORDS
• Install ORDS
• Say “YES” to SQL Developer Web
Video Tutorial
https://www.youtube.com/watch?v=1LBmLTe8sIc
Copyright @ 2022 Oracle and/or its affliates
Slide 5
Slide 5 text
5
SQLDev Web & Autonomous Database
• ADMIN SSO
• Updated Quarterly
• ORDS is managed
by Oracle
Copyright @ 2022 Oracle and/or its affliates
Slide 6
Slide 6 text
6
Database Logins
• Database User/Password
• REST Enable SCHEMAs
• ADMIN enabled by
default in Autonomous DB
ORDS API – ENABLE SCHEMA
Copyright @ 2022 Oracle and/or its affliates
Slide 7
Slide 7 text
7
Start Here
/ords/sql-developer
Copyright @ 2022 Oracle and/or its affliates
Slide 8
Slide 8 text
8
Database Actions
• “Launchpad”/Home
• Access to features
determined by:
• Environment
• Version of Oracle
• Database USER privs
Copyright @ 2022 Oracle and/or its affliates
Slide 9
Slide 9 text
9
Usability Features: Mega Menu
Copyright @ 2022 Oracle and/or its affliates
Slide 10
Slide 10 text
10
Help & Docs
In-App Help Oracle Docs
Copyright @ 2022 Oracle and/or its affliates
Slide 11
Slide 11 text
11
Guided On-Screen Tours
Application Tour
Copyright @ 2022 Oracle and/or its affliates
Slide 12
Slide 12 text
12
Language (9) Support
Copyright @ 2022 Oracle and/or its affliates
Slide 13
Slide 13 text
13
SQL
• Queries & Scripts
• Tuning
• Imports & Exports
• Editor Assistance
• Formatting
• Insight
• Parser
• SQL History
• Command Palette
Copyright @ 2022 Oracle and/or its affliates
Slide 14
Slide 14 text
14
SQL
Query Execution
• Same Keyboard Shortcuts,
e.g., CTRL+ENTER
• Grid or text presentation
• SQL*Plus Script Engine Support
• Quick Download Popular Formats
• CSV, JSON, XML, TEXT
Copyright @ 2022 Oracle and/or its affliates
Slide 15
Slide 15 text
15
SQL: full featured editor
Copyright @ 2022 Oracle and/or its affliates
Slide 16
Slide 16 text
16
Data Grids
• Click to Sort
• Single Record View
• Advanced Export
• Type Viewers
Copyright @ 2022 Oracle and/or its affliates
Slide 17
Slide 17 text
17
Grids: Single Record View
Columnar vs Row Displays
Copyright @ 2022 Oracle and/or its affliates
Slide 18
Slide 18 text
18
Grids: Manage Columns & Sorts
Hide & Reorder Columns & Apply Multiple Sort Conditions
Copyright @ 2022 Oracle and/or its affliates
Slide 19
Slide 19 text
19
Grid: Type Viewers
Click Cell Pencil to Activate
Dates/Timestamps
Intervals
BLOBs
Copyright @ 2022 Oracle and/or its affliates
Slide 20
Slide 20 text
20
Grid: Exports
Right Click, Export
Copyright @ 2022 Oracle and/or its affliates
Slide 21
Slide 21 text
21
Worksheet - Queries
Script Execution
• Automatic Formatting
• SQL*Plus Style Reports
• Same shortcut as
desktop (F5)
Copyright @ 2022 Oracle and/or its affliates
Slide 22
Slide 22 text
22
Worksheet: Execution Plans
Copyright @ 2022 Oracle and/or its affliates
Slide 23
Slide 23 text
23
Worksheet: AutoTrace
Copyright @ 2022 Oracle and/or its affliates
Slide 24
Slide 24 text
24
Worksheet Assistance: SQL History
Previous Queries/Scripts Executed
Copyright @ 2022 Oracle and/or its affliates
Slide 25
Slide 25 text
25
Worksheet Assistance: Insight
CTRL+Space
Copyright @ 2022 Oracle and/or its affliates
Slide 26
Slide 26 text
26
Worksheet Assistance: Parser
Mouse Over Gutter or Grammar Squiggles for Advice
Copyright @ 2022 Oracle and/or its affliates
Slide 27
Slide 27 text
27
Worksheet Assistance: Formatter
Before
After
Copyright @ 2022 Oracle and/or its affliates
Slide 28
Slide 28 text
28
Worksheet: Browser
Drag and Drop Code Generation
Copyright @ 2022 Oracle and/or its affliates
Slide 29
Slide 29 text
29
Worksheet: PL/SQL
Browse and Generate Anon Exec Blocks
Copyright @ 2022 Oracle and/or its affliates
Slide 30
Slide 30 text
30
Worksheet: PL/SQL
Open and Edit/Compile in a Full Screen Editor
Copyright @ 2022 Oracle and/or its affliates
Slide 31
Slide 31 text
31
Worksheet: JavaScript
Powered by Multilingual Engine (MLE) and GraalVM in Database 21c
Copyright @ 2022 Oracle and/or its affliates
Slide 32
Slide 32 text
32
Worksheet: JavaScript
Powered by Multilingual Engine (MLE) and GraalVM in Database 21c
Copyright @ 2022 Oracle and/or its affliates
Slide 33
Slide 33 text
33
Worksheet: Local File Editing
Open and Save Files from your local machine
Copyright @ 2022 Oracle and/or its affliates
Slide 34
Slide 34 text
34
Worksheet: Schema Browser
Create Table
Video Demonstration
Copyright @ 2022 Oracle and/or its affliates
Slide 35
Slide 35 text
35
Worksheet: Schema Browser
Edit Table
Copyright @ 2022 Oracle and/or its affliates
Slide 36
Slide 36 text
36
Worksheet: Schema Browser
Open Table
Copyright @ 2022 Oracle and/or its affliates
Slide 37
Slide 37 text
37
Worksheet: Schema Browser
SEARCH
Copyright @ 2022 Oracle and/or its affliates
Slide 38
Slide 38 text
38
Worksheet: Data Loading
Import data to NEW or Existing TABLEs
+ JSON
+ XML
+ AVRO
+ CSV
+ Excel
Copyright @ 2022 Oracle and/or its affliates
Slide 39
Slide 39 text
39
Worksheet: Data Loading
Add multiple files, create multiple tables all in one go!
Copyright @ 2022 Oracle and/or its affliates
Slide 40
Slide 40 text
40
Worksheet: Data Loading
Importing JSON to a Relational Table
Copyright @ 2022 Oracle and/or its affliates
Slide 41
Slide 41 text
41
Charts
Take SQL query results and create rich HTML charts | combine for dashboards
Copyright @ 2022 Oracle and/or its affliates
Slide 42
Slide 42 text
42
Creating a Chart
Just supply the SQL
Copyright @ 2022 Oracle and/or its affliates
Slide 43
Slide 43 text
43
Dashboards
Take your favorite charts and combine in any order/grouping you want
Copyright @ 2022 Oracle and/or its affliates
Slide 44
Slide 44 text
44
Charting: Getting Started
Promote Query from SQL worksheet
Copyright @ 2022 Oracle and/or its affliates
Slide 45
Slide 45 text
45
User Management
Copyright @ 2022 Oracle and/or its affliates
Slide 46
Slide 46 text
46
User Management, Create/Edit Users
Copyright @ 2022 Oracle and/or its affliates
Slide 47
Slide 47 text
47
Instance Viewer
Copyright @ 2022 Oracle and/or its affliates
Slide 48
Slide 48 text
48
Instance Viewer, Memory/Storage
Copyright @ 2022 Oracle and/or its affliates
Slide 49
Slide 49 text
49
Activity/DBA
Instance Overview and Performance Pages
• DB Status
• Storage
• Sessions
• Waits
• Users
• Expired PWDs
• Alerts
• Top SQL
Copyright @ 2022 Oracle and/or its affliates
Slide 50
Slide 50 text
50
Activity/DBA
Storage Drill Down
Copyright @ 2022 Oracle and/or its affliates
Slide 51
Slide 51 text
51
Activity/DBA
Storage Drill Down/View Data Files
Copyright @ 2022 Oracle and/or its affliates
Slide 52
Slide 52 text
52
Activity/DBA
Sessions Drill Down
Copyright @ 2022 Oracle and/or its affliates
Slide 53
Slide 53 text
53
Activity/DBA
Alerts Drill Down
Copyright @ 2022 Oracle and/or its affliates
Slide 54
Slide 54 text
54
Activity/DBA
TOP SQL Drill Down
Copyright @ 2022 Oracle and/or its affliates
Slide 55
Slide 55 text
55
DBA
Parameters
Copyright @ 2022 Oracle and/or its affliates
Slide 56
Slide 56 text
56
Activity/DBA
Performance Hub
Copyright @ 2022 Oracle and/or its affliates
Slide 57
Slide 57 text
57
Data Modeler
Reverse Engineer/Diagrams from your SCHEMA
Video Demonstration
Copyright @ 2022 Oracle and/or its affliates
Slide 58
Slide 58 text
58
Data Modeler
Automatic and STAR Schema Layouts
Copyright @ 2022 Oracle and/or its affliates
Slide 59
Slide 59 text
59
Data Modeler
Implied Foreign Keys
Copyright @ 2022 Oracle and/or its affliates
Slide 60
Slide 60 text
60
Data Modeler
Customize Objects, Relationships, Colors
Copyright @ 2022 Oracle and/or its affliates
Slide 61
Slide 61 text
61
Data Modeler
DDL Generation
Copyright @ 2022 Oracle and/or its affliates
Slide 62
Slide 62 text
62
Data Modeler
Data Dictionary Reports
Copyright @ 2022 Oracle and/or its affliates
Slide 63
Slide 63 text
63
Data Modeler
Managing Your Diagrams
• Save Diagrams (stored in local database TABLE)
• Search Diagrams, Preview before opening
Copyright @ 2022 Oracle and/or its affliates
Slide 64
Slide 64 text
64
Data Modeler
Desktop Integration
• Open Diagrams from Oracle SQL Developer Data Modeler
• And Vice Versa!
Built in SQL Developer Web Built in SQL Developer Data Modeler (Desktop)
Copyright @ 2022 Oracle and/or its affliates
Slide 65
Slide 65 text
65
REST
Full RESTful Web Service Development Interface
Copyright @ 2022 Oracle and/or its affliates
Slide 66
Slide 66 text
66
REST Dashboard
Copyright @ 2022 Oracle and/or its affliates
Slide 67
Slide 67 text
67
Browse, Edit, Create Modules
Copyright @ 2022 Oracle and/or its affliates
Slide 68
Slide 68 text
68
Cards vs Grids
All browsers supports both views
Copyright @ 2022 Oracle and/or its affliates
Slide 69
Slide 69 text
69
URI Templates
Copyright @ 2022 Oracle and/or its affliates
Slide 70
Slide 70 text
70
HTTP Handlers
Copyright @ 2022 Oracle and/or its affliates
Slide 71
Slide 71 text
71
REST IDE
Copyright @ 2022 Oracle and/or its affliates
Slide 72
Slide 72 text
72
Parameters
Copyright @ 2022 Oracle and/or its affliates
Slide 73
Slide 73 text
73
OAUTH2
Copyright @ 2022 Oracle and/or its affliates
Slide 74
Slide 74 text
74
Creating OAUTH2 Clients
Copyright @ 2022 Oracle and/or its affliates
Slide 75
Slide 75 text
75
‘Show Code’
Copyright @ 2022 Oracle and/or its affliates
Slide 76
Slide 76 text
76
Documentation & Testing
Built-in OpenAPI3 {swagger} clients & doc
Copyright @ 2022 Oracle and/or its affliates
Slide 77
Slide 77 text
77
Quickly Import from OpenAPI
Take OpenAPI Spec definition to a fully built-out REST Module
Copyright @ 2022 Oracle and/or its affliates
Slide 78
Slide 78 text
78
JSON
JSON Document Store – Create collections, manage documents, query, data guides
Copyright @ 2022 Oracle and/or its affliates
Slide 79
Slide 79 text
79
JSON – Add Document
Copyright @ 2022 Oracle and/or its affliates
Slide 80
Slide 80 text
80
JSON Editor
Copyright @ 2022 Oracle and/or its affliates
Slide 81
Slide 81 text
81
JSON – Filtering (Query by Example)
Copyright @ 2022 Oracle and/or its affliates
Slide 82
Slide 82 text
82
JSON Filtering, get the SQL!
Copyright @ 2022 Oracle and/or its affliates
Slide 83
Slide 83 text
83
JSON Data Guide (Diagram)
Copyright @ 2022 Oracle and/or its affliates
Slide 84
Slide 84 text
84
JSON – Create INDEX or VIEW
Copyright @ 2022 Oracle and/or its affliates
Slide 85
Slide 85 text
85
Data Pump
IMPORT – Autonomous and On Premises Support
Copyright @ 2022 Oracle and/or its affliates
Slide 86
Slide 86 text
86
Data Pump
Monitor jobs, check logs, download DMP files
Copyright @ 2022 Oracle and/or its affliates
Slide 87
Slide 87 text
87
DBMS_SCHEDULER
Create and edit Jobs, Chains, Schedules, Windows, and more!
Copyright @ 2022 Oracle and/or its affliates
Slide 88
Slide 88 text
88
DBMS_SCHEDULER
Reporting, Job Forecasting, & Chain Diagramming
Copyright @ 2022 Oracle and/or its affliates
Slide 89
Slide 89 text
89
APEX Workspace Management
Create, Delete, Get URL, Navigate to SCHEMA
Copyright @ 2022 Oracle and/or its affliates
Slide 90
Slide 90 text
90
Watch the Movie
50 Minute Video Tour
https://www.youtube.com/watch?v=szqH2r-6KzU
Copyright @ 2022 Oracle and/or its affliates
Slide 91
Slide 91 text
91 Copyright @ 2022 Oracle and/or its affliates
Jeff Smith
Email: [email protected]
Twitter: @thatjeffsmith
Blog: https://www.thatjeffsmith.com
Thank You!
Questions?