UTL_FILE example

Hi friends,
Could you please give an sample code using UTL_FILE package with few procedures it uses.

cheese
anandkl

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.

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;

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;
/

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;