Oracle Developer Tools for VS Code
Execute SQL and PL/SQL in Visual Studio Code
I hear it every day: Developers love to use Visual Studio Code, the multi-platform and open source code editor that runs on Linux, macOS and Windows. There’s 11 million users and nearly 20,000 extensions available in the Visual Studio Code Marketplace which provide customized editors and language support for Python, Java, C#, Node.js and well, just about any language and framework.
Over a year ago we introduced Oracle Developer Tools for VS Code which is a free extension for Visual Studio Code that enables editing and execution of SQL and PL/SQL for Oracle Database and Oracle Autonomous Database along with many other features. Our extension is often used by developers as part of their workflow. For example, Python developers using the Python extension use Oracle Developer Tools for VS Code while they are coding to browse the Oracle Database, view and change data, run scripts and test ad hoc SQL. Then they can check in their SQL scripts alongside their Python code using Visual Studio Code’s integrated source control.
We recently released an update (version 19.3.3) which adds a lot of new features and improves existing ones. If you’ve already been using our extension, I hope you will get the update to see the many enhancements which came from extensive community feedback. Today, I’d like to take this opportunity to step though the product as a whole, starting with how to install it.
Get it from the Visual Studio Marketplace
Installing Oracle Developer Tools for VS Code is easy and takes about 15 seconds from start to finish. Just open the Extension Marketplace from within VS Code and type “Oracle” in the search bar. Scroll until you see Oracle Developer Tools for VS Code, click it and then press the install button. A few seconds later you will get a message that you’re done. Restart Visual Studio Code and you are ready to go.
Connect to Oracle Autonomous Database
Click the large database icon on the left side of VS Code. That will open the Oracle Explorer pane. Click the plus sign to open the connection dialog.
The connection dialog has a Connection Type drop down with several choices:
- Basic: You provide the database’s host name, service name and port number
- TNS Alias: Accepts a connection alias as defined in your network config files
- Advanced: Takes Easy Connect connection strings or connect descriptors
- ODP.NET Connection String: Uses the same syntax as the Oracle Data Provider for .NET
For Oracle Autonomous Database, we first download client credentials and unzip them into a directory. These credentials will include our wallet file. Then, in the connection dialog, we will choose the TNS Alias connection type and we’ll also provide the path to our wallet file location.
In the screenshot above, you’ll notice the Proxy User Name and Proxy Password fields which make it easy to connect if you are using a proxy user. There’s also a Current Schema drop down so that if you connect as user A but have rights on schema B you can use this drop down to set schema B as the default schema that the tool will use. More on that later in this article.
If you are connecting to an on premises database such as Oracle Database Express Edition, you can use the Basic connection type, like so:
Browse your schema with Oracle Database Explorer
Once connected, you’ll see an icon representing the connection in Oracle Explorer. Click it and you’ll see a tree control showing your database schema.
You can expand the tree control to explore the various schema object types. For each schema object there are menus to perform operations. For example, if you click on a table, you can select the menu item Show Data.
This opens a results window with the data. You can save the data as CSV or JSON formats as well.
Stored procedures, functions and packages have menus for editing the PL/SQL in the database:
This will open the Oracle SQL editor and once editing is complete, you can save the changes back to the database. (We’ll go into more detail about some of the SQL editor features and show how you can run SQL scripts and execute adhoc SQL in a moment.)
If you make a mistake while editing, the Problems tab in Visual Studio Code will show the error and you can click on it to take you to the line of code with the error.
Finally, you can test out your procedure or function by using the Run menu item. This opens a dialog to accept input parameters. The output of this particular function is a ref cursor so I press the fetch button to fetch the result.
Browsing Other Schemas
Many developers connect as one user, but then spend much of their time using other schemas. In the first connection dialog screenshot above, I showed you the Current Schema dropdown where you can set another schema to be used as the default schema. Another option is to browse using the Other Users node in Oracle Explorer:
Execute SQL and PL/SQL with the Oracle SQL Editor
Click on the connection node in Oracle Explorer and select the Open New SQL File menu item. This will open an empty file in the Oracle SQL editor and will associate it with the database connection. You could also use Open Existing SQL File menu item if you already have a script you’d like to edit or run.
The new SQL file is created and automatically associated with the Oracle Developer Tools for VS Code extension and also with this particular database connection. You can see this association by looking at the status bar at the bottom of Visual Studio Code.
The Oracle SQL editor opens and you can begin typing some SQL or a PL/SQL block. You might notice some interesting things popping up on the screen as you type.
These popups are autocomplete suggestions that help remind you of schema object names and attributes and to speed up typing. For example, if I want to query against the EMPLOYEES table and I type “LA” in the select list, it will give me some options including LAST_NAME as a select list item.
If I am calling a stored procedure or function, autocomplete provides a template complete with data types for me to fill out. If I don’t want that, I can start typing parameter values, and it will display the parameter types as I type.
If you type a schema name and then dot (“.”), the editor will show the list of the schema objects available. And a dot after a schema object will show the appropriate sub-objects. For example, a dot after a table name will show columns of a table. A dot after a package name will show procedures and functions in a package. This is known as intellisense.
Finally if you type “oracle”, you’ll see a list of code snippets available to you in case you’d like a template for create a PL/SQL package, for example. When you select one, the snippet is placed in your SQL file.
Once you’ve got a line of SQL or a PL/SQL block you wish to execute, you can right click and choose either “Execute SQL” or “Execute All”. The first will execute the line of SQL or a PL/SQL block that the cursor is on, or whatever line(s) you have selected. “Execute All” will execute everything in the file. If you are like me, after a few times of using the menus for this, you will probably start using the shortcut keys all the time (Ctrl-E and Ctrl-R),
SQL*Plus commands
You may I have noticed that I used some SQL*Plus commands (SET AUTOCOMMIT) and SQL*Plus substitution variables (DEFINE) in the script above. SQL*Plus commands provide useful functionality in scripts such as:
- Connecting and disconnecting
- Enabling and disabling autocommit
- Running other SQL*Plus scripts
- Describing database object metadata
- Defining and using substitution variables
- Defining and using bind variables
- Saving script output to a file
Oracle Developer Tools for VS Code supports a subset of the SQL*Plus commands. If you try to run a script that contains (or it’s child scripts contain) unsupported commands, you’ll get a warning asking if you want to proceed. To learn more about SQL*Plus commands in this extension, please head over to our Using SQL*Plus Commands with Oracle Developer Tools for VS Code page.
SQL Bookmarks and History
If you are like me, you have SQL statements and scripts you repeatedly execute over and over again. Oracle Developer Tools for VS Code has a nice feature that lets you save snippets or whole scripts as SQL Bookmarks.
To save a bookmark, select the code you wish to bookmark in the editor, right click and select Bookmark SQL. Provide a folder name to put the bookmark in, provide a name for the bookmark and hit return.
You can view your bookmarks in Oracle Explorer in the Bookmarks tab. You can hover over a bookmark to preview it, and you can also use the menu to Run, Copy to Editor, and Open in Editor.
In the Oracle Explorer History tab (which you can also see in the screenshot above), you can find your command history for the lifetime of your Visual Studio Code session. Similar to the way SQL Bookmarks work you can hover to view the SQL, and use menu items Run, Copy to Editor, and Open in Editor. There’s also a menu item Bookmark SQL which will store the item as a bookmark.
Next Steps
This has been a whirlwind tour of some of the key features in Oracle Developer Tools for VS Code. I hope you will try it out yourself. To learn more, please view our Getting Started Quickstart. You can also join us over at the Oracle Developer Tools for VS Code online forum and view the product page over at the Visual Studio Marketplace.
