Oracle + txt

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 23 Jun 2004 2:47 pm

Good afternoon, I would like to generate a txt file from a sql result in an Oracle database, know that mysql contains this statement: SELECT * INTO OUTFILE \"texto.txt\" FROM table;

Is there something similar to oracle??

I Appreciate ...
Fabiano

Poston Wed, 23 Jun 2004 2:54 pm

In native SQL, this is not possible.

You have to let us know which tool you want to do this: * in SQL * Plus, you can use SPOOL c:arq.txt (later, to finish, uses SPOOL OFF) * in the forms, you use TEXTO_IO * the Bank uses the UTL_FILE * Etc. ..
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 24 Jun 2004 9:36 am

I usually use the first hint that the friend above gave, using SPOOL and SPOOL OFF in SQLPlus. Works fine.
moonlight
Location: Goiânia

:P

Poston Thu, 24 Jun 2004 10:50 am

aí guys, follow the tips and saw the use of spooling, thanks for the help work so well ...

Falow ...
Fabiano

Poston Wed, 25 Aug 2004 10:58 am

I think what you want to do is it not?
before you have to set the parameter utl_file_dir =/tmp?
This parameter is ' init <sid>. ora '-how it works, first you'll create a procedure with a cursor that will produce the result that you need.
After you call this procedure by passing his return as paramentro for this procedure below.

Code: Select all
create or replace procedure sp_grave_arquivotxt
  (pdiretorio      in varchar2,
   pnome_arquivo   in varchar2,
   ptexto          in varchar2)
is
    vOutput_file  utl_file.file_type;
begin
-- Se já existir abre , senão cria !
    vOutput_file := utl_file.fopen (pdiretorio,pnome_arquivo, 'W');
-- Grava linhas no arquivo.
    Utl_File.put_line(vOutput_file, ptexto );
-- fecha
    Utl_File.fclose(vOutput_file);
    Dbms_Output.Put_Line('Arquivo gerado com sucesso.');
EXCEPTION
      WHEN UTL_FILE.INVALID_OPERATION THEN
               Dbms_Output.Put_Line('Operação inválida no arquivo.');
               UTL_File.Fclose(vOutput_file);
      WHEN UTL_FILE.WRITE_ERROR THEN
               Dbms_Output.Put_Line('Erro de gravação no arquivo.');
               UTL_File.Fclose(vOutput_file);
      WHEN UTL_FILE.INVALID_PATH THEN
               Dbms_Output.Put_Line('Diretório inválido.');
               UTL_File.Fclose(vOutput_file);
      WHEN UTL_FILE.INVALID_MODE THEN
               Dbms_Output.Put_Line('Modo de acesso inválido.');
               UTL_File.Fclose(vOutput_file);
      WHEN Others THEN
               Dbms_Output.Put_Line('Problemas na geração do arquivo.');
               UTL_File.Fclose(vOutput_file);
END;
jack
Location: CURITIBA - PR


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: Bing [Bot] and 4 guests