This post shows you how to configure the Oracle Developer Tools extension for VS Code and then use it to query an existing table, create or alter database objects and execute PL/SQL.
The versions I have used are shown in brackets
Microsoft VS Code (1.35.1)
Oracle Database ( XE 184.108.40.206.0)
Start VS Code and navigate to extensions then search for Oracle Developer Tools. At the time of writing the home page for this extension was:
There are several methods of connecting to the database from VS Code, in this guide I will be using the tnsnames.ora approach.
Check where Oracle Developer Tools extension looks for the tnsnames.ora file by:
Selecting the Oracle Developer Tools extension in VS Code and selecting the manage icon.
Select Configure Extension Settings
Look for Config Files Location
If required amend the location to point to where the tnsnames.ora file is located.
Restart VS Code.
Connecting to the Oracle Database
Create a new file and from the lower status bar click Plain Text and then select Oracle-SQL and PLSQL (oraclesql).
Press F1 to open Command Palette and select Oracle:Connect from the drop down
Select New Connection using TNSNAMES.ORA
Select the alias name you wish to connect to
Enter user id and then press return. I connected as the HR user.
Enter password and then press return
Save connection info if desired
Provide profile name or press return for default name
Once you have successfully connected the VS Code status bar will show pertinent information:
Construct the SQL statement you want to execute. Using the schema name followed by a period to utilise intellisense to view and select available database objects.
To execute the statement you can use the CTRL + E keyboard shortcut or select the statement, right click and choose Execute Query. The results are displayed in a new tab.
Create or alter database objects
Construct the statement required; CREATE TABLE, CREATE PACKAGE, ALTER USER…
Execute the statement using the same method used for querying. Once the statement has completed, the results are displayed in a new tab.
Executing PL/SQL is identical to the methods above; construct the statement and use CTRL + E to execute.
A limitation at the time of writing is that I have not yet found a way to inspect output from a call to dbms_output.put_line. So whilst I can see that the anonymous block has executed successfully, the expected output of “Hello World” is not shown.
I will update this guide once I discover this feature or it becomes available.
The Reload button in Chrome has three options. If you open the Chrome DevTools (F12) the three options are visible by holding down the “Reload this page” button.
Windows keyboard shortcut: F5 or CTRL + R
Reloads the current page.
Windows keyboard shortcut: CTRL + Shift + R
Reloads the current page but ignoring the cached content.
Empty Cache and Hard Reload
Windows keyboard shortcut: Not available
This option empties the entire Browser cache and not just the parts for the current page. Given the impact of this option it makes sense that there is no keyboard shortcut which might accidentally trigger this option.
Scott Hanselman’s keynote at NDC London 2019 where he first made me (and half the hall if the sounds of “ooooo’s” is anything to go by) of this functionality in Chrome
This answer from Stack Overflow. Accessed 23/05/2019
This answer from Stack Overflow. Accessed 24/05/2019
I have wanted to volunteer to help run a Code Club since first hearing about it back in June 2013 thanks to this episode of the Ubuntu Podcast where they interviewed one of the founders; Linda Sandvik
The universe had different plans for me for a while but it was in the back of mind that I needed to at least try one term of teaching kids to code even though I have never worked with children or have done any sort of volunteering.
Code Clubs are usually run as an after school club and that was a problem or what I thought was a problem. As a freelancer I am usually at a client’s site and leaving early once a week, every week is not usually an option. Whilst working at one client, I met a Code Club volunteer who was helping run a Code Club from a library which started at a more achievable time.
Finding this out coupled with my visit to a Code Club session made me double down my efforts to become a volunteer.
I got in touch with my local library with a view to getting a Club started and met with the Library outreach worker who was also enthusiastic about getting a Code Club up and running. So fast forward to January 2019 and the first Code Club where I will be volunteering is 24 hours away.
I remember being very nervous before the first session. I had prepared myself as much as I had thought possible. I had completed the first Scratch project, Rock Band so many times that I could do it from memory and probably without looking at the screen. On arrival I met with the two other volunteers and the Library outreach worker. I had initially thought there would be too many volunteers for the 8 children but like most assumptions this turned out to be incorrect.
Once the first session got under way and the questions from the children came in the time passed quickly and soon it was over. Wow. I felt like I was walking on air on the way back to the car. It had been an amazing experience and one that I had waited many years for. I felt I had achieved something after that first session.
I was still nervous the following week for the second session although noticeable less than the first session. By week three the nerves although still present were being replaced by getting excited and looking forward to the upcoming session. I was still completing that weeks Scratch project many times and and trying to second guess what questions would come up. However I was starting to see there wasn’t much value in doing this because the children were rarely were interested in creating identical copies of the Scratch project for that week.
Instead they took the projects in all sorts of weird and wonderful directions. It was magnificent and something I wasn’t prepared for at all. The children’s technical ability is amazing. They are rarely phased by new things being introduced and all were very comfortable in using a computer.
Some weeks were more successful than others. The most popular weeks were the gaming projects especially Boat Race when we arranged competitions with the other volunteers and children to get the best time. It was also my favourite session out of the seven.
All too soon the Club had reached the seventh and last session of our first term. There was a huge appetite from both children and parents to continue so after a couple of months break I will be back starting again in May for another term.
The out of the box behaviour of SQL Developer is that SQL worksheets share the same connection.
What does that mean?
If you perform a insert, update or delete in one worksheet without issuing a commit, the result(s) can be viewed in another shared worksheet. This can be illustrated by the following example.
After launching SQL Developer and connecting to a database, I start two new SQL worksheets
The next two screen shots show that for both SQL worksheets that the table t is empty
Next using using the first worksheet, an insert into table t operation is performed. No commit has been issued.
Navigating to the second worksheet and querying the table again and the newly inserted row can be seen.
It is possible to open a SQL Worksheet with a dedicated database connection. In SQL Developer this is called an unshared connection.
From an existing worksheet the keyboard shortcut on Windows is CTL + SHIFT + N. This will only work from a shared worksheet. It is not possible to open a new unshared worksheet from an existing unshared worksheet.
The following screenshots demonstrates how an unshared connection differs from a shared connection.
Two shared SQL Worksheets: hr and hr~1 are started along with and one unshared: hr (Unshared). In the first worksheet I perform an insert operation which has not been committed.
Switching to the second shared worksheet, the uncommitted record is shown after querying the table.
Moving to the unshared connection and querying the table no row is returned.
The unshared worksheet is working as expected as a new session will not see uncommitted records.
Jeff Smith, the Oracle Product manager for SQL Developer has a superb blog where he shares many SQL Developer hints and tips. One of the posts Opening Unshared Worksheets was the inspiration and source for this post.
NDC London 2019 managed the impossible by being even better than last year.
How is that possible? Everything just seemed more ++. The facilities, the choice of food, snacks and beverages, the swag socks. Sure sometimes the lines for food were long during the traditional lunch hours and the halls were very busy but you could always find a place to talk or pull out your laptop.
I attended 21 talks during the three days and I took away at least one thing from each one that is going to make me a better developer.
This year there were many talks that were focused on the life of the developer and the nonsense that he or she finds themselves in. From the shocking state state of agile, to dealing with incompetent managers and finding themselves thrust into leadership roles
The slides used during talks are worth mentioning. One of the things speakers at NDC don’t do is to use them as a crutch or read from them. They are used sparingly and only then to add something to the talk. They are usually innovative in their design or enjoyable to look at. Fortunately both bullet points and Death By PowerPoint presentations are not on the agenda.
My Top four
My favourite talks this year were
Friday’s key note – Scott Hanselman
Everything is Cyber-broken – Scott Helme & Troy Hunt
Leadership Guide for the Reluctant Leader – David Neal
Insecure Transit – Sam Newman
It is worth keeping an eye on the NDC site so when the videos of the presentations appear I recommend watching these. Scott and Troys talk was not recorded because it was part of the after show party.
My Conference Agenda
Keynote: Welcome to the Machine – Hadi Hariri
Reading Other peoples Code – Patricia Aas
Teaching New Tricks – How to enhance the skills of experienced developers – Clare Sudbery
Insecure Transit – Microsoft Security – Sam Newman
Why Your ASP.NET Core application won’t scale – Damian Edwards, David Fowler
Avoiding the Agile Alignment Trap – Mike Long
Hack To The Future – Troy Hunt
ComSci and My Day Job – Rob Conery
Leadership Guide for the Reluctant Leader – David Neal.
Beyond Developer – Dan North
Think like a Trainer: Improving Your Communication Skills – Olivia Liddell
ML.NET for Developers without any AI experience – Lee Mallon
A Practical Guide to Deep Learning – Tess Ferrandez-Norlander
Dot Net Rocks Live with Christine Yen
Everything is Cyber-broken – Scott Helme & Troy Hunt
Keynote: The Microsoft Open Source Cinematic Universe Phase 2 – Scott Hanselman
Ctrl-Alt-Del: Learning to Love Legacy Code – Dylan Beattie
Versioning 1.0.1 – Jon Skeet
Solving Diabetes with an Open Source Artificial Pancreas – Scott Hanselman
Crash, Burn, Report – Scott Helme
Deep Learning in the world of little ponies – Galiya Warrier