[Dev] ubuntuでオラクルを使ってみる

オラクルイメージを取得する。
docker pull container-registry.oracle.com/database/express:latest

コンテナ起動
docker run -p 1521:1521 -p 5500:5500
-e ORACLE_PWD=hinomaruc
--name oracledb
container-registry.oracle.com/database/express:latest

bash起動
docker exec -it oracledb /bin/bash

ユーザーの作成
su
mkdir -p /usr/lib/oracle/xe/
chmod 777 /usr/lib/oracle/xe/

sqlplus system/hinomaruc
SQL> alter session set container = XEPDB1;
SQL> CREATE USER scott identified by tiger123 default tablespace TEST temporary tablespace TESTTEMP;
SQL> grant connect to scott;
SQL> grant resource to scott;
SQL> grant UNLIMITED TABLESPACE TO scott;
SQL> exit

作成したユーザーで接続
export LANG=ja_JP.utf8
export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
sqlplus scott/tiger123@//localhost:1521/XEPDB1

テーブル作成
create table cities (
id varchar2 (10),
name varchar2 (20),
population number (10),
date_mod date
);

レコードを追加する
insert into cities values ('t0131','函館',51742,'2001-7-12');

テーブルを表示する
select * from cities;

例の方法で

sqlplus scott/tiger123@//localhost:1521/XEPDB1 <<EEE
insert into cities values ('t0200','仙台',31548,'2024-06-30');
EEE

sqlplus scott/tiger123@//localhost:1521/XEPDB1 <<EEE
select * from cities;
EEE

sqlplus scott/tiger123@//localhost:1521/XEPDB1 <<EEE
delete from cities;
EEE

sqlローダーを使ってみ

cat <<EEE > test.ctl
LOAD DATA
INFILE 'test.csv'
APPEND
INTO TABLE cities
-- FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(id,name,population,date_mod)
EEE

cat <<EEE > test.csv
t0300,宮城,31548,2024-06-30
t0301,福島,31548,2024-06-30
EEE

タブを含むクリップボードをコピーする場合
bind 'set disable-completion on'

cat <<EEE > test.tsv
t0300 宮城 31548 2024-06-30
t0301 福島 31548 2024-06-30
EEE

sqlldr scott/tiger123@//localhost:1521/XEPDB1 control=test.ctl

cat <<EEE > test.ctl
LOAD DATA
INFILE 'test.tsv'
APPEND
INTO TABLE cities
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(id,name,population,date_mod)
EEE

sqlldr scott/tiger123@//localhost:1521/XEPDB1 control=test.ctl data=test.tsv

参考記事
https://qiita.com/ekzemplaro/items/b9e04c6b1e6a935a6fc6
https://imatake.cfbx.jp/fswiki/wiki.cgi?page=Oracle%2FSqlLoader#p12