SELECT SQLだけでLONG型をVARCHAR2型へ変換する事はできません。TO_CHAR()や、TO_LOB()ファンクションを使ってLONG型の変換を試みても以下のようなエラーとなります。
- ORA-00932: データ型が一致しません: CHARが予想されましたがLONGです。
- ORA-00932: データ型が一致しません: -が予想されましたがLONGです。
ではどうするか
LONGデータをTO_LOBファンクションで変換してCLOBカラムへINSERTします。そのCLOBをTO_CHARファンクションでVARCHAR型へ変換する。といったイレギュラーな方法をとらないと実現できません。以下リンクではインデックス対象カラムの表示を試みるもLONG型の扱いに限界を感じ途中であきらめていましたが、今回はなんとかやり切ってみます。
１．事前にLONG型をCLOB型へ変換するためのテーブルを用意する
DBA_IND_EXPRESSIONSビューのCOLUMN_EXPRESSIONカラムはLONGで定義されています。これをいったんCLOBで定義したカラムで新たなテーブルを作成する。
create table TAB_INF( INDEX_OWNER varchar2(30) ,INDEX_NAME varchar2(30) ,TABLE_OWNER varchar2(30) ,TABLE_NAME varchar2(30) ,COLUMN_EXPRESSION CLOB ,COLUMN_POSITION number );
２．TO_LOBファンクションを使ってLONG型データをCLOBへ変換しINSERT
不本意ですが、いったんCLOBのカラムへINSERTする事で変換ができました。
insert into tab_inf (INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_EXPRESSION,COLUMN_POSITION) select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TO_LOB(COLUMN_EXPRESSION),COLUMN_POSITION from DBA_IND_EXPRESSIONS t where t.INDEX_OWNER='SOONI' ;
上記insert文で使っているselect文ですが、こちら単体ではエラーが発生し動作しません。 『ORA-00932: データ型が一致しません: -が予想されましたがLONGです。 』が発生します。
SQL> select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TO_LOB(COLUMN_EXPRESSION),COLUMN_POSITION
2 from DBA_IND_EXPRESSIONS t
3 where t.INDEX_OWNER='SOONI'
4 ;
select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TO_LOB(COLUMN_EXPRESSION),COLUMN_POSITION
*
行1でエラーが発生しました。:
ORA-00932: データ型が一致しません: -が予想されましたがLONGです。
-- 以下の通りINSERTでは正常に動作します
SQL> insert into tab_inf
2 (INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_EXPRESSION,COLUMN_POSITION)
3 select INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TO_LOB(COLUMN_EXPRESSION),COLUMN_POSITION
4 from DBA_IND_EXPRESSIONS t
5 where t.INDEX_OWNER='SOONI'
6 ;
5行が作成されました。
SQL>３．変換後のデータを出力する
CLOB型に変換したおかげでTO_CHARも使えますし、VARCHAR2カラムとのUNIONも可能になります。（当然VARCHAR2のサイズを超えると制限がでてきます）
select w.TABLE_OWNER,w.TABLE_NAME,w.INDEX_NAME ,LISTAGG(w.IND_COL||' '||w.DESCEND ,',') within group (order by w.COLUMN_POSITION ) ind_cols from ( select c.TABLE_OWNER,c.TABLE_NAME,c.INDEX_NAME,c.COLUMN_POSITION ,decode(to_char(t.COLUMN_EXPRESSION),null,c.COLUMN_NAME,to_char(t.COLUMN_EXPRESSION)) ind_col ,decode(c.DESCEND,'DESC',c.DESCEND,null) descend ,c.COLUMN_NAME,t.COLUMN_EXPRESSION from dba_ind_columns c left outer join tab_inf t on c.TABLE_OWNER = t.TABLE_OWNER and c.TABLE_NAME = t.TABLE_NAME and c.INDEX_NAME = t.INDEX_NAME and c.COLUMN_POSITION = t.COLUMN_POSITION where c.TABLE_OWNER='SOONI' and c.TABLE_NAME='MST_ADDRESS' order by c.TABLE_OWNER,c.TABLE_NAME,c.INDEX_NAME,c.COLUMN_POSITION ) w group by w.TABLE_OWNER,w.TABLE_NAME,w.INDEX_NAME ;
実行結果
SQL> select w.TABLE_OWNER,w.TABLE_NAME,w.INDEX_NAME
2 ,LISTAGG(w.IND_COL||' '||w.DESCEND ,',') within group (order by w.COLUMN_POSITION ) ind_cols
3 from
4 (
5 select c.TABLE_OWNER,c.TABLE_NAME,c.INDEX_NAME,c.COLUMN_POSITION
6 ,decode(to_char(t.COLUMN_EXPRESSION),null,c.COLUMN_NAME,to_char(t.COLUMN_EXPRESSION)) ind_col
7 ,decode(c.DESCEND,'DESC',c.DESCEND,null) descend
8 ,c.COLUMN_NAME,t.COLUMN_EXPRESSION
9 from dba_ind_columns c left outer join tab_inf t
10 on c.TABLE_OWNER = t.TABLE_OWNER
11 and c.TABLE_NAME = t.TABLE_NAME
12 and c.INDEX_NAME = t.INDEX_NAME
13 and c.COLUMN_POSITION = t.COLUMN_POSITION
14 where
15 c.TABLE_OWNER='SOONI' and c.TABLE_NAME='MST_ADDRESS'
16 order by c.TABLE_OWNER,c.TABLE_NAME,c.INDEX_NAME,c.COLUMN_POSITION
17 ) w
18* group by w.TABLE_OWNER,w.TABLE_NAME,w.INDEX_NAME;
TABLE_OWNER TABLE_NAME INDEX_NAME IND_COLS
______________ ______________ _____________________ _____________________________________________________________________
SOONI MST_ADDRESS PK_MST_ADDRESS OAZA_TOWN_CHOME_CODE
SOONI MST_ADDRESS IDX_MST_ADDRESS_01 CITY_NAME
SOONI MST_ADDRESS IDX_MST_ADDRESS_02 "OAZA_TOWN_CHOME_NAME" DESC
SOONI MST_ADDRESS IDX_MST_ADDRESS_03 TO_CHAR("LATITUDE") ,TO_CHAR("LONGITUDE") ,"PREFECTURES_NAME" DESC
SOONI MST_ADDRESS IDX_MST_ADDRESS_04 PREFECTURES_CODE ,"PREFECTURES_NAME" DESC
SQL>なんとかLONG型に納まっていたデータをVARCHAR2型に合わせてUNIONする事ができました。
