[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / êꪤҳàõªòàõªÎÒ¿Ö˪˪·ª¿ð¨ÏÐñ«ëùìíÜâÏÚªÎ?ð¤àõªòÍëãÒªËìãªá¡¢Þóñªª·ªÊª±ªìªÐªÊªéªÊª¤. ]

»ç¿ëÀÚ

ID:
PW:

INDEX
01.°Ô½ÃÆÇ
°Ô½ÃÆÇ [72]
02.File Book
File ÀÚ·á½Ç [96]
Site Link [14]
°³ÀÎÆú´õ [201]
03.Field Book
Altibase [19]
Tibero [31]
MS-SQL [32]
MySQL [47]
DB2 [79]
ORACLE [3202]
PSQL [143]
CUBRID [5]
±âŸÁ¤º¸ [194]
¿î¿µÃ¼Á¦ [634]
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, Á¶È¸ : 396


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; Ãß°¡
216.73.216.248


  LIST

Á¦¸ñ ÀÛ¼ºÀÚ ÀÛ¼ºÀÏ Á¶È¸
Admin  [ °øÁö ] 😃 ================ ÇÊ¿ä ÇÒ¶§¸¸ ¿î¿µ ÇÕ´Ï´Ù. ¾ÈµÇ¸é ³ªÁß¿¡ ´Ù½Ã ¹æ¹® ÇØÁÖ¼¼¿ä~. =================== 😅    ÃÖ±æÈ£ 2008/11/26 116
Admin  [ ÇÊ¿ä ÇÏ½Å°Ô ÀÖÀ¸½Ã¸é ÂÊÁö ¶Ç´Â gilho.kr@gmail.com ¸ÞÀÏ ÁÖ¼¼¿ä~ ]    ÃÖ±æÈ£ 2006/07/14 136
Admin  The clock on host node2 differs from mean cluster time by    ÃÖ±æÈ£ 2025/06/27 8
Admin  PRCD-1229 ORA-29701: unable to connect to Cluster Synchronization Service [ 19c grid - 12c SE2 rac ]    ÃÖ±æÈ£ 2025/06/26 5
Admin  opatch lsinventory [ LsInventorySession failed: Array index out of range: 0 ]    ÃÖ±æÈ£ 2025/06/26 2
Admin  Linux 9 oracle 12.2 install [ /usr/bin/ld: cannot find /usr/lib64/libpthread_nonshared.a ] [ linux 7, 8¿¡¼­ ¼³Ä¡ÈÄ »ç¿ëÀº °¡´É ]    ÃÖ±æÈ£ 2025/06/26 2
Admin  windows sqlplus "/as sysdba" ORA-01031: insufficient privileges    ÃÖ±æÈ£ 2025/06/16 5
Admin  ORA-00800: soft external error, arguments: [Set Priority Failed]    ÃÖ±æÈ£ 2025/06/12 8
Admin  grep /usr/bin/make $ORACLE_HOME/install/make.log [ EE / SE2 ]    ÃÖ±æÈ£ 2025/06/09 18
Admin  acfs asmcmd volcreate / srvctl add filesystem / acfsutil size / srvctl start filesystem    ÃÖ±æÈ£ 2025/06/05 15
Admin  CRS-2643 PRCD-1302 PRCD-2088 PRKF-1125 PRKF-1241    ÃÖ±æÈ£ 2025/06/04 10
Admin  HA web control    ÃÖ±æÈ£ 2025/06/02 30
Admin  srvctl add havip -id dbip / with acfs / PRCE-1026 : Cannot start HAVIP resource without an Export FS resource.    ÃÖ±æÈ£ 2025/06/01 32
Admin  systemctl status oracle-ohasd.service [ auto start fail / crsctl config|disable|enable crs ]    ÃÖ±æÈ£ 2025/05/26 37

    ¸ñ·Ïº¸±â   ´ÙÀ½ÆäÀÌÁö 1 [2][3][4][5][6][7][8][9][10]..[161]   [´ÙÀ½ 10°³]
       

Copyright 1999-2025 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 ÃÖ±æÈ£
ÃÖ±Ù °Ô½Ã¹°
07/02
[¿î¿µÃ¼Á¦]
rsync -auv.
by ÃÖ±æÈ£
07/01
[±âŸÁ¤º¸]
shell for 001 ~ 100.
by ÃÖ±æÈ£
06/27
[ORACLE]
The clock on host node2 differ....
by ÃÖ±æÈ£