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

    Unanswered: append record when file already exists

    Hi,
    I've any problem to append new record in my file when file already exists.

    My table is TAB_ID:
    ID
    1
    2
    3
    4

    I created this stored procedure:

    CREATE OR REPLACE PROCEDURE WRITE_FILE_IF_EXIST
    AS
    err_num number;
    err_msg varchar2(1000);
    check_file boolean;
    lv_a number;
    lv_b number;

    v_file UTL_FILE.FILE_TYPE;

    directory varchar2(50) := 'E:\myfile';
    file_name varchar2(50) := 'my_file';

    cursor mycur is
    SELECT DISTINCT ID
    FROM TAB_ID;

    BEGIN
    utl_file.fgetattr (directory, file_name||'.txt', check_file,lv_a, lv_b);

    if check_file is null then
    v_file := sys.utl_file.fopen(directory, file_name||'.txt','w');
    end if;

    if check_file is not null then
    v_file := sys.utl_file.fopen(directory, file_name||'.txt','a');
    end if;

    FOR cur_rec IN mycur LOOP
    UTL_FILE.PUT_LINE(v_file,
    cur_rec.ID);
    END LOOP;

    UTL_FILE.FCLOSE(v_file);

    EXCEPTION
    WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    err_msg:= SUBSTR(SQLERRM, 1, 300);
    err_num:= SQLCODE;
    INSERT INTO errors (proc_name, err_code, err_msg, err_date)
    VALUES ('WRITE_FILE_IF_EXIST', err_num, err_msg, sysdate);
    COMMIT;


    END WRITE_FILE_IF_EXIST;

    and It create correct the file my_file.txt with this values:
    1
    2
    3
    4

    Now if I add this record:
    ID
    1
    2
    3
    4
    5
    6
    7

    I get the file my_file.txt with this values:
    1
    2
    3
    4
    1
    2
    3
    4
    5
    6
    7

    with duplicate value.

    Can I insert in file "my_file.txt" just distinct values??

    Thanks in advance!

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You will have to sort of read the file to know that. You can create an external table for the file and use it as a left join to tab_id.

    Sort of like..
    Code:
    SQL> create or replace directory MyDir as 'C:\';
    
    Directory created.
    
    SQL> create table MyFile
      2  ( id       number )
      3  organization external
      4  ( type     oracle_loader
      5     default directory MyDir
      6     location ( 'my_file.txt' )
      7  )
      8  /
    
    Table created.
    
    SQL> select * from MyFile;
    
            ID
    ----------
             1
             2
             3
             4
    
    SQL> select * from tab_id;
    
            ID
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
    
    SQL> select tab_id.id
      2    from tab_id left outer join MyFile
      3      on tab_id.id = MyFile.id
      4   where MyFile.id is null
      5    order by tab_id.id
      6  /
    
            ID
    ----------
             5
             6
             7
             8
             9
            10
    
    6 rows selected.
    
    SQL>
    The above query is what you will have in the cursor mycur. I hope you're doing this for testing purporses, since this can be done entirely, more efficient and less error prone using a sql*plus script.

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    I tried:

    SQL> create table MyFile
    2 ( id number )
    3 organization external
    4 ( type oracle_loader
    5 default directory MyDir
    6 location ( 'my_file.txt' )
    7 )
    8 /

    but I get this error:
    ORA-00406: COMPATIBLE parameter needs to be 9.0.0.0.0 or greater ...

    My oracle version is 9.2.0.0

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is what Oracle says about it:
    Quote Originally Posted by Oracle
    ORA-00406 COMPATIBLE parameter needs to be string or greater

    Cause: The value of the COMPATIBLE initialization parameter is not high enough to allow the operation. Allowing the command would make the database incompatible with the release specified by the current COMPATIBLE parameter.

    Action: Shut down and restart with a higher compatibility setting.
    Your version IS 9.2.0.0., but what COMPATIBILE parameter says?

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    But.. did you upgrade from a previous version (<9.2.0.0) ?

    If you didn't, you will have to
    Code:
    alter system set compatible = '9.2.0.0.0' scope = spfile;
    and bounce the DB. Otherwise, you will have to modify a litle bit your code. Assuming your file is ORDERED by id, instead of
    Code:
    FOR cur_rec IN mycur LOOP
    UTL_FILE.PUT_LINE(v_file,
    cur_rec.ID);
    END LOOP;
    You will
    Code:
    FOR cur_rec IN mycur LOOP
    UTL_FILE.GET_LINE(v_file, v_buffer, 32767 );
    if ( trim( v_buffer ) <> cur_rec.ID )
    then
      UTL_FILE.PUT_LINE(v_file,cur_rec.ID);
    end if;
    END LOOP;
    you will need to declare variable v_buffer and add an order by to the query definition of the cursor mycur. I haven't tested this, it is your job to do so.

Posting Permissions

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