Results 1 to 4 of 4

Thread: Utl_file

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: Utl_file

    Hi,

    I want extract data from the table and put it in File.
    I am using utl_file for this purpose.

    for example.

    Table A
    Code Name db_code
    1 Frank 3120
    2 Jonny null
    3 Mathew null

    I only want to write those rows in file having db_code=NULL.
    The file should contain following details:

    "1","Jonny"
    "2","Mathew"

    Table A is having thousands of rows with db_code=NULL. Is it possible to do mass write to the file
    or i have to use cursor for this.

    What are the system parameters i have to set for UTL_FILE in oracle.

    Oracle version: 9.2.0.3.0
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Jan 2004
    Location
    Hyderabad, India
    Posts
    37
    Hi

    You can refer to the metalink note 119644.1 for this purpose.

    Here i am pasting the code for your reference...

    CREATE OR REPLACE PROCEDURE write_file AS
    id UTL_FILE.FILE_TYPE;
    name VARCHAR2(20) := 'my_file';
    err VARCHAR2(100);
    num NUMBER;
    BEGIN
    id := UTL_FILE.FOPEN('/u05/home/tsupport/asoracco/utl_file',name, 'w');
    IF UTL_FILE.IS_OPEN(id)
    THEN
    DBMS_OUTPUT.PUT_LINE('Opened');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Still Closed');
    END IF;
    UTL_FILE.PUT_LINE(id,name); UTL_FILE.PUT(id,'It worked and wrote to this file'); UTL_FILE.FCLOSE(id);
    DBMS_OUTPUT.PUT_LINE('Successful write to file'); EXCEPTION
    WHEN OTHERS THEN
    err := SQLERRM;
    num := SQLCODE;
    DBMS_OUTPUT.PUT_LINE(err); DBMS_OUTPUT.PUT_LINE(num);
    DBMS_OUTPUT.PUT_LINE('Error in writing to file');
    END;
    Regards
    Suneel

  3. #3
    Join Date
    Jan 2004
    Location
    India, UK
    Posts
    3

    Re: Utl_file

    Hi,
    Two possible settings for using utl_file package. Either set utl_file_dir parameter in your .ora file or create a new directory. I prefer second option as this allows access control on directory created.

    I believe you can write only one row at a time, so cursor is necessary. Alternatively you can concatenate the data and write only once, however this may not work on large data.

    Good luck

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Thanks very much for the help
    Pagnint
    (No need to search web before posting new question)

Posting Permissions

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