oracle-databaseperldbisql-loaderctl

DBI Oracle and ctl file


i have to use some ctl file with csv file to insert data into an oracle database. Untill now, it has been done by using sqlloader. But, i want to use DBI Oracle CPAN to do the same work and i ask myself how to do that.

Here there is an example of a CTL file

LOAD DATA
APPEND INTO TABLE DOSSIER
FIELDS TERMINATED BY ';'
(
DSR_IDT,
DSR_CNL,
DSR_PRQ,
DSR_CEN,
DSR_FEN,
DSR_AN1,
DSR_AN2,
DSR_AN3,
DSR_AN4,
DSR_AN5,
DSR_AN6,
DSR_PI1,
DSR_PI2,
DSR_PI3,
DSR_PI4,
DSR_NP1,
DSR_NP2,
DSR_NP3,
DSR_NP4,
DSR_NFL,
DSR_NPG,
DSR_LTP,
DSR_FLF,
DSR_CLR,
DSR_MIM,
DSR_TIM,
DSR_NDC,
DSR_EMS NULLIF DSR_EMS=BLANKS "sysdate",
JOB_IDT,
DSR_STT,
DSR_DAQ "CASE WHEN :DSR_DAQ IS NOT NULL THEN SYSDATE ELSE NULL END"

)

This is just an example.

My question is simple: - With DBI Oracle CPAN module, may i use CSV with CTL File ? - OR, i have to re-write CSV and CTL file both to produce SQL request ?


Solution

  • To resolve this problem i simply create an additionnal function for dbi oracle wrapper as following:

                sub WS_SQL_Loader
                {
                    my ($this, $controlFile, $csvFile, $logFile, $logBadFile) = @_
    
                    my $returnVal       = 1;
    
                    # Récupération des données de connexion Oracle
                    my $dbi_data_source = $this->{DBLINK};
                    my $dbi_username    = $this->{DBUSER};
                    my $dbi_password    = $this->{DBPASSWORD};
    
                    #Préparation de la commande sqlldr a charger
                    my $command_line    = "sqlldr $dbi_username/$dbi_password\@$dbi_data_source data=$csvFile control=$controlFile ".
                                            "log=$logFile bad=$logBadFile silent=ALL";
                    my $output          = `$command_line`;
                    my $retcode         = $? / 256;
    
                    # S'il y a eu une erreur => On trace une erreur
                    if ($retcode ne 0) {
                        $this->{logger}->createTrace(1310, $logFile);
                        $returnVal = 0;
                    } else {
                        unlink("$logFile");
                        $this->{logger}->debug("execution de SQL*LOADER. return code => $retcode");
                        $this->{logger}->debug("execution de SQL*LOADER. commande    => sqlldr $dbi_username/<mot de passe>\@$dbi_data_source ".
                                                    "data=$csvFile control=$controlFile log=$logFile bad=$logBadFile silent=ALL");
                    }
    
                    # Si le fichier de rejet .bad existe => le sqlldr n'a pas fonctionné
                    if( -e "$logBadFile" ) {
                        $this->{logger}->createTrace($ERR_SQL_LDR, $logFile);
                        $returnVal = 0;
                    }
    
                    return $returnVal;
                }
    

    But i do not tested it yet.