「データベース」カテゴリーアーカイブ

DBクライアントソフトなどでOracle DBへ接続時に「ORA-01109：データベースがオープンされていません。」が表示された場合の原因と対応方法についてメモしておきます。

「ORA-01109：データベースがオープンされていません。」の原因

文字通り接続しようとしているDBがオープンされていないのが原因となります。今回接続先を「ORCLPDB」に指定していましたがそこがオープンされていないようです。
念のためSQL Plusを起動して「show pdbs」コマンドでプラガブル・データベース(PDB)のオープン状態を確認するとやはりオープンされていませんでした。

「ORA-01109：データベースがオープンされていません。」の対処方法

SQL Plusで「alter pluggable database ORCLPDB open;」コマンドを実行し、ORCLPDBをオープンします。
「show pdbs」コマンドでプラガブル・データベース(PDB)のオープン状態を確認します。
これでORCLPDBのオープンだけなら完了していますが、このままPCを再起動するとまた再びクローズ状態に戻ってしまいます。ですので最後に現在オープンされている全てのPDBを自動起動（オープン）するように「alter pluggable database all save state;」コマンドで保存します。

SQL, データベース, ブログ

SQL：文字列や数値項目へ埋め込み文字を挿入して桁数を揃える方法

2022年6月29日管理者コメントする

SQLでゼロ埋めスペース埋めして桁数を揃えたい場合にはLPAD関数やRPAD関数を使用することで取得可能となります。DBMSによっては使用出来ないものもあります。

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
LPAD	○	○	✕	○
RPAD	○	○	✕	○

書式は「LPAD(列名,バイト数,’埋め込み文字’)」、「RPAD(列名,バイト数,’埋め込み文字’)」で指定します。第三引数の埋め込み文字を省略すると半角スペースが埋め込まれます。

サンプルではOracleでの使用例となります。

サンプルテーブル

「GOODS」テーブル

LPAD関数の使用例1

項目「GOODS_CODE」を10桁でゼロ埋めするSQL（クエリー）

Oracle PL/SQL

SELECT LPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;

1

SELECT LPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;
実行結果

LPAD関数の使用例2

項目「GOODS_CODE」を10桁で全角「＊」埋めするSQL（クエリー）

Oracle PL/SQL

SELECT LPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;

1

SELECT LPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;
実行結果
以下のように埋め込み文字へ全角文字を指定すると1文字=2バイト扱いで埋め込まれます。

RPAD関数の使用例1

項目「GOODS_CODE」を10桁でゼロ埋めするSQL（クエリー）

Oracle PL/SQL

SELECT RPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;

1

SELECT RPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;
実行結果

RPAD関数の使用例2

項目「GOODS_CODE」を10桁で全角「＊」埋めするSQL（クエリー）

Oracle PL/SQL

SELECT RPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;

1

SELECT RPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;
実行結果
RPADでも以下のように埋め込み文字へ全角文字を指定すると1文字=2バイト扱いで埋め込まれます。

Oracle, SQL, データベース, ブログ

SQL：WHERE句内で「(+)」を指定する意味

2022年6月28日管理者コメントする

SQLを見てるとたまにWHERE句内で「(+)」と記載されているのを見かけることがあります。

この「(+)」はOracle独自で記載が可能となる外部結合演算子といって、「g.GOODS_CODE = gt.GOODS_CODE(+)」のように指定するとLEFT OUTER JOINと同じ結果を取得することが出来ます。

サンプルテーブル

「GOODS」テーブル
「GOODS_TYPE」テーブル

外部結合演算子を利用したクエリー（SQL）例

SELECT g.name, gt.type_name
FROM USER1.GOODS g, USER1.GOODS_TYPE gt
WHERE g.GOODS_CODE = gt.GOODS_CODE(+);

SELECT g.name, gt.type_name

FROM USER1.GOODS g, USER1.GOODS_TYPE gt

WHERE g.GOODS_CODE = gt.GOODS_CODE(+);

通常の左外部結合でのクエリー（SQL）例

SELECT g.name, gt.type_name
FROM USER1.GOODS g
LEFT OUTER JOIN USER1.GOODS_TYPE gt ON g.GOODS_CODE = gt.GOODS_CODE;

SELECT g.name, gt.type_name

FROM USER1.GOODS g

LEFT OUTER JOIN USER1.GOODS_TYPE gt ON g.GOODS_CODE = gt.GOODS_CODE;

実行結果

外部結合演算子、左外部結合どちらも同じ結果となります。

Oracle, SQL, データベース, トラブル対応, ブログ

Oracle：ユーザー作成時に「ORA-65096」エラーが出た場合の原因と対応方法

2022年6月25日管理者コメントする

Oracleのインストール後にSQL*Plusなどでユーザー作成しようとした際、「ORA-65096」エラーが発生した場合の原因と対応方法についてメモしておきます。

「ORA-65096：共通ユーザーまたはロール名が無効です」の原因

ルートコンテナにローカルユーザーを作成しようとした場合に発生するエラーとなります。
ルートコンテナには共有ユーザー(common user) と呼ばれる特殊なユーザーしか作成することはできません。
Oracle 11gまでと違いOracle 12c以降からは一つのインスタンスには一つのコンテナ・データベース(CDB)と、プラガブル・データベース(PDB)と呼ばれる子DBが存在しています。sysなどのユーザーでログイン直後はコンテナ・データベース(CDB)に接続されている状態となっているため、そのままローカルユーザーを作成しようとしてもエラーが発生してしまうということになります。

「ORA-65096：共通ユーザーまたはロール名が無効です」の対処方法

原因が分かってしまえば対応はシンプルです。接続先がコンテナ・データベース(CDB)であるのがまずいのであればプラガブル・データベース(PDB)に変更してしまえばいいだけです。

まずは「show con_name;」で現在接続されているデータベースを確認します。
次に「select name, open_mode from v$pdbs;」でPDBの名前と現在のOPEN_MODEを確認します。
PDBの名前が「ORCLPDB」というのがわかったのでデータベースの接続先を「ORCLPDB」へ変更します。
もう一度「show con_name;」を実行して接続先が変更されていることを確認します。
接続先がPDBへ変更されたのでもう一度ユーザー作成を実行すると正常に実行されます。

SQL, データベース, ブログ

SQL：TRIM関数を使って任意の文字や文字列を削除する方法

2022年6月21日管理者コメントする

trimは通常、文字列の前後の半角スペースを削除して取得する場合に使用する場合が多いと思いますが、引数へLEADING、TRAILING、BOTHを指定することで任意の文字や文字列を削除して取得することが出来ます。

サンプルテーブル

「goods」テーブル

TRIMに引数「LEADING」を指定した場合

TRIMに引数「LEADING」を指定することで文字列の先頭にある任意の文字や文字列を削除することが出来ます。

クエリー（SQL）例

PgSQL

SELECT TRIM(LEADING '商品' From name) FROM goods ORDER BY id;

1

SELECT TRIM(LEADING '商品' From name) FROM goods ORDER BY id;
実行結果

TRIMに引数「TRAILING」を指定した場合

TRIMに引数「TRAILING」を指定することで文字列の末尾にある任意の文字や文字列を削除することが出来ます。

クエリー（SQL）例

PgSQL

SELECT TRIM(TRAILING 'A' From name) FROM goods ORDER BY id;

1

SELECT TRIM(TRAILING 'A' From name) FROM goods ORDER BY id;
実行結果

TRIMに引数「BOTH」を指定した場合

TRIMに引数「BOTH」を指定することで文字列の先頭と末尾にある任意の文字や文字列を削除することが出来ます。

クエリー（SQL）例

PgSQL

SELECT TRIM(BOTH 'A' From name) FROM goods ORDER BY id;

1

SELECT TRIM(BOTH 'A' From name) FROM goods ORDER BY id;
実行結果

SQL, データベース, ブログ

SQL：大文字 ⇒ 小文字、小文字 ⇒ 大文字へ変換する方法

2022年6月20日管理者コメントする

SQLで大文字⇒小文字に変換する場合はLCASE関数 or LOWER関数、小文字⇒大文字へ変換するにはUCASE関数 or UPPER関数を使用します。DBMS毎で使用出来る関数が異なるので整理しておきます。

サンプルではPostgreSQLでの使用例となります。

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
UCASE ※小文字 ⇒ 大文字	○	✕	✕	✕
UPPER ※小文字 ⇒ 大文字	✕	○	○	○
LCASE ※大文字 ⇒ 小文字	○	✕	✕	✕
LOWER ※大文字 ⇒ 小文字	✕	○	○	○

サンプルテーブル

「goods」テーブル

UPPER関数（小文字 ⇒ 大文字へ変換）の使用例

クエリー（SQL）例

PgSQL

SELECT UPPER(name) FROM goods ORDER BY id;

1

SELECT UPPER(name) FROM goods ORDER BY id;
実行結果

LOWER関数（大文字 ⇒ 小文字へ変換）の使用例

クエリー（SQL）例

PgSQL

SELECT LOWER(name) FROM goods ORDER BY id;

1

SELECT LOWER(name) FROM goods ORDER BY id;
実行結果

SQL, データベース, ブログ

SQLで一部の文字列を取得する方法

2022年6月18日管理者コメントする

SQLで一部の文字列を取得するにはSUBSTR関数やLEFT関数、RIGHT関数を使用することで可能となります。DBMS毎に利用できる関数に違いがありOracleではLEFT関数、RIGHT関数は使用出来ません。

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
SUBSTR or SUBSTRING	○	○	○	○
LEFT	○	○	○	✕
RIGHT	○	○	○	✕

サンプルテーブル

「goods」テーブル

SUBSTR関数の使用例

クエリー（SQL）例
SUBSTR関数は「SUBSTR(文字列, 開始位置, (文字数))」で指定します。第三引数を省略すると第二引数へ指定した文字以降全て取得します。
SUBSTRはSUBSTRINGでも同様の動作となります。

SELECT 
SUBSTR(name,2),   -- 先頭から見て2文字目以降を取得
SUBSTR(name,2,1), -- 先頭から見て2文字目～1文字を取得
SUBSTR(name,2,3)  -- 先頭から見て2文字目～3文字を取得
FROM goods WHERE name = 'ABCDE';

SELECT

SUBSTR(name,2), -- 先頭から見て2文字目以降を取得

SUBSTR(name,2,1), -- 先頭から見て2文字目～1文字を取得

SUBSTR(name,2,3) -- 先頭から見て2文字目～3文字を取得

FROM goods WHERE name = 'ABCDE';

実行結果

LEFT関数の使用例

クエリー（SQL）例
LEFT 関数は「LEFT(文字列, 文字数)」で指定します。

LEFT関数の使用例
PgSQL

SELECT LEFT(name,2) 　 -- 左から2文字を取得 FROM goods WHERE name = 'ABCDE';

1
2
3

SELECT
LEFT(name,2) 　 -- 左から2文字を取得
FROM goods WHERE name = 'ABCDE';
実行結果

RIGHT関数の使用例

クエリー（SQL）例
LEFT 関数は「RIGHT(文字列, 文字数)」で指定します。

RIGHT関数の使用例
PgSQL

SELECT RIGHT(name,2)　 -- 右から2文字を取得 FROM goods WHERE name = 'ABCDE';

1
2
3

SELECT
RIGHT(name,2)　 -- 右から2文字を取得
FROM goods WHERE name = 'ABCDE';
実行結果

SQL, データベース, ブログ

SQLで文字列置換や文字置換する方法

2022年6月16日管理者コメントする

SQLで文字列置換をするにはREPLACE関数を使用することで可能となります。類似の関数にTRANSLATE関数がありますが、こちらは文字列単位ではなく1文字ずつ文字単位で置換します。

ここではPostgreSQLでの実行サンプルをベースに説明していきます。

サンプルテーブル

「goods」テーブル

REPLACE関数の使用例

クエリー（SQL）例
REPLACE関数は第二引数へ指定した文字列と一致した箇所のみ置換します。

REPLACE関数の使用例
PgSQL

SELECT REPLACE(name, '商品A', '012') FROM goods ORDER BY name;

1

SELECT REPLACE(name, '商品A', '012') FROM goods ORDER BY name;
実行結果

TRANSLATE関数の使用例

クエリー（SQL）例
TRANSLATE関数は第二引数へ指定した文字列を１文字ずつ第三引数へしていた文字へ置換します。下記例の場合、’商’ ⇒ ‘0’、’品’ ⇒ ‘1’、’A’ ⇒ ‘2’へ１文字ずつ変換されます。

TRANSLATE関数の使用例
PgSQL

SELECT TRANSLATE(name, '商品A', '012') FROM goods ORDER BY name;

1

SELECT TRANSLATE(name, '商品A', '012') FROM goods ORDER BY name;
実行結果

SQL, データベース, ブログ

SQLで文字列を結合（連結）する方法

2022年6月15日管理者コメントする

SQLで文字列を結合（連結）して取得するためにはCONCAT関数やCONCAT_WS関数、文字列結合子「||」を使用することで可能となります。DBMS毎で微妙に違いはありますが、ここではPostgreSQLでの実行サンプルをベースに説明していきます。

サンプルテーブル

「goods」テーブル

CONCAT関数の使用例

クエリー（SQL）例
CONCAT関数は連結したい文字をカンマ「,」で区切って指定すれば文字列連結可能となります。 Oracleの場合はCONCAT関数の引数は2つしか指定できないのでCONCAT関数を入れ子にして指定する必要があります。面倒なのでそういう場合は文字列結合子「||」を使用します。

CONCAT関数の使用例
PgSQL

SELECT CONCAT('商品名=', name, ', 商品コード=',type_code) AS "連結文字列" FROM goods;

1

SELECT CONCAT('商品名=', name, ', 商品コード=',type_code) AS "連結文字列" FROM goods;
実行結果

文字列結合子「||」の使用例

クエリー（SQL）例
文字列結合子「||」はCONCAT関数と同様の結果を取得できます。連結する文字列が少ない場合はこちらの方が比較的シンプルに記述できます。

文字列結合子「||」の使用例
PgSQL

SELECT ('商品名=' || name || ', 商品コード=' || type_code) AS "連結文字列" FROM goods;

1

SELECT ('商品名=' || name || ', 商品コード=' || type_code) AS "連結文字列" FROM goods;
実行結果

CONCAT_WS関数の使用例

クエリー（SQL）例
CONCAT_WS関数は第一引数にセパレータ（区切り文字）を指定し、第二引数から連結したい文字列をカンマ区切りで指定します。

CONCAT_WS関数の使用例
PgSQL

SELECT CONCAT_WS('-',name, type_code) AS "連結文字列" FROM goods;

1

SELECT CONCAT_WS('-',name, type_code) AS "連結文字列" FROM goods;
実行結果

SQL, データベース, ブログ

SQL：UNION、UNION ALL演算子の使用方法

2022年6月13日管理者コメントする

SQLで複数のSELECTで問い合わせた結果を連結したいという場合は集合演算子関数と言われる「UNION」や「UNION ALL」演算子を使用することで取得可能となります。

イメージ図

UNIONは重複行を削除して取得するので「A & C」を取得出来ます。
UNION ALLは重複行を含めて取得するので「A & B & C」を取得出来ます。

サンプルテーブル

「goods」テーブル

UNIONの使用例

サンプルテーブル「goods」をUNIONで連結して取得するサンプルとなります。

SQL（クエリー）

UNIONで連結して取得するSQL
PgSQL

SELECT id,name,type_code FROM goods WHERE name = '商品A' UNION SELECT id,name,type_code FROM goods WHERE type_code = '101'

1
2
3

SELECT id,name,type_code FROM goods WHERE name = '商品A'
UNION
SELECT id,name,type_code FROM goods WHERE type_code = '101'
出力結果

UNION ALLの使用例

サンプルテーブル「goods」をUNION ALLで連結して取得するサンプルとなります。

SQL（クエリー）

UNIONで連結して取得するSQL
PgSQL

SELECT id,name,type_code FROM goods WHERE name = '商品A' UNION ALL SELECT id,name,type_code FROM goods WHERE type_code = '101'

1
2
3

SELECT id,name,type_code FROM goods WHERE name = '商品A'
UNION ALL
SELECT id,name,type_code FROM goods WHERE type_code = '101'
出力結果

「ORA-01109：データベースがオープンされていません。」の原因

「ORA-01109：データベースがオープンされていません。」の対処方法

関連記事

サンプルテーブル

LPAD関数の使用例1

LPAD関数の使用例2

RPAD関数の使用例1

RPAD関数の使用例2

関連記事

サンプルテーブル

外部結合演算子を利用したクエリー（SQL）例

通常の左外部結合でのクエリー（SQL）例

実行結果

関連記事

「ORA-65096：共通ユーザーまたはロール名が無効です」の原因

「ORA-65096：共通ユーザーまたはロール名が無効です」の対処方法

関連記事

サンプルテーブル

TRIMに引数「LEADING」を指定した場合

TRIMに引数「TRAILING」を指定した場合

TRIMに引数「BOTH」を指定した場合

関連記事

サンプルテーブル

UPPER関数（小文字 ⇒ 大文字へ変換）の使用例

LOWER関数（大文字 ⇒ 小文字へ変換）の使用例

関連記事

サンプルテーブル

SUBSTR関数の使用例

LEFT関数の使用例

RIGHT関数の使用例

関連記事

サンプルテーブル

REPLACE関数の使用例

TRANSLATE関数の使用例

関連記事

サンプルテーブル

CONCAT関数の使用例

文字列結合子「||」の使用例

CONCAT_WS関数の使用例

関連記事

イメージ図

サンプルテーブル

UNIONの使用例

UNION ALLの使用例

関連記事

「駑馬十駕」 IT系情報を中心に調べた事をコツコツ綴っています。

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事