想通过shell脚本，读取txt文件，插入到Oracle数据库中，求大神帮忙啊

邪气丨战魄

回复 30# Shell_HAT


报错信息如下：  还有就是 SQL 命令不能退出啊

./test.sh: line 11: IMPORT_TEST: command not found
dos2unix: converting file a.txt to UNIX format ...
exit

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 7月 29 15:32:10 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

INSERT INTO $TABLENAME VALUES(15304,?..?.О1,74543184-1,2014.,20140430)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(19051,?..?.О2,79247030,2014.,20140430)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(18386,?..?.О3,16664083-3,2014.,20140515)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(22283,?..?.О4,66731611-8,2014.,20140526)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(16616,?..?.О5,73952011-9,2013.,20131021)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(15627,?..?.О6,73637776-7,2014.,20140319)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(20727,?..?.О7,7931258-3,2014.,20140526)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(17907,?..?.О8,21440016-5,2013.,20130926)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(17778,?..?.О9,79670089-6,2013.,20130819)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(19615,?..?.О10,520000000027994,2013.,20130715)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(19584,?..?.О11,75150549-9,2013.,20130703)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(25226,?..?.О12,,2014.,20140701)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(22227,?..?.О13,79914821-6,2014.,2014022
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(17090,?..?.О14,72442811-7,2013.,2013082
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(17753,?..?.О15,70219691-7,2013.,20130314)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(18513,?..?.О16,74241519-2,2014.,20140607)
            *
ERROR at line 1:
ORA-00911: invalid character


INSERT INTO $TABLENAME VALUES(19728,?..?.О17,71809837锛.,2014.,20140514)
            *
ERROR at line 1:
ORA-00911: invalid character


SQL>

   

邪气丨战魄

回复 22# li0924

你参考顶楼的文件，写一下呗，我是了一下 导不进去数据，应该是分隔符的问题


   

Shell_HAT

回复 31# 邪气丨战魄

1. filename="a.txt"
   
2. sqlfile="test.sql"
   
3. dos2unix $filename
   
4. sed 's/^/INSERT INTO table_name VALUES(\x27/; s/\x7f./\x27,\x27/g; s/$/\x27);/' $filename  > $sqlfile
   
5. echo "" >> $sqlfile
   
6. echo exit >> $sqlfile
   
7. sqlplus DB_USER/DB_PASS@DB_SID @$sqlfile

复制代码

weikle

请教楼上，x27和x7f啥东东？

邪气丨战魄

回复 33# Shell_HAT


已经测试成功了 ，非常感谢！

我还想麻烦你下 ，就是将数据插入到数据库表中的时候，可以在数据表中加一个字段 显示为插入时间的字段，这个该怎么写？

Shell_HAT

回复 34# weikle


    ASCII码

邪气丨战魄

回复 6# li0924


你好，6楼的方法不能将 顶楼文件中的分隔符转换为逗号 ，你能再看下么？


   

Shell_HAT

回复 35# 邪气丨战魄

1. filename="a.txt"
   
2. sqlfile="test.sql"
   
3. dos2unix $filename
   
4. TIME_NOW=`date "+%Y-%m-%d %H:%M:%S"`
   
5. sed "s/^/INSERT INTO table_name VALUES(\x27/; s/\x7f./\x27,\x27/g; s/$/\x27,\x27${TIME_NOW}\x27);/" $filename  > $sqlfile
   
6. echo "" >> $sqlfile
   
7. echo exit >> $sqlfile
   
8. sqlplus DB_USER/DB_PASS@DB_SID @$sqlfile

复制代码

li0924

回复 37# 邪气丨战魄

HAT不是给你了答案吗？ 我是不知道你提供文本隐含符号是什么？

1. sed 's#\x7f.#,#g' file > a.txt

复制代码

li0924

回复 38# Shell_HAT
oracle其实有sysdate函数；所以那个TIME_NOW可不用定义。