Oracle Script Workspace

Oracle Script Workspace turns VS Code into a focused Oracle SQL workbench. It is built for developers, DBAs, implementers, and support engineers who keep SQL in real files and need database connections, execution results, object metadata, and export tools close to the editor.

It is not a temporary worksheet clone. A .sql file can be bound to a saved connection by file name, executed repeatedly, inspected with object metadata, and shared without leaking local connection choices or passwords.

What It Does

• Run Oracle SQL from normal files: execute the current statement, the selected SQL, or a full script.
• Handle multi-statement scripts: keep every statement result in one result area with tabs and scrolling.
• Keep transactions explicit: DML stays uncommitted until Commit or Rollback; DDL/DCL and explicit commit/rollback statements are handled as clean success states.
• Manage Oracle connections: create custom connections, parse TNS aliases, paste raw connect descriptors, test connections, duplicate profiles, and assign Oracle Client profiles.
• Support modern and legacy Oracle clients: use Thin mode for most Oracle Database 12.1+ connections, or Thick mode with Oracle Client libraries for older databases and Oracle Net client-side configuration.
• Work through SSH jump hosts: use fixed SSH tunnels for normal custom connections, or Dynamic Thin forwarding for RAC/listener redirect scenarios.
• Browse database objects: explore schemas, tables, and views from the activity bar.
• Inspect object details: view columns, indexes, comments, source/DDL, and table DDL that includes create table, index, and comment statements.
• Complete SQL intelligently: complete table/view names and alias-qualified columns for SQL files bound to a connection.
• Export data: copy or export CSV, and export SQL INSERT statements for simple single-table query results.
• Keep secrets out of files: Oracle passwords, SSH passwords, and private-key passphrases are stored with VS Code SecretStorage.

Choosing Thin Or Thick Mode

Oracle Script Workspace uses node-oracledb under the hood.

Official node-oracledb documentation states that Thin mode directly connects to Oracle Database 12.1 or later and does not need Oracle Client libraries. Thick mode requires Oracle Client libraries 19 or later, and can connect to Oracle Database 11.2 or later depending on the Oracle Client library version:

• https://node-oracledb.readthedocs.io/en/latest/user_guide/installation.html
• https://node-oracledb.readthedocs.io/en/latest/user_guide/appendix_a.html

Connection Management

Open Oracle Script Workspace > Manage Connections to maintain all connection profiles in one place.

Supported connection types and options:

• Custom connection with host, port, service name, or SID.
• Custom connection with raw Oracle Net connect descriptor.
• TNS alias discovery from one or more tnsnames.ora files.
• Saved login profiles for parsed TNS aliases.
• Thin and Thick Oracle Client profiles.
• Optional SSH tunnel settings for custom connections.
• Test, edit, duplicate, and delete connection actions.

Example custom connection:

{
  "oracleWorkspace.connections": [
    {
      "name": "DEV_KGDB",
      "type": "custom",
      "host": "127.0.0.1",
      "port": 1521,
      "serviceName": "orclpdb1",
      "username": "aaaa",
      "clientProfileName": "Default"
    }
  ]
}

Example raw connect descriptor for RAC, load balancing, failover, or existing Oracle Net strings:

{
  "oracleWorkspace.connections": [
    {
      "name": "RAC_UAT",
      "type": "custom",
      "username": "aaaaa",
      "connectDescriptor": "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.25.xxx.xxx)(PORT=1526))(ADDRESS=(PROTOCOL=TCP)(HOST=10.25.xxx.xxx)(PORT=1526))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=aaaora)))",
      "clientProfileName": "Default"
    }
  ]
}

SSH Tunnels And Dynamic Forwarding

Oracle Script Workspace has two SSH forwarding modes for custom connections.

Fixed SSH Tunnel

Use fixed forwarding when the database endpoint is known before login.

• Simple custom connections forward one database host/port through the SSH server.
• Descriptor connections can create local forwards for each TCP (ADDRESS=...) entry and rewrite those ADDRESS host/port values to local ports.
• Works for ordinary single-host, service-name, SID, and known multi-address descriptor scenarios.
• SSH authentication supports password, private key, and agent.

Dynamic Thin SSH Forwarding

Use Dynamic Thin forwarding when Oracle Thin opens additional TCP targets during login or failover, such as RAC/listener redirect environments.

• Requires a custom Connect Descriptor connection.
• Requires a Thin Oracle Client profile.
• Keeps the original descriptor intact.
• Routes each non-local TCP target opened by the Thin driver through the SSH connection on demand.
• Helps when the listener returns internal VIPs or node addresses that are reachable only from the jump host.
• Does not support Thick mode because Thick connections are handled by Oracle Client native libraries outside the Node.js network patching layer.

Saved tnsnames.ora alias logins are not automatically rewritten through the built-in tunnel layer. If an alias must be used through SSH, paste the alias descriptor into a custom Connect Descriptor connection.

SQL Execution And Transactions

The extension provides SQL Developer-style and PL/SQL Developer-style keymap profiles. You can run the current statement, selected SQL, or the full script.

Execution behavior:

• DML runs with autoCommit: false.
• Commit and Rollback act on the active script connection.
• Explicit commit or rollback statements in a batch clear the uncommitted status.
• DDL and DCL statements are treated as auto-committed success results.
• Explain Plan runs EXPLAIN PLAN and displays DBMS_XPLAN output.

Results And Export

The result view is designed for repeated SQL work:

• Single query results render as a grid.
• Multi-statement batches render as tabs.
• Non-query statements render as success/update/error items.
• Click row numbers to select rows, click column headers to select columns, right-click to copy selected data.
• Large result sets stay scrollable inside the panel.
• oracleWorkspace.maxRows defaults to 1000.
• Fetch Next reruns the original SQL with an Oracle-compatible paging wrapper.
• Fetch All reruns the original SQL without a row limit.
• CSV copy/export is available from query grids.
• SQL INSERT export is enabled for simple single-table query results and disabled for joins, subqueries, set queries, and expression-only projections.

Object Browser And Object Details

The activity bar includes an Oracle connection tree and object browser. It can browse schemas, object groups, tables, and views through the active Oracle session.

Object Details provides:

• Column metadata with readable Oracle types.
• Index list and indexed columns.
• Table and column comments.
• Source or DDL tabs where applicable.
• Table DDL that includes create table, index DDL, and comment statements.
• Copy and open-as-SQL actions for DDL/source content.

SQL Completion

SQL completion is connection-aware. When a .sql file is bound to a connection, the extension can query Oracle metadata and suggest:

• Table and view names in object-name positions such as FROM, JOIN, UPDATE, INSERT INTO, MERGE INTO, GRANT ... ON, and DDL/DCL object contexts.
• Columns for resolved table aliases, such as a..
• Target-table columns for insert/update/DDL column-list contexts.
• Oracle keywords when no connection metadata is available.
• Over 90 Oracle built-in function names such as SUBSTR, NVL, DECODE, TO_CHAR, and commonly used aggregates and analytics.

Function completions show their parameter signatures in the suggestion list and display Chinese usage documentation in the detail panel.

Object completion uses refreshed metadata for narrower prefixes, so continuing from s to sh can retrieve SHARES instead of relying on a stale broader candidate list.

Language And Marketplace Text

The extension UI follows the VS Code display language. English is the fallback language, and Simplified Chinese strings are provided through:

• package.nls.json
• package.nls.zh-cn.json
• l10n/bundle.l10n.json
• l10n/bundle.l10n.zh-cn.json

The Marketplace extension details page uses the packaged root README.md. It does not reliably switch the README body according to the user's VS Code display language, so this README includes English content and a Chinese summary.

中文说明

Oracle Script Workspace 是一个面向日常 Oracle 数据库开发的 VS Code 扩展。它把普通 .sql 文件变成可长期使用的数据库脚本工作区：脚本可以按文件名绑定连接，直接执行当前语句、选中语句或整个脚本，并在同一个界面里查看结果、对象结构、DDL、字段注释和导出数据。

它当前包含的主要能力：

• SQL 执行、批量脚本执行、Explain Plan、手动提交/回滚。
• 连接管理、自定义连接、TNS alias、原始 connect descriptor、Thin/Thick 档案。
• 固定 SSH 隧道和 RAC/listener redirect 场景下的 Thin 动态 SSH 转发。
• 查询结果分页、Fetch Next、Fetch All、CSV 导出、简单单表查询导出 SQL INSERT。
• 表/视图名补全、别名字段补全、目标表字段补全。
• 对象树、对象详情、字段类型、索引、注释和完整表 DDL。
• 密码和 SSH 凭据使用 VS Code SecretStorage 保存，不写入 SQL 文件或工作区配置。

数据库版本选择：

• Oracle Database 12.1 及以上：优先使用 Thin 模式，不需要安装 Oracle Client。
• Oracle Database 11.2：使用 Thick 模式，并配置 Oracle Client 19+。
• 更老的 Oracle Database：当前版本不作为推荐支持目标。
• 需要 sqlnet.ora、wallet、完整 Oracle Net 客户端行为时：使用 Thick 模式。
• 需要 Dynamic Thin SSH 转发时：必须使用 Thin 模式和 Connect Descriptor。

Licensing

The extension includes a license management entry point. The current build keeps the database workflow available while commercial licensing is wired in. Future paid licensing can be connected behind LicenseService without changing saved database connections or SQL scripts.

Support

The source repository is private while commercial licensing work is prepared. For issues, compatibility feedback, or licensing questions, contact:

• guanyezhui@163.com