반응형
오라클 SQL 개발자에서 clob 필드 데이터를 내보내는 방법
오라클 SQL 개발자에서 clob 필드 데이터를 내보내는 방법.현재 clob 필드 데이터를 Oracle sql developer에서 내보낼 수 없습니다.
데이터를 내보내고 가져올 수 없으며 삽입 문 집합으로 사용하려면 SQL Developer의 기본 제공 형식 지정 도구를 사용하여 CLOB를 문자열 리터럴로 사용할 수 있을 정도로 작은 여러 청크로 자동으로 분할한 다음 결과를 파일로 스풀링할 수 있습니다.
spool clob_export.sql
select /*insert*/ * from your_table;
spool off
최신 버전에서는 쿼리를 수정할 필요 없이 명령을 사용하여 출력 형식을 제어할 수 있습니다. 이는 다음과 같습니다.
set sqlformat insert
spool clob_export.sql
select * from your_table;
spool off
생성된 삽입 문은 다음과 같습니다.
REM INSERTING into YOUR_TABLE
SET DEFINE OFF;
Insert into YOUR_TABLE (ID,CLOB_COLUMN) values (1,TO_CLOB('... up to 4k of characters with quotes escaped ...')
|| TO_CLOB('... up to 4k of characters with quotes escaped ...')
|| TO_CLOB('... up to 4k of characters with quotes escaped ...')
...
|| TO_CLOB('... up to 4k of characters with quotes escaped ...'));
다음 EXP_IMP_LOB 패키지는 단순 SQL(텍스트) 파일을 사용하여 CLOB, NCLOB, BLOB 유형 열 데이터를 내보내고 가져올 수 있습니다.
사용 방법:
먼저 소스 스키마와 대상 스키마 모두에 패키지를 설치합니다.내보내기하려면 이 선택을 실행합니다.
select * from table( EXP_IMP_LOB.EXPORT('table_name','lob_column_name','condition') );
여기서 Table_Name 및 LOB_Column_Name은 데이터 열을 정의하고 선택적 조건은 행을 정의합니다.조건이 없으면 모든 행 데이터가 한 행씩 내보내집니다.
예:
select * from table( EXP_IMP_LOB.EXPORT('person','image','id=103' ) );
결과:
/******************************************************
TABLE :PERSON
COLUMN :IMAGE
ROW :103
******************************************************/
BEGIN
EXP_IMP_LOB.IMPORT_NEW;
EXP_IMP_LOB.IMPORT_APPEND ( 'FFD8FFE000104A464....23232323232');
EXP_IMP_LOB.IMPORT_APPEND ( '32323232323232323....798999AA2A3');
.........
EXP_IMP_LOB.IMPORT_APPEND ( 'B2316524267279AA9....51401FFFD9');
EXP_IMP_LOB.IMPORT_UPDATE ( 'PERSON','IMAGE','103' );
COMMIT;
END;
/
따라서 내보내기는 이진 데이터를 400자 길이의 헥사 문자열로 변환하고 이를 기반으로 스크립트를 생성합니다.저는 많은 문자를 상징하기 위해 ...을 사용했습니다. 왜냐하면 그것은 위의 샘플에 불과하기 때문입니다.
결과를 정렬하지 마십시오!
가져오려면 대상 스키마에도 패키지를 설치하고 대상 스키마에서 위의 스크립트를 실행하기만 하면 됩니다.이상입니다.
...추가:
- 원본 및 대상 테이블 이름, 열 이름은 동일해야 합니다!
- 테이블(소스 및 대상 모두)에 기본 키가 있어야 하며 둘 다 동일해야 합니다.
- EXPORT 기능은 기본 키를 자동으로 감지할 수 있습니다.이론적으로 작성된 키도 관리할 수 있습니다.
- 16진수 문자열의 크기는 G_LENGTH 전역 변수에 정의됩니다.200 문자는 400 헥사 문자를 의미합니다.
- 추가 절차:
- IMPORT_NEW : 패키지 변수를 재설정하여 새 LOB를 수락하도록 준비합니다.
- IMPORT_APPEND : 헥사 문자열을 이진 데이터로 변환하고 패키지 변수를 추가합니다.
- IMPORT_UPDATE : 패키지 변수로 지정된 테이블, 행, 열을 업데이트합니다.
- DIRECT_SQL : 글로벌 LOB 변수를 매개 변수로 사용하여 지정된 SQL을 실행합니다.예: EXP_IMP_LOB.DIRECT_SQL('ANY_TABLE (ID, IMAGE ) 값에 삽입(123, :1 )';
/*============================================================================================*/
create or replace package EXP_IMP_LOB is
/*============================================================================================*/
type T_STRING_LIST is table of varchar2( 32000 );
---------------------------------------------------------------------------
function EXPORT ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_WHERE in varchar2 default null
) return T_STRING_LIST pipelined;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure IMPORT_NEW;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure IMPORT_APPEND ( I_RAW in varchar2);
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure DIRECT_SQL ( I_SQL in varchar2 );
---------------------------------------------------------------------------
---------------------------------------------------------------------------
procedure IMPORT_UPDATE ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_PK in varchar2
);
---------------------------------------------------------------------------
end;
/
/*============================================================================================*/
create or replace package body EXP_IMP_LOB is
/*============================================================================================*/
G_TABLE_NAME varchar( 40 );
G_COLUMN_NAME varchar( 40 );
G_COLUMN_TYPE varchar( 40 );
G_PK_KEY varchar( 4000 );
G_PK_LST varchar( 4000 );
G_LENGTH number := 200;
G_BLOB blob;
G_CLOB clob;
---------------------------------------------------------------------------
procedure GET_PK ( I_TABLE_NAME in varchar ) is
---------------------------------------------------------------------------
L_SEP varchar ( 40 ) := ',';
L_DATA_TYPE varchar2( 30 );
begin
G_PK_KEY := '';
G_PK_LST := '';
for L_A_PK in ( select COLUMN_NAME
from USER_CONSTRAINTS UC
, USER_CONS_COLUMNS DBC
where UC.CONSTRAINT_TYPE = 'P'
and DBC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
and DBC.TABLE_NAME = I_TABLE_NAME
order by position
)
loop
if nvl( length( G_PK_KEY ), 0 ) + length( L_A_PK.COLUMN_NAME ) < 4000 then
select DATA_TYPE into L_DATA_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = L_A_PK.COLUMN_NAME;
if instr( L_DATA_TYPE, 'CHAR') > 0 then
G_PK_KEY := G_PK_KEY||'''''''''||'||L_A_PK.COLUMN_NAME||'||''''''''||'''||L_SEP||'''||';
elsif instr( L_DATA_TYPE, 'DATE') > 0 then
G_PK_KEY := G_PK_KEY||'''TO_DATE(''''''||TO_CHAR('||L_A_PK.COLUMN_NAME||',''YYYY.MM.DD HH24:MI:SS'')||'''''',''''YYYY.MM.DD HH24:MI:SS'''')''||'''||L_SEP||'''||';
else
G_PK_KEY := G_PK_KEY||L_A_PK.COLUMN_NAME||'||'''||L_SEP||'''||';
end if;
G_PK_LST := G_PK_LST||L_A_PK.COLUMN_NAME||L_SEP;
end if;
end loop;
G_PK_KEY := substr( G_PK_KEY, 1, length( G_PK_KEY ) - ( 6 + length( L_SEP ) ) );
G_PK_LST := substr( G_PK_LST, 1, length( G_PK_LST ) - length(L_SEP));
end;
---------------------------------------------------------------------------
function EXPORT ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_WHERE in varchar2 default null
) return T_STRING_LIST pipelined is
---------------------------------------------------------------------------
V_BLOB blob;
V_CLOB clob;
V_CUR_SQL varchar( 32000 );
V_LOB_SQL varchar( 32000 );
V_RAW varchar( 32000 );
V_START number;
V_PK varchar( 4000 );
V_REC_SET sys_refcursor;
begin
G_TABLE_NAME := upper( trim( I_TABLE_NAME ) );
G_COLUMN_NAME := upper( trim( I_COLUMN_NAME ) );
GET_PK( G_TABLE_NAME );
select DATA_TYPE into G_COLUMN_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = G_COLUMN_NAME;
if G_COLUMN_TYPE not in ('CLOB','NCLOB','BLOB') then
raise_application_error ( -20001, 'The type of column '||I_COLUMN_NAME||' is not CLOB, NCLOB or BLOB' );
end if;
V_CUR_SQL := 'select '||G_PK_KEY||' from '||G_TABLE_NAME||' where '||nvl( I_WHERE, ' 1 = 1 ');
open V_REC_SET for V_CUR_SQL;
loop
fetch V_REC_SET into V_PK;
exit when V_REC_SET%notfound;
PIPE ROW( '/******************************************************' );
PIPE ROW( ' TABLE :'||G_TABLE_NAME );
PIPE ROW( ' COLUMN :'||G_COLUMN_NAME );
PIPE ROW( ' ROW :'||V_PK );
PIPE ROW( '******************************************************/' );
PIPE ROW( 'BEGIN' );
PIPE ROW( ' EXP_IMP_LOB.IMPORT_NEW;' );
V_LOB_SQL := 'select '||G_COLUMN_NAME||' from '||G_TABLE_NAME||' where ('||G_PK_LST||') in ( select '||V_PK||' from dual )';
if G_COLUMN_TYPE = 'BLOB' then
execute immediate V_LOB_SQL into V_BLOB;
if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
V_START := 1;
for L_I IN 1..ceil( dbms_lob.getlength( V_BLOB ) / G_LENGTH )
loop
V_RAW := dbms_lob.substr( V_BLOB, G_LENGTH, V_START );
PIPE ROW( ' EXP_IMP_LOB.IMPORT_APPEND ( '''||V_RAW||''');' );
V_START := V_START + G_LENGTH;
end loop;
PIPE ROW( ' EXP_IMP_LOB.IMPORT_UPDATE ( '''||G_TABLE_NAME||''','''||G_COLUMN_NAME||''','''||replace(V_PK,'''','''''')||''' ); ');
PIPE ROW( ' COMMIT;' );
end if;
else
execute immediate V_LOB_SQL into V_CLOB;
if nvl( dbms_lob.getlength( V_CLOB ), 0 ) > 0 then
V_START := 1;
for L_I IN 1..ceil( dbms_lob.getlength( V_CLOB ) / G_LENGTH )
loop
V_RAW := UTL_RAW.CAST_TO_RAW( dbms_lob.substr( V_CLOB, G_LENGTH, V_START ) );
PIPE ROW( ' EXP_IMP_LOB.IMPORT_APPEND ( '''||V_RAW||''');' );
V_START := V_START + G_LENGTH;
end loop;
PIPE ROW( ' EXP_IMP_LOB.IMPORT_UPDATE ( '''||G_TABLE_NAME||''','''||G_COLUMN_NAME||''','''||replace(V_PK,'''','''''')||''' ); ');
PIPE ROW( ' COMMIT;' );
end if;
end if;
PIPE ROW( 'END;' );
PIPE ROW( '/' );
PIPE ROW( ' ' );
end loop;
close V_REC_SET;
return;
end;
---------------------------------------------------------------------------
procedure IMPORT_NEW is
---------------------------------------------------------------------------
begin
G_BLOB := null;
G_CLOB := null;
end;
---------------------------------------------------------------------------
procedure IMPORT_APPEND ( I_RAW in varchar2 ) is
---------------------------------------------------------------------------
V_BLOB blob;
begin
V_BLOB := hextoraw( I_RAW );
if nvl( dbms_lob.getlength( V_BLOB ), 0 ) > 0 then
if nvl( dbms_lob.getlength( G_BLOB ), 0 ) = 0 then
G_BLOB := V_BLOB;
else
DBMS_LOB.APPEND( G_BLOB, V_BLOB );
end if;
end if;
end;
---------------------------------------------------------------------------
procedure DIRECT_SQL ( I_SQL in varchar2 ) is
---------------------------------------------------------------------------
begin
if nvl( dbms_lob.getlength( G_BLOB ), 0 ) > 0 then
execute immediate I_SQL using G_BLOB;
else
execute immediate I_SQL using G_CLOB;
end if;
commit;
end;
-- I downloaded this from the Net:
function clobfromblob( p_blob blob ) return clob is
l_clob clob;
l_dest_offsset integer := 1;
l_src_offsset integer := 1;
l_lang_context integer := dbms_lob.default_lang_ctx;
l_warning integer;
begin
if p_blob is null then
return null;
end if;
dbms_lob.createTemporary(lob_loc => l_clob
,cache => false);
dbms_lob.converttoclob(dest_lob => l_clob
,src_blob => p_blob
,amount => dbms_lob.lobmaxsize
,dest_offset => l_dest_offsset
,src_offset => l_src_offsset
,blob_csid => dbms_lob.default_csid
,lang_context => l_lang_context
,warning => l_warning);
return l_clob;
end;
---------------------------------------------------------------------------
procedure IMPORT_UPDATE ( I_TABLE_NAME in varchar2
, I_COLUMN_NAME in varchar2
, I_PK in varchar2
) is
---------------------------------------------------------------------------
V_SQL varchar( 32000 );
begin
G_TABLE_NAME := upper( trim( I_TABLE_NAME ) );
G_COLUMN_NAME := upper( trim( I_COLUMN_NAME ) );
GET_PK( G_TABLE_NAME );
select DATA_TYPE into G_COLUMN_TYPE from user_tab_columns where table_name = G_TABLE_NAME and column_name = G_COLUMN_NAME;
V_SQL := 'update '||I_TABLE_NAME||' set '||I_COLUMN_NAME||' = :1 where ('||G_PK_LST||') in ( select '||I_PK||' from dual )';
if G_COLUMN_TYPE in ( 'CLOB', 'NCLOB' ) then
G_CLOB := clobfromblob ( G_BLOB );
G_BLOB := null;
DIRECT_SQL( V_SQL );
elsif G_COLUMN_TYPE in ( 'BLOB' ) then
DIRECT_SQL( V_SQL );
end if;
end;
end;
/
데이터를 저장했습니다.NCLOB테이블을 마우스 오른쪽 단추로 클릭하고 선택했을 때 내보낸 데이터에 표시되지 않는 필드Export...아래와 같이
다음은 효과적인 접근 방식입니다.
나는 테이블을 열고 테이블로 전환했습니다.Data탭, 랜덤 필드를 마우스 오른쪽 단추로 클릭하고 선택Export...마법사를 클릭하여NCLOB필드가 내보낸 sql 문에 있습니다.
CLOB 열이 있는 테이블을 한 데이터베이스 서버에서 다른 데이터베이스 서버로 복사하려고 할 때 이 문제가 발생했습니다.Oracle SQL Developer의 "Tools > Copy Database" 도구는 대상 테이블에 대한 다른 이름이 필요했기 때문에 적합한 선택이 아니었습니다. 그래서 저는 "Tools > Export Database"를 사용해야 했습니다. 그러면 .sql 파일에 사용자를 위한 삽입 문을 생성합니다.
수행한 단계:
원본 DB에서 실행:
SELECT someColumA, someColumB, SubStr(myClobColumn, 1, 4000) myClobColumn_part1, SubStr(myClobColumn, 4001, 8000) myClobColumn_part2, . . . SubStr(Clob_field, .., ..) Clob_field_partN, OtherColumns FROM YourTable ;마우스 오른쪽 버튼을 클릭하여 파일로 내보내면 모든 삽입 문이 임시 테이블인 EXPORT_TABLE에 기록됩니다.
대상 DB 서버에서 해당 파일을 실행하여 EXPORT_TABLE을 생성합니다.
이제 처음 4000자를 가져오려면 다음을 실행합니다.
Insert Into YourDestinationTable(.., myClobColumn, ..) SELECT .., myClobColumn_part1, .. FROM EXPORT_TABLE ;다음을 실행하여 나머지 Clob 부품을 추가합니다.
Update YourDestinationTable A set myClobColumn = myClobColumn || (Select myClobColumn_part2 From EXPORT_TABLE B Where B.tableKey = A.tableKey and myClobColumn_part2 is not null) ; Update YourDestinationTable A set myClobColumn = myClobColumn || (Select myClobColumn_part3 From EXPORT_TABLE B Where B.tableKey = A.tableKey and myClobColumn_part3 is not null) ; ...
N부까지
모든 작업이 완료됩니다.
삽입, clob 또는 blob 필드를 내보낼 때 삽입 쿼리에 포함되지 않는 것과 같은 문제가 발생합니다.
이 문제는 삽입 제한으로 인해 발생하는 문제로, 클러스터 데이터 유형 크기에 사용할 수 없습니다.
솔루션:
XML, PDF, 로더 또는 Json으로 데이터를 내보냅니다.그런 다음 나는 clob 또는 blob 데이터를 얻었습니다.
Tools > Database Export > select connection > 데이터만 원하는 경우 export DDL 선택 해제 > 내보내기 데이터만 확인 > 포맷 선택 : XML > next > next > next > next > finish
파일을 열어 해당 XML 파일을 저장하고 확인합니다.
즐기세요...
아래 코드를 사용하여 clob 데이터에 대한 업데이트 스크립트를 만들 수 있습니다.
DECLARE
TYPE varchar2_t IS TABLE OF VARCHAR2 (32767)
INDEX BY BINARY_INTEGER;
read_str VARCHAR2 (32000);
V_records varchar2_t;
PROCEDURE Clob_to_records (p_clob IN CLOB, p_records OUT varchar2_t)
IS
v_record_separator VARCHAR2 (2) := CHR (13) || CHR (10);
v_last INTEGER;
v_current INTEGER;
BEGIN
-- If the file has a DOS newline (cr+lf) - manually created - use above
-- If the file does not have a DOS newline, use a Unix newline (lf)
IF (NVL (DBMS_LOB.INSTR (p_clob,
v_record_separator,
1,
1),
0) = 0)
THEN
v_record_separator := CHR (10);
END IF;
v_last := 1;
LOOP
v_current :=
DBMS_LOB.INSTR (p_clob,
v_record_separator,
v_last,
1);
EXIT WHEN (NVL (v_current, 0) = 0);
p_records (p_records.COUNT + 1) :=
DBMS_LOB.SUBSTR (p_clob, v_current - v_last, v_last);
v_last := v_current + LENGTHB (v_record_separator);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error ' || SQLERRM);
END Clob_to_records;
BEGIN
-- DBMS_OUTPUT.put_line ('Processing Clob');
FOR eachrec
IN (SELECT a.clob_data
FROM table1 a
WHERE COLUMN1=CONDITION1 )
LOOP
Clob_to_records (eachrec.data, v_records);
DBMS_OUTPUT.put_line ('update table2 a set a.clob_data =');
FOR i IN 1 .. v_records.COUNT
LOOP
read_str := v_records (i);
IF i = 1
THEN
DBMS_OUTPUT.put_line ('TO_CLOB(''' || read_str || ''')');
ELSE
DBMS_OUTPUT.put_line ('||CHR(10)|| TO_CLOB(''' || read_str || ''')');
END IF;
END LOOP;
DBMS_OUTPUT.put_line (' WHERE COLUMN1=CONDITION1;');
END LOOP;
END;
언급URL : https://stackoverflow.com/questions/42244941/how-to-export-clob-field-datas-in-oracle-sql-developer
반응형
'programing' 카테고리의 다른 글
| 'finally'는 항상 파이썬에서 실행됩니까? (0) | 2023.06.13 |
|---|---|
| Next 2와 3에서 현재 경로 이름을 얻는 방법은 무엇입니까? (0) | 2023.06.13 |
| 사용자 지정 구성 요소 | 초기화 전에 '스토어'에 액세스할 수 없습니다. (0) | 2023.06.13 |
| rs.last()가 전달 전용 결과 집합에 대해 잘못된 작업을 제공함: last (0) | 2023.06.13 |
| dplyr을 사용한 상대 빈도/비율 (0) | 2023.06.13 |