Change language and prompt in SQLcl for VS Code

Windows and language settings is a common problem for users outside English speaking countries. The windows language on my laptop is English, but time to time I use another laptop in Portugal with the same Microsoft account as login. This laptop is configured in Portuguese. My work laptop’s keyboard is Swiss, locale also set for Switzerland. Windows just gets confused and shows some applications in English, others in German, others in Portuguese.

Today I was testing SQLcl inside VS Code. I tried the help function and get answers in German, plus with the öäü signs wrong.

So I needed to change it to English. Based on SQLcl documentation it reads a file startup.sql once when it starts. And on VS Code, to know where it is currently running, you just have to run the ‘pwd’ command:

SQL> pwd

Then, on this path, you can create a startup.sql file and change the prompt, and language. My startup.sql starts by changing the display language, then the SQLcl layout and the prompt:

set feedb off termout off
-- changes the display language
alter session set nls_language=american;

-- Changes SQLcl to have word highlight and statusbar
set highlighting on
set highlighting keyword foreground green
set highlighting identifier background blue
set highlighting string foreground yellow
set highlighting number foreground cyan
set highlighting comment foreground white
set statusbar on
set statusbar add editmode
set statusbar add txn
set statusbar add timing

-- Changes prompt to show the instance and pdb name
DEFINE prompt="SQL"
COLUMN col_prompt NEW_VALUE prompt
SELECT UPPER(USER || '@' || SYS_CONTEXT('userenv', 'instance_name')) ||'.'|| SYS_CONTEXT('userenv', 'con_name') col_prompt FROM dual;
set sqlprompt "&prompt> " ;
set termout on feed on

At the ends it looks like this: