Running Oracle SQL & PL/SQL: Free Online Execution environments compared
Over the years, I’ve built up a small library (sadly not organised) of SQL notes, samples and test cases. Some of them I have documented in posts on Oracle, SQL Server and other articles on SQL in general. I decided that it was long over due to create a slightly organised GitHub repository for all my SQL notes.
You can see the repository (which I’m going to slowly add to as and when I find notes/samples) over on my GitHub: https://github.com/d-roman-halliday/sql-and-data-notes-and-samples
As part of pulling things together, I needed to test some of the SQL and PL/SQL before publishing it. I also wanted to find ways to share better online.
Why online oracle SQL & PL/SQL environments
If you only want to run something quickly, do a test or share samples web based options save you from getting your hands (or hardware) dirty with becoming your own DBA. You probably want an online platform that provides a convenient environment for writing, executing, and sharing SQL queries. There are quite a few out there for most SQL dialects, but fewer for Oracle. They are a valuable tool for both beginners and experienced SQL users.
I’ve tried them out to see what was best for me, and decided to share my findings here.
I’d like to give an honourable mention for rextester.com, I’ve used it for a few ideas, including my blog post on Dates In Oracle: Ranges, Manipulation & Loops their oracle is now only for supporters (it was previously free).
Comparison of online oracle SQL & PL/SQL environments
Oracle Live SQL
Link: https://livesql.oracle.com/
Oracle from oracle, it’s used for their online learning and training materials. It didn’t seem to work for me outside of incognito mode (cookies/adblockers probably to blame). It has by far the fastest performance of any of the online platforms I’ve tried (not that that’s a big issue for small test cases).
Oracle live SQL has two versions available at the moment:
Classic
Running on Oracle Database 19c, this is the main live version for now. Older with a more simple interface, although many features.
- Sample (simple shop model): https://livesql.oracle.com/ords/livesql/s/cvg7vgea87dtnj5vrhgtcwnn4
Live SQL V2
Running on Oracle Database 23ai, again I had some access issues, once I’d opened the live SQL window I needed to sign in again (top right corner). It has great functionality to document and explain the examples, however the interface to add statements is a little slow and labour intensive in comparison to other options.
An added bonus is that people (who have an account with Oracle Live SQL) can then take it and work with it in their ‘workspace’, by selecting ‘Open in worksheet’.
To share an example (as it wasn’t immediately obvious to me):
- Under Library (in the right hand panel), click ‘My content’
- Then (either):
- Create tutorial script
- Upload script (this didn’t work as expected at the time of writing)
- Fill in the details (title, description etc)
- Click ‘Create’
- Once it’s opened in a new window, use the ‘Actions’ menu to ‘Add script statement’
- Sample (Simple shop model): https://livesql.oracle.com/next/library/scripts/simple-shop-model-Lb7znR?share_key=VjsXK2ppvD
Pros
- It’s oracle from oracle, and pretty complete
- It comes with a collection of sample data databases
- It includes both SQL functionality and most (if not all) PL/SQL functionality
Cons
- It feels like you are signing your life away to oracle for the sign up
- I had difficulty signing in/staying logged in (more a frustration than anything)
- Creating tutorials to share is a little more laborious than other sites
sqlfiddle.com
Link: https://sqlfiddle.com
I’ve used this a few times including in a previous blog post, it’s the most simple interface for sharing solutions.
Samples:
Pros
- It includes both SQL functionality and most (if not all) PL/SQL functionality
- It’s more complete than it used to be
- Built for sharing solutions & samples
Cons
- The single SQL window (with everything erased each time) makes more complicated examples difficult to follow.
- Despite logging in, it’s not possible to get a list of your individual fiddles (keep a list somewhere)
dbfiddle.uk
Link: https://dbfiddle.uk
This has a great interface, but I wasn’t able to do all the PL/SQL stuff I wanted (probably a website/syntax issue). I also ended up doing some changes for INSERT statements, as each statement needs to sit in it’s own ‘box’.
Samples:
- Simple shop model
- Oracle dynamic code creation (Didn’t work as expected)
Pros
- Great interface (similar to ‘notepad’ tools)
- Easy to use, no sign-up required
- Easy to share
Cons
- Each statement needs to be in it’s own box (so I reworked the INSERT statements)
- No login, and no way to manage own code samples
- I couldn’t get all the PL/SQL to work and output as expected (PL/SQL is supported to some extent)
Summary
All the above are great, each with advantages and disadvantages.
- Oracle Live SQL is the best for interface, performance, features and presentation… But it’s a bit laborious to get going and sharing content (but the presentation/functionality once done is good)
- If you want very quick and simple: sqlfiddle.com
- If you want something more interactive: dbfiddle.uk