Using UTL_FILE

[cleberz]

Good morning, gentlemen,

I need to export data to a file. I'm doing this with UTL_File.
The problem is that I get a query and I have to run this query by saving the data in the file, all that dynamically.
Query can come with multiple tables or not, with several columns or not.

Can anyone help me in this?

[nelson.anchite]

Dude, the UTL_File is pretty cool, but it has a file size limitation. The data contained in the file can only reach up to 32767. What do you think of using spool.

I needed to make a great routine with the UTL_File and answered very well, but since you always need to export the result of a query perhaps the use of spool is interesting.

[cleberz]

What would spool be? do not know.

[nelson.anchite]

You could set up a script allocating your querys and using this command.
[[0] https://docs.oracle.com/cd/E12825_01/ep ... _spool.htm

[cleberz]

Can I do this incred the query as a parameter? I perform queys with Immediate execute.

[cleberz]

I did here, only that he saved Query, but did not salve the result of Query, the data.
I used the command below:

Select all

spool c:\Tmp\test.csv  
select /*csv*/ username, user_id, created from all_users; 
spool off;

[nelson.anchite]

I performed the command you put here, and recorded the result in CSV.

[cleberz]

Vixi.
Now I do not know.

[cleberz]

I executed in SQL Developer. Is that why?

[cleberz]

Next, the use of spool is slowly mutual than using UTL_File.
I'll have to do this for UTL_File. But how to do this dynamically?

[nelson.anchite]

Record the queries on a table.

[cleberz]

It has to be on file.

[nelson.anchite]

Record the querys in tables .. and run them within a procedure that uses UTL_File.

[cleberz]

It can not be like this.
I'm doing it with the procedure below. I have not tested:

Select all

create or replace procedure SP_TESTE_ARQUIVO (p_query     in varchar2,   
                                                     p_filename  in varchar2 ) is 
begin 
 
       vFILE := utl_file.fopen('/acfs/flx/tmp',  p_filename, 'w', 32767); -- Abre o arquivo para gravação. Se o arquivo não existir será criado. Se o arquivo existir, isso sobrescreverá. 
   
       dbms_sql.parse(v_theCursor,  p_query, dbms_sql.native );  -- Analisa a instrução SQL 
   
 
       for i in 1 .. 255 loop 
           begin 
               dbms_sql.define_column(v_theCursor, i, v_columnValue, 32000); 
              
               v_colCnt := i; 
           exception 
               when others then 
                    exit; 
           end; 
       end loop; 
 
 
       dbms_sql.define_column(v_theCursor, 1, v_columnValue, 2000); -- Especifica as variáveis que estão a receber os valores SELECT. Da mesma forma como uma cláusula INTO faz para uma consulta estática. 
 
       v_status := dbms_sql.execute(v_theCursor); -- Executa a instrução SQL. 
 
 
       loop 
           exit when (dbms_sql.fetch_rows(v_theCursor) <= 0); -- Recupera as linhas que satisfazem a consulta até o cursor não retornar mais linhas. 
           v_separator := ''; 
            
          for i in 1 .. v_colCnt loop 
           
               dbms_sql.column_value(v_theCursor, i, v_columnValue); -- Recupera os valores de cada coluna para serem gravados abaixo. 
 
               utl_file.put(vFILE, v_separator || v_columnValue); -- Grava os dados no arquivo. 
               v_separator := ';'; 
                
           end loop; 
           utl_file.new_line(vFILE); 
           v_cont := v_cont+1; 
            
       end loop; 
       dbms_sql.close_cursor(v_theCursor); -- Fecha o cursor. 
   
       utl_file.fclose(vFILE); -- Fecha o arquivo. 
       DBMS_OUTPUT.PUT_LINE('ARQUIVO: '|| v_cont); 
        
        
        
end; 
/

[stcoutinho]

Hi Cleberz,

has this topic in Oracle Forum that explains how to dynamically run with the use of dbms_sql.
https://community.oracle.com/thread/935498?tstart=0
Maybe it meets your needs. The example he passes seems to be well "way" - rs

Hugs

Coutinho