oracle

Oracle LONG型をVARCHAR型へ変換する

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型の扱いに限界を感じ途中であきらめていましたが、今回はなんとかやり切ってみます。

1.事前に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
);

2.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>

3.変換後のデータを出力する

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する事ができました。

スポンサーリンク
コピペで使う