Vscode-sqltools: Oracle DB support

[Danieleeee]

This is a guideline to help us to understand SQLTools Issues.

You don't need to stick to this, but please try to guide us to reproduce the errors or understand your feature requests.

Issue Type

• [x] Feature Request
• [ ] Bugfix/Errors
• [ ] Questions
• [ ] Other

Prerequisites (For bugfixes)

• [ ] Are you running the latest version?
• [ ] Did you check the logs?
• [ ] Did you check the Setup?

Description

Hi, it's possible to use it with Oracle DB?

[mtxr]

Sure, it will be developed soon, ok?

[Danieleeee]

Thanks

[mtxr]

What are the most needed features for you?

[Danieleeee]

In order of importance:

• _Code completion_ and _go to definition_ in packages
• Run SQL queries
• Format output of query (Tables)

[callamaris]

Browsing database objects like tables, packages, views etc. (View user_objects/dba_objects) would be very usefull.

also a command to open&compile packages

[davidnussio]

What do you think about knexjs to minimize effort to support multiple databases?
I tried to implement a proof of concept api dialect for oracle with knexjs and it seems to work.

[mtxr]

Hey guys, could you please submit a pull request of this feature?

As you are a user that understands the needs for Oracle db, you could follow this commit mtxr/vscode-sqltools@6cc9774d867b70054b5902e7d0846d2366527012 and use this lib https://github.com/oracle/node-oracledb to accomplish the same result that I've got with Postgres. Is it ok?

They have a lot of examples in their repository. As you can see at the commit, it's not difficult, it's almost to define the queries it needs to run.

Thanks!

[Danieleeee]

Hi,
there is an extension that can do this: Oradew
I think that is better than a pull request. You can start from it.
You can see this file for the DB connection: db.js
If you need some queries in PL/SQL for table/column/object description-information, you just have to ask.

[Danieleeee]

These are some select that can be used:

function describeTable(err, connection) { if (err) { console.error(err.message); return; } connection.execute( 'Select * from dba_tables where upper(table_name) like upper(:table_name) and owner in (:user_owner)', ['%siu%serv%sogg%','UTE'], // bind value function(err, result) { if (err) { console.error(err.message); doRelease(connection); return; } console.log(result.rows); doRelease(connection); } ); }

function columnInformation(err, connection) { if (err) { console.error(err.message); return; } connection.execute( ' Select * from dba_tab_columns where upper(TABLE_NAME) = upper(:table_name) and upper(COLUMN_NAME) = upper(':column_name') and owner = :user_owner', ['SIU_sii_RICHIESTe','stato','UTE'], function(err, result) { if (err) { console.error(err.message); doRelease(connection); return; } console.log(result.rows); doRelease(connection); } ); }

[Danieleeee]

I see that in the Sublime version SQLTools you have support for Oracle DB.
Why in VScode version it isn't planned? :(

[mickeypearce]

Hey!
The Node-oracledb lib uses native modules binaries which complicates the installation of extension as it requires a rebuild for a specific version of Node.js (electron) inside vscode.

Is that a problem?

[Danieleeee]

Hi Mikey,
I think that if you realize a PR it will be taken into consideration.
Better to have some limitation with the node version that don't use the Oracle DB at all.
Thanks for you help in this issue.

[mickeypearce]

:point_right: #88.

[Danieleeee]

@mtxr , are you able to marge #88 to main build?
Thanks

[mtxr]

Closed in #120

[DanRic]

Hi, is possible to use this marge?
Add Oracle support and SQLite #120

[mtxr]

I'm wrapping up the code and writings some docs. It will be released during the weekend, ok?

[DanRic]

OK, Thanks!

[quenck]

@mtxr I'm desperately waiting for that feature... Please release v0.17.x. Thx!

[mtxr]

@quenck sorry for that; It will be released today for sure! I just took some time to do some polishing.

[DanRic]

Hi,
installing oracledb dipendence I have this error:

[coding4smart]

Hi

ApplicationInsights:CorrelationIdManager [ { Error: connect ETIMEDOUT *
at TCPConnectWrap.afterConnect [as oncomplete] (net.js:1163:14)
errno: 'ETIMEDOUT',
code: 'ETIMEDOUT',
syscall: 'connect',
address: '*',
port: 443 } ]
Request connection/ConnectRequest received
Request connection/ConnectRequest received
Request connection/ConnectRequest received
Request connection/GetConnectionsRequest received
Request connection/ConnectRequest received
Request connection/ConnectRequest received

Attention

getFunctions not implemented for OracleDB

#

Request connection/RefreshAllRequest received

Attention

getFunctions not implemented for OracleDB

#

Error: Error: NJS-040: connection request timeout
at Pool.onRequestTimeout (/Users/*/.vscode/extensions/mtxr.sqltools-0.17.13/node_modules/oracledb/lib/pool.js:146:38)
at Timeout._onTimeout (/Users/*/.vscode/extensions/mtxr.sqltools-0.17.13/node_modules/oracledb/lib/pool.js:209:28)
at ontimeout (timers.js:424:11)
at tryOnTimeout (timers.js:288:5)
at listOnTimeout (timers.js:251:5)
at Timer.processTimers (timers.js:211:10)

I'm connecting remote db.Tested the connection works in NodeJS with oracledb module and query returned rows of data.

Thanks

[janosimas]

Hi,
installing oracledb dipendence I have this error:

You may be behind a restricted network, check this link: https://stackoverflow.com/a/21385322

[Danieleeee]

Thanks, it works!
Now I have this errors when I try to connect:
ERROR: Error: NJS-040: connection request timeout WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5) WARN: rejected promise not handled within 1 second: Error: NJS-040: connection request timeout WARN: stack trace: Error: NJS-040: connection request timeout at handleResponse (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:919:48) at processMessageQueue (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:746:17) at Immediate.module.exports.setImmediate (C:\Users\dricci\.vscode\extensions\mtxr.sqltools-0.19.4\extension.js:730:13) at runCallback (timers.js:696:18) at tryOnImmediate (timers.js:667:5) at processImmediate (timers.js:649:5)