|
최길호
[LIST]
|
2022-06-17 09:40:43, 조회 : 23 |
select 'select /* '||data_type||' */ '||column_name||' from '||owner||'.'||table_name||' where rownum < 5;' from
dba_tab_cols where data_type in ('CLOB')
and table_name not in ('PLAN_TABLE')
and owner in ('SCOTT')
order by owner,table_name,column_name;
// https://oracle-base.com/articles/8i/export-clob
create or replace procedure clob_to_file (p_clob in clob,
p_dir in varchar2,
p_filename in varchar2)
as
l_file utl_file.file_type;
l_buffer varchar2(32767);
l_amount binary_integer := 32767;
l_pos integer := 1;
begin
l_file := utl_file.fopen(p_dir, p_filename, 'w', 32767);
loop
dbms_lob.read (p_clob, l_amount, l_pos, l_buffer);
utl_file.put(l_file, l_buffer);
utl_file.fflush(l_file);
l_pos := l_pos + l_amount;
end loop;
exception
when no_data_found then
-- expected end.
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
end if;
when others then
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
end if;
raise;
end clob_to_file;
/
select * from dba_directories;
declare
l_id varchar2(1000);
l_clob clob;
cursor c1 is
select ESHTSHKY,ESHTDATA
from SCOTT.ESHDOC;
begin
OPEN c1;
loop fetch c1 into l_id,l_clob;
exit when c1%notfound;
if ( length(l_clob) > 1 )then
clob_to_file(p_clob => l_clob,
p_dir => 'EXPDP',
p_filename => l_id );
end if;
end loop;
close c1;
end;
/
// https://oracle-base.com/articles/8i/import-clob
CREATE OR REPLACE PROCEDURE file_to_clob (p_clob IN OUT NOCOPY CLOB,
p_dir in varchar2,
p_filename in varchar2)
as
l_bfile bfile;
l_dest_offset integer := 1;
l_src_offset integer := 1;
l_bfile_csid number := 0;
l_lang_context integer := 0;
l_warning integer := 0;
begin
l_bfile := bfilename(p_dir, p_filename);
dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
dbms_lob.trim(p_clob, 0);
dbms_lob.loadclobfromfile (
dest_lob => p_clob,
src_bfile => l_bfile,
amount => dbms_lob.lobmaxsize,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
bfile_csid => l_bfile_csid ,
lang_context => l_lang_context,
warning => l_warning);
dbms_lob.fileclose(l_bfile);
end file_to_clob;
/
declare
l_id varchar2(1000);
l_clob clob;
cursor c1 is
select ESHTSHKY,ESHTDATA
from SCOTT.ESHDOC for update;
begin
OPEN c1;
loop fetch c1 into l_id,l_clob;
exit when c1%notfound;
if ( length(l_clob) > 1 )then
file_to_clob(p_clob => l_clob,
p_dir => 'EXPDP',
p_filename => l_id );
end if;
end loop;
close c1;
end;
/
ORA-22920: row containing the LOB value is not locked
ORA-06512: at "SYS.DBMS_LOB", line 803
ORA-06512: at "SYS.FILE_TO_CLOB", line 15
-> from tbname FOR UPDATE; 추가
|
3.235.78.122
|
|
06/29 