Select DB

[Danieleeee]

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

[mickeypearce]

Hey @Danieleeee , It is possible.

1. Oradew chooses DB USER accordingly to directory structure where the file/script is saved.

For example, if you have this structure:

• scripts
  • USER1
    • file_1.sql
  • USER2
    • files_2.sql
• src
  • USER1
    • PROCEDURE
      • p_procedure1.sql
  • USER2
    • PROCEDURE
      • p_procedure2.sql

When you compile file1_sql or p_procedure1.sql it compiles with user USER1 (you must have its credentials saved in dbconfig.json). Or with USER2 when you compile file_2.sql or p_procedure2.sql etc...

2. As for compiling to different DBs, you can choose between 3 different environments you select with vscode command:

Oradew: Compile Current File --- compiles to "DEV" environment (DEV is default env)
Oradew: TEST: Compile Current File --- compiles to "TEST" environment
Oradew: UAT: Compile Current File --- compiles to "UAT" environment
...
Similar with other commands (e.g.: Oradew: Run Current File as Script ...)

Environemnts credentials are also defined in dbconfig.json.

Hopefully I made it more clear.

[DanRic]

I know that I can change user, but I need to change DB (SVIL,TEST,PROD, DEBUG,...)
Now I can only run on DEV and TEST DB. And I have to configure DB connection
(dbconfig.json) for every workspace.

[DanRic]

It would be nice to be able to select a file and run it in a DB and Schema selected from a list.
Without having to create the folder structure each time

[mickeypearce]

I could do sth like Compile with Choose connection type of command that would open a dialog list with all user/env to choose from.

For global dbconfig I could make a setting oradew.dbConfigFile with default value ${workspaceFolder}/dbconfig.json. Eventually expand 3 environments structure to a more customazible format for defining enviroments...

What are your thoughts?

[DanRic]

Yes, thanks!
you could use db connections list in Language PL/SQL extension.

[mickeypearce]

I am adding a "set DB environment" command that let you select an environment which is then used as execution environment for all commands (compile, etc).

Environment selection list is generated from dbconfig, so you can also add you custom environments (debug, etc) and you are not limited only to predefined envs.

This new "set environment" command has also unselected option 'None'. When selected it means that every command prompts you with environment selection on command execution. This will be most suitable usage in your case I think.

Environment specific commands (ex TEST: Compile...) are not needed any longer.

I also added a status item to indicate which environment is currently set.

I am not adding user selection at the moment , although possible , it would require quite a bit of rethinking.

Hopefully DB selection prompt will be useful to you.. I am releasing in a few days.

[mickeypearce]

Released in v0.3.0.

[DanRic]

Thank you!
I have noticed that problems are not shown for custom environments

[mickeypearce]

What do you mean by problems are not shown? "ORA-00900: invalid SQL statemen" is your problem. Can you please provide more information, an example maybe compared to "DEV" enviroment for example.

[DanRic]

If I Compile current file:

I have no problems if it isn't in the src folder:

If I run the same command in the src folder I have errors and problems:

[mickeypearce]

Try setting a property in oradewrc.json configuration:
"compile.force": true

If I run the same command in the src folder I have errors and problems:

hmm I see no difference in my case. Where is this file that you are running located?

[DanRic]

compile.force is true
This is what I see in the terminal (no errors):

[mickeypearce]

Your workspace is not git repostiroy (git init would solve that).

Sql file compiled with "Success" though. It failed to stage file in git repo (git add) which is done after file is successfully compiled.

[DanRic]

The file has errors, look KK_VARCHAR2(500).
Why it says: Sql file compiled with "Success"
And no problem is shown?

[mickeypearce]

Aha I see file is actually outsitde of SRC directory.... I will look into it and let you know.

[mickeypearce]

This is because file is not in dir structure ./src/OBJECT_TYPE/OBJECT_NAME.SQL. Information we need to get errors for object after compilation (from table all_errors) is therefore missing in your case.

I advise you to place your objects inside ./SRC structure.

[DanRic]

OK, thanks.
If I put it in the scr folder I have this error though I have set compile.force = true :

[mickeypearce]

Compile again, but I am not sure why "compile.force": true would not work.