Finally, we can load text files from Linux to the database's CLOB field.

Background: The monitoring script SHELL finds it troublesome to log on to the server every day, and the number of servers is growing.

Originally, I wanted to use the SendMail function of the system, but the company said it was not safe. You have to use the application system email function!

However, the mail table content of this function is a CLOB field.

From the top of the site to see the top of the http://www.eygle.com/archives/2005/08/ecieoadbms_lobo.html, the last mail is garbled!

Neither can I use Linux export LANG = en_US.UTF8!

A few days later, we found that the dbms_lob package has the loadclobfrom file process. The above process uses loadfromfile to load binary streams by default without character conversion.

The preceding section describes how to save a text file as a unicode code on windows. On linux, how does one use vi to save unicode? How to sh xxxx. sh> xxx. log?

Every small problem in Linux is so annoying that it is complicated to be a thief. Vi ~ /. What is vimrc compiled .... Then iconve...

Then I won't go round for you.

Step 1 Check what file type is in your system?

[Oracle @ DB-DG dbscripts] file-I unix2dos. c
Unix2dos. c: text/x-c ++; charset = us-ascii

Step 2 Check ORACLE character set encoding

SELECT Nls_Charset_Id (Value), Value from v $ NLS_VALID_VALUES WHERE parameter = 'characterset ';

1 US7ASCII

Step 3: create a directory object under an ORACLE user

# CLOB_DIR = "/home/oracle/dbscripts/logs"

Step 4 Write the insert SHELL script

#! /Bin/bash
Source ~ /. Bash_profile
Username = dba
Pass = 007

Chartset = $1

# CLOB_DIR = "/home/oracle/dbscripts/logs"

Sqlplus-s $ username/$ pass <EOF
Set serveroutput on;

Declare
L_bfile bfile; -- file handle
Rochelle clob; -- CLOB segment object
L_str varchar2 (1000); -- exception information
Src_offset number: = 1; -- source offset
Dst_offset number: = 1; -- destination offset
Lang_ctx number: = dbms_lob.default_lang_ctx;
Charset_id number: = 0; -- Language Character Set ID
Warning number; -- warning ID

Begin

Charset_id: = NLS_CHARSET_ID ('us7ascii '); -- obtains the database character set encoding ID.

-- Charset_id: = NLS_CHARSET_ID ('$ charset ');

L_bfile: = bfilename('clob_dir', 'everydayreport.txt '); -- get the file handle

-- L_bfile: = bfilename ('clob _ dir', '$ FileName ');

Insert into DBA_sendmails
VALUES (DBA_sendmails_seq.nextval, 'dba01 @ 139. COM ', 'warning', 'dba01 @ lotery.hk', 'lotery1401', 'dayreport _ all', empty_clob (), 'smtp ', 'smtp .gmail.com', '123 ', sysdate, sysdate) returning S_CONTENT into l_clob;
-- Insert the record first, empty the CLOB object, and return it to the CLOB variable.

Dbms_lob.fileopen (l_bfile );

Dbms_lob.loadclobfromfile (l_clob, l_bfile, dbms_lob.getlength (l_bfile), src_offset, dst_offset, charset_id, lang_ctx, warning );
-- Load the file to the CLOB Field

Dbms_lob.fileclose (l_bfile );
Commit;

Exception when others then
L_str: = sqlerrm (sqlcode );
Dbms_output.put_line (l_str );
End;
/

Exit;

EOF