to wrinting clob data to os file(clob2file)

admin

1. CREATE OR REPLACE PROCEDURE CLOB2FILE (
   
2. clob_in IN CLOB,
   
3. directory_name IN VARCHAR2,
   
4. file_name IN VARCHAR2
   
5. )
   
6. IS
   
7. file_handle UTL_FILE.FILE_TYPE;
   
8. clob_part VARCHAR2(1024);
   
9. clob_length NUMBER;
   
10. offset NUMBER := 1;
    
11. BEGIN
    
12. clob_length := LENGTH(clob_in);
    
13. file_handle := UTL_FILE.FOPEN(directory_name, file_name, 'W');
    
14. LOOP
    
15.      EXIT WHEN offset >= clob_length;
    
16.      clob_part := DBMS_LOB.SUBSTR (clob_in, 1024, offset);
    
17.      UTL_FILE.PUT_LINE(file_handle, clob_part, FALSE);
    
18.      offset := offset + 1024;
    
19. END LOOP;
    
20. UTL_FILE.FFLUSH(file_handle);
    
21. UTL_FILE.FCLOSE(file_handle);
    
22. EXCEPTION
    
23. WHEN OTHERS THEN
    
24.      UTL_FILE.FCLOSE(file_handle);
    
25.      RAISE;
    
26. END;
    

複製代碼

ifs clob data to os file
CREATE DIRECTORY MY_DIR AS 'C:\temp';

1. create or replace procedure SAVE_TO_FILE_FILE
   
2. IS
   
3. 
   
4. l_blob CLOB;
   
5. --CURSOR C1 IS SELECT data From  XML_REPORT_DATA_TAB  Where result_key = 1  ;
   
6. 
   
7. BEGIN
   
8. 
   
9. SELECT data INTO l_blob FROM XML_REPORT_DATA_TAB  Where result_key = 1;
   
10. 
    
11.   clob2file(l_blob,'MY_DIR','utlfile.txt');
    
12. 
    
13. end SAVE_TO_FILE_FILE;

複製代碼

c:/temp/utlfile.txt  contents as below (as a example)

<?xml version="1.0" encoding="UTF-8"?>
<BranchTrack xmlns="urn:GEINV:eInvoiceMessage:E0401:3.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:GEINV:eInvoiceMessage:E0401:3.1 E0401.xsd">
  <Main>
    <HeadBan>00008000</HeadBan>
    <BranchBan>00008000</BranchBan>
    <InvoiceType>02</InvoiceType>
    <YearMonth>10212</YearMonth>
    <InvoiceTrack>YB</InvoiceTrack>
    <InvoiceBeginNo>00001000</InvoiceBeginNo>
    <InvoiceEndNo>00001249</InvoiceEndNo>
  </Main>
  <Details>
    <BranchTrackItem>
      <InvoiceBeginNo>00001000</InvoiceBeginNo>
      <InvoiceEndNo>00001249</InvoiceEndNo>
      <InvoiceBooklet>5</InvoiceBooklet>
    </BranchTrackItem>
  </Details>
</BranchTrack>

as the maximum length of the clob field exceeds the limit 32767  then exec clob2file(l_blob,'MY_DIR','utlfile.txt') will caused error
clob2file(l_blob,'MY_DIR','utlfile.txt') need to changed by using
DBMS_XSLPROCESSOR.clob2file(l_blob,'MY_DIR','utlfile.txt') as instead.

1. create or replace procedure SAVE_TO_FILE_FILE
   
2. IS
   
3. 
   
4. l_blob CLOB;
   
5. BEGIN
   
6. 
   
7. SELECT data INTO l_blob FROM XML_REPORT_DATA_TAB  Where result_key = 7013;
   
8. 
   
9. DBMS_XSLPROCESSOR.clob2file(l_blob,'MY_DIR','utlfile.txt');
   
10. end SAVE_TO_FILE_FILE;
    

複製代碼

1. API Specification:
   
2. 
   
3. DBMS_XSLPROCESSOR.CLOB2FILE(
   
4.       cl          IN  CLOB,
   
5.       flocation   IN  VARCHAR2,
   
6.       fname       IN  VARCHAR2,
   
7.       CSID        IN  NUMBER:=0);
   
8. 
   
9. Script:
   
10. 
    
11. SET SERVEROUTPUT ON:
    
12. DECLARE
    
13.    -- CLOB data
    
14.    lv_clob  CLOB   DEFAULT 'This is a example testing by SHAREORACLEAPPS';
    
15.    -- Database directory for a physical location
    
16.    lv_utl_file_directory   VARCHAR2 (100) DEFAULT 'TEST_DB_DIRECTORY';
    
17.    -- Output Filename                           
    
18.    lv_file_name        VARCHAR2 (100) DEFAULT 'TESTFILE.txt';
    
19. BEGIN
    
20.    DBMS_XSLPROCESSOR.clob2file (lv_clob,
    
21.                                 lv_utl_file_directory,
    
22.                                 lv_file_name
    
23.                                );
    
24. EXCEPTION
    
25.    WHEN OTHERS
    
26.    THEN
    
27.       DBMS_OUTPUT.put_line ('Error :' || SQLERRM);
    
28. END;

複製代碼