如何将具有不同数据类型的动态列的平面文件加载到oracle数据库中? [英] How to load flat file with dynamic columns with different datatype into oracle database?

问题描述

我想将平面文件加载到oracle数据库中.此平面文件可以从表A或表B或表C生成.

I want to load a flat file into oracle Database. This flat file may be generated from table A or table B or table C.

因此，当我将此文件加载到oracle表中时，我不确定该平面文件具有多少列以及该列具有哪种数据类型(取决于它是从表A还是从表B或表C生成的)

So when I am loading this file into oracle table , I am exactly not sure how many columns and what data type is column has that flat file has ( depends on whether it is generated from table A or table B or table C ).

因此，请让我知道将可变列长文件加载到oracle数据库中的通用方法和技术.

So, pls let me know the generic method, technique to load variable column length file into oracle database.

示例:

• 表A

• Table A

1 | 2 | 3 | 4

表B

1 | XYZ | 3 | 4 | 5 | XXX

表3

xxx | 2013-09-28 | 10.0

因此，这里每个表都有可变的列和不同的数据类型.如何将这些文件加载​​到oracle数据库中.

So, here each table has variable columns and different datatype. How can I load these file into oracle database.

谢谢.

推荐答案

一种选择是使用SQLLoader将文件加载到表中.

假设我们创建了三个表:

One option is to use SQLLoader to load files into tables.

Say we have created three tables:

CREATE TABLE tableA(
  col1 int, col2 int, col3 int, col4 int 
);

CREATE TABLE tableB(
  col1 int, col2 varchar2(100), col3 int, col4 int, col5 int, col6 varchar2(100)
);

CREATE TABLE tableC(
  col1 varchar2(100), col2 date, col3 number(10,2)
);

我假设该文件始终仅以一种格式(三种可能的格式之一)进行记录.
在这种情况下，您可以为每种格式创建3个不同的控制文件:

format_a.ctl

I am assuming that the file has always records in one format only (one of 3 possible formats).
In such a case, you can create 3 different control files for each format:

format_a.ctl

load data
 infile 'c:\tmp\test\file.txt'
 into table tableA
 fields terminated by "|"         
 ( col1, col2, col3, col4 )

format_b.ctl

load data
 infile 'c:\tmp\test\file.txt'
 into table tableB
 fields terminated by "|"         
 ( col1, col2, col3, col4, col5, col6 )

format_c.ctl

infile 'c:\tmp\test\file.txt'
 into table tableC
 fields terminated by "|"         
 ( col1 , 
   col2 date 'yyyy-mm-dd',
   col3 )

然后创建一个简单的脚本，该脚本可检测文件格式并使用适当的控制文件上载数据-这是Windows环境的示例:

Then create a simple script that detects a format of the file and uploads data using an appropriate control file - this is an example for Windows environment:

@echo off
set filename=file.txt
IF NOT EXIST   %filename%  GOTO error

findstr /M "\|.*\|.*\|.*\|.*\|" file.txt
IF NOT ERRORLEVEL 1 GOTO formatB

findstr /M "\|.*\|.*\|" file.txt
IF NOT ERRORLEVEL 1 GOTO formatA

findstr /M "\|.*\|" file.txt
IF NOT ERRORLEVEL 1 GOTO formatC

:error
Echo Error: file %filename% doesn't exist or doesn't match any proper format
goto end

:formatA
set ctl_file=format_a
goto import

:formatB
set ctl_file=format_b
goto import

:formatc
set ctl_file=format_c
goto import

:import
echo Import using: %ctl_file%
sqlldr test/test@//192.168.2.51:1521/orcl control=%ctl_file%.ctl log=%ctl_file%.log
:end

在这一行:

sqlldr test/test@//192.168.2.51:1521/orcl control=%ctl_file%.ctl log=%ctl_file%.log

test/test @是具有密码test

test/test@ is a database user test having password test

这篇关于如何将具有不同数据类型的动态列的平面文件加载到oracle数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！