How can I know if a txt file exists from PLSQL

Question

How can I know if a txt file exists from PLSQL

Navigation

#1 by (2 votes)

1

I have the following problem, I am creating a Trigger in Oracle so that I write information in a txt file, up to here everything is fine, I can write without dramas, the problem is when I need to add information to the file, apart from the one that already has . I understand that I must read the file, extract what it contains and put it together with the new text, the big problem arises when I try to open the file but it does not exist. How can I add this validation (if it does not exist, what do you think)?

This is the Trigger:

CREATE OR REPLACE TRIGGER BAJOSTOCK
AFTER 
UPDATE
ON PRODUCTO
FOR EACH ROW

DECLARE
V_ARCHIVO UTL_FILE.FILE_TYPE;
V_LINEA VARCHAR2(1024);
BEGIN
IF :NEW.CANTIDAD <= :OLD.STOCK_CRITICO THEN

 --Aqui me da el errror, cuando no existe

    --Abro el archivo
    V_ARCHIVO := UTL_FILE.FOPEN ('DIR_TMP', 'stock.txt', 'r');

  --Leo lo que contiene  
  LOOP
    UTL_FILE.GET_LINE (V_ARCHIVO, V_LINEA);
  END LOOP; 
    UTL_FILE.FCLOSE(V_ARCHIVO);--Lo cierro

  --Vuelvo a abrirlo y escribo
    V_ARCHIVO := UTL_FILE.FOPEN ('DIR_TMP', 'stock.txt', 'w');
  --Escribo lo que tenía y el nuevo contenido
    UTL_FILE.PUT_LINE(V_ARCHIVO,V_LINEA);
    UTL_FILE.PUT_LINE (V_ARCHIVO, 'El producto '|| :OLD.DESCRIPCION ||' se encuentra en stock crítico, por favor revíselo');
    UTL_FILE.FCLOSE(V_ARCHIVO); --Cierro el archivo
END IF;

--Cierro el archivo si hay errores
EXCEPTION
WHEN NO_DATA_FOUND THEN
 UTL_FILE.FCLOSE(V_ARCHIVO);
END;
/

I appreciate your help ....

oracle oracle-sqldeveloper archivo plsql

asked by Jonathan 27.05.2018 в 00:26

source

1 answer

How do I create service contracts in webapi Date format in rails

score 2 · Accepted Answer

You do not necessarily have to read everything to add information. But let's go in parts:

Whereas in my environment the query:

select  * from ALL_DIRECTORIES

Returns

OWNER   DIRECTORY_NAME  DIRECTORY_PATH
SYS     XMLDIR          /u01/app/oracle/product/11.2.0/xe/rdbms/xml
SYS     DATA_PUMP_DIR   /u01/app/oracle/admin/XE/dpdump/

On the other hand, in my directory / u01 / app / oracle / admin / XE / dpdump / I have the file: dp.log

Then, to check if the file exists, you can use the FGETATTR function. According to my environment I can put:

DECLARE
  nombreDirectorio CONSTANT VARCHAR2(100):= 'DATA_PUMP_DIR';
  nombreArchivo    CONSTANT VARCHAR2(100) := 'dp.log';
  existeElArchivo  BOOLEAN;
  longitudEnBytes  NUMBER;
  numeroDeBloques  NUMBER;


BEGIN

    UTL_FILE.FGETATTR( nombreDirectorio, nombreArchivo
                     , existeElArchivo, longitudEnBytes, numeroDeBloques);

     IF existeElArchivo THEN
        DBMS_OUTPUT.PUT_LINE( 'El archivo '|| nombreArchivo  ||' existe ' );
     ELSE
        DBMS_OUTPUT.PUT_LINE( 'El archivo '|| nombreArchivo  ||' NO existe ' );
     END IF;

END;
/

Which throws me as output that the file exists.

To add to the file, you do not have to read everything. You can open it in aggregate mode.

Instead of indicating that it is for writing (by the final parameter w ):

V_ARCHIVO := UTL_FILE.FOPEN ('DIR_TMP', 'stock.txt', 'w');

You can use the 'to' parameter:

V_ARCHIVO := UTL_FILE.FOPEN ('DIR_TMP', 'stock.txt', 'a');

It is important to mention that if you open the file in ' to ' mode, if the file does not exist, Oracle will create it at that moment and open it in ' w ' mode