mickeypearce / oradew-vscode Goto Github PK

I would like to use the gulp script outside of the vscode extension.

Would it be possible to publish an Azure DevOps Plugin as well as a vscode extension.

There are a number of use cases this would enable for me.

• Automating the creation of multiple git based oradew database projects
• Implementing a devops pipeline for deployment of oracle databases
• Implementing a system that monitors development databases for changes and stores them in source control

@mickeypearce Love your extension btw, great job filling the gaping hole of opensource oracle development tooling.

What changes are needed in order to support latest versions of node js? Is there any dependency thats needs that version?

When trying to Initialize Workspace/Version I am running into those Problems on my Windows 10 machine:

• When having a space in the folder path it wont work
• After fixing the path to have no spaces in it, I'm getting the following error:

> Executing task in folder Oradew_Test: c:\VSCode\data\extensions\mp.oradew-vscode-0.1.0\out\gulp.cmd --cwd \\wacker.corp\user\koellex001\Data\Oradew_Test --gulpfile c:\VSCode\data\extensions\mp.oradew-vscode-0.1.0\out\gulpfile.js --color true --silent true initProject <

Der Befehl "node" ist entweder falsch geschrieben oder
konnte nicht gefunden werden.
The terminal process terminated with exit code: 1
(translated: command "node" is wrong or could not be found)

• When trying to use "npm rebuild" it also doesnt find that command

I haven't worked with Linux systems before but it sounds like these errors are all because I'm running windows and these commands are not there by default.

I'm trying to generate a source from a database. Unfortunately, although the database has objects, none is downloaded and saved to the source. Have output like:
[13:05:36] Using gulpfile c:\Users\rpietka.vscode\extensions\mp.oradew-vscode-0.3.29\dist\gulpfile.js
[13:05:36] Starting 'create'...
[13:05:36] Starting 'createSrcFromDbObjects'...
[13:05:36] Finished 'create' after 18 ms
[13:05:36] Finished 'createSrcFromDbObjects' after 18 ms
[13:05:36] Starting ''...
[13:05:36] Finished '' after 68 ms

Maybe I should set up something extra to make the import work

Hi, how are you?
First of all i need to congratulate you for the great job developing this one, its awsome!

So, i have multiples schemas where i want to control, but, when a use de command Create source from DB only extract the objects from user DEV. I already tried to change the env and the user but its not effective.

I already read Add multi-schema support

When I try to run "Oradew: Import all Sources from DB" im getting "Cannot read property 'toLowerCase' of undefined". All DB-Objects are created as correctly named empty files though.

• Issue Type: Performance
• Extension Name: oradew-vscode
• Extension Version: 0.3.26
• OS Version: Windows_NT x64 10.0.18363
• VSCode version: 1.51.1

⚠️ Make sure to attach this file from your home-directory:
⚠️c:\Users\dricci\AppData\Local\Temp\mp.oradew-vscode-unresponsive.cpuprofile.txt

Find more details here: https://github.com/microsoft/vscode/wiki/Explain-extension-causes-high-cpu-load

I could fix the encoding problem in packages with "package.encoding": "win1252", but in simple SQL-Statements I still got a problem.

For testing I created a script sqlplus\test_encoding-table.SQL

--DROP table test_encoding;
CREATE TABLE test_encoding
AS SELECT 'ÄÖÜ-äöüß-€' AS umlaute FROM DUAL;
SELECT * FROM test_encoding;

The ouput is

> Executing task: C:\Program Files\nodejs\node.exe c:\Users\Truetken-H\.vscode\extensions\mp.oradew-vscode-0.3.26\dist\oradew.js run --env RES_TEST --file D:\git\resus\datenbank\sqlplus\test_encoding-table.SQL --user <Auto> <

Success => RESUS@RES_TEST $D:\git\resus\datenbank\sqlplus\test_encoding-table.SQL
=============================== STDOUT ===============================
Table created.


UMLAUTE
----------
???-????-?

No errors.

When I select on this table with SQL*Plus I see

SQL> SELECT * FROM test_encoding;

UMLAUTE
----------
¿¿¿-¿¿¿¿-¿

When I run the same script using sqlplus, I got no problem.

this is my oradew.json:

{
	"source.input": ["./**/*.sql"],
	"source.encoding": "win1252",
	"source.pattern": {
		"packageSpec": "./packages/{object-name}-spec.sql",
		"packageBody": "./packages/{object-name}-body.sql",
		"trigger": "./trigger/{object-name}-trigger.sql",
		"views": "./views/{object-name}-view.sql",
		"table": "./sqlplus/{object-name}-table.sql"
	},
	"package.encoding": "win1252",
	"compile.warnings": "NONE",
	"compile.force": true,
	"compile.stageFile": false,
	"import.getDdlFunction": "dbms_metadata.get_ddl"
}

and settings.json is still the same as in #49

	"files.encoding": "windows1252",
	"terminal.integrated.shell.windows": "C:\\Windows\\System32\\cmd.exe",
	"terminal.integrated.shellArgs.windows": "/k chcp 1252",
	"oradew.envVariables": {
		"NLS_LANG": "GERMAN_GERMANY.WE8MSWIN1252"
	}

Hi @mickeypearce,

I've noticed that every time that a package or something gets compiled, a new sqlplus session is established, then compiles, and then terminates. This is quite slow as you have to wait for sqlplus initialisation every time. Would it be possible to keep a persistent connection for sqlplus waiting for new compile commands? This way the session would be always active after first compile attempt with no waiting. The idea is keeping an active session for every schema simultaneously every time that you perform a compile command, and following compile commands would execute in the same session with no waiting.

I just came across with this nice extension for vscode and I've been trying it, because I want to leave using sqldeveloper as IDE as it is very basic and a nightmare of bugs, hangs, slow etc. I have some ideas to improve it and I would love help developing it if you need help, as I think that it is near perfect for our needs.

Hi,
is possible to select the DB where to execute current script/procedure?

hello everyone,

im working with vsc and python since a few weeks. stept by step i´m adding new packages to vsc and now i try to run oradew. it would be perfect for my daily developing.

i installed the package and initialized the workspace. now i try to config the file dbconfig.json. after doing this i run the command "Create source from db". unfortunatley that doesn´t work.

following line is in the dbconfig.json:

{"DEV":{connectString":"name in tnsnames", "users":[{"user":"user_name", "password":"user_password", "schemas": "user_schema"})}}

the command runs, no exception, but no DB-Object ist in the file-explorer, all directories src, scripts, test are empty

i tried a long time in many versions, but no success

any help is welcome :-)

What do you think about dropping support for sqlplus? I say it because I plan to add sqlcl liquibase ci/cd compatibility in liquibase, and for me I don't see any advantages of mantaining two different implementations as sqlcl can do everything and more than sqlplus can do.

I gave the output file a name, but it is not used:

it should be "NETAINFAggDati.sql", instead it is called "file_CONF_VMOP_LOTTO_1606411245325.sql"

Hi,
is possible to open in new tab a selected (ctrl+click) package that is written in a file.sql?
It is usefull with plsql-language.

Thanks

Hi,

when using "Initialize Workspace" in an empty folder I'm prompted to create a task.json with default options. What am I doing wrong?
See screen recording...

Thank you

When I compile anything (package, package body, view, table, triger...), I always have this error. Always on the same line, even if there is a comment on a couple of lines.
When i run the file as script (F5) always it success
Success => USER@DATABASE script.sql
=============================== STDOUT ===============================
2
But nothing will be created / modified on the database.

When I import a DB package, using the "import selected object" function, the encoding is not respected. You can test using the WE8MSWIN1252 encoding

It would very nice adding compatibility for more oracle db types and oracle apex applications support. The applications could be exported as one file in a .sql file, and separated by application components at the same time, pages, plugins etc:

This would be the function that could be used for doing so:
https://docs.oracle.com/en/database/oracle/application-express/20.1/aeapi/APEX_EXPORT.html#GUID-6A4628A6-9F86-4394-9938-87A7FFFC7BC8

When installing on Windows with
node-v10.15.1-x64.msi and
instantclient-basiclite-windows.x64-18.3.0.0.0dbru.zip

npm rebuild fails with a download error.

Can you please update the node-oracledb dependency to 3.1 which I believe fixes the download issue?

Thanks,
Jason

This is pretty straight-forward, but it's only happening on one of my schemas.

I am attempting to import the existing tables, sequences, triggers, and packages from a fairly new schema that I'm working in. When I attempt to run the command "Import All Source from DB", the command runs and completes but nothing seems to be happening.

To be specific, I get all of the default folders created - scripts, src, and test, with the functions, package bodies, packages, etc... sub folders under src - but none of the actual database objects that are currently in there are having their DDL populated into the appropriate folder.

I'm not getting any sort of error messages or, well, anything when I run the command. It just completes.

Any ideas?

Hi, great extension.
Can you put more information/examples for configuration.
Whan I press f6 I have this error:
command 'oradew.compileTask' not found

Hi,
is possible to have the possibility to change DB schema?
If I run the command "Run current file as Script" I can run it only in the single schema configured in dbconfig.json file?

Thanks

Would like to pass different template variables per environment in to the scripts.

My use case is for a custom SEND_MAIL package.

v_email_server VARCHAR2(30) := '${config["version.number"]}';

Hi,
is possible to have HoverProvider form DB objects?
Like columns, tables, packages,....
Thanks.

I can't run the task runner to compile files after upgrading to Catalina.

I was getting this error everywhere before but re-installing SQLPlus has resolved it everywhere else e.g. in the terminal.
So, it is correctly set in my profile as the terminal in VS code works (below screenshot).

Hopefully this is something as simple as permissions somewhere in the extensions that need to run external libraries (Catalina has enforced a lot of other permissions) but I'm a little lost with finding a solution.

Hi,
Importing DB objects locally is a nice idea, especially if I want to work with the extension "Language PL/SQL".

The problem is that I have a large DB and it takes me a long time to import all the objects. In addition I need a lot of space on the local disk.
How can I solve the problem?
Thanks

Compile Current File works. But when I got error messages, I can't see them in the problems window. What can I do?

Hi Mickeypearce,
I noticed that when I launch a file as script (f5), it freezes for a few minutes.
The same file launched with SQLPlus (Oracle) is executed instantly.
Is there any configuration to do or is it the extension that takes time to run the script?

Thanks

Hi,
I have a package that if I run as a script (f5) does not give errors.
If I only run the final part (Run Selected statement) of the package, where the grants are, I get errors.

I have similar errors if I don't set the correct econding (utf-8)

When I import a package from the DB, VSCode doesn't handle the word EDITIONABLE.

es. CREATE OR REPLACE EDITIONABLE PACKAGE UTE.PIPPO

The word is correct and the toad recognizes it. If used in VScode it fails to process package symbols.
Maybe it depends on the plsql extension, but would it be possible to remove it from the package import so as not to create errors?

Another problem, when I import a package, the "/" terminator is not placed at the end.
Would it be possible to have it inserted automatically?

Thanks.

Unfortunately we have almost any sql-source in our repository with an uppercased File-Extention, e.g. MYPACKAGE-body.SQL.
Because of this I don't see the "Run Current File As Script (F5)" Button. I can't find even no way to configure this with something like
"source.input": ["./**/*.SQL"]

I'm using VSCode 1.46.1 under Windows 10

Hey there. I'm getting this when initializing the workspace:

Error: ENOENT: no such file or directory, uv_chdir
    at Liftoff.handleArguments (C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\gulp\bin\gulp.js:108:13)
    at Liftoff.execute (C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\liftoff\index.js:203:12)
    at module.exports (C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\flagged-respawn\index.js:51:3)
    at Liftoff.<anonymous> (C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\liftoff\index.js:195:5)
    at C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\liftoff\index.js:165:9
    at C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\v8flags\index.js:110:14
    at C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\v8flags\index.js:38:12
    at C:\Users\x\.vscode\extensions\mp.oradew-vscode-0.0.4\node_modules\v8flags\index.js:49:7
    at _combinedTickCallback (internal/process/next_tick.js:131:7)
    at process._tickCallback (internal/process/next_tick.js:180:9)
The terminal process terminated with exit code: 1

Terminal will be reused by tasks, press any key to close it.

Any tips? Thanks!

Hi,
I Think its important secure the users by a master password or cryptography. Its possible?

I'm using the custom UTL_GENERATE_ORADEW package, the procedure is the one you proposed getDdl.
If I try to import the definition of tables or triggers, I get the following error, given by the procedure dbms_metadata.get_ddl:

ORA-31603: "TABLE_NAME" object of type TABLE not found in the "SCHEMA_NAME" schema
ORA-06512: a "SYS.DBMS_METADATA", line 6478
ORA-06512: a "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: a "SYS.DBMS_METADATA", line 6465
ORA-06512: a "SYS.DBMS_METADATA", line 9202
ORA-06512: a "SIU.UTL_GENERATE_ORADEW", line 726

Importing objects fail when the object contains "SQL" somewhere in the name. In a concrete example, let's imagine a FUNCTION named "UpdateMysqlFromOracle". When trying to import that function (doesn't matter if via Create source from DB or Import current file) the following error appears:

[16:03:45] Starting 'import'...
ORA-31603: object "UPDATEMFROMORACLE" of type FUNCTION not found in schema "SCHEMA_NAME"
ORA-06512: at "SYS.DBMS_METADATA", line 6731
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 6718
ORA-06512: at "SYS.DBMS_METADATA", line 9734
ORA-06512: at line 1
[16:03:46] Finished 'import' after 965 ms

Notice that from MYSQL, YSQL is stripped from the name while the M remains.

Happens with all procedures and functions that follow a similar pattern.

I think the problem is caused in this section of the extension's code:

More precisely, the .sql portion of the pattern is matched against MySQL and ends up getting stripped.

I've got a similar problem.

I use just a simple Script for testing, stored with win-1252 encoding:

COLUMN parameter FORMAT A20
COLUMN value FORMAT A20
SELECT * FROM V$NLS_PARAMETERS
WHERE parameter IN ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');
SELECT USERENV('LANGUAGE') FROM dual;
SELECT 'ÄÖÜ-äöüß-€' AS umlaute FROM DUAL;

In oradewrc.json I have
"source.encoding": "win1252",

and in settings.json:

	"files.encoding": "windows1252",
	"terminal.integrated.shell.windows": "C:\\Windows\\System32\\cmd.exe",
	"terminal.integrated.shellArgs.windows": "/k chcp 1252",
	"oradew.envVariables": {
		"NLS_LANG": "GERMAN_GERMANY.WE8MSWIN1252"
	}

When I Show NLS_LANG in Dos-Shell:

Z:\>echo %NLS_LANG%
AMERICAN_AMERICA.we8mswin1252

When I use sqlplus.exe in the vscode-Terminal, everything is fine:

What can I do?

Originally posted by @true-hb in #48 (comment)

When I do Ctrl+Enter on a SQL statement, it give an error {"error":"No params !"}

The only way i am able to run the statement is to select it and then "Run Selected Statement" - when you have a large SQL statement to run and there are multiple statements in a single file, this becomes quite a chore. Is there some kind of setting that I need to have?

Move CLI to a separate project (submodule) that is publishable on NPM registry.
Use it as a dependency in extension...

Continuation from: #11

Hi,
I'm using oradew but using svn instead of git.
I don't really care about versionning functionnality so i won't ask you to manage svn, but is there a way to hide the notification
"Oradew : "git" command (Git) is required in command-line"
?
I didn't found a generic way in vscode to filter it by extension.
Can you do something about it ?

When I compire package with:

• Run Current file as script (f5)

• Compile Current File

No errors are shown

This line needs env as param:

Originally posted by @mickeypearce in #66 (comment)

I've tried to import all source from db, but i can't change the encoding of imported files. My db encoding is WIN1250 whereas Imported files are encoded in UTF8. How to change it? Property "package.encoding" doesn't change anything in this case.

Hi,
is it possible to customize the terminal output when I run some package or script? Errors and warnings risk not being detected:

problem matcher?
I'm using Run Current File as Script (F5)

Thanks

We often have more than one DDL-commands in file, for example

CREATE OR REPLACE PACKAGE xy AS
...
END xy;
/

GRANT EXECUTE ON xy TO PUBLIC
/

This ist very useful for us, but it's a problem for ORADEW-extension.
After compiling I get the message ERROR PLS-00103: Encountered the symbol "/"

Isn't it possible to support such a command structure in ORADEW?

Within sqlplus or sqlcl you can setup userless and passwordless authentication if you have an auto-login wallet in your oracle_home directory. I don't see any way to setup this kind of db connections with oradew. Passwordless login works like this:

1. Create oracle wallet with mkstore and autologin:

2. wallet create -wallet "C:\Oracle\oracle_home\network\admin" -pwd "PASSWORD" -auto_login_local

3. Setup your tnsnames.ora as normal:

TNS_CONNECTION_NAME=
(description=
(address_list=
(address = (protocol = TCP)(host = yourhost)(port = yourport))
)
(connect_data =
(service_name=yourservicename)
)
)

4. Add an auto login credential to the wallet
   mkstore -wrl "C:\Oracle\oracle_home\network\admin" -createCredential TNS_CONNECTION_NAME USER_NAME PASSWORD

5. Then you can connect with sqlplus just with this command:
   sqlplus /@TNS_CONNECTION_NAME

Is there a way to allow setup db configs without specifying user and password and just set up multiple connection strings for every environment? Users is mandatory, and I've tried not specifying any password but it asks me for the password when executing import objects from db.

DB config connection looks like this
{
"DEV":{
"connectString":"TNS_CONNECTION_NAME",
"users":[
{
"user": "?????"
, "password":"?????"
, "schemas":["USER_NAME"]
, "askForPassword": false
}
]
}
}

It would be nice having compatibility with config like this, making connectString at user level and user and password not required:

{
"DEV":{
"users":[
{
"connectString":"TNS_CONNECTION_NAME"
, "schemas":["SCHEMA_NAME"]
},
{
"connectString":"TNS_CONNECTION_NAME_2"
, "schemas":["SCHEMA_NAME_2"]
}
]
}
}

I could look into it, but I really haven't found where is the sql or sqlcl command built, any ideas where shoud I look at?

I'm trying to create a new workspace. But I get an error. In this case, oracledb is installed, and the extensions Language PL/SQL works.

> Executing task: node /home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/node_modules/gulp/bin/gulp.js --cwd /home/funtik/Projects/fsharpProject/plsql --gulpfile /home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/out/gulpfile.js --color true --silent true initProject <

/home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/node_modules/oracledb/lib/oracledb.js:68
      throw new Error(nodbUtil.getErrorMessage('NJS-045', nodeInfo));
      ^

Error: NJS-045: cannot load the oracledb add-on binary for Node.js 8.14.0 (linux, x64)
Cannot load /home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/node_modules/oracledb/build/Release/oracledb.node
/home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/node_modules/oracledb/build/Release/oracledb.node: invalid ELF header
Node-oracledb installation instructions: https://oracle.github.io/node-oracledb/INSTALL.html
You must have 64-bit Oracle client libraries in LD_LIBRARY_PATH, or configured with ldconfig.
If you do not have Oracle Database on this computer, then install the Instant Client Basic or Basic Light package from
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

    at Object.<anonymous> (/home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/node_modules/oracledb/lib/oracledb.js:68:13)
    at Module._compile (module.js:653:30)
    at Object.Module._extensions..js (module.js:664:10)
    at Module.load (module.js:566:32)
    at tryModuleLoad (module.js:506:12)
    at Function.Module._load (module.js:498:3)
    at Module.require (module.js:597:17)
    at require (internal/module.js:11:18)
    at Object.<anonymous> (/home/funtik/.vscode/extensions/mp.oradew-vscode-0.1.6/node_modules/oracledb/index.js:1:80)
    at Module._compile (module.js:653:30)
Процесс терминала завершен с кодом выхода: 1

When I try to compile an object I get a "1/1 ERROR Error while trying to retrieve text for error ORA-01804" message

Hello there,

I was checking your tool and I realised synonyms script are not created and seems not to be managed by the tool. Is this something not implemented yet or maybe something wrong going on on my installation?

Many thanks,
Juanma

the keybinding F8 is overriding they powershell module's keybinding for F8.

The extension should configure appropriate keybinding when conditions to prevent conflicts like this.

• https://stackoverflow.com/questions/44852670/adding-conditions-to-when-from-vs-code-extension
• https://code.visualstudio.com/docs/getstarted/keybindings#_when-clause-contexts

I see that there are activation events defined for the module already but it appears these that keybindings are applied even if the extension is not activated.