http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [194]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2896]
PostgreSQL [103]
기타정보 [150]
운영체제 [583]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: ORACLE

 회원가입

clob to file / file to clob / clob_to_file / file_to_clob
최길호 [LIST]   2022-06-17 09:40:43, 조회 : 50


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; 추가
18.207.157.152


  LIST

제목 작성자 작성일 조회
Admin  [ 공지 ] 😃 서비스 시간 08:00 ~ 23:00 / 안되면 나중에 다시 방문 해주세요~.😅    최길호 2008/11/26 113
Admin  [ 필요 하신게 있으시면 쪽지 또는 gilho.kr@gmail.com 메일 주세요~ ]    최길호 2006/07/14 134
Troubleshoot  ORA-12521 listener does not currently know of instance requested in connect descriptor    최길호 2022/08/09 3
Script  ogg save table DML total count to table lv:9   최길호 2022/08/08 4
Admin  UDI-31626: operation generated ORACLE error 31626    최길호 2022/08/02 7
SQL,PL*SQL  제품 코드, 정기 검사, 다음 검사 검색, 재신청 하면 재신청일 기준 다음 검사 검색    최길호 2022/07/28 16
Troubleshoot  ORA-1653: unable to extend table SYS.IDL_UB2$ by 128 in tablespace SYSTEM    최길호 2022/07/26 11
WebLogic  which is more than the configured time (StuckThreadMaxTime) of "600" seconds. Stack trace    최길호 2022/07/20 15
Troubleshoot  ORA-04021 / ORA-00054 / select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0    최길호 2022/07/08 29
Oracle GoldenGate  ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available | 1031-ORA-01031: insufficient privileges).    최길호 2022/07/05 30
Troubleshoot  DDE: Problem Key 'ORA 600 [13310]' was completely flood controlled (0x6)    최길호 2022/06/29 39
Oracle GoldenGate  Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set.    최길호 2022/06/25 39
Admin  ORA-14452: attempt to create, alter or drop an index on temporary table already in use    최길호 2022/06/24 28
Admin  external table expdp    최길호 2022/06/18 46
Admin  clob to file / file to clob / clob_to_file / file_to_clob    최길호 2022/06/17 50
Troubleshoot  ORA-13203: failed to read USER_SDO_GEOM_METADATA view    최길호 2022/06/14 45
Troubleshoot  ORA-13249: SRID 5186 does not exist in MDSYS.CS_SRS table    최길호 2022/06/13 71
Troubleshoot  Exception in kzam_upd_props, 942:ORA-00942: table or view does not exist    최길호 2022/06/11 43

    목록보기   다음페이지 1 [2][3][4][5][6][7][8][9][10]..[145]   [다음 10개]
       

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2021/06/30
윈도우 서버에서 sqlplus .... ORA-...
by 최길호
2021/06/30
ORA-28500: connection from O...
by 최길호
2019/05/02
pkg 확인 SELECT * FROM AL...
by 최길호
최근 게시물
08/10
[운영체제]
mobaxterm Remote desktop (RDP,....
by 최길호
08/09
[ORACLE]
ORA-12521 listener does not cu....
by 최길호
08/08
[ORACLE]
ogg save table DML total count....
by 최길호