LONG 형을 VARCHAR2형으로 변환
오라클 sys.dbms_metadata_util.long2varchar() 패키지
-- 테이블1 생성
CREATE TABLE TBL_NEWS(NEWS LONG);
-- long 형 컬럼에 데이터 입력
insert into TBL_NEWS (news) values ('d1234321dfsadsa');
commit;
select * from TBL_NEWS;
-- 테이블2 생성 (테이블1을 테이블2에 입력할 예정임=> 그냥 입력시 에러 발생함.)
CREATE TABLE TMP_LONG(TMP_NEWS LONG);
select * from TMP_LONG;
INSERT INTO TMP_LONG AS SELECT * FROM TBL_NEWS.
- 조치 => 패키지 실행 권한 추가 sys유저로 실행
GRANT Execute on DBMS_METADATA_UTIL TO DBCAFE;
INSERT INTO TMP_LONG
(SELECT sys.dbms_metadata_util.long2varchar(4000,'TBL_NEWS','NEWS',rowid) from dual);
or
INSERT INTO TMP_LONG
(SELECT sys.dbms_metadata_util.long2varchar(length =>4000,tab =>'TBL_NEWS',col =>'NEWS',row =>rowid));
commit;
ALL_TAB_COLUMNS 의 data_default 컬럼(long type) 조회
=== dbms_xmlgen.getxmltype XML 쿼리 이용 ===
<source lang=sql>
select owner
, table_name
, column_name
, data_type
, data_length
, case
when data_precision is null then 0
else data_precision
end data_precision
, case
when data_scale is null then 0
else data_scale
end data_scale
, nullable
, column_id
, default_length
, case
when default_length is null then '0'
else
extractvalue
( dbms_xmlgen.getxmltype
( 'select data_default from user_tab_columns where table_name = ''' || c.table_name || ''' and column_name = ''' || c.column_name || '''' )
, '//text()' )
end as data_default
from all_tab_columns c
where owner=''
-- where table_name like 'TABLE1'
;