2019年9月30日 (月)

なぜ、そこに、LONG型があるんだ / FAQ

all/dba/user_tab_columns

https://docs.oracle.com/cd/E82638_01/refrn/ALL_TAB_COLUMNS.html#GUID-F218205C-7D76-4A83-8691-BFD2AD372B63

これらのビューは、列の属性関連の情報を持つビューです。
たまに、便利なびゅーではあるのですが、これらのビューをアクセスする使うスクリプトというかPL/SQLでコード書くこともあるのですが、一箇所だけ、使いにくいところがあります。

 

どこかわかります?

下位互換のためだろうと思われるのですが、一般には推奨されていない LONG型の列 が残っています。

ご存知だとは思いますが、一般的なガイドだと、CLOBの利用が推奨されています。
下位互換のためだから仕方ないのだとは思うのですが。

LONG型といえば、とにかく制約が多くて、文字列操作を行うにもめんどくさいわけで、実際に利用したい状況になると、うううううっとなることしばしば。

LONG型

で、普段どうやって、その面倒くさいところを回避しているかといえば、CLOBに変換してしまうことがが多いです。
CLOBにしてしまえば、沢山の制約から解放されますしね :)

以下のような感じで。


SCOTT> l
1 CREATE TABLE my_dba_tab_columns
2 AS
3 SELECT
4 owner
5 ,table_name
6 ,column_name
7 ,data_type
8 ,data_type_mod
9 ,data_type_owner
10 ,data_length
11 ,data_precision
12 ,data_scale
13 ,nullable
14 ,column_id
15 ,TO_CLOB(default_length) AS default_length
16 ,num_distinct
17 ,low_value
18 ,high_value
19 ,density
20 ,num_nulls
21 ,num_buckets
22 ,last_analyzed
23 ,sample_size
24 ,character_set_name
25 ,char_col_decl_length
26 ,global_stats
27 ,user_stats
28 ,avg_col_len
29 ,char_length
30 ,char_used
31 ,v80_fmt_image
32 ,data_upgraded
33 ,histogram
34 ,default_on_null
35 ,identity_column
36 ,sensitive_column
37 ,evaluation_edition
38 ,unusable_before
39 ,unusable_beginning
40 ,collation
41 FROM
42* dba_tab_columns
SCOTT> /

Table created.

SCOTT> desc dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)

SCOTT> desc my_dba_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH CLOB
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
SENSITIVE_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)

 


db tech showcase 2019もおわり、今年も残すところ 3ヶ月あまり。一年早い. そして。
来週は、開催時期を秋に変更してから2回目の多摩川花火大会。天気がよいといいのですが:)

ではまた。

| | コメント (0)

2019年8月25日 (日)

FAQ / PL/SQL PACKAGEでパプリックスコープを持つ定数をSQL文中で利用するには...

かなーり、ご無沙汰しておりました。(本業でいっぱいいっぱいで、という言い訳はこれぐらいにしてw) 偶に聞かれることがあるので、FAQネタから。 パッケージでパブリックなスコープを持つ定数は無名PL/SQLブロックやパッケージ、プロシージャ、ファンクションでしか参照できないんですよねー 例えば、DBMS_CRYPTOパッケージでHASHファンクションを利用してSH-256を作成したいなーと思って、

39.4 DBMS_CRYPTOのアルゴリズム
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_CRYPTO.html#GUID-CE3CF17D-E781-47CB-AEE7-19A9B2BCD3EC
DBMS_CRYPTO.HASH()は関数なのでSQL文から呼びたーい、と以下のような使い方をすると...

SQL> SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual;
SELECT DBMS_CRYPTO.HASH(TO_CLOB('hoge'), DBMS_CRYPTO.HASH_SH2569) AS "SH-256" FROM dual
*
ERROR at line 1:
ORA-00904: "DBMS_CRYPTO"."HASH_SH2569": invalid identifier


SQL>
SQL> select DBMS_CRYPTO.HASH_SH256 from dual;
select DBMS_CRYPTO.HASH_SH256 from dual
*
ERROR at line 1:
ORA-06553: PLS-221: 'HASH_SH256' is not a procedure or is undefined
見事にエラーとなるわけです。 DBMS_CRYPTO.HASH_SH256は、パッケージファンクションではないので...利用可能なのはPL/SQLでのみ。
SQL> set serveroutput on
SQL>
¥SQL>
SQL> begin
2 dbms_output.put_line('DBMS_CRYPTO.HASH_SH256 : ' || DBMS_CRYPTO.HASH_SH256);
3 end;
4 /
DBMS_CRYPTO.HASH_SH256 : 4

PL/SQL procedure successfully completed.
SQL文で活用する為には、ファンクションでラップする必要があります。 以下のように。
SQL> l
1 CREATE OR REPLACE FUNCTION get_hash_sh256_type
2 RETURN NUMBER
3 AS
4 BEGIN
5 RETURN DBMS_CRYPTO.HASH_SH256;
6* END;
SQL> /

Function created.

SQL>
冒頭でエラーとなっていたSQL文をDBMS_CRYPTO.HASH_SH256を返すファンクションを使うように書き換えると、 はい、できました。
SQL> l
1 SELECT
2 DBMS_CRYPTO.HASH(
3 TO_CLOB('hoge')
4 , get_hash_sh256_type()
5 ) AS "SH-256"
6 FROM
7* dual
SQL> /

SH-256
--------------------------------------------------------------------------------
ECB666D778725EC97307044D642BF4D160AABB76F56C0069C71EA25B1E926825

SQL>


露店の焼きそばと焼き鳥を食べつつ、晩夏の夏祭りと、涼しい朝晩の気温で熟睡可能な山形より。 では、では。

| | コメント (0)