Home Database Mysql Tutorial 终于搞定了从Linux下加载文本文件到数据库CLOB字段上

终于搞定了从Linux下加载文本文件到数据库CLOB字段上

Jun 07, 2016 pm 03:28 PM
clob linux load database document text

背景:搞些监控脚本SHELL 后发现 每天登上服务器很麻烦,而且服务器越来越多了。 本来想用系统SendMail功能发,可公司说为了安全不可以。只好用应用系统发邮件功能! 然而该功能的邮件表内容是CLOB字段。 从盖国强 前辈网站看到的 http://www.eygle.com/arch

背景:搞些监控脚本SHELL 后发现 每天登上服务器很麻烦,而且服务器越来越多了。

本来想用系统SendMail功能发,可公司说为了安全不可以。只好用应用系统发邮件功能!

然而该功能的邮件表内容是CLOB字段。

从盖国强 前辈网站看到的 http://www.eygle.com/archives/2005/08/ecieoadbms_lobo.html 按其上面做,最后发出来的邮件全是乱码!

用Linux export LANG=en_US.UTF8 也不行!

过了几天发现有dbms_lob包有额loadclobfrom file过程。而上面是使用loadfromfile过程 默认是装进二进制流,没有做字符转换。

上面介绍windows平台下先要把文本文件保存为unicode编码。在linux平台下如何用vi保存unicode呢? 如何 sh xxxx.sh > xxx.log呢?

讨厌死了Linux 每个小问题都搞贼复杂,转了一圈又一圈。vi ~/.vimrc 编入啥。。。。然后iconve。。。

那我就不给你们兜圈子了 顺序的来下

第一步 检查 你的系统中的文件类型是什么?

[oracle@DB-DG dbscripts]file -i unix2dos.c

第二步 检查ORACLE字符集编码

SELECT Nls_Charset_Id(Value) ,Value FROM V$NLS_VALID_VALUES WHERE parameter = 'CHARACTERSET';

1 US7ASCII

第三步 创建目录对象在ORACLE某个用户下

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

第四步 编写插入SHELL脚本

#!/bin/bash

chartset=$1

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

sqlplus -s $username/$pass

declare

charset_id := NLS_CHARSET_ID('US7ASCII'); --获得数据库字符集编码ID

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

l_bfile := bfilename('CLOB_DIR','EveryDayReport.txt'); --获得文件句柄

-- l_bfile := bfilename('CLOB_DIR','$FileName');

 dbms_lob.loadclobfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile), src_offset,dst_offset,charset_id,lang_ctx,warning);

dbms_lob.fileclose(l_bfile);

exception when others then

exit;

EOF

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Linux Architecture: Unveiling the 5 Basic Components Linux Architecture: Unveiling the 5 Basic Components Apr 20, 2025 am 12:04 AM

The five basic components of the Linux system are: 1. Kernel, 2. System library, 3. System utilities, 4. Graphical user interface, 5. Applications. The kernel manages hardware resources, the system library provides precompiled functions, system utilities are used for system management, the GUI provides visual interaction, and applications use these components to implement functions.

How to check the warehouse address of git How to check the warehouse address of git Apr 17, 2025 pm 01:54 PM

To view the Git repository address, perform the following steps: 1. Open the command line and navigate to the repository directory; 2. Run the "git remote -v" command; 3. View the repository name in the output and its corresponding address.

Oracle's Role in the Business World Oracle's Role in the Business World Apr 23, 2025 am 12:01 AM

Oracle is not only a database company, but also a leader in cloud computing and ERP systems. 1. Oracle provides comprehensive solutions from database to cloud services and ERP systems. 2. OracleCloud challenges AWS and Azure, providing IaaS, PaaS and SaaS services. 3. Oracle's ERP systems such as E-BusinessSuite and FusionApplications help enterprises optimize operations.

How to run java code in notepad How to run java code in notepad Apr 16, 2025 pm 07:39 PM

Although Notepad cannot run Java code directly, it can be achieved by using other tools: using the command line compiler (javac) to generate a bytecode file (filename.class). Use the Java interpreter (java) to interpret bytecode, execute the code, and output the result.

What is the main purpose of Linux? What is the main purpose of Linux? Apr 16, 2025 am 12:19 AM

The main uses of Linux include: 1. Server operating system, 2. Embedded system, 3. Desktop operating system, 4. Development and testing environment. Linux excels in these areas, providing stability, security and efficient development tools.

MySQL: Structured Data and Relational Databases MySQL: Structured Data and Relational Databases Apr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

How to run sublime after writing the code How to run sublime after writing the code Apr 16, 2025 am 08:51 AM

There are six ways to run code in Sublime: through hotkeys, menus, build systems, command lines, set default build systems, and custom build commands, and run individual files/projects by right-clicking on projects/files. The build system availability depends on the installation of Sublime Text.

laravel installation code laravel installation code Apr 18, 2025 pm 12:30 PM

To install Laravel, follow these steps in sequence: Install Composer (for macOS/Linux and Windows) Install Laravel Installer Create a new project Start Service Access Application (URL: http://127.0.0.1:8000) Set up the database connection (if required)

See all articles