DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: UTL_FILE example

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi friends,
    Could you please give an sample code using UTL_FILE package with few procedures it uses.

    cheese
    anandkl
    anandkl

  2. #2
    Join Date
    Sep 2001
    Posts
    163
    Not exactly sure what you mean, but here is how I used Utl_file recently. I used it to read in a parameter file.

    v_dir varchar2(4) := 'C:\';
    v_name varchar2(20) := 'rangerulesinput.txt';


    dbms_output.enable(1000000);

    v_inputfile := utl_file.fopen(v_dir,v_name,'r');
    Loop
    cntr := cntr + 1;
    begin
    utl_file.get_line(v_inputfile,v_inputfield);
    dbms_output.put_line ('Read in record '|| v_inputfield);
    EXCEPTION
    when no_data_found then
    exit;
    END;

    if cntr = 1 then
    target_inst := substr(v_inputfield,16,4);
    end if;
    if cntr = 2 then
    new_model_1 := substr(v_inputfield,16,4);
    end if;
    if cntr = 3 then
    new_model_2 := substr(v_inputfield,16,4);
    end if;
    if cntr = 4 then
    new_model_3 := substr(v_inputfield,16,4);
    end if;
    if cntr = 5 then
    new_model_4 := substr(v_inputfield,16,4);
    end if;
    if cntr = 6 then
    new_model_5 := substr(v_inputfield,16,4);
    end if;


    END LOOP;
    utl_file.fclose(v_inputfile);

    Hope this helps.

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    Here is a sample procedure that creates tab delimited excel file on the server. The filename is supplied from the form.

    CREATE OR REPLACE PROCEDURE nrs_export_data (v_business_id varchar2, filename varchar2)
    IS

    out_file UTL_FILE.File_Type;
    linebuf VARCHAR2(1999);
    directory_name varchar2(100) := '/devl/nbr/data/exports';
    v_total number := 0;
    v_database VARCHAR2(30);
    v_db_desc VARCHAR2(255);
    v_rep_url VARCHAR2(255);
    v_ret_val VARCHAR2(2000);
    v_export_dir VARCHAR2(30);
    lv_error_text varchar2(255);

    CURSOR export_cur IS
    SELECT p1.prodno PRIM_Prodno,
    e.serial_no PRIM_Serialno,
    c1.code PRIM_Status,
    c2.code PRIM_Status_Reason,
    e.status_user_id PRIM_Status_Userid,
    e.status_timestamp PRIM_Status_Time,
    e.registration_date PRIM_Regist_Date,
    e.device_invoice_found PRIM_Device_Invoice_Found,
    e.custno PRIM_Atlas_Custno,
    b.operating_unit PRIM_Business,
    tc.sales_rep_name PRIM_Sales_Rep,
    p2.prodno SEC_Prodno,
    se.serial_no SEC_Serialno,
    c3.code SEC_Status,
    c4.code SEC_Status_Reason,
    se.status_user_id SEC_Status_Userid,
    se.status_timestamp SEC_Status_Time,
    se.registration_date SEC_Regist_Date,
    se.device_invoice_found SEC_Device_Invoice_Found,
    ph.docno Physician_Docno,
    ph.firstname Physician_First_Name,
    ph.lastname Physician_Last_Name,
    ph.clinic_name Clinic_Name,
    ph.address_line1 Address_Line1,
    ph.address_line2 Address_Line2,
    ph.address_line3 Address_Line3,
    ph.city City,
    ph.state State,
    ph.zip Zip,
    c5.code Country,
    ph.phy_phone Physician_Phone,
    ph.clinic_phone Clinic_Phone,
    ph.active Physician_Status,
    c6.code Physician_Status_Reason,
    ph.status_user_id Physician_Status_Userid,
    ph.status_timestamp Physician_Status_Time
    FROM nrs_equipment e,nrs_equipment_xref ex,
    nrs_physician ph,
    nrs_product p1,
    nrs_product p2,
    nrs_sales_rep tc,
    nrs_business b,
    nrs_codes c1,
    nrs_codes c2,
    nrs_codes c3,
    nrs_codes c4,
    nrs_codes c5,
    nrs_secondary_equipment se,
    nrs_codes c6
    WHERE e.business_id=v_business_id
    AND e.equipment_id = ex.equipment_id_primary(+)
    AND ex.equipment_id_secondary = se.equipment_id(+)
    AND se.product_id = p2.product_id(+)
    AND se.status_code_id = c3.code_id(+)
    AND se.status_reason_code_id = c4.code_id(+)
    AND e.business_id = b.business_id
    AND e.product_id = p1.product_id
    AND e.physician_id = ph.physician_id
    AND e.sales_rep_id = tc.sales_rep_id(+)
    AND e.status_code_id = c1.code_id
    AND e.status_reason_code_id = c2.code_id
    AND ph.country_code_id = c5.code_id
    AND ph.status_reason_code_id = c6.code_id(+);

    BEGIN
    begin
    out_file := UTL_FILE.Fopen(directory_name, filename, 'w');
    exception
    when others then
    dbms_output.put_line('Error opening file');
    end;

    linebuf:= 'PRIM_Prodno'||' '||
    'PRIM_Serialno'||' '||
    'PRIM_Status'||' '||
    'PRIM_Status_Reason'||' '||
    'PRIM_Status_Userid'||' '||
    'PRIM_Status_Time'||' '||
    'PRIM_Regist_Date'||' '||
    'PRIM_Device_Invoice_Found'||' '||
    'PRIM_Atlas_Custno'||' '||
    'PRIM_Business'||' '||'PRIM_Sales_Rep'||' '||'SEC_Prodno'||' '||
    'SEC_Serialno'||' '||'SEC_Status'||' '||'SEC_Status_Reason'||' '||
    'SEC_Status_Userid'||' '||'SEC_Status_Time'||' '||
    'SEC_Regist_Date'||' '||'SEC_Device_Invoice_Found'||' '||
    'Physician_Docno'||' '||'Physician_First_Name'||' '||
    'Physician_Last_Name'||' '||'Clinic_Name'||' '||
    'Address_Line1'||' '||'Address_Line2'||' '||
    'Address_Line3'||' '||'City'||' '||
    'State'||' '||'Zip'||' '||
    'Country'||' '||'Physician_Phone'||' '||
    'Clinic_Phone'||' '||'Physician_Status'||' '||
    'Physician_Status_Reason'||' '||
    'Physician_Status_Userid'||' '||'Physician_Status_Time';
    UTL_FILE.Put_Line(out_file, linebuf);
    FOR export_rec in export_cur LOOP
    dbms_output.put_line(export_rec.PRIM_Prodno||export_rec.PRIM_serialno);
    linebuf := export_rec.PRIM_Prodno||' '||
    export_rec.PRIM_Serialno||' '||
    export_rec.PRIM_Status||' '||
    export_rec.PRIM_Status_Reason||' '||
    export_rec.PRIM_Status_Userid||' '||
    to_char(export_rec.PRIM_Status_Time,'DD-MON-YYYY')||' '||
    to_char(export_rec.PRIM_Regist_Date,'DD-MON-YYYY')||' '||
    export_rec.PRIM_Device_Invoice_Found||' '||
    export_rec.PRIM_Atlas_Custno||' '||
    export_rec.PRIM_Business||' '||
    export_rec.PRIM_Sales_Rep||' '||
    export_rec.SEC_Prodno||' '||
    export_rec.SEC_Serialno||' '||
    export_rec.SEC_Status||' '||
    export_rec.SEC_Status_Reason||' '||
    export_rec.SEC_Status_Userid||' '||
    to_char(export_rec.SEC_Status_Time,'DD-MON-YYYY')||' '||
    to_char(export_rec.SEC_Regist_Date, 'DD-MON-YYYY')||' '||
    export_rec.SEC_Device_Invoice_Found||' '||
    export_rec.Physician_Docno||' '||
    export_rec.Physician_First_Name||' '||
    export_rec.Physician_Last_Name||' '||
    export_rec.Clinic_Name||' '||
    export_rec.Address_Line1||' '||
    export_rec.Address_Line2||' '||
    export_rec.Address_Line3||' '||
    export_rec.City||' '||
    export_rec.State||' '||
    export_rec.Zip||' '||
    export_rec.Country||' '||
    export_rec.Physician_Phone||' '||
    export_rec.Clinic_Phone||' '||
    export_rec.Physician_Status||' '||
    export_rec.Physician_Status_Reason||' '||
    export_rec.Physician_Status_Userid||' '||
    to_char(export_rec.Physician_Status_Time, 'DD-MON-YYYY');
    v_total := v_total + 1;
    UTL_FILE.Put_Line(out_file, linebuf);
    END LOOP;
    UTL_FILE.Fclose(out_file);
    EXCEPTION
    WHEN no_data_found THEN
    UTL_FILE.Fclose(out_file);
    WHEN OTHERS THEN
    dbms_output.put_line('Error in UTL_FILE');
    END nrs_export_data;
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    Jan 2002
    Posts
    65
    Here is a simple procedure that uses UTL_FILE for writing error messages in a log file.

    declare
    p_v_Exchange VARCHAR2(20) := 'STR26CCT';
    c_v_PackageName CONSTANT glob0.v_v_PackNam%TYPE DEFAULT 'Pck_Rou_Transl_S12';
    c_v_ProgramName varchar2(30):= 'LoadRouting';
    v_v_ErrorLocation VARCHAR2(254) := '-'; -- To locate the occured error
    v_v_FileErrName VARCHAR2(50) := c_v_PackageName||'.'||c_v_ProgramName||'.err';
    v_v_FileRouteErrName VARCHAR2(50) := c_v_PackageName||'.'||c_v_ProgramName||'.DupRoute';
    v_v_FileRouteCombErrName VARCHAR2(50) := c_v_PackageName||'.'||c_v_ProgramName||'.DupRouteComb';
    v_v_FileLocation VARCHAR2(50) := '/isnf/isn/var/matrix';
    v_fh_Err UTL_FILE.FILE_TYPE; -- Handle on the error file
    v_fh_DupRoute UTL_FILE.FILE_TYPE; -- Handle on the error file
    v_fh_DupRouteComb UTL_FILE.FILE_TYPE; -- Handle on the error file
    BEGIN
    -------------------------------------
    -- Using the RBIG rollback segment --
    -------------------------------------
    v_v_ErrorLocation := 'use the RBIG rollback segment at the start';
    COMMIT;
    DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('RBIG');
    --------------------------------
    -- Opening of the error files --
    --------------------------------
    v_v_ErrorLocation := 'opening of the error file';
    v_fh_Err := UTL_FILE.FOPEN (v_v_FileLocation, v_v_FileErrName, 'W');
    v_v_ErrorLocation := 'opening of the error file for the duplicate record into Route table';
    v_fh_DupRoute := UTL_FILE.FOPEN (v_v_FileLocation, v_v_FileRouteErrName, 'W');
    v_v_ErrorLocation := 'opening of the error file for the duplicate record into Route Combination table';
    v_fh_DupRouteComb := UTL_FILE.FOPEN (v_v_FileLocation, v_v_FileRouteCombErrName, 'W');
    ---------------------------------------------------------
    -- Call the procedure to correct the r_rtblk_s12 table --
    ---------------------------------------------------------
    v_v_ErrorLocation := 'perform the CorrectTRtblkS12Table procedure';
    Pck_Rou_Transl_S12.CorrectTRtblkS12Table
    (
    v_fh_Err
    );
    ------------------------------------------------------
    -- Call the first procedure to load the route table --
    ------------------------------------------------------
    v_v_ErrorLocation := 'perform the LoadRouteTable procedure for: '||p_v_Exchange;
    Pck_Rou_Transl_S12.LoadRoute(
    p_v_Exchange,
    v_fh_Err,
    v_fh_DupRoute,
    v_fh_DupRouteComb
    );
    v_v_ErrorLocation := 'write the buffer of the files';
    UTL_FILE.FFLUSH (v_fh_DupRoute);
    v_v_ErrorLocation := 'closing of the error file for the duplicate record into Route Combination table';
    UTL_FILE.FCLOSE (v_fh_DupRouteComb);
    v_v_ErrorLocation := 'closing of the error file for the duplicate record into Route table';
    UTL_FILE.FCLOSE (v_fh_DupRoute);
    v_v_ErrorLocation := 'closing of the error file';
    UTL_FILE.FCLOSE (v_fh_Err);
    -------------------------------
    -- Perform a last committing --
    -------------------------------
    v_v_ErrorLocation := 'perform a final committing';
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    UTL_FILE.PUT_LINE (v_fh_Err,'Error when <'||v_v_ErrorLocation||'> in '||c_v_ProgramName);
    UTL_FILE.PUT_LINE (v_fh_Err,'Oracle error: '||SQLCODE||' - '||SQLERRM);
    UTL_FILE.FFLUSH (v_fh_Err);
    END;
    /
    Sabitabrata

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Here is an example which generates 'Insert' statements for all tables in a scheema and saves SQL statements in a flat file which has the same name as a table.

    -- set scan off;

    exec dbms_output.enable;
    set serveroutput on;

    create or replace package dump_trax as
    procedure dump_table(table_name_ IN varchar2);
    procedure dump_all;
    end;
    /

    create or replace package body dump_trax as

    procedure dump_all as
    CURSOR table_name_c IS SELECT table_name FROM user_tables;
    tn table_name_c%ROWTYPE;
    begin
    open table_name_c;
    LOOP
    FETCH table_name_c into tn;
    EXIT WHEN table_name_c%NOTFOUND;
    dump_table(tn.table_name);
    END LOOP;
    close table_name_c;
    end dump_all;

    procedure dump_table(table_name_ IN varchar2) as
    stmt varchar2(200) := 'select column_name from user_tab_columns where table_name = '''|| table_name_ ||'''';
    stmt_c integer; -- := dbms_sql.open_cursor;
    stmt_c1 integer; -- := dbms_sql.open_cursor;
    dummy_int integer;
    column_name_ varchar2(30);
    data_type_ varchar2(106);
    data_length_ number;
    prows number;
    pstatus numeric;
    v_FileHandle UTL_FILE.FILE_TYPE;
    v_FileError UTL_FILE.FILE_TYPE;
    stmt1 varchar2(200) := 'select rowid as row_id from '||table_name_;
    stmt11 integer := dbms_sql.open_cursor;
    row_id varchar2(18);
    tmp varchar2(4000);
    data_ varchar2(4000);
    com NUMBER := 0;
    com_int NUMBER := 100;
    path VARCHAR2(200) := '/u02/we/admin/trax_sql'; -- 'C:\Temp'; --'C:\temp\trax_sql'
    cursor help_c is
    select column_name,DATA_TYPE,DATA_LENGTH from user_tab_columns where table_name = table_name_;
    help_var help_c%ROWTYPE;
    begin

    dbms_sql.parse(stmt11, stmt1, dbms_sql.native);
    dbms_sql.define_column(stmt11, 1, row_id, 18);
    dummy_int := dbms_sql.execute(stmt11);

    -- v_FileHandle := UTL_FILE.FOPEN('C:\temp\trax_sql', table_name_ || '.sql', 'w');
    v_FileHandle := UTL_FILE.FOPEN(path, table_name_ || '.sql', 'w');

    while (dbms_sql.fetch_rows(stmt11) > 0)
    LOOP
    dbms_sql.column_value(stmt11, 1, row_id);

    UTL_FILE.PUTF(v_FileHandle, '%s\n','insert into ' || table_name_ ||'(');
    --------------------------------------------------------------------------
    stmt_c := dbms_sql.open_cursor;
    dbms_sql.parse(stmt_c, stmt, dbms_sql.native);
    dbms_sql.define_column(stmt_c, 1, column_name_, 30);
    dummy_int := dbms_sql.execute(stmt_c);
    tmp := '';
    while (dbms_sql.fetch_rows(stmt_c) > 0)
    LOOP
    dbms_sql.column_value(stmt_c, 1, column_name_);
    tmp := tmp || column_name_ ||','|| chr(10);
    END LOOP;
    dbms_sql.close_cursor(stmt_c);
    UTL_FILE.PUTF(v_FileHandle, '%s\n', SUBSTR(tmp,1,length(tmp) - 2) || ') values(');
    --------------------------------------------------------------------------

    OPEN help_c;

    LOOP
    FETCH help_c into help_var;
    EXIT WHEN help_c%NOTFOUND;
    BEGIN
    stmt_c1 := dbms_sql.open_cursor;
    tmp := 'select distinct decode(c.data_type,''VARCHAR2'',replace(m.'|| help_var.column_name;
    tmp := tmp ||',chr(39),chr(39)||chr(39)),''NUMBER'',to_char('|| help_var.column_name;
    tmp := tmp ||'),''DATE'',to_char(m.'|| help_var.column_name ||'),'''') as data_ from ';
    tmp := tmp || table_name_ ||' m, user_tab_columns c where c.column_name = ''';
    tmp := tmp || help_var.column_name ||''' and c.table_name = ''' || table_name_ || ''' and m.rowid = '''||row_id||'''';
    dbms_sql.parse(stmt_c1, tmp, dbms_sql.native);

    dbms_sql.define_column(stmt_c1, 1, data_, 4000);

    dummy_int := dbms_sql.execute(stmt_c1);
    dummy_int := dbms_sql.fetch_rows(stmt_c1);
    dbms_sql.column_value(stmt_c1, 1, data_);
    IF help_c%ROWCOUNT > 1 then
    UTL_FILE.PUTF(v_FileHandle, '%s\n', ',''' || data_ || '''');
    else
    UTL_FILE.PUTF(v_FileHandle, '%s\n', '''' || data_ || '''');
    end if;
    dbms_sql.close_cursor(stmt_c1);
    EXCEPTION WHEN OTHERS THEN
    UTL_FILE.PUTF(v_FileHandle, '%s\n', ',''''');
    tmp := SQLERRM;
    v_FileError := UTL_FILE.FOPEN(path, 'error_log.txt', 'a');
    UTL_FILE.PUTF(v_FileError, '%s\n', 'Error for table : ' || table_name_);
    UTL_FILE.PUTF(v_FileError, '%s\n', 'Column NAME : ' || help_var.column_name);
    UTL_FILE.PUTF(v_FileError, '%s\n', 'Column TYPE : ' || help_var.data_type);
    UTL_FILE.PUTF(v_FileError, '%s\n', 'Column LENGTH : ' || help_var.data_length);
    UTL_FILE.PUTF(v_FileError, '%s\n', tmp);
    tmp := SQLCODE;
    UTL_FILE.PUTF(v_FileError, '%s\n', 'SQLCODE: ' || tmp);
    UTL_FILE.PUTF(v_FileError, '%s\n', '------------------------------------------------');
    UTL_FILE.FCLOSE(v_FileError);
    END;
    END LOOP;
    -- dbms_sql.close_cursor(stmt_c);
    CLOSE help_c;
    --------------------------------------------------------------------------
    UTL_FILE.PUTF(v_FileHandle, '%s\n', ');');
    UTL_FILE.PUTF(v_FileHandle, '%s\n', '');
    com := com + 1;
    if com >= com_int then
    UTL_FILE.PUTF(v_FileHandle, '%s\n', 'COMMIT;');
    UTL_FILE.PUTF(v_FileHandle, '%s\n', '');
    com := 0;
    end if;

    END LOOP; -- row_id
    dbms_sql.close_cursor(stmt11);
    UTL_FILE.PUTF(v_FileHandle, '%s\n', 'COMMIT;');
    UTL_FILE.PUTF(v_FileHandle, '%s\n', '');
    UTL_FILE.PUTF(v_FileHandle, '%s\n', '-- DONE');
    UTL_FILE.FCLOSE(v_FileHandle);
    EXCEPTION WHEN OTHERS THEN
    -- dbms_sql.close_cursor(stmt11);
    -- dbms_output.put_line('Error: ' || table_name_);
    tmp := SQLERRM;
    v_FileError := UTL_FILE.FOPEN(path, 'error_log.txt', 'a');
    UTL_FILE.PUTF(v_FileError, '%s\n', 'Error for table : ' || table_name_);
    UTL_FILE.PUTF(v_FileError, '%s\n', tmp);
    tmp := SQLCODE;
    UTL_FILE.PUTF(v_FileError, '%s\n', 'SQLCODE: ' || tmp);
    UTL_FILE.PUTF(v_FileError, '%s\n', '------------------------------------------------');
    UTL_FILE.FCLOSE(v_FileError);
    end;

    end dump_trax;
    /


    show errors;
    Best wishes!
    Dmitri

Posting Permissions

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


Click Here to Expand Forum to Full Width