导入csv数据到远程oracle数据库和listener.ora

oracle在Windows，以下命令在linux，csv文件从windows的sqldeveloper导出，

• 用sakura打开csv文件发现换行符是\r\n，linux是\n，导致报错age处ora-01722/invalid nuber，替换成\n即可

#a.sh
#! /bin/bash
#删除修改一些数据再导入
sqlplus scott/tiger@192.168.26.66/orcl <<EOF
DELETE FROM test1 WHERE id = 5;
UPDATE TEST1 SET AGE=0 WHERE ID>3;
EOF

sqlldr scott/tiger@192.168.26.66/orcl control=/home/htcca/control.ctl log=/home/htcca/log.log bad=/home/htcca/bad.bad

#control.ctl 
options(skip=1)
LOAD DATA
INFILE '/home/htcca/1n.csv'
APPEND
INTO TABLE test1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id,
  name,
  age
)

sqlplus scott/tiger@192.168.26.66/orcl各种报错
（用sqlplus -S scott/tiger@192.168.26.66/orcl似乎连不上，原因不明）

• lsnrctl status查看服务名，db地址是sqlplus scott/tiger@192.168.26.66/{服务名}
• 如果报错ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务，可能就是服务名字错了
• 似乎只要配置listener.ora的SID_NAME= orcl，然后重启oracledb19hometnsListener即可，不用配置tnsnames.ora
• listener.ora和tnsnames.ora都在oraclehome/network/admin下
• lsnrctl reload重启,去任务管理器/服务里重启oracleserviceorcl
• 注释(PROGRAM = extproc)，不然报错，要重新输入账号密码才能登录

# listener.ora Network Configuration File: D:\workspace\oracle\a\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = D:\workspace\oracle\a)
      #(PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\workspace\oracle\a\bin\oraclr19.dll")
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.66)(PORT = 1521))
  )
ADR_BASE_LISTENER = D:\workspace\oracle\a\log

# tnsnames.ora
ORCL =
(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.26.66)(PORT = 1521))
		(CONNECT_DATA =
			(SERVER = DEDICATED)
			(SERVICE_NAME = orcl)
		)
	)
)

添加了配置文件的写法

• control文件的loaddata的INFILE '/home/htcca/1n.csv’可以不写，再sqlldr里用data=filepath的方式传进去，并根据不同的参数加载不同的env变量文件
• csv文件是utf8格式，插入数据库后日文显示乱码，controll文件里设置CHARACTERSET UTF8即可
• 表里addtime 是date type，csv是字符串"2005/4/7 21:13"，controll文件里设置addtime DATE “YYYY/MM/DD HH24:MI”
• 表里updatetime 是date type，值是SYSDATE，controll文件里设置updatetime SYSDATE,并在csv里删除该字段，否则读数据时会错位

# cat b.sh
#!/bin/sh

env=$1
echo $env
password=""

if [[ $env = "test" ]]; then
  echo "test"
  . oracle.env
elif [[ $env = "dev" ]]; then
  echo "develop"
  . oracle1.env
else
  echo "error:not found parameter1"
fi

echo $url
echo $username
echo $password

sqlplus $username/$password@$url <<EOF
truncate table test2;
EOF
=================
# cat e.sh
#!/bin/sh
password=""

env=$1
echo $env

if [[ $env = "test" ]]; then
  echo "test"
  . oracle.env
elif [[ $env = "dev" ]]; then
  echo "develop"
  . oracle1.env
else
  echo "error:not found parameter1"
fi

sqlldr $username/$password@$url control=control.ctl log=log.log bad=bad.bad data=$filename
=====================
# cat oracle1.env
url="192.168.126.66/orcl"
username="scott"
password="tiger"
filename="test2.csv"
===============
# cat control.ctl
options(skip=1)
LOAD DATA
CHARACTERSET UTF8
APPEND
INTO TABLE test2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
        id,
		name,
        addtime DATE "YYYY/MM/DD HH24:MI",
        updatetime SYSDATE,
)

=====================================================
# if run [. f.sh test],it will print test and read oracle.dev file
# by this way u can pass parameter in command to the sh file
# chatgpt said run [. f.sh env=test] so u can env=$env get the parameter in shell file
# but i failed
# cat f.sh
#!/bin/bash

env=$1
echo $env

if [[ $env = "test" ]]; then
  echo "test"
  . oracle.env
elif [[ $env = "dev" ]]; then
  echo "develop"
  . oracle1.env
else
  echo "error:not found parameter1"
fi
echo $username