Oracle Database Management in VS Code

DBCode is an Oracle extension for VS Code: connect to Oracle Database, browse schemas and data, write SQL with schema-aware autocomplete, and edit rows visually without leaving your editor. Install DBCode to get started, or see how it compares to standalone database tools.

Overview

Oracle is a relational database management system (RDBMS). It is designed to handle large volumes of data and is widely used in enterprise applications. Oracle provides a range of features and capabilities, including support for complex queries, indexing, and geospatial data.

Supported Connection Methods

DBCode supports the following connection methods for Oracle:

• Thin Client
• Instant Client/Thick Client

Connecting

To connect to Oracle, follow these general steps:

1. Open the DBCode Extension: Launch Visual Studio Code and open the DBCode extension.
2. Add a New Connection: Click on the “Add Connection” icon.
3. Complete new connection form: Choose Oracle as the type, and enter the required information.
4. Connect: Click save to connect to your Oracle database.
5. Start Managing Your Databases: Once connected, you can start managing your databases directly from Visual Studio Code.

For detailed instructions on connecting to Oracle, refer to the Connect article.

Debugging

DBCode can debug deployed standalone Oracle procedures and functions with the native VS Code debugger. It uses Oracle’s classic SYS.DBMS_DEBUG package and currently requires a node-oracledb Thin mode connection. Thick or Instant Client connections can still run ordinary queries, but they cannot start an Oracle debug session.

See Debugger for how to start a session, set breakpoints, step, inspect the call stack, and use watches.

Prepare the database user

The connected user needs DEBUG CONNECT SESSION. A database administrator can grant it according to the site’s access policy:

GRANT DEBUG CONNECT SESSION TO YOUR_USER;

For a routine owned by another schema, the connected user also needs effective EXECUTE and DEBUG privileges on that exact object:

GRANT EXECUTE ON TARGET_SCHEMA.YOUR_PROCEDURE TO YOUR_USER;
GRANT DEBUG ON TARGET_SCHEMA.YOUR_PROCEDURE TO YOUR_USER;

The routine owner does not need these object grants for its own routine. DBCode checks the effective setup before launch. It never grants privileges or recompiles a routine automatically.

Compile the routine for debugging

The exact deployed procedure or function must report the first two settings, and PLSCOPE_SETTINGS must include IDENTIFIERS:ALL:

• PLSQL_DEBUG=TRUE
• PLSQL_OPTIMIZE_LEVEL=1
• PLSCOPE_SETTINGS includes IDENTIFIERS:ALL

For a procedure, compile it with:

ALTER PROCEDURE YOUR_SCHEMA.YOUR_PROCEDURE
  COMPILE DEBUG
  PLSQL_OPTIMIZE_LEVEL=1
  PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

Use ALTER FUNCTION for a function. Recompile after replacing the routine if its settings no longer match. IDENTIFIERS:ALL is required because classic DBMS_DEBUG reads a variable by name but does not enumerate Locals. DBCode uses PL/Scope metadata and the exact deployed source to build the complete supported scalar Locals list.

Supported values and inspection

Oracle debugging supports IN, OUT, and IN OUT parameters and function returns for these scalar families:

Oracle type	Argument format
NUMBER	Canonical decimal text with up to 38 digits, such as -123.45
VARCHAR2	Text up to 32,767 UTF-8 bytes
BOOLEAN	true or false
DATE	YYYY-MM-DDTHH:mm:ss
TIMESTAMP	YYYY-MM-DDTHH:mm:ss.ffffff with exactly six fractional digits
TIMESTAMP WITH TIME ZONE	YYYY-MM-DDTHH:mm:ss.ffffff+HH:mm with a numeric offset

Use (null) in the argument panel for SQL NULL. A scalar column %TYPE declaration is supported when DBCode can resolve the exact column to one of the built-in families above.

Debug Source is the exact ALL_SOURCE text published as a read-only document. Breakpoints, Continue, Step Over, Step Into, Step Out, nested call stacks, selected-frame Locals, bare-variable watches, results, and up to 1,000 lines of DBMS_OUTPUT per invocation are supported. Locals are read-only. A declared value that Oracle cannot read yet is shown as unavailable instead of being omitted.

Current limits

• Package routines, wrapped source, and Thick mode are not supported.
• Records, collections, cursors, LOB streams, other structured values, and TIMESTAMP WITH LOCAL TIME ZONE are not supported.
• Variable mutation is not supported.
• A paused routine can hold transaction locks. Continue or stop the session when inspection is finished.
• Stop is bounded while the extension host still owns its two Oracle sessions. If the extension host process is lost while the routine is actively running, the database work can continue until Oracle terminates the session or an administrator ends it.

Troubleshooting Thin Client Errors

DBCode uses the node-oracledb Thin mode by default. Some database versions require the Thick/Instant Client driver instead and the connection attempt fails with an error similar to:

NJS-138: connections to this database server version are not supported by node-oracledb in Thin mode

To switch the connection to the Instant Client/Thick mode:

1. Download the appropriate Oracle Instant Client package for your operating system from the Oracle Instant Client downloads page.
2. Extract the archive to a local folder that DBCode can access (for example ~/oracle/instantclient_19_x). Keep the folder path handy.
3. Edit your Oracle connection in DBCode. In the connection form, change the driver to Instant Client / Thick and set the client library directory to the folder you extracted in the previous step (the folder that contains libclntsh on macOS/Linux or oci.dll on Windows).
4. Save the connection and reconnect.

Linux-specific Setup

On Ubuntu and other Linux distributions, the Oracle Instant Client requires additional dependencies. If VS Code crashes or restarts when connecting, install the required library and set up the environment variables:

# Install the required library
sudo apt-get update
sudo apt-get install libaio1t64

# Create a symbolic link for compatibility (some Ubuntu versions)
sudo ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1

Add these lines to your ~/.bashrc (adjust the path to match your Instant Client location):

export ORACLE_HOME=/path/to/instantclient_23_8
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH

After editing .bashrc, restart your terminal or run source ~/.bashrc, then restart VS Code.

Note: On older Ubuntu versions, the package may be named libaio1 instead of libaio1t64. Try libaio1 if libaio1t64 is not available.

By using Oracle with DBCode, you can connect to your Oracle databases, query and manage your data, and visualize your results, all directly from Visual Studio Code.

For more information about Oracle, check out Oracle.