oracle sqlload 导入数据_oracle sqlloader导入数据-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_46764753/article/details/135955778
本文介绍了如何在Oracle数据库中使用SQL*Loader工具，通过创建控制文件并执行`sqlldr`命令，从CSV文件导入数据。方法包括处理分隔符、字段定义、错误处理以及控制文件的生成过程。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
public class OracleLoadDataUntil {
     /**
     * 编写控制文件
     * @param tableName   表名
     * @param delimiter   csv文件分隔符
     * @param fieldNames  属性集合
     * @param ctlFilePath ctl控制文件地址
     * @param tempCsvPath csv文件地址
     * @return
     */
    private void createCtlFile(String tempCsvPath, String ctlFilePath, String tableName, String ownerAcct, String delimiter, List<String> fieldNames) {
        FileWriter fw = null;
        try {
            String ctlStr = getStrCtr(tempCsvPath, ownerAcct, tableName, delimiter, fieldNames);
            fw = new FileWriter(ctlFilePath);
            fw.write(ctlStr);
        } catch (IOException e) {
            LogUtil.error("写出文件:" + ctlFilePath + "异常", e);
        } finally {
            try {
                if(fw != null){
                    fw.close();
                }
            } catch (IOException e) {
                LogUtil.error(e);
            }
        }
    }

     /**
     * @return sqlload的执行语句
     */
    public static void excuteLoadData(String logfilePath, String tempCsvPath, String tempCtlPath, String ip, String port, String password, String userName, String serviceName, String tableName, boolean isRun, List<String> failLogFiles) throws IOException {
        SequenceInputStream sis = null;
        try {
            LogUtil.info("开始导入数据:" + tempCsvPath);
            //导入命令
            String sqlldrStr = "sqlldr userid=" + userName + "/'" + password + "'@" + ip + ":" + port + "/" + serviceName;
            sqlldrStr += " control=" + tempCtlPath + " log=" + logfilePath + " rows = 5000 bindsize = 8388608 direct = true" ;
            ProcessBuilder pb = new ProcessBuilder("sh", "-c", sqlldrStr);
            pb.redirectErrorStream(true);
            //执行导入数据命令
            Process prs = pb.start();
            //获取导入信息结果
            sis = new SequenceInputStream(prs.getInputStream(), prs.getErrorStream());
            BufferedReader reader = new BufferedReader(new InputStreamReader(sis,Charset.forName("UTF-8")));
            String line = null;
            Integer totalCount = -1;
            Integer successCount = -1;
            while (null != (line = reader.readLine())){
                LogUtil.info("调用sqlldr的执行结果:" + line);
                if (line != null){
                    try {
                        //ORA-01658: unable to create INITIAL extent for segment in tablespace test_50g
                        if (line.contains("ORA-01658: unable to create INITIAL extent for segment in tablespace")){
                            throw new RuntimeException("表" + tableName + "导入数据失败,详情查看日志文件：" + logfilePath);
                        } else if (line.startsWith("ORA-04043: object") && line.endsWith("does not exist")) {
                            throw new RuntimeException("表" + tableName + "导入数据失败，原因：表不存在");
                        }
                        //Load completed - logical record count 3.
                        if (line.contains("Load completed")){
                            //获取总条数
                            totalCount = Integer.valueOf(line.substring(line.indexOf("count") + 6,line.length() - 1));
                        }
                        // 0 Rows successfully loaded.
                        if (line.contains("Rows successfully loaded")){
                            //获取成功导入条数
                            successCount = Integer.valueOf(line.substring(0, line.indexOf("Rows") -1).trim());
                        }else if(line.contains("Row successfully loaded")){
                            // 1 Row successfully loaded.
                            //获取成功导入条数
                            successCount = Integer.valueOf(line.substring(0, line.indexOf("Row") -1).trim());
                        }
                    } catch (Exception e) {
                        //如果ctl文件出错,可能会抛异常
                        LogUtil.error(e);
                        failLogFiles.add(logfilePath);
                        throw new RuntimeException("表" + tableName + "导入失败数据" + (totalCount - successCount) + "条,详情查看日志文件：" + logfilePath);
                    }
                }
            }
            //如果有导入失败的原因（导入失败的原因可能多个，所以用总数-导入成功的来记录失败数量）
            if (totalCount != -1 && successCount != -1 && (totalCount - successCount) > 0){
                failLogFiles.add(logfilePath);
                throw new RuntimeException("文件" + tempCsvPath + "导入数据到数据库失败" + (totalCount - successCount) + "条,详情查看日志文件：" + logfilePath);
            }
        } catch (Exception e) {
            failLogFiles.add(logfilePath);
            LogUtil.error("执行sqlloader CSV文件到oracle数据库异常，异常信息：" + e + ",详情查看日志文件："  + logfilePath);
            throw e;
        }
    }


     /**
     * 获取控制命名字符串
     * @param tempCsvPath  导入的文件名，全路径
     * @param tableName 表名
     * @param delimiter 分割符
     * @param fileds 属性集合
     * @return
     */
    private String getStrCtr(String tempCsvPath, String ownerAcct, String tableName, String delimiter, List<String> fileds) {
        StringBuffer sb = new StringBuffer();
        String lineSeparator = System.getProperty("line.separator");
        // 加载数据文件
        sb.append("LOAD DATA").append(lineSeparator);
        // 防止中文乱码
        sb.append("CHARACTERSET UTF8").append(lineSeparator);
        sb.append("INFILE ").append("\'").append(tempCsvPath).append("\'").append(lineSeparator);
        // 覆盖写入
        sb.append("APPEND INTO TABLE ").append('\"' + ownerAcct  + '\"' + ".").append(tableName).append(lineSeparator);
        //导入数据使用SQL*LOADER中optionally enclosed时要么数据左边不能带引号；
        //如果有引号左右都必须为奇数个，但不必相等。
        sb.append("FIELDS TERMINATED BY '" + delimiter + "' OPTIONALLY ENCLOSED BY '\"' " ).append(lineSeparator);
        // 将数据写入对应的字段
        sb.append("trailing nullcols ( ").append(lineSeparator);
        sb.append(getFieldsStr(fileds)).append(lineSeparator);
        sb.append(")");
        LogUtil.info("ctl控制文件内容:" + sb.toString());
        return sb.toString();
    }


 protected String getFieldsStr(List<String> fileds) {
        StringJoiner sj = new StringJoiner("," + System.getProperty("line.separator"));
        for (String s : fileds) {
            //TIMESTAMP类型需要做数据转换,双引号用来处理大小写敏感问题
            //if ("TIMESTAMP".equalsIgnoreCase(s.getDbType())){
                //sj.add(s.getColumnName() + " timestamp 'yyyy-mm-dd hh24:mi:ss'");
            //}else if("DATE".equalsIgnoreCase(s.getDbType())){
                //DATE类型需要做数据转换
                //sj.add(s.getColumnName() + " date 'yyyy-mm-dd hh24:mi:ss'");
            //}else if("BLOB".equalsIgnoreCase(s.getDbType()) || "CLOB".equalsIgnoreCase(s.getDbType())){
                //BLOB、CLOB、VARCHAR2类型指定长度,默认char最长256，如果大数据长度超过256会导不进去，给2136746229(测试超过2G就导入报错)
                //sj.add(s.getColumnName() + " char(2136746229)");
            //} else if("VARCHAR2".equalsIgnoreCase(s.getDbType())){
                //BLOB、CLOB、VARCHAR2类型指定长度,默认char最长256，如果大数据长度超过256会导不进去，给2136746229(测试超过2G就导入报错)
                //sj.add(s.getColumnName() + " char(" + s.getMaxLen() + ")" );
            //} else {
                sj.add(s.getColumnName());
            //}
        }
        return sj.toString();
    }
}