sql - 使用 SQL Loader 和 perl 导入 csv 错误

Question

大家好，我有一个问题希望你们能帮助我。我试图包括所有相关信息。我正在构建一个 perl 脚本，该脚本最终将循环通过不同的 sqlloader 控制文件并将它们各自的 csv 数据导入到 oracle sql 数据库表中。我在循环它们之前测试多个控制负载。
问题是即使脚本连接到数据库并上传所有 csv 数据而我看到的没有任何问题，我也会收到错误。所有行都被考虑在内，日志并没有真正帮助:

 ================================================================================
 [root@sanasr06 scripts]# perl db_upload.pl
 connection made! Starting database upload...
 Error: Can't open import control_general to SQL DB : at db_upload.pl line 44
 ================================================================================

第44行是系统连接:

system ("sqlldr $userid\@$sid/$passwd control=@control_pools log=$log silent=all ") or $logger->logdie("Error: Can't open import control data to SQL DB :$!");

我包括控制文件输出、perl 脚本和控制文件。 (提到的跳过的文件是针对 csv header 的:)

 SQL*Loader: Release 11.2.0.1.0 - Production on Tue Aug 14 12:32:36 2012
 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 Control File:   /despliegue/san/project/sql_ctrl/general.ctl
 Character Set UTF8 specified for all input.

 Data File:      /despliegue/san/project/csv/Pools.csv
 Bad File:     /despliegue/san/project/logs/sql_error.bad
 Discard File: /despliegue/san/project/logs/sql_discard.dsc 
 (Allow all discards)

 Number to load: ALL
 Number to skip: 1
 Errors allowed: 50
 Bind array:     64 rows, maximum of 256000 bytes
 Continuation:    none specified
 Path used:      Conventional
 Silent options: FEEDBACK, ERRORS and DISCARDS

 Table I_GENERAL, loaded from every logical record.
 Insert option in effect for this table: TRUNCATE
 TRAILING NULLCOLS option in effect
 Column Name                  Position   Len  Term Encl Datatype
 ------------------------------ ---------- ----- ---- ---- ---------------------
 OBJECTID   (FILLER FIELD)           FIRST     *     ,  O(")  CHARACTER

 DESCRIPTION   (FILLER FIELD)         NEXT     *     ,  O(")  CHARACTER  

 SERIALNUMBER                         NEXT     *     ,  O(")  CHARACTER  

 PRODUCT_NAME                         NEXT     *     ,  O(")  CHARACTER   

 CONTROLLER_VERSION                   NEXT     *     ,  O(")  CHARACTER    

 NUMBER_OF_CONTROLLERS                NEXT     *     ,  O(")  CHARACTER    

 CAPACITY_GB                          NEXT     *     ,  O(")  CHARACTER    

 PRODUCT_CODE                         NEXT     *     ,  O(")  CHARACTER            

 value used for ROWS parameter changed from 64 to 15

 Table I_GENERAL:
 2512 Rows successfully loaded.
 0 Rows not loaded due to data errors.
 0 Rows not loaded because all WHEN clauses were failed.
 0 Rows not loaded because all fields were null.

 Space allocated for bind array:                 247680 bytes(15 rows)
 Read   buffer bytes: 1048576
 Total logical records skipped:          1
 Total logical records read:          2512
 Total logical records rejected:         0
 Total logical records discarded:        0

 Run began on Tue Aug 14 12:32:36 2012
 Run ended on Tue Aug 14 12:32:38 2012

==================================================== =================================

上述文件当然会缩短，但包含所有相关信息。
这是 perl 脚本。

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Log::Log4perl;
#this script loads multiple saved csv files into the database using the control files

################ Initialization #############################################
my $homepath = "/despliegue/san/project";

my $log_conf = "$homepath/logs/log.conf";
Log::Log4perl->init($log_conf)or die("Error: Can't open log.config Does it exist? $!");
my $logger = Log::Log4perl->get_logger();

################ database connection variables####
my ($serial, $model);
my $host="me.notyou33.safety";
my $port="1426";
my $userid="user";
my $passwd="pass";
my $sid="sid";
my $log="$homepath/logs/sql_import.log";

#Control file location
my @control_pools= "$homepath/sql_ctrl/pools.ctl";
my @control_general = "$homepath/sql_ctrl/general.ctl";
my @control_ports= "$homepath/sql_ctrl/ports.ctl";
my @control_replication = "$homepath/sql_ctrl/replication.ctl";

#######################Database connection and data upload #################
my $dbh = DBI->connect( "dbi:Oracle:host=$host;sid=$sid;port=$port", "$userid", "$passwd", 
{ RaiseError => 1}) or $logger->logdie ("Database connection not made: $DBI::errstr");

print " connection made! Starting database upload...\n";

system ("sqlldr $userid\@$sid/$passwd control=@control_general log=$log silent=all") or $logger->logdie("Error: Can't open import control_general to SQL DB :$!");

print "one done moving to next one\n";

system ("sqlldr $userid\@$sid/$passwd control=@control_pools log=$log silent=all ") or $logger->logdie("Error: Can't open import control data to SQL DB :$!");

system ("sqlldr $userid\@$sid/$passwd control=@control_ports log=$log  ") or $logger->logdie("Error: Can't open import control data to SQL DB :$!");

print "three done moving to last one\n";

system ("sqlldr $userid\@$sid/$passwd control=@control_replication log=$log silent=feedback ") or    $logger->logdie("Error: Can't open import control data to SQL DB :$!");

print "................Done\n";
############################################################################
$dbh->disconnect;

==================================================== =================================
控制文件:

OPTIONS (SKIP=1) 
LOAD DATA
CHARACTERSET UTF8
INFILE      '/despliegue/san/project/csv/Pools.csv'
BADFILE     '/despliegue/san/project/logs/sql_error.bad'
DISCARDFILE '/despliegue/san/project/logs/sql_discard.dsc'
TRUNCATE INTO TABLE I_GENERAL 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "\"" 
 TRAILING NULLCOLS
( 
OBJECTID FILLER,
DESCRIPTION FILLER,
SERIALNUMBER,
PRODUCT_NAME,
CONTROLLER_VERSION,
NUMBER_OF_CONTROLLERS,
CAPACITY_GB,
PRODUCT_CODE,
)

Answer 1

system()返回 wait 的返回值调用其中包括您执行的程序的返回值。如果一切顺利，这将是 0 .这与 Perl 中几乎所有其他功能不同，您希望它们返回一些在 bool 上下文中计算为 True 的值。因此，使用 or 运算符的常用错误处理无法正常工作。您可能想尝试这样的事情:

system ("sqlldr $userid\@$sid/$passwd control=@control_pools log=$log silent=all") == 0
    or $logger->logdie("Error: Can't open import control data to SQL DB :$?");

您可以阅读有关处理 system() 的返回值的更多信息。在 perldoc -f system 下的文档中