仕事メモとか

仕事中に調べた情報とか知ったことをメモしています。
unixコマンド, vim, oracle, putty, postgresql, bash, EXCEL, python, SQL全般 など。
最近は tableau, movabletype とかも触ったりしています。
雑な読書感想とかはこちら

カテゴリ: oracle


oracle SQL Developer で文字をエスケープしようとしたときの話

はい、ちょっと環境が変わってSQL Developer使っています。
SQLで文字列を出すときに&(の半角)みたいなのって特殊文字扱いになるのですが、

select 'aaa & bbb' as dat from dual;

とすると、
「BBB:を入力せよ」みたいになります。
空で実行すると、こうなります。

aaa null

&BBBと解釈され、空がnullになるのでこうなったようです。

ちなみに、DBeaverで実行すると普通に実行されるので、
oracleとかSQLの仕様じゃなく、SQL Developerの仕様のようです。

それじゃエスケープすればいいのかな、と。思ってバックスラッシュとかいろいろやってみました。

select 'aaa \& bbb' as dat from dual;
select 'aaa && bbb' as dat from dual;
select '{aaa & bbb}' as dat from dual;

が、ダメでした。

調べた結果、以下の方法くらいしかなさそうでした。


1.&の後ろに文字指定をしない(後ろを別文字列として結合)
select 'aaa &' || ' bbb' as dat from dual;

2.設定変更
SET DEFINE OFF

3.chr(38)で出す
select 'aaa ' || chr(38) || ' bbb' as dat from dual;


ツールでちょっと見たい、が、作ったSQLを他でも流用したい、
みたいなときは、chr(38)にしておくと無難かと思います。



参考:
http://output-place.blogspot.com/2013/05/sqlplus.html
https://code-examples.net/ja/q/115aca


SQLで正規表現でタブとか改行とか制御文字全般を削除する方法

chr(9) chr(10) chr(13)
とか呪いのように削除していましたが、
そもそも制御文字全般を消してしまえばよいのでは? ということで調べたら普通にありました。

[[:cntrl:]]

ということで、こいつを使うと簡単に行けました。

例:

select regexp_replace(dat, '[[:cntrl:]]', null)
from table

こんなに簡単になりました。



参考:
https://www.saka-en.com/oracle/sql-crlf-tab-delete-or-replace/


微妙なカンマ区切りをSQLの正規表現で頑張る

はい、どちらかというと頭の悪い方向に進んでいる感が否めませんが、
取り急ぎ対応としては良く発生するものです。

こんなデータがあった時に、

,aa,,b,,,b,

これを複数カンマは1つに纏め、前後のカンマは無視する、という形に作り替えようと思います。

正規表現置換で対応してみました。
REGEXP_REPLACE

●実行例
select REGEXP_REPLACE(REGEXP_REPLACE(',aa,,b,,,b,', '[,]+',
','),'^,|,$','') as dat
from dual

最初に複数回発生しているものは全て1カンマに置換。
その後先頭、末尾のカンマはあれば消す、としています。


なんでこんなものを用意する必要があったのかというと、ListAggでデータを構築している場所があったのですが、
気が付くとデータが別列になっており、これらを合体させる必要があった。

{dat1},{dat2},{dat3} ...


とした場合、dat2とかのデータが空の場合、

{dat1},,{dat3}

となってしまい、ListAggの挙動と変わってしまうため、
こんなトリッキーな対応になりました。


参考
https://www.shift-the-oracle.com/sql/regular-expression-metacharacter.html


DatabaseError: column at array pos 0 fetched with error: 1406 が出た

はい、なんか普段見ないエラーコード体系ですが、
この文字面で検索しても出なかったので、あえてこのタイトルそのままにしました。

結論から書くとORA-01406だと思うのですが、
これが出た原因はもう少し複雑でした。

エラーの内容は、適切なデータ型を使ってないため、
値が切り捨てられるという珍しいもの。

参考
https://www.oraexcel.com/oracle-11gR2-ORA-01406/lang-jp


該当SQLを調査してみると、

自動実行環境→エラーになる:ORA-01406
手動実行環境→エラーにならない

なので最初なにが起こっているかわからなかったのですが、
SQL内で特に何もしていなく割り算してる部分がありました。

A / B as xxx

これ、放っておくとBにゼロが入ってくる問題がありそうだったので、
ゼロ対応と、ついでに桁の明示指定をしたところ、上記のエラーも無くなりました。

to_char( case when B > 0 then A / B else 0 end, 'FM999.0000')

きっと割り算した結果、桁の問題から型が正しくなくなる、
みたいな状況だったのでしょう(憶測


ORA-01555 エラーが出たけど、SQLはそんなにヘンそうに見えない

はい、最近高頻度に「ORA-01555」が出るようになってきたので、
ちょっと真面目に調べました。

DatabaseError: ORA-01555: snapshot too old: rollback segment number x
with name "xxxxxxxxxx" too small

こんな内容です。
内容としてはロールバックセグメントの枯渇。
中間テーブルを作ろうとしたときに、その領域が広大すぎるのと、
他で動いてるプログラムとの兼ね合いとかで、発生したりしなかったりと、割と厄介なやつです。

根本解決は不必要に見る領域を増やしてSQLの効率を下げている部分をカットして、
中間テーブルを作成するスピード自体をあげることと、
トランザクションスロットが枯渇するくらいまで並行稼働しないようにする、という感じです。

健康な体を維持するため、運動と適度な食事をとる、
みたいなことを医者に進められたような感じ。

肥大化して領域を圧迫するようなSQLは控えましょう。


参考
http://www.intellilink.co.jp/article/column/ora-report20150901.html
https://www.shift-the-oracle.com/oerrs/ora-01555.html

↑このページのトップヘ