Read file with default name

[d.calasans]

Hello everyone, this is my first doubt here in the forum, but I always come to read about PL / SQL around here.

I need to read a file, but I do not have the full name of this file, but I know it has a pattern in the name.
The name of the file will always be: log_ok _ ######## _ ######
where the '#' will always be numbers.
There will always be only one file in the directory to be read and it will always have this pattern.

I thought of using regular expressions, but I can not find a way to check if the file name belongs to the regular expression because I do not know how to read the name of the file present in the directory.

I hope you can help me, sorry if I have not been so clear.

[Julian Campagnoli]

All right d.calasans ?????

Unfortunately, no way to open a .txt file, without passing as a parameter a valid file name and directory.

ie ... from what I understand from your doubt ... You need a function or something that searches for the file "log_ok_ ..." because even being a single file I did not see yet No search way via UTL_File.

really the numbers (#) that comes after the "log_ok_" has no pattern ????? They really are random and impossible to define them ????

rsrsrsrsrs Let's try to find a solution ...

Hugs

[Julian Campagnoli]

Question: Who generates this log file ????? Is it generated via procedure ?????

[Rique]

Hello!!!

I already passed a similar situation .... I decided as follows ....

I asked the person who set the program q generate the file Generate another file with the fixed name and the file contention being the name of the dynamic file.

So my program begins by reading the file with the fixed name and picks up the content of this file, q will be the name of the dynamic file.

at the time was the best solution that can ride ...

ABS

[d.calasans]

Hello guys!
Thank you all for the help.
I did something like what the rich did to solve his situation, but instead of asking to change the program that generates the scripts I created a .bat file that generates a txt containing all the names of the files Log_ok _ *. Txt
hence I read this file and walk all scripts.
But another problem appeared now: How to call this .bat file from a procedure?
I'vê been reading a methods that use Java, but I still could not understand.
could anyone give me a help?

My procedure to run the scripts:

Select all

  procedure sp_executar_scripts as 
   
  arqRead utl_file.file_type; 
  arqWrite utl_file.file_type; 
  fileList utl_file.file_type; 
  fileNameSource  varchar2(30); 
  fileNameDest  varchar2(25);   
  linha varchar2(1000); 
  FimScript boolean; 
  FimList boolean; 
   
  begin 
    fileNameSource := ''; 
    FimScript := false; 
    FimList := false; 
    fileNameDest := 'LOG_LIDO_'||to_char(sysdate, 'DDMMYYYY')||'.txt'; 
    fileList := utl_file.fopen('DIC_DIR', 'fileList.txt', 'R');       --arquivo com a lista dos nomes dos arquivos que contém os scripts 
    arqWrite := utl_file.fopen('DIC_EXPORTACAO', fileNameDest, 'W');  --arquivo destino que vai receber a linhas executadas do arquivo origem 
   
    while not (FimList) loop 
      begin 
        FimScript := false; 
       utl_file.get_line(fileList, fileNameSource); 
      arqRead := utl_file.fopen('DIC_DIR', fileNameSource, 'R');  --arquivo com os comandos sql a serem executados 
 
    while not (FimScript) loop 
      begin 
        linha := ''; 
        utl_file.get_line(arqRead, linha);   --lê a linha do arquivo de origem 
        linha := replace(linha, ';'); 
          EXECUTE IMMEDIATE linha; 
        utl_file.put_line(arqWrite, linha||';');  --escreve a linha executada com êxito no arquivo destino 
        exception 
          when no_data_found then 
            FimScript := true;   --quando chega o final do arquivo seta a variavel fim como true para parar o laço       
        end; 
    end loop; 
     
    utl_file.fclose(arqRead); 
    utl_file.fremove('DIC_DIR', fileNameSource); --verificar se é antes ou depois de fechar o arquivo 
       
    exception 
      when no_data_found then 
        FimList := true;   --quando chega o final do arquivo seta a variavel fim como true para parar o laço 
    end; 
  end loop; 
   
  utl_file.fclose(fileList);  --fecha o arquivo da lista 
     
  utl_file.fflush(arqWrite);  --força a escrita do buffer no arquivo de escrita 
  utl_file.fclose(arqWrite);  --fecha o arquivo de escrita 
  sp_enviar_relatorio('diego.costa@sefaz.se.gov.br'); 
   
  exception 
    when others then 
      utl_file.fclose_all;   --fecha todos os arquivos abertos em caso de erro 
      rollback; 
      sp_enviar_erro('diego.costa@sefaz.se.gov.br'); 
  end;

.bat file code If someone has an interest:

Select all

dir /b \\sedebd01\Carga_Complementar\LOG_OK_*.txt > \\sedebd01\Carga_Complementar\fileList.txt

[Trevisolli]

Brother,

This solution has to be via procedure, correct?

If I can do in java , it has a valid test to be done:

1) Create the class that will shoot the .bat file;
2) Play this class in an Oracle (Bank) procedure;
3) Create a Call in PL / SQL to this procedure;

I went through a situation that I had to call a Java classes and did that way.

Anything, send us.

[d.calasans]

Thanks for help Trevisolli.

I managed to do what I wanted here personal!
I created a Java code in PL to execute the command of the SO:

Select all

create or replace and compile java source named util as 
public class execHostCmd 
{ 
  public static void execute (String command)  
    throws java.io.IOException 
  { 
   String osName = System.getProperty("os.name"); 
   command = "cmd /c " + command; 
   Runtime rt = java.lang.Runtime.getRuntime(); 
   rt.exec(command); 
  } 
} 

I created a stored procedure that performs the Java code:

Select all

--chama o Java Source UTIL para executar o comando do SO que gera a lista de arquivos 
  procedure sp_execute (cmd in varchar2) as language java name 
           'execHostCmd.execute(java.lang.String)';

I called SP passing as parameter the execution command:

Select all

sp_execute('dir /b \\sedebd01\Carga_Complementar\LOG_OK_*.txt > \\sedebd01\Carga_Complementar\fileList.txt');

I had to give 3 permissions to the bank user:

Select all

DBMS_JAVA.grant_permission ('USER' 
                             , 'SYS:java.io.FilePermission' 
                             , '<<ALL FILES>>' 
                             , 'execute' 
                              ); 
   DBMS_JAVA.grant_permission ('USER' 
                             , 'SYS:java.lang.RuntimePermission' 
                             , 'writeFileDescriptor' 
                             , '' 
                              ); 
   DBMS_JAVA.grant_permission ('USER' 
                             , 'SYS:java.lang.RuntimePermission' 
                             , 'readFileDescriptor' 
                             , '' 
                              );

is turning 100% here.
I would like to thank everyone for help.
Valeu Personal: D [/ Code]