Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: problem to write on txt file...

    HI,
    I have create this procedure to write on txt file:

    CREATE OR REPLACE procedure SCARICO_SU_FILE IS
    file_log_scarto sys.utl_file.file_type;
    file_log_immobili sys.utl_file.file_type;

    -- dichiarazione variabili

    DATA_ELAB varchar2(50);
    COUNT_TOTALE number(6) :=0;
    MSG varchar2(200);
    i number(6):=0;
    directory varchar2(50) := 'C:\test';
    nome_file varchar2(50) := 'TEST';

    VAR_TESTA_LS_ID CHAR(32) := 'LS_ID';
    VAR_TESTA_FINE CHAR(32) := 'FINE';

    VAR_LS_ID CHAR(32);
    VAR_FINE CHAR(32);



    CURSOR CUR_IMMOBILI IS
    select LS_ID,
    DATA_FINE_UTILIZZO
    from LS;

    PROCEDURE LEGGI_DATA_ODIERNA IS
    begin

    SELECT to_char(SYSDATE,'Day dd Month yyyy * hh24:mi:ss')
    INTO DATA_ELAB
    FROM DUAL;

    end LEGGI_DATA_ODIERNA;

    PROCEDURE APRI_FILE IS
    BEGIN

    file_log_immobili := sys.utl_file.fopen(directory,nome_file||'_'||TO_CH AR(SYSDATE,'DD_MM_YYYY')||'.txt','w');
    file_log_scarto:=sys.utl_file.fopen(directory,nome _file||'_'||TO_CHAR(SYSDATE,'DD_MM_YYYY')||'_scart o.log','w');

    END APRI_FILE;

    PROCEDURE SCRIVI_TESTA_FILE IS
    BEGIN
    sys.utl_file.put_line(file_log_immobili,('File dati - Record di testa'));
    sys.utl_file.put_line(file_log_immobili,VAR_TESTA_ LS_ID||VAR_TESTA_FINE);
    sys.utl_file.put_line(file_log_scarto,('File di scarto - Record di testa'));
    END SCRIVI_TESTA_FILE;

    PROCEDURE SCRIVI_CODA_FILE IS
    BEGIN
    sys.utl_file.put_line(file_log_immobili,('File dati - Record di testa'));
    sys.utl_file.put_line(file_log_scarto,('File di scarto - Record di testa'));
    END SCRIVI_CODA_FILE;

    PROCEDURE CHIUDI_FILE IS
    BEGIN

    sys.utl_file.fclose(file_log_scarto);
    sys.utl_file.fclose(file_log_immobili);

    END CHIUDI_FILE;

    PROCEDURE SCRIVI_NON_TROVATI IS
    BEGIN

    sys.utl_file.put_line(file_log_scarto,('no data found'));

    END SCRIVI_NON_TROVATI;

    PROCEDURE SCRIVI_TROVATI IS
    BEGIN

    sys.utl_file.put_line(file_log_immobili,VAR_LS_ID| |VAR_FINE);

    END SCRIVI_TROVATI;

    --************************************************** ***************************--
    ---------------------inizio della procedura -------------------------------------
    --************************************************** ***************************--

    BEGIN

    LEGGI_DATA_ODIERNA;
    dbms_output.put_line(test- Procedura iniziata il : '||DATA_ELAB );

    APRI_FILE;
    SCRIVI_TESTA_FILE;

    OPEN CUR_IMMOBILI;

    FETCH CUR_IMMOBILI INTO VAR_LS_ID, VAR_FINE;

    IF (CUR_IMMOBILI%NOTFOUND)
    THEN
    SCRIVI_NON_TROVATI;
    ELSE
    LOOP
    EXIT WHEN (CUR_IMMOBILI%NOTFOUND);
    SCRIVI_TROVATI;

    COUNT_TOTALE := COUNT_TOTALE + 1;

    i := i + 1;

    FETCH CUR_IMMOBILI INTO VAR_LS_ID, VAR_FINE;

    IF VAR_FINE is null
    THEN
    VAR_FINE := '**';
    end if;

    END LOOP;

    END IF;

    CLOSE CUR_IMMOBILI;
    SCRIVI_CODA_FILE;
    CHIUDI_FILE;

    LEGGI_DATA_ODIERNA;

    dbms_output.put_line('Record letti dalla DECLARE____________________ : '||COUNT_TOTALE);
    dbms_output.put_line('Anagrafica per ISIS - Procedura terminata il : '||DATA_ELAB );


    EXCEPTION
    when UTL_FILE.INVALID_PATH
    then
    dbms_output.put_line('INVALID_PATH - Verificare che il percorso sia corretto');
    CHIUDI_FILE;
    when UTL_FILE.INVALID_MODE
    then
    dbms_output.put_line('INVALID_MODE');
    CHIUDI_FILE;
    when UTL_FILE.INVALID_FILEHANDLE
    then
    dbms_output.put_line('INVALID_FILEHANDLE');
    CHIUDI_FILE;
    when UTL_FILE.INVALID_OPERATION
    then
    dbms_output.put_line('INVALID_OPERATION');
    CHIUDI_FILE;
    when UTL_FILE.WRITE_ERROR
    then
    dbms_output.put_line('WRITE_ERROR');
    CHIUDI_FILE;
    when UTL_FILE.INTERNAL_ERROR
    then
    dbms_output.put_line('INTERNAL_ERROR');
    CHIUDI_FILE;
    when others
    then
    dbms_output.put_line(SQLERRM);
    CHIUDI_FILE;
    END SCARICO_SU_FILE;


    but I get this output:
    16862854........................
    05001727........................**
    05001719........................**
    02601104........................**
    01009779........................**
    01303956........................**
    01045138........................**
    02606665........................**
    02015016........................**
    01060335........................**
    16605931........................**
    16898015........................**
    16604116........................**

    just one first record is incorrect
    I tried also others table, but i get always same error
    Are there any problem with first line??
    How can I resolve my problem?
    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    REPLACE THIS:
    Code:
    .....
    LOOP
    EXIT WHEN (CUR_IMMOBILI%NOTFOUND);
    SCRIVI_TROVATI;
    COUNT_TOTALE := COUNT_TOTALE + 1;
    i := i + 1;
    FETCH CUR_IMMOBILI INTO VAR_LS_ID, VAR_FINE;
    IF VAR_FINE is null
    THEN
    VAR_FINE := '**';
    end if;
    .....
    WITH THIS:
    Code:
    .....
    LOOP
    EXIT WHEN (CUR_IMMOBILI%NOTFOUND);
    IF VAR_FINE is null
    THEN
    VAR_FINE := '**';
    end if;
    SCRIVI_TROVATI;
    COUNT_TOTALE := COUNT_TOTALE + 1;
    i := i + 1;
    FETCH CUR_IMMOBILI INTO VAR_LS_ID, VAR_FINE;
    .....
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •