User Oracle in VScode

VScode_Docker_Oracle

Download oracle in docker

docker login
docker pull store/oracle/database-enterprise:12.2.0.1

Connect Oracle (in docker container) with VScode

Follow the instruction in Oracle Database Server Docker Image Documentation:

Step 1

Create docker container with -p, which can map the docker port with host port.

docker run -d -it --name outOracle -P store/oracle/database-enterprise:12.2.0.1

Or you can use the following command. Mapping the host file system to the container file system is recommanded by

docker run -d -it --name outOracleMapper -P -v `pwd`:/home/syk/ -w /home/syk store/oracle/database-enterprise:12.2.0.1

Step 2

Check ports

docker port outOracle

You might get:

5500/tcp -> 0.0.0.0:55000
5500/tcp -> :::55000
1521/tcp -> 0.0.0.0:55001
1521/tcp -> :::55001

Step 3

Enter docker container, changetnsnames.orafile

docker exec -it 8179a515f4be bash # to enter docekr container

Use echo to find the directory of tnsnames.ora

echo $TNS_ADMIN
# the posible output:
# /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB
cd /u01/app/oracle/product/12.2.0/dbhome_1/admin/ORCLCDB
vi tnsnames.ora # to modify this file

the content of tnsnames.ora:

ORCLCDB =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 55001))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = ORCLCDB.localdomain)     )   )
ORCLPDB1 =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 55001))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = ORCLPDB1.localdomain)     )   )

Step 4

Use vscode to connect

Step 5

Execute sql file.

Create a file named s.sql:

SELECT SYSDATE FROM DUAL;

Right click and choose execute sql

The result:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tMfDm1N3-1655482316247)(https://raw.githubusercontent.com/Yukun4119/BlogImg/main/img/sqlresult.png)]

Some problems

1 How to find service name?

Enter oracle directly

docker exec -it 8179a515f4be bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

connect sys

connect sys as sysdba
# the default password is Oradoc_db1

find service name

Show parameter service_name

2 userName and password are not correct

You should create a user first.

Access Cannot connect to new user just created on oracle on docker (ORA-01017) for detail.

alter session set "_ORACLE_SCRIPT"=true;
create user dummy identified by dummy;
GRANT CONNECT, RESOURCE, DBA TO dummy;
create table Docker (id int,name varchar2(20));

The password is the same with username by default.

Oracle usage

Enter Oracle

docker exec -it 8179a515f4be bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

Connect user

sql> connect user/passwd

Execute sql file

the path is the same with the container file system

sql> @/home/data/test.sql;
sql> commit;