OraFAQ Forum: SQL & PL/SQL » SQL Macro using UTL

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Macro using UTL_FILE (Oracle 19.0.0.0.0 Windows 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Macro using UTL_FILE [message #690053]

Thu, 26 September 2024 09:37

Duane
Messages: 578
Registered: December 2002

Senior Member

I have this SQL Macro that creates CSV output but I would like to have the Macro also write those results to a file on the OS system.

The problem I have is I need the results to be transformed before those results are written to the file.

Any suggestions?

Desired Output written to the file:


"ID","NAME"
1,"AMSTERDAM"
2,"UTRECHT"
3,"THE HAGUE"
4,"ROTTERDAM"

Test Case:


CREATE TABLE CITIES
(
  ID    NUMBER(5),
  NAME  VARCHAR2(30 CHAR)
)

SET DEFINE OFF;
Insert into CITIES
   (ID, NAME)
 Values
   (1, 'AMSTERDAM');
Insert into CITIES
   (ID, NAME)
 Values
   (2, 'UTRECHT');
Insert into CITIES
   (ID, NAME)
 Values
   (3, 'THE HAGUE');
Insert into CITIES
   (ID, NAME)
 Values
   (4, 'ROTTERDAM');
COMMIT;

SQL Macro


CREATE OR REPLACE function CSVDataFile (InputData     in dbms_tf.table_t,
                                        DirectoryName in varchar2 default 'DIR1',
                                        FileName      in varchar2 default null,
                                        CreateFile    in varchar2 default 'Y',
                                        Delimiter     in varchar2 default ',',
                                        Quote         in varchar2 default '"',
                                        Escape        in varchar2 default '\') return clob sql_macro as
     
  pragma          autonomous_transaction;
  
  ChunkSize       pls_integer  default 3000;
  DelimiterRecord varchar2(24) default '||Delimiter||';
  ColumnName      varchar2(200);
  ColumnRecord    varchar2(300);
  HeaderRecord    clob;
  DataRecord      clob;
  SQLStatement    clob;
  
  CSVFile         utl_file.file_type;
  
  
  begin
    for i in 1..InputData.column.count 
      loop
        ColumnName := InputData.column(i).description.name;
        
        case
          when InputData.column(i).description.type in (dbms_tf.type_varchar2, 
                                                        dbms_tf.type_char, 
                                                        dbms_tf.type_clob)
            then
              if Quote is not null
                then
                  ColumnRecord := 'replace('||ColumnName||','''||Quote||''','''||Escape||Quote||''')';
                  ColumnRecord := ''''||Quote||'''||'||ColumnRecord||'||'''||Quote||'''';
              
                else
                  ColumnRecord := ColumnName;
              end if;
              
          when InputData.column(i).description.type = dbms_tf.type_number
            then
              ColumnRecord := 'to_char('||ColumnName||')';
              
          when InputData.column(i).description.type = dbms_tf.type_date 
            then
              ColumnRecord := 'to_char('||ColumnName||',''YYYY-MM-DD'')';
              
          when InputData.column(i).description.type = dbms_tf.type_timestamp
            then
              ColumnRecord := 'to_char('||ColumnName||',''YYYY-MM-DD"T"HH24:MI:SS.FF6'')';
              
            else
              null;
        end case;
   
        HeaderRecord := HeaderRecord||'||Delimiter||'||''''||ColumnName||'''';
        DataRecord := DataRecord||DelimiterRecord||ColumnRecord;
   
      end loop;
    
    HeaderRecord := substr(HeaderRecord, length('||Delimiter||') + 1);
    DataRecord := substr(DataRecord, length(DelimiterRecord) + 1);
    
    SQLStatement := q'[
                       select @COLUMN_HEADER@ as csv_row from dual
                       union all
                       select @DATA_RECORD@ as csv_row from CSVData.InputData
                      ]';
   
    SQLStatement := replace(replace(SQLStatement, '@DATA_RECORD@', DataRecord), '@COLUMN_HEADER@', HeaderRecord);
    
    if CreateFile = 'Y'
     then
       CSVFile := utl_file.fopen(nvl(DirectoryName, 'DIR1'), nvl(FileName, 'csv_file_'||to_char(sysdate, 'yyyymmddhh24miss')||'.txt'), 'wb', max_linesize => 32767);

       for i in 1..ceil(length(HeaderRecord)/ChunkSize)
         loop
           utl_file.put_raw(CSVFile, utl_raw.cast_to_raw(substr(HeaderRecord, (i - 1) * ChunkSize + 1, ChunkSize)));
           utl_file.fflush(CSVFile);
         end loop;
         
       ChunkSize := 3000;
       
       for i in 1..ceil(length(DataRecord)/ChunkSize)
         loop
           utl_file.put_raw(CSVFile, utl_raw.cast_to_raw(substr(DataRecord, (i - 1) * ChunkSize + 1, ChunkSize)));
           utl_file.fflush(CSVFile);
         end loop;

       utl_file.fclose(CSVFile);
    end if;

    return SQLStatement;
  
  end;

SQL Statement


with Cities1 as (select *
                 from cities)
                 
  select * from dnettles10.CSVDataFile(InputData => Cities1);

File Contents


'"ID"'||Delimiter||'"NAME"'to_char("ID")||Delimiter||'"'||replace("NAME",'"','\"')||'"'