Oracle Write function read/write log instance

1. Assign permissions with DBA Login
Create or replace directory D_output as ' D:\TEMP ';
Grant Read,write on directory d_output to TestDB;
GRANT EXECUTE on Utl_file to TestDB;

2. Write a function to create the directory and write the data
Calling functions in stored procedures for data cleansing
First declare the variable on the top
XX varchar2 (1000);
The function is then called below the execution of the Insert failure
Else
--insert into Fail_log (xh,proc_mc,title,content,log_type,log_time) values
(Fail_log_seq. Nextval, ' Pro_txz_road_plan ', ' data insertion failed ', c_yaid_t, ' ERROR ', sysdate);
Dbms_output.put_line (' Pass plan table Yaid for ' | | c_yaid_t | | ' Data already exists ');
XX: =cux_my (To_char (Sysdate (), ' Yyyy-mm-dd HH24:MI:SS ') | | ' ===> ' | | ' Pass plan table Yaid for ' | | c_yaid_t | | ' Data is inserted
Failure ');
______________________________ Creating a log _______________________________________

--Name table names code incoming data, failed data

--Stored procedure failure call naming modification a little bit of meaning

Create or Replace function cux_my (name Varchar2,code VARCHAR2)
RETURN VARCHAR2 is

FileHandle Utl_file.file_type; --Handle
A varchar2 (100);

Begin
A:=to_char (Sysdate (), ' Yymmddhh24miss '); --Time format
FileHandle: = Utl_file.fopen (' d_output ', name| | A | | '. txt ', ' a '); --Open File
Utl_file.put_line (Filehandle,code);--Write a row of records
--dbms_output.put_line (' 1 ');
Utl_file.fclose (FileHandle);--Close handle

Return ' OK ';
End

_____________________________________________________________________

______________________________ Read Log _______________________________________

1:--when the table method needs to use this read log file according to the file name

--The filename in this case is the name of the file passed in by the Java code call function, which must be consistent with the creation of the log specification

Create or Replace function Shuchu (filename VARCHAR2)
RETURN VARCHAR2 is

FileHandle Utl_file.file_type;
Filebuffer varchar (200);
BEGIN
FileHandle: = Utl_file.fopen (' d_output ', filename | | '. txt ', ' r ');
Loop
Begin
Utl_file.get_line (Filehandle,filebuffer);
INSERT INTO T values ("', filename,filebuffer,filebuffer); To insert the Read log data into the newly created table
Dbms_output.put_line (Filebuffer);
EXCEPTION
When No_data_found Then
Exit;
End;
END LOOP;
Utl_file.fclose (FileHandle);
COMMIT;
Return ' OK '; --return at this time can be arbitrary
END;

2,--when you don't need to build a table

--The filename in this case is the name of the file passed in by the Java code call function, which must be consistent with the creation of the log specification

Create or Replace function Shuchu (filename VARCHAR2)
RETURN VARCHAR2 is

FileHandle Utl_file.file_type;
Filebuffer varchar (200);
BEGIN
FileHandle: = Utl_file.fopen (' d_output ', ' 160508235600.txt ', ' R ');
Loop
Begin
Utl_file.get_line (Filehandle,filebuffer);

Dbms_output.put_line (Filebuffer);
EXCEPTION
When No_data_found Then
Exit;
End;
END LOOP;
Utl_file.fclose (FileHandle);
COMMIT;
return filebuffer; --The return value is the read log data at this time
END;

_____________________________________________________________________

Oracle Write function read/write log instance