19cr3のoracleで初期環境構築スクリプト作ろうかなって思った話

ディレクトリ構成

ffで作業

コード表示

[oracle@centos ff]$ [ -e init.sql -a -e output/* -a -e seq/* ] && rm init.sql output/* seq/*
[oracle@centos ff]$ tree
.
├── output
├── seq
├── sh
│   ├── def_order.sh
│   ├── exec.sh
│   └── replace.sh
├── src
│   └── user
└── tmpl
    ├── create_or_replace_directory
    ├── create_user
    ├── drop_user
    ├── grant_read_on_directory
    ├── grant_role_or_priviledge
    ├── grant_tablespace
    └── grant_write_on_directory

5 directories, 11 files

tmpl/*作成

テンプレ作成

コード表示

[oracle@centos ff]$ ls tmpl/* | xargs -t -I@ bash -c 'cat @'
bash -c cat tmpl/create_or_replace_directory 
CREATE OR REPLACE DIRECTORY @_in_dir AS '/mnt/@/in';
CREATE OR REPLACE DIRECTORY @_out_dir AS '/mnt/@/out';
bash -c cat tmpl/create_user 
CREATE USER @ IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
bash -c cat tmpl/drop_user 
DROP USER @;
bash -c cat tmpl/grant_read_on_directory 
GRANT READ ON DIRECTORY @_in_dir TO @;
GRANT READ ON DIRECTORY @_out_dir TO @;
bash -c cat tmpl/grant_role_or_priviledge 
GRANT dba TO @;
bash -c cat tmpl/grant_tablespace 
GRANT UNLIMITED TABLESPACE TO @;
bash -c cat tmpl/grant_write_on_directory 
GRANT WRITE ON DIRECTORY @_in_dir TO @;
GRANT WRITE ON DIRECTORY @_out_dir TO @;

sh/*作成

忘れないように先に実行権限を付与しておこう

コード表示

[oracle@centos ff]$ find $(pwd) -name "*sh" | xargs -I@ bash -c 'chmod a+x @'
[oracle@centos ff]$ ll *sh
合計 12
-rwxr-xr-x. 1 oracle docker 164  6月  3 21:38 def_order.sh
-rwxr-xr-x. 1 oracle docker  83  6月  3 20:02 exec.sh
-rwxr-xr-x. 1 oracle docker 282  6月  3 21:37 replace.sh
[oracle@centos ff]$ find $(pwd) -name "*sh" | xargs -t -I@ bash -c 'cat @'
bash -c cat /home/oracle/ff/sh 
cat: /home/oracle/ff/sh: ディレクトリです
bash -c cat /home/oracle/ff/sh/exec.sh 
#!/bin/bash
while read line; do
  echo ${line} | bash;
done< <(cat $(pwd)/seq/exec_order)>$(pwd)/init.sql
bash -c cat /home/oracle/ff/sh/replace.sh 
#!/bin/bash
while read line1;do
  [ -e $(pwd)/output/$(basename ${line1}) ] && rm $(pwd)/output/$(basename ${line1});
  cat src/user | while read line2; do
    cat ${line1} | sed -e s/@/${line2}/g >> $(pwd)/output/$(basename ${line1});
  done
done < <(find $(pwd)/tmpl/* -name "*")
bash -c cat /home/oracle/ff/sh/def_order.sh 
#!/bin/bash
find $(pwd)/tmpl/* -name "*" | xargs -I@ bash -c 'echo [ -e $(pwd)/output/$(basename @) ] \&\& cat $(pwd)/output/$(basename @)' > $(pwd)/seq/exec_order

src/*作成

コード表示

[oracle@centos ff]$ cat src/*
aine
kuraine
nahato
mujiku

replace.sh→def_order.shの順に実行

コード表示

[oracle@centos ff]$ ./sh/replace.sh && ./sh/def_order.sh
[oracle@centos ff]$ tree
.
├── output
│   ├── create_or_replace_directory
│   ├── create_user
│   ├── drop_user
│   ├── grant_read_on_directory
│   ├── grant_role_or_priviledge
│   ├── grant_tablespace
│   └── grant_write_on_directory
├── seq
│   └── exec_order
├── sh
│   ├── def_order.sh
│   ├── exec.sh
│   └── replace.sh
├── src
│   └── user
└── tmpl
    ├── create_or_replace_directory
    ├── create_user
    ├── drop_user
    ├── grant_read_on_directory
    ├── grant_role_or_priviledge
    ├── grant_tablespace
    └── grant_write_on_directory

5 directories, 19 files
[oracle@centos ff]$ ls {output,seq}/* | xargs -t -I@ bash -c 'cat @'
bash -c cat output/create_or_replace_directory 
CREATE OR REPLACE DIRECTORY aine_in_dir AS '/mnt/aine/in';
CREATE OR REPLACE DIRECTORY aine_out_dir AS '/mnt/aine/out';
CREATE OR REPLACE DIRECTORY kuraine_in_dir AS '/mnt/kuraine/in';
CREATE OR REPLACE DIRECTORY kuraine_out_dir AS '/mnt/kuraine/out';
CREATE OR REPLACE DIRECTORY nahato_in_dir AS '/mnt/nahato/in';
CREATE OR REPLACE DIRECTORY nahato_out_dir AS '/mnt/nahato/out';
CREATE OR REPLACE DIRECTORY mujiku_in_dir AS '/mnt/mujiku/in';
CREATE OR REPLACE DIRECTORY mujiku_out_dir AS '/mnt/mujiku/out';
bash -c cat output/create_user 
CREATE USER aine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER kuraine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER nahato IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER mujiku IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
bash -c cat output/drop_user 
DROP USER aine;
DROP USER kuraine;
DROP USER nahato;
DROP USER mujiku;
bash -c cat output/grant_read_on_directory 
GRANT READ ON DIRECTORY aine_in_dir TO aine;
GRANT READ ON DIRECTORY aine_out_dir TO aine;
GRANT READ ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT READ ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT READ ON DIRECTORY nahato_in_dir TO nahato;
GRANT READ ON DIRECTORY nahato_out_dir TO nahato;
GRANT READ ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT READ ON DIRECTORY mujiku_out_dir TO mujiku;
bash -c cat output/grant_role_or_priviledge 
GRANT dba TO aine;
GRANT dba TO kuraine;
GRANT dba TO nahato;
GRANT dba TO mujiku;
bash -c cat output/grant_tablespace 
GRANT UNLIMITED TABLESPACE TO aine;
GRANT UNLIMITED TABLESPACE TO kuraine;
GRANT UNLIMITED TABLESPACE TO nahato;
GRANT UNLIMITED TABLESPACE TO mujiku;
bash -c cat output/grant_write_on_directory 
GRANT WRITE ON DIRECTORY aine_in_dir TO aine;
GRANT WRITE ON DIRECTORY aine_out_dir TO aine;
GRANT WRITE ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT WRITE ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT WRITE ON DIRECTORY nahato_in_dir TO nahato;
GRANT WRITE ON DIRECTORY nahato_out_dir TO nahato;
GRANT WRITE ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT WRITE ON DIRECTORY mujiku_out_dir TO mujiku;
bash -c cat seq/exec_order 
[ -e /home/oracle/ff/output/create_or_replace_directory ] && cat /home/oracle/ff/output/create_or_replace_directory
[ -e /home/oracle/ff/output/create_user ] && cat /home/oracle/ff/output/create_user
[ -e /home/oracle/ff/output/drop_user ] && cat /home/oracle/ff/output/drop_user
[ -e /home/oracle/ff/output/grant_read_on_directory ] && cat /home/oracle/ff/output/grant_read_on_directory
[ -e /home/oracle/ff/output/grant_role_or_priviledge ] && cat /home/oracle/ff/output/grant_role_or_priviledge
[ -e /home/oracle/ff/output/grant_tablespace ] && cat /home/oracle/ff/output/grant_tablespace
[ -e /home/oracle/ff/output/grant_write_on_directory ] && cat /home/oracle/ff/output/grant_write_on_directory

exec_orderよしなに直す

コード表示

[oracle@centos ff]$ vi seq/exec_order
[oracle@centos ff]$ cat seq/exec_order
[ -e /home/oracle/ff/output/drop_user ] && cat /home/oracle/ff/output/drop_user
[ -e /home/oracle/ff/output/create_user ] && cat /home/oracle/ff/output/create_user
[ -e /home/oracle/ff/output/grant_role_or_priviledge ] && cat /home/oracle/ff/output/grant_role_or_priviledge
[ -e /home/oracle/ff/output/grant_tablespace ] && cat /home/oracle/ff/output/grant_tablespace
[ -e /home/oracle/ff/output/create_or_replace_directory ] && cat /home/oracle/ff/output/create_or_replace_directory
[ -e /home/oracle/ff/output/grant_read_on_directory ] && cat /home/oracle/ff/output/grant_read_on_directory
[ -e /home/oracle/ff/output/grant_write_on_directory ] && cat /home/oracle/ff/output/grant_write_on_directory

exec.sh実行

コード表示

[oracle@centos ff]$ ./sh/exec.sh
[oracle@centos ff]$ tree
.
├── init.sql
├── output
│   ├── create_or_replace_directory
│   ├── create_user
│   ├── drop_user
│   ├── grant_read_on_directory
│   ├── grant_role_or_priviledge
│   ├── grant_tablespace
│   └── grant_write_on_directory
├── seq
│   └── exec_order
├── sh
│   ├── def_order.sh
│   ├── exec.sh
│   └── replace.sh
├── src
│   └── user
└── tmpl
    ├── create_or_replace_directory
    ├── create_user
    ├── drop_user
    ├── grant_read_on_directory
    ├── grant_role_or_priviledge
    ├── grant_tablespace
    └── grant_write_on_directory

5 directories, 20 files
[oracle@centos ff]$ cat init.sql
DROP USER aine;
DROP USER kuraine;
DROP USER nahato;
DROP USER mujiku;
CREATE USER aine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER kuraine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER nahato IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER mujiku IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
GRANT dba TO aine;
GRANT dba TO kuraine;
GRANT dba TO nahato;
GRANT dba TO mujiku;
GRANT UNLIMITED TABLESPACE TO aine;
GRANT UNLIMITED TABLESPACE TO kuraine;
GRANT UNLIMITED TABLESPACE TO nahato;
GRANT UNLIMITED TABLESPACE TO mujiku;
CREATE OR REPLACE DIRECTORY aine_in_dir AS '/mnt/aine/in';
CREATE OR REPLACE DIRECTORY aine_out_dir AS '/mnt/aine/out';
CREATE OR REPLACE DIRECTORY kuraine_in_dir AS '/mnt/kuraine/in';
CREATE OR REPLACE DIRECTORY kuraine_out_dir AS '/mnt/kuraine/out';
CREATE OR REPLACE DIRECTORY nahato_in_dir AS '/mnt/nahato/in';
CREATE OR REPLACE DIRECTORY nahato_out_dir AS '/mnt/nahato/out';
CREATE OR REPLACE DIRECTORY mujiku_in_dir AS '/mnt/mujiku/in';
CREATE OR REPLACE DIRECTORY mujiku_out_dir AS '/mnt/mujiku/out';
GRANT READ ON DIRECTORY aine_in_dir TO aine;
GRANT READ ON DIRECTORY aine_out_dir TO aine;
GRANT READ ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT READ ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT READ ON DIRECTORY nahato_in_dir TO nahato;
GRANT READ ON DIRECTORY nahato_out_dir TO nahato;
GRANT READ ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT READ ON DIRECTORY mujiku_out_dir TO mujiku;
GRANT WRITE ON DIRECTORY aine_in_dir TO aine;
GRANT WRITE ON DIRECTORY aine_out_dir TO aine;
GRANT WRITE ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT WRITE ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT WRITE ON DIRECTORY nahato_in_dir TO nahato;
GRANT WRITE ON DIRECTORY nahato_out_dir TO nahato;
GRANT WRITE ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT WRITE ON DIRECTORY mujiku_out_dir TO mujiku;

sqldeveloperから実行

ぉぉぉ。うまくいったぜ。

docker rlwrap周りの整備で粘り勝った話

まえがき

docker images oracleを使う時にさっと環境整備したかったので、粘ってシェルスクリプト書き切った。

参考文献

su oracleがrootユーザーからできないとき、んっってなったので、調べた。

su: cannot open session: Permission denied  

事前にdockerホストにepel-repo登録しておく

ほんとは一元化したいけど、エスケープハマったので、妥協してしまった。粘り勝っていない。。エスケープ上手く扱えるようになりたい。

コード表示

[oracle@centos doclan]$ sudo cp /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 /etc/yum.repos.d/epel.repo .
[sudo] oracle のパスワード:
[oracle@centos doclan]$ ll
合計 16
drwxr-xr-x. 3 root   root   4096  5月 25 13:14 19cr3
-rw-r--r--. 1 root   root   1662  5月 25 17:45 RPM-GPG-KEY-EPEL-7
-rwxr-xr-x. 1 oracle docker  659  5月 25 17:37 env.sh
-rw-r--r--. 1 root   root    951  5月 25 17:45 epel.repo

env.sh

汚い ^^;まぁ、うまいやりかた思いついたら、描き直そう。

コード表示

[oracle@centos doclan]$ cat env.sh
#!/bin/bash

echo 'root_pwd' | passwd --stdin root
yum install -y passwd && \
yum install -y sudo
echo 'oracle_pwd' | passwd --stdin oracle
echo 'oracle ALL=(ALL) NOPASSWD:ALL' >> /etc/sudoers
sed -i -e 's/^\(oracle   hard   memlock\)/#\1/' /etc/security/limits.d/oracle-database-preinstall-19c.conf

cp /mnt/RPM-GPG-KEY-EPEL-7 /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 && \
cp /mnt/epel.repo /etc/yum.repos.d/epel.repo

su oracle -c 'cd ~ && \
sudo yum update -y && \
sudo yum install -y rlwrap && \
sudo yum install -y vim && \
sudo mkdir -p rlwrap-extensions && \
cd rlwrap-extensions && \
sudo curl -LO http://www.linuxification.at/download/rlwrap-extensions-V12-0.05.tar.gz && \
sudo tar xvfz rlwrap-extensions-V12-0.05.tar.gz
'

su oracle -c 'sed -i -e "$ a alias sqlplus=\"rlwrap -pRed -if ~/rlwrap-extensions/sqlplus sqlplus\"" ~/.bashrc'

su oracle -c '
cat </opt/oracle/product/19c/dbhome_1/sqlplus/admin/glogin.sql
COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
set feed on
--set autotrace traceonly
set timing on
--set hist on
DEFINE_EDITOR=nano
set sqlp "_USER'''\'@''\''_CONNECT_IDENTIFIER> "
EOF
'
su oracle

実行

コード表示

[oracle@centos doclan]$ docker run -v /home/oracle/doclan:/mnt -v /home/oracle/doclan/19cr3/init:/docker-entrypoint-initdb.d/startup -d --name orcl_19cr3 --shm-size=4g -p 1521:1521 -p 5500:5500 -e TZ=Asia/Tokyo -e ORACLE_PWD=ORACLE_PWD -e ORACLE_SID=ORCL -e ORACLE_PDB=pdb1 oracle/database:19.3.0-ee
[oracle@centos doclan]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                   PORTS                                            NAMES
42b3dd2da618        oracle/database:19.3.0-ee   "/bin/sh -c 'exec $O…"   7 hours ago         Up 7 hours (unhealthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcl_19cr3
[oracle@centos doclan]$ docker exec --interactive --tty --user root --workdir / orcl_19cr3 bash
bash-4.2# cd /mnt && ./env.sh
[oracle@42b3dd2da618 mnt]$ cat /opt/oracle/product/19c/dbhome_1/sqlplus/admin/glogin.sql
COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
set feed on
--set autotrace traceonly
set timing on
--set hist on
DEFINE_EDITOR=nano
set sqlp "_USER'@'_CONNECT_IDENTIFIER> "
[oracle@42b3dd2da618 mnt]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 25 19:54:10 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@pdb1> show pdbs

       CON_ID CON_NAME                       OPEN MODE  RESTRICTED
------------- ------------------------------ ---------- ----------
            3 PDB1                           READ WRITE NO

あとがき

エスケープうまくなりたい。

oracle sql 差分更新について考える話

まえがき

考えてみようかなと。

環境

コード表示

[oracle@centos ~]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                 PORTS                                            NAMES
42b3dd2da618        oracle/database:19.3.0-ee   "/bin/sh -c 'exec $O…"   3 hours ago         Up 3 hours (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcl_19cr3
[oracle@centos ~]$ docker exec -it orcl_19cr3 /bin/bash
[oracle@42b3dd2da618 ~]$ sqlplus aine/ORACLE_PWD@pdb1 

SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 25 15:59:05 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat May 25 2019 15:17:56 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> 

対象データ

コード表示

drop table src purge;
create table src as
select
  level as rn
  ,chr(level + 64) as str
from
  dual
connect by
  level <= 10;

drop table tgt purge;
create table tgt as
select
  level as rn
  ,chr(level + 64) as str
from
  dual
where
 level between 3 and 7
connect by
  level <= 10;

対象データ確認

コード表示

SQL> select * from src;

	RN STR
---------- ----
	 1 A
	 2 B
	 3 C
	 4 D
	 5 E
	 6 F
	 7 G
	 8 H
	 9 I
	10 J

10 rows selected.

SQL> select * from tgt;

	RN STR
---------- ----
	 3 C
	 4 D
	 5 E
	 6 F
	 7 G

chk.sql

これでなにも取れなかったら、差分更新できたことにする。

コード表示

WITH src_minus_tgt AS (
  SELECT
    *
  FROM
    src
  MINUS
  SELECT
    *
  FROM
    tgt
), tgt_minus_src AS (
  SELECT
    *
  FROM
    tgt
  MINUS
  SELECT
    *
  FROM
    src
)
SELECT
  *
FROM
  src_minus_tgt
UNION ALL
SELECT
  *
FROM
  tgt_minus_src;

ptn1

コード表示

insert into tgt
select
  s1.*
from
  src s1
    left outer join tgt s2
      on
        s1.rn=s2.rn
where
  s2.rn is null
;
commit;

ptn2

コード表示

insert into tgt
select
  s1.*
from
  src s1
where
  not exists(
          select
            1
          from
            tgt s2
          where
            s1.rn=s2.rn
          )
;
commit;

ptn3

コード表示

merge into tgt
using(
      select
        s1.*
      from
        src s1
      where
        not exists(
                select
                  1
                from
                  tgt s2
                where
                  s1.rn=s2.rn
                )
      ) src
on(tgt.rn=src.rn)
when not matched then
insert values(src.rn,src.str)
;
commit;

ptn4

コード表示

merge into tgt
using(
      select
        s1.*
      from
        src s1
      minus
      select
        s2.*
      from
        tgt s2
      ) src
on(tgt.rn=src.rn)
when not matched then
insert values(src.rn,src.str)
;
commit;

あとがき

んんん、もうちょい臨場感がほしいい。fin。

docker oracle 19cr3 imageファイル作成

バイトサイズ確認!!!

imageファイル作成準備


[oracle@centos ~]$ cd /home/oracle/docker-images/OracleDatabase/SingleInstance/dockerfiles/19.3.0
[oracle@centos 19.3.0]$ ll
合計 2988076
-rw-r--r--. 1 oracle docker         63  5月  1 20:17 Checksum.ee
-rw-r--r--. 1 oracle docker         63  5月  1 20:17 Checksum.se2
-rw-r--r--. 1 oracle docker       3357  5月  1 20:17 Dockerfile
-rw-r--r--. 1 oracle docker 3059705302  5月  1 20:01 LINUX.X64_193000_db_home.zip
-rwxr-xr-x. 1 oracle docker       1050  5月  1 20:17 checkDBStatus.sh
-rwxr-xr-x. 1 oracle docker        905  5月  1 20:17 checkSpace.sh
-rwxr-xr-x. 1 oracle docker       3088  5月  1 20:17 createDB.sh
-rw-r--r--. 1 oracle docker       6878  5月  1 20:17 db_inst.rsp
-rw-r--r--. 1 oracle docker       9204  5月  1 20:17 dbca.rsp.tmpl
-rwxr-xr-x. 1 oracle docker       2526  5月  1 20:17 installDBBinaries.sh
-rwxr-xr-x. 1 oracle docker       6526  5月  1 20:17 runOracle.sh
-rwxr-xr-x. 1 oracle docker       1015  5月  1 20:17 runUserScripts.sh
-rwxr-xr-x. 1 oracle docker        758  5月  1 20:17 setPassword.sh
-rwxr-xr-x. 1 oracle docker        932  5月  1 20:17 setupLinuxEnv.sh
-rwxr-xr-x. 1 oracle docker        678  5月  1 20:17 startDB.sh

imageファイル作成


[oracle@centos 19.3.0]$ cd ..
[oracle@centos dockerfiles]$ ll
合計 32
drwxr-xr-x. 2 oracle docker 4096  5月  1 20:17 11.2.0.2
drwxr-xr-x. 2 oracle docker 4096  5月  1 20:17 12.1.0.2
drwxr-xr-x. 2 oracle docker 4096  5月  1 20:17 12.2.0.1
drwxr-xr-x. 2 oracle docker 4096  5月  1 20:17 18.3.0
drwxr-xr-x. 2 oracle docker 4096  5月  1 20:17 18.4.0
drwxr-xr-x. 2 oracle docker 4096  5月  1 20:20 19.3.0
-rwxr-xr-x. 1 oracle docker 5103  5月  1 20:17 buildDockerImage.sh
[oracle@centos dockerfiles]$ ./*sh -h

Usage: buildDockerImage.sh -v [version] [-e | -s | -x] [-i] [-o] [Docker build option]
Builds a Docker Image for Oracle Database.
  
Parameters:
   -v: version to build
       Choose one of: 11.2.0.2  12.1.0.2  12.2.0.1  18.3.0  18.4.0  19.3.0  
   -e: creates image based on 'Enterprise Edition'
   -s: creates image based on 'Standard Edition 2'
   -x: creates image based on 'Express Edition'
   -i: ignores the MD5 checksums
   -o: passes on Docker build option

* select one edition only: -e, -s, or -x

LICENSE UPL 1.0

Copyright (c) 2014-2019 Oracle and/or its affiliates. All rights reserved.
[oracle@centos dockerfiles]$ ./buildDockerImage.sh -i -v 19.3.0 -e
Successfully built 3c941f34e1d3
Successfully tagged oracle/database:19.3.0-ee


  Oracle Database Docker Image for 'ee' version 19.3.0 is ready to be extended: 
    
    --> oracle/database:19.3.0-ee

  Build completed in 346 seconds.
  

imageファイル作成結果

5分ぐらいかーはやい。。


[oracle@centos dockerfiles]$ docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
oracle/database     19.3.0-ee           3c941f34e1d3        2 minutes ago       6.64GB
oraclelinux         7-slim              f7512ac13c1b        2 weeks ago         118MB

どうさ確認

sqlplusつなぐまで


[oracle@centos doclan]$ docker run -v /home/oracle/doclan:/mnt -v /home/oracle/doclan/19cr3/init:/docker-entrypoint-initdb.d/startup -d --name orcl_19cr3 --shm-size=4g -p 1521:1521 -p 5500:5500 -e TZ=Asia/Tokyo -e ORACLE_PWD=ORACLE_PWD -e ORACLE_SID=ORCL -e ORACLE_PDB=pdb1 oracle/database:19.3.0-ee
7347fe12d03f3a191c114d01043d3f72e473552f6f18df1d099f6117beb1c0a9
[oracle@centos doclan]$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                    PORTS                                            NAMES
7347fe12d03f        oracle/database:19.3.0-ee   "/bin/sh -c 'exec $O…"   37 minutes ago      Up 37 minutes (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:5500->5500/tcp   orcl_19cr3
[oracle@centos doclan]$ docker exec -it orcl_19cr3 /bin/bash
[oracle@7347fe12d03f ~]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 1 21:47:46 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
SQL> select BANNER_FULL from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@7347fe12d03f ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 1 21:52:12 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 3 PDB1 			  READ WRITE NO
SQL> select BANNER_FULL from v$version;

BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> CREATE USER aine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;

User created.

SQL> GRANT dba TO aine;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO aine;

Grant succeeded.

[oracle@7347fe12d03f ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 1 21:56:10 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Wed May 01 2019 21:55:13 +09:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> DROP TABLE warehouse_mst PURGE;
CREATE TABLE warehouse_mst (
        warehouse_code                  VARCHAR2(24 BYTE)
        , shipping_permission_over_days   NUMBER
        , CONSTRAINT warehouse_mst_pk PRIMARY KEY ( warehouse_code )
);

INSERT INTO warehouse_mst (warehouse_code,shipping_permission_over_days) VALUES ('w_BS98',25);
INSERT INTO warehouse_mst (warehouse_code,shipping_permission_over_days) VALUES ('w_DIX4',28);
INSERT INTO warehouse_mst (warehouse_code,shipping_permission_over_days) VALUES ('w_C5MN',29);
COMMIT;
Table dropped.

SQL>   2    3    4    5  
Table created.

SQL> SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 

Commit complete.

SQL> select * from warehouse_mst;

WAREHOUSE_CODE		 SHIPPING_PERMISSION_OVER_DAYS
------------------------ -----------------------------
w_BS98						    25
w_DIX4						    28
w_C5MN						    29


LOB型の削除に関して

参考文献

How to drop Oracle LOB  

今の状況

拙作スキーマクリーニングスクリプトですとLOBオブジェクトが残っている。。消さなくてもいいと思うけど、気になったから、調べた。

EXECUTE IMMEDIATE をはじめて書いた。スキーマ内クリーニングスクリプト  

結論

パージすればいい

コード表示

purge recyclebin;

dmpとかするシェルスクリプト

ディレクトリマウント

OSとORACLEをつなぐ。

コード表示

cd /mnt/18cr3/share/dump/ORCL/aine/in
cd /mnt/18cr3/share/dump/ORCL/aine/in/liz
cd /mnt/18cr3/share/dump/ORCL/aine/out
cd /mnt/18cr3/share/dump/ORCL/aine/out/liz
cd /mnt/18cr3/share/query
cd /mnt/18cr3/share/sh
--sys session
CREATE OR REPLACE DIRECTORY dp_out_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/out';
GRANT READ ON DIRECTORY dp_out_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_out_aine_dir TO aine;
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

フォルダ構成

RACできたら、かえると思う

コード表示

[oracle@f285aba0589a mnt]$ pwd
/mnt
[oracle@f285aba0589a mnt]$ tree
.
|-- 11gr2
|   |-- init
|   |   |-- create_user.sql
|   |   `-- startup.sh
|   |-- oradata
|   `-- share
|       |-- dump
|       |   `-- xe
|       |       |-- aine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- kuraine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- mujiku
|       |       |   |-- in
|       |       |   `-- out
|       |       `-- nahato
|       |           |-- in
|       |           `-- out
|       |-- login
|       |-- query
|       |-- sh
|       `-- spool
|-- 12cr2
|   |-- init
|   |   |-- create_user.sql
|   |   `-- startup.sh
|   |-- oradata
|   `-- share
|       |-- dump
|       |   `-- ORCL
|       |       |-- aine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- kuraine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- mujiku
|       |       |   |-- in
|       |       |   `-- out
|       |       `-- nahato
|       |           |-- in
|       |           `-- out
|       |-- login
|       |-- query
|       |-- sh
|       `-- spool
`-- 18cr3
    |-- init
    |   |-- create_user.sql
    |   `-- startup.sh
    |-- oradata
    `-- share
        |-- dump
        |   `-- ORCL
        |       |-- aine
        |       |   |-- in
        |       |   |   |-- dup___test_01___.png
        |       |   |   |-- dup___test_02___.png
        |       |   |   |-- dup___test_03___.png
        |       |   |   |-- tbl___loader___.dat
        |       |   |   |-- tbl___loader___.log
        |       |   |   |-- tbl___loader____18130.log_xt
        |       |   |   `-- test.png
        |       |   `-- out
        |       |       |-- aine_user_scheduler_job_log_20190406111520.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111520.log
        |       |       |-- aine_user_scheduler_job_log_20190406111533.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111533.log
        |       |       |-- aine_user_scheduler_job_log_20190406111929.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111929.log
        |       |       |-- aine_user_scheduler_job_log_20190406113707.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406113707.log
        |       |       |-- aine_user_scheduler_job_log_20190406114236.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406114236.log
        |       |       |-- aine_user_scheduler_job_log_20190406115931.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406115931.log
        |       |       `-- liz
        |       |           `-- tar_tbl
        |       |-- kuraine
        |       |   |-- in
        |       |   `-- out
        |       |-- mujiku
        |       |   |-- in
        |       |   `-- out
        |       `-- nahato
        |           |-- in
        |           `-- out
        |-- login
        |-- query
        |   `-- drop_create.sql
        |-- sh
        |   `-- exe.sh
        |-- spool
        |   `-- test.html
        `-- zip
            `-- v18c.zip

68 directories, 30 files

シェルスクリプト

雰囲気でつくった。imp、query、spoolの場合はまだ。bash再入門してるから、もっとスマートかきたいものだ。ひっさしぶりにかいた。

コード表示

#!/bin/bash

echo "enter username number."
echo "-->1:aine 2:kuraine 3:nahato 4:mujiku"
read USR_PAT
echo "enter password number."
echo "-->1:ORACLE_PWD"
read PWD_PAT
echo "enter database connect identifer number."
echo "-->1:@pdb1 2:@pdb2 3:@pdb3 4:@pdb4"
read DB_PAT
echo "enter target table liz file name number."
echo "-->1:tar_tbl"
read FNM_PAT
echo "enter version number."
echo "-->3:18cr3 2:12cr2 1:11gr2"
read VRN_PAT
echo "enter ORACLE_SID number."
echo "-->1:ORCL"
read SID_PAT
echo 'enter usage number'
echo '1:dump_out 2:dump_in 3:spool 4:query'
read USE_PAT

case ${USR_PAT} in
    1)
        USR=aine
        ;;
    2)
        USR=kuraine
        ;;
    3)
        USR=nahato
        ;;
    4)
        USR=mujiku
        ;;
    *)
        ;;
esac

case ${PWD_PAT} in
    1)
        PWD=ORACLE_PWD
        ;;
    *)
        ;;
esac

case ${DB_PAT} in
    1)
        DB=@pdb1
        ;;
    2)
        DB=@pdb2
        ;;
    3)
        DB=@pdb3
        ;;
    4)
        DB=@pdb4
        ;;
    *)
        ;;
esac

case ${FNM_PAT} in
    1)
        FNM=tar_tbl
        ;;
    *)
        ;;
esac

case ${VRN_PAT} in
    1)
        VRN=11gr2
        ;;
    2)
        VRN=12cr2
        ;;
    3)
        VRN=18cr3
        ;;
    *)
        ;;
esac

case ${SID_PAT} in
    1)
        SID=ORCL
        ;;
    *)
        ;;
esac

USC=_
SLH=/
PFX=tmp_

MNT_DIR=/mnt/${VRN}/share

LGN_INFO=${USR}${SLH}${PWD}${DB}
SYS_DATE=`date '+%Y%m%d%H%M%S'`
DP_EXT=.dmp
LOG_EXT=.log
QRY_DIR=${MNT_DIR}${SLH}query${SLH}

case ${USE_PAT} in
    1)
        DP_DIR=${MNT_DIR}${SLH}dump${SLH}${SID}${SLH}${USR}${SLH}out${SLH}
        LZ_DIR=${DP_DIR}liz${SLH}
        while read LN
        do
            sqlplus ${LGN_INFO} @${QRY_DIR}drop_create.sql ${PFX} ${LN} <<EOF
EOF
            LOG_NM=${USR}${USC}${LN}${USC}
            expdp ${LGN_INFO} DIRECTORY=dp_out_${USR}_dir DUMPFILE=${LOG_NM}${SYS_DATE}${DP_EXT} LOG=${LOG_NM}${SYS_DATE}${LOG_EXT} TABLES=${PFX}${LN}
        done < ${LZ_DIR}${FNM}
        ;;
    2)
        DP_DIR=${MNT_DIR}${SLH}dump${SLH}${SID}${SLH}${USR}${SLH}in${SLH}
        LZ_DIR=${DP_DIR}liz${SLH}
        ;;
    *)
        ;;
esac

sqlスクリプト

シェルスクリプトのなかで@${QRY_DIR}drop_create.sqlとかしてるやつ

コード表示

drop table &1&2;
create table &1&2 nologging parallel as select * from &2;

tar_tblリスト

エキスポート対象のテーブルリスト。

コード表示

user_scheduler_job_log

実行結果

tar_tblリストはすこし増やして4つにした。できてそう。


コード表示

[oracle@f285aba0589a out]$ pwd
/mnt/18cr3/share/dump/ORCL/aine/out
[oracle@f285aba0589a out]$ ll -lt | head -9
total 4224
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___RCP____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___RCP____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___MST____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___MST____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___AVL____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___AVL____20190407175116.log
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___STF____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___STF____20190407175116.dmp

hotなディクショナリビュー

user_source

行番号でるの!ってなった。

コード表示

select * from user_source;

user_dependencies

loopで階層構造を表現できそう

コード表示

select * from user_dependencies;

user_arguments

ファンクションやプロシージャの引数がわかる!!!

コード表示

select * from user_arguments;

user_tab_privs

オブジェクトに対する権限わかる

コード表示

select * from user_tab_privs;

dba_directories

オラクルにマウントしたディレクトリオブジェクトの一覧分かる

コード表示

select * from dba_directories;

dictionary

未知の開拓するときとりあえずぐぐる

コード表示

select * from dictionary;

dict_columns

未知の開拓するときとりあえずぐぐる。カラムの情報手に入る。

コード表示

select * from dict_columns;

recyclebin

ゴミ箱。

コード表示

select * from recyclebin;
purge recyclebin;

user_objects

クリーニングするときとか

コード表示

select * from user_objects;

コード表示


コード表示


コード表示


コード表示


コード表示


コード表示


コード表示


コード表示


blob バイナリ複製&バイナリ取込 with plsql

まえがき

おもしろそうだったので触れてみた!!!面白かった!!仕事疲れたけど、気晴らしに触れてみた!まぁ余計に疲れるけどね、よいのです、そんなことは。

参考文献

事の発端

UTL_FILE.FCOPY()とかFRENAME()とか  

clobだったら、LOADCLOBFROMFILE

LOADBLOBFROMFILEプロシージャ  

オープンする

FILEOPENプロシージャ  

クローズする

FILECLOSEプロシージャ  

ファイルをインスタンス化するてきな感じ。メモリ上にオブジェクト作るかんじかな。

BFILENAME  

DBMSLOB.SQLパッケージで定義される定数の一つlobmaxsize INTEGER 18446744073709551615

DBMS_LOB.LOBMAXSIZE  

コストラクタ的なやつ

EMPTY_BLOB、EMPTY_CLOB  

clob型にぶち込むときに文字コード指定したりするやつ。

NLS_CHARSET_ID  

バッファにためて

GET_RAWファンクション  

ためたバッファをファイルに吐く。んで、バッファフラッシュ。ファイルから読み込んで取得したバイナリデータをメモリにのせてファイルに吐いた後、バッファをクリーンするイメージかな。

PUT_RAWプロシージャ  

事前準備

OSのディレクトリをオラクルアプリが認識できるようにマウントさせる。sqlでそういうことできるんだもんな。べんりだな。

コード表示

--sys session
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

--aine session with dba role

select * from dba_directories;
select * from dba_tab_privs where table_name = 'DP_IN_AINE_DIR';

コンスト定義

定義したらいい

コード表示

create or replace package const___symbolic_character___
as
	comma___ constant varchar2(100) := ',';
	coron___ constant varchar2(100) := ':';
    dot___ constant varchar2(100) := '.';
	equal___ constant varchar2(100) := '=';
	left_parentheses___ constant varchar2(100) := '(';
	right_parentheses___ constant varchar2(100) := ')';
	semi_coron___ constant varchar2(100) := ';';
	single_quote___ constant varchar2(100) := '''';
	space___ constant varchar2(100) := ' ';
    under_score___ constant varchar2(100) := '_';
    function of___comma___(p_comma___ varchar2 := comma___) return varchar2;
    function of___coron___(p_coron___ varchar2 := coron___) return varchar2;
	function of___dot___(p_dot___ varchar2 := dot___) return varchar2;
    function of___equal___(p_equal___ varchar2 := equal___) return varchar2;
    function of___left_parentheses___(p_left_parentheses___ varchar2 := left_parentheses___) return varchar2;
    function of___right_parentheses___(p_right_parentheses___ varchar2 := right_parentheses___) return varchar2;
    function of___semi_coron___(p_semi_coron___ varchar2 := semi_coron___) return varchar2;
    function of___single_quote___(p_single_quote___ varchar2 := single_quote___) return varchar2;
    function of___space___(p_space___ varchar2 := space___) return varchar2;
    function of___under_score___(p_under_score___ varchar2 := under_score___) return varchar2;
end;
/
create or replace package body const___symbolic_character___
is
    function of___comma___(p_comma___ varchar2) return varchar2 is rt varchar2(100) := p_comma___; begin return rt; end; 
    function of___coron___(p_coron___ varchar2) return varchar2 is rt varchar2(100) := p_coron___; begin return rt; end; 
    function of___dot___(p_dot___ varchar2) return varchar2 is rt varchar2(100)  :=  dot___; begin return rt; end; 
    function of___equal___(p_equal___ varchar2) return varchar2 is rt varchar2(100) := p_equal___; begin return rt; end; 
    function of___left_parentheses___(p_left_parentheses___ varchar2) return varchar2 is rt varchar2(100) := p_left_parentheses___; begin return rt; end; 
    function of___right_parentheses___(p_right_parentheses___ varchar2) return varchar2 is rt varchar2(100) := p_right_parentheses___; begin return rt; end; 
    function of___semi_coron___(p_semi_coron___ varchar2) return varchar2 is rt varchar2(100) := p_semi_coron___; begin return rt; end; 
    function of___single_quote___(p_single_quote___ varchar2) return varchar2 is rt varchar2(100) := p_single_quote___; begin return rt; end; 
    function of___space___(p_space___ varchar2) return varchar2 is rt varchar2(100) := p_space___; begin return rt; end;
    function of___under_score___(p_under_score___ varchar2) return varchar2 is rt varchar2(100)  :=  under_score___; begin return rt; end; 
end;
/

ファイル系のコンスト定義

定義したらいい

コード表示

create or replace package const___file_usage___
is
    prefix_file_name___ constant varchar2(100):='dup';
    file_png_extension___ constant varchar2(100):='png';
    file_gif_extension___ constant varchar2(100):='gif';
    file_jpg_extension___ constant varchar2(100):='jpg';
    file_jpeg_extension___ constant varchar2(100):='jpeg';
    file_mp3_extension___ constant varchar2(100):='mp3';
    function of___prefix_file_name___(p_prefix_file_name___ varchar2 := prefix_file_name___) return varchar2;
    function of___file_png_extension___(p_file_png_extension___ varchar2 := file_png_extension___) return varchar2;
    function of___file_gif_extension___(p_file_gif_extension___ varchar2 := file_gif_extension___) return varchar2;
    function of___file_jpg_extension___(p_file_jpg_extension___ varchar2 := file_jpg_extension___) return varchar2;
    function of___file_jpeg_extension___(p_file_jpeg_extension___ varchar2 := file_jpeg_extension___) return varchar2;
    function of___file_mp3_extension___(p_file_mp3_extension___ varchar2 := file_mp3_extension___) return varchar2;
end;
/
create or replace package body const___file_usage___
is
    function of___prefix_file_name___(p_prefix_file_name___ varchar2) return varchar2 is rt varchar2(100) := prefix_file_name___; begin return rt; end; 
    function of___file_png_extension___(p_file_png_extension___ varchar2) return varchar2 is rt varchar2(100) := file_png_extension___; begin return rt; end; 
    function of___file_gif_extension___(p_file_gif_extension___ varchar2) return varchar2 is rt varchar2(100) := file_gif_extension___; begin return rt; end; 
    function of___file_jpg_extension___(p_file_jpg_extension___ varchar2) return varchar2 is rt varchar2(100) := file_jpg_extension___; begin return rt; end; 
    function of___file_jpeg_extension___(p_file_jpeg_extension___ varchar2) return varchar2 is rt varchar2(100) := file_jpeg_extension___; begin return rt; end; 
    function of___file_mp3_extension___(p_file_mp3_extension___ varchar2) return varchar2 is rt varchar2(100) := file_mp3_extension___; begin return rt; end; 
end;
/

スクリプト

ファイルのバイナリは吐き出すと、マウントしたファイルのバイナリーデータはメモリからなくなるから、毎回オープンしてメモリにのせないといけないとおもう。そういうもんじゃないかな。パッケージプロシージャにしました。引数に複製する個数と複製元ファイル名(拡張子除く)と拡張子を指定してgo。ちなみに同じファイル名存在するとora errorなので、take care。ユーザー定義型で例外きってもいいと思う。

コード表示

create or replace package pkg___fio_usage___
is
	procedure proc___dup___(p_dup_cnt number,p_src_file varchar2,p_ext number);
end;
/

create or replace package body pkg___fio_usage___
is
	procedure proc___dup___(p_dup_cnt number,p_src_file varchar2,p_ext number)
	is
		src_file   utl_file.file_type;
		dst_file   utl_file.file_type;
		buffer     raw(32767);
		dup_cnt number := p_dup_cnt;
		src_fnm varchar2(100) := '';
		dup_fnm varchar2(100) := '';
	begin
		loop
			exit when dup_cnt = 0;
			src_fnm := p_src_file
			|| const___symbolic_character___.of___dot___
			|| const___file_usage___.of___file_png_extension___;
			dup_fnm := const___file_usage___.of___prefix_file_name___ 
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| p_src_file
			|| const___symbolic_character___.of___under_score___
			|| lpad(dup_cnt,2,0) 
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___under_score___
			|| const___symbolic_character___.of___dot___
			|| case
					when '01' = lpad(p_ext,2,0) then const___file_usage___.of___file_png_extension___
					when '02' = lpad(p_ext,2,0) then const___file_usage___.of___file_gif_extension___
					when '03' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpg_extension___
					when '04' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpeg_extension___
					when '05' = lpad(p_ext,2,0) then const___file_usage___.of___file_mp3_extension___
					else null
				end
			;
			src_file := utl_file.fopen('DP_IN_AINE_DIR', src_fnm, 'rb', 32767);
			dst_file := utl_file.fopen('DP_IN_AINE_DIR', dup_fnm, 'wb', 32767);
			loop
				begin
					utl_file.get_raw(src_file, buffer, 32767);
					utl_file.put_raw(dst_file, buffer, true);
				exception
					when no_data_found then
						exit;
				end;
			end loop;
			utl_file.fclose(dst_file);
			utl_file.fclose(src_file);
			dup_cnt := dup_cnt - 1;
		end loop;
	end;
end;
/

exec pkg___fio_usage___.proc___dup___(3,'test',1);
/

結果

コピーされたバイナリファイルは644でつかられるぽい。

コード表示

[oracle@f285aba0589a in]$ ll
total 24
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png
[oracle@f285aba0589a in]$ ll
total 60
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_01___.png
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_02___.png
-rw-r--r--. 1 oracle oinstall 9611 Apr  2 00:49 dup___test_03___.png
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png

作成したファイル取り込もうとおもう

EMPTY_BLOB()してるから必ず、blb_locは0となってポインタは先頭から処理開始するイメージかな。ただ、複製したファイルだとちゃんと取り込めてるかわからんから、異なるバイナリデータを3つ用意してみる。

コード表示

[oracle@f285aba0589a in]$ ll
total 52
-rw-r--r--. 1   1000     1001 4287 Feb 24 13:09 dup___test_01___.png
-rw-r--r--. 1   1000     1001 5221 Feb 25 08:18 dup___test_02___.png
-rw-r--r--. 1   1000     1001 9611 Mar 22 06:46 dup___test_03___.png
-rw-r--r--. 1   1000     1001   61 Mar 31 19:50 tbl___loader___.dat
-rw-r--r--. 1 oracle oinstall 2283 Mar 31 20:05 tbl___loader___.log
-rw-r--r--. 1 oracle oinstall  654 Mar 31 20:05 tbl___loader____18130.log_xt
-rwxrwxrwx. 1 oracle oinstall 9611 Apr  1 07:18 test.png

取込スクリプト

苦戦したのが、dst_oftとsrc_oftの2つには常に1を設定しておきたいが、コンストできないから、変数無駄に増えてしまったこと。うまくできないかなー。

コード表示

drop table tbl___blob___ purge;
create table tbl___blob___ (rn number,blb blob);
select * from tbl___blob___;
desc tbl___blob___;

create or replace procedure proc___imp___(p_dup_cnt number,p_src_file varchar2,p_ext number)
as
	bfl_pnt bfile;
	blb_loc blob;
	rn number;
	dst_oft number := 1;--not allowable constant variable
	src_oft number := 1;--not allowable constant variable
	tmp_dst_oft constant number := 1;--error escape
	tmp_src_oft constant number := 1;--error escape
	dup_cnt number := p_dup_cnt;
	src_fnm varchar2(100) := '';
begin
	loop
		exit when dup_cnt = 0;
		src_fnm := const___file_usage___.of___prefix_file_name___ 
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| p_src_file
		|| const___symbolic_character___.of___under_score___
		|| lpad(dup_cnt,2,0) 
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___under_score___
		|| const___symbolic_character___.of___dot___
		|| case
				when '01' = lpad(p_ext,2,0) then const___file_usage___.of___file_png_extension___
				when '02' = lpad(p_ext,2,0) then const___file_usage___.of___file_gif_extension___
				when '03' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpg_extension___
				when '04' = lpad(p_ext,2,0) then const___file_usage___.of___file_jpeg_extension___
				when '05' = lpad(p_ext,2,0) then const___file_usage___.of___file_mp3_extension___
				else null
			end
		;
		dbms_output.put_line(src_fnm);
		bfl_pnt := bfilename('DP_IN_AINE_DIR', src_fnm);
		select nvl(max(rn), 0) + 1 into rn from tbl___blob___;
		insert into tbl___blob___ values (rn, empty_blob()) returning blb into blb_loc;
		begin
			dbms_lob.fileopen(bfl_pnt, dbms_lob.file_readonly);
		exception when utl_file.invalid_operation then
			rollback;
			exit;
		end;
		dbms_output.put_line ( 'writable size : ' || dst_oft || 'bytes' );
		dbms_lob.loadblobfromfile (
		   blb_loc
		   ,bfl_pnt
		   ,dbms_lob.lobmaxsize
		   ,dst_oft
		   ,src_oft);
		commit;
		dbms_output.put_line ( 'writable size : ' || dst_oft || 'bytes' );
		dbms_lob.fileclose(bfl_pnt);
		dup_cnt := dup_cnt - 1;
		src_oft := tmp_src_oft;
		dst_oft := tmp_dst_oft;
	end loop;
end;
/

exec proc___imp___(3,'test',1);
/

実行ログ

できてそう

コード表示

dup___test_03___.png
writable size : 1bytes
writable size : 9612bytes
dup___test_02___.png
writable size : 1bytes
writable size : 5222bytes
dup___test_01___.png
writable size : 1bytes
writable size : 4288bytes


PL/SQLプロシージャが正常に完了しました。


結果

はじめてつくったけど、面白いねgif。ちょっとゆがんでるけど
。ご愛嬌ってことで。

エラーメッセージの実装について with plsql

参考文献

思い通りに作れてよかった

PL/SQL で使用する ユーザー定義例外の宣言と呼び出し

ポイントは一元化

思い通りに作れてよかった

コード表示

set serveroutput on;
create or replace package const___exception_usage___
as
	expt_20000___ exception;
	expt_20001___ exception;
	expt_20002___ exception;
	pragma exception_init(expt_20000___,-20000);
	pragma exception_init(expt_20001___,-20001);
	pragma exception_init(expt_20002___,-20002);
	function of___expt_20000_message___ return varchar2;
	function of___expt_20001_message___ return varchar2;
	function of___expt_20002_message___ return varchar2;
end;
/

create or replace package body const___exception_usage___
as
	function of___expt_20000_message___ return varchar2 as rt varchar2(100) := 'exception_20000'; begin return rt; end; 	
	function of___expt_20001_message___ return varchar2 as rt varchar2(100) := 'exception_20001'; begin return rt; end; 	
	function of___expt_20002_message___ return varchar2 as rt varchar2(100) := 'exception_20002'; begin return rt; end; 	
end;
/

create or replace package const___symbolic_character___
as
	begin___ constant varchar2(100):='begin';
	end___ constant varchar2(100):='end';
	space___ constant varchar2(100):=' ';
	semi_coron___ constant varchar2(100):=';';
	under_score___ constant varchar2(100):='___';
	left_parentheses___ constant varchar2(100):='(';
	right_parentheses___ constant varchar2(100):=')';
	comma___ constant varchar2(100):=',';
    dot___ constant varchar2(100):='.';
    minus___ constant varchar2(100):='-';

    function of___begin___(p_begin___ varchar2:=begin___) return varchar2;
    function of___end___(p_end___ varchar2:=end___) return varchar2;
    function of___space___(p_space___ varchar2:=space___) return varchar2;
    function of___semi_coron___(p_semi_coron___ varchar2:=semi_coron___) return varchar2;
    function of___under_score___(p_under_score___ varchar2:=under_score___) return varchar2;
    function of___left_parentheses___(p_left_parentheses___ varchar2:=left_parentheses___) return varchar2;
    function of___right_parentheses___(p_right_parentheses___ varchar2:=right_parentheses___) return varchar2;
    function of___comma___(p_comma___ varchar2:=comma___) return varchar2;
    function of___dot___(p_dot___ varchar2:=dot___) return varchar2;
    function of___minus___(p_minus___ varchar2:=minus___) return varchar2;
end;
/

create or replace package body const___symbolic_character___
as
    function of___begin___(p_begin___ varchar2) return varchar2 is rt varchar2(100):= p_begin___; begin return rt; end; 
    function of___end___(p_end___ varchar2) return varchar2 is rt varchar2(100):= p_end___; begin return rt; end; 
    function of___space___(p_space___ varchar2) return varchar2 is rt varchar2(100):= p_space___; begin return rt; end;
    function of___semi_coron___(p_semi_coron___ varchar2) return varchar2 is rt varchar2(100):= p_semi_coron___; begin return rt; end; 
    function of___under_score___(p_under_score___ varchar2) return varchar2 is rt varchar2(100):= p_under_score___; begin return rt; end; 
    function of___left_parentheses___(p_left_parentheses___ varchar2) return varchar2 is rt varchar2(100):=p_left_parentheses___; begin return rt; end; 
    function of___right_parentheses___(p_right_parentheses___ varchar2) return varchar2 is rt varchar2(100):=p_right_parentheses___; begin return rt; end; 
    function of___comma___(p_comma___ varchar2) return varchar2 is rt varchar2(100):=p_comma___; begin return rt; end; 
    function of___dot___(p_dot___ varchar2) return varchar2 is rt varchar2(100) := dot___; begin return rt; end; 
    function of___minus___(p_minus___ varchar2) return varchar2 is rt varchar2(100) := minus___; begin return rt; end; 
end;
/

create or replace procedure proc___raise_exception___(p_expt_no in number)
as
	call_exception varchar2(100) := 'raise_application_error'
	||const___symbolic_character___.of___left_parentheses___
	||const___symbolic_character___.of___minus___
	||p_expt_no
	||const___symbolic_character___.of___comma___
	||'const___exception_usage___'
	||const___symbolic_character___.of___dot___
	||'of___expt_'
	|| p_expt_no
	||'_message___'
	||const___symbolic_character___.of___right_parentheses___
	||const___symbolic_character___.of___semi_coron___
	;
	exe_script varchar2(100) := '';
begin
	exe_script := const___symbolic_character___.of___begin___
	||const___symbolic_character___.of___space___
	||call_exception
	||const___symbolic_character___.of___space___
	||const___symbolic_character___.of___end___
	||const___symbolic_character___.of___semi_coron___
	;
	execute immediate exe_script;
end;
/

create or replace procedure proc___disp_err_msg___(p_srt_no in number,p_end_no in number)
as
	exe_script varchar2(100) := const___symbolic_character___.of___begin___
	||const___symbolic_character___.of___space___
	|| 'proc___raise_exception___'
	||const___symbolic_character___.of___left_parentheses___
	|| ':i'
	||const___symbolic_character___.of___right_parentheses___
	||const___symbolic_character___.of___semi_coron___
	||const___symbolic_character___.of___space___
	||const___symbolic_character___.of___end___
	||const___symbolic_character___.of___semi_coron___
	;
begin
	for i in 20000 + p_srt_no - 1 .. 20000 + p_end_no - 1 loop
		begin
			execute immediate exe_script using i;
		exception when others then dbms_output.put_line('[ '|| sqlcode||']'||sqlerrm);
		end;
	end loop;
end;
/


実行ログ

コード表示

exec proc___disp_err_msg___(1,5);
/

[ -20000]ORA-20000: exception_20000
[ -20001]ORA-20001: exception_20001
[ -20002]ORA-20002: exception_20002
[ -6550]ORA-06550: 行1、列65:
PLS-00302: コンポーネントOF___EXPT_20003_MESSAGE___を宣言してください。
[ -6550]ORA-06550: 行1、列65:
PLS-00302: コンポーネントOF___EXPT_20004_MESSAGE___を宣言してください。


PL/SQLプロシージャが正常に完了しました。


exec proc___raise_exception___(20000);
/
exec proc___raise_exception___(20001);
/
exec proc___raise_exception___(20002);
/


次のコマンドの開始中にエラーが発生しました : 行 116 -
BEGIN proc___raise_exception___(20000); END;
エラー・レポート -
ORA-20000: exception_20000
ORA-06512: 行1
ORA-06512: "AINE.PROC___RAISE_EXCEPTION___", 行27
ORA-06512: 行1
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

次のコマンドの開始中にエラーが発生しました : 行 118 -
BEGIN proc___raise_exception___(20001); END;
エラー・レポート -
ORA-20001: exception_20001
ORA-06512: 行1
ORA-06512: "AINE.PROC___RAISE_EXCEPTION___", 行27
ORA-06512: 行1


次のコマンドの開始中にエラーが発生しました : 行 120 -
BEGIN proc___raise_exception___(20002); END;
エラー・レポート -
ORA-20002: exception_20002
ORA-06512: 行1
ORA-06512: "AINE.PROC___RAISE_EXCEPTION___", 行27
ORA-06512: 行1


sleepとかの話

参考文献

ここに書いてあることを理解できるようになる。まだできていない。

DBMS_LOCK.SLEEPの代替SQLをいろいろ考えてみよう(Oracle)  

コード表示

declare
	SRT_TP timestamp;
	END_TP timestamp;
begin
	select SYSTIMESTAMP into SRT_TP from DUAL;
	DBMS_SESSION.SLEEP(1);
	select SYSTIMESTAMP into END_TP from DUAL;
	DBMS_OUTPUT.PUT_LINE(END_TP - SRT_TP);
	select SYSTIMESTAMP into SRT_TP from DUAL;
	DBMS_SESSION.SLEEP(5);
	select SYSTIMESTAMP into END_TP from DUAL;
	DBMS_OUTPUT.PUT_LINE(END_TP - SRT_TP);
end;
/

+000000000 00:00:00.999889000
+000000000 00:00:04.999971000


PL/SQLプロシージャが正常に完了しました。