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;
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
Reply With Quote
Bookmarks