1 2 3 Previous Next 31 Replies Latest reply: Jun 15, 2010 12:53 PM by SomeoneElse Go to original post RSS
      • 15. Re: sql loader script
        692864
        hi thanks so much.....

        different control files was just my thought....i hope i can start writingn something on it


        thanks again
        • 16. Re: sql loader script
          OrionNet
          Hello,

          So you will be loading into different tables based on the control file/data file or you will be loading into one table.

          Regards
          • 17. Re: sql loader script
            692864
            hi orion....

            i will have to load all the files in to a single table....

            so i need to dynamically alter the in_file parameter in the control file for every file_name

            and call sql loader cmd
            • 18. Re: sql loader script
              328100
              If the only change you want to make to the controlfile is the INFILE parameter, it will be a lot easier to just use the command line syntax "file=" to override the INFILE parameter inside the controlfile. If the files were different formats and you had to figure out how to parse the file based on the filename, using several controlfiles would work better.
              • 19. Re: sql loader script
                692864
                yes the only change i want to do is ..the in_file parameter
                • 20. Re: sql loader script
                  328100
                  Oops, it's actually "data=", not "file=".

                  DATA (datafile)

                  Default: The name of the control file, with an extension of .dat.

                  DATA specifies the name of the datafile containing the data to be loaded. If you do not specify a file extension or file type, the default is .dat.

                  If you specify a datafile on the command line and also specify datafiles in the control file with INFILE, the data specified on the command line is processed first. The first datafile specified in the control file is ignored. All other datafiles specified in the control file are processed.
                  • 21. Re: sql loader script
                    Etbin
                    Must it be SQL*Loader? Why not considering External Tables too?
                    Then you could just:
                    ALTER TABLE EXTERNAL_TABLE_NAME LOCATION ('file_1.txt,file_2.txt, ... ,file_n.txt);
                    INSERT INTO YOUR_TABLE SELECT * FROM EXTERNAL_TABLE_NAME;
                    The only problem is to construct the list of file names. (AskTom has a java example which loads a gtt with file names to be used in further processing)

                    Regards

                    Etbin
                    • 22. Re: sql loader script
                      692864
                      our environment doesnt want to use external tables to place the application specific files on the oracle server

                      hence we need to go back to sql loader else that was my earlier plan
                      • 23. Re: sql loader script
                        OrionNet
                        Hello,

                        A unix script will do for you, I have got a unix scipt I can share with you, but you need to modify it to edit sqlldr control template file to replace INFILE variable with the
                        multiple INFILE variables based on your datafile. So I take you just need one control file but you got several data file.

                        Regards
                        • 24. Re: sql loader script
                          692864
                          hi orion that would be great......
                          yes i have multiple data files..... and i can load them using single control file.

                          i shall look in to ur script and will change accordingly thannks a lot in advance
                          waiting for the script

                          regards
                          raj
                          • 25. Re: sql loader script
                            OrionNet
                            Raj,

                            Here you can extend this simple script to fit your needs, this simple function iterate through control directory, so create control, data, log subdirectory under NEW_HOME.
                            Let me know if you get stuck anywhere, I will more than happy to help you out.
                            #!/usr/bin/bash
                            prog_name="`basename $0`"
                            typeset username=$1
                            typeset pw=$2
                            typeset sid=$3
                            
                            NEW_HOME=/myhome
                            LOG_DIR=${NEW_HOME}/log
                            orasid=$sid
                            
                            load=myload
                            
                            function dataloader 
                            {
                              unset filename   
                              unset loadfile
                              unset controlFile
                              unset dataFile
                              unset logFile
                              
                              # File extension for loading files
                              control=ctl
                              data=dat
                              log=log
                            
                              for file in ${NEW_HOME}/control/*.ctl
                              do 
                               
                                 unset tableName
                                 unset dfile
                                 filename=$file
                                 loadfile=`basename $file $control`$data
                                 dfile=$loadfile
                                 logFile=`basename $file $control`$log
                                 tableName=`basename $file .$control`
                                 tableName=`echo $tableName | tr '[a-z]' '[A-Z]'`
                            
                                 if [ ! -f ${NEW_HOME}/data/"$loadfile" ]
                                 then
                                   echo "     $loadfile does not exists in data directory"      
                                   fexistFlag=n
                                   exit 1;
                                 else
                                   fexistFlag=y
                                   controlFile=$filename
                                   dataFile=${NEW_HOME}/data/$loadfile
                                   logFile=${NEW_HOME}/logs/$logFile
                                 fi
                            
                                 if [ $fexistFlag != 'n' ] 
                                 then
                                    # Pass username, password and sid , you can change loading option as needed
                            
                                           # Here you can use sed to edit sqlldr control and add infile=... for multiple data files.
                                 echo `sqlldr $username/$pw@$orasid direct=true control=$controlFile data=$dataFile log=$logFile` 
                                 fail=$?
                                 if [ $fail -ne 0 ] 
                                 then
                                      echo  "FAILED."                                                 
                                      echo "     Load failed for $loadfile--See logs for more information"                     
                                      exit 1;
                                    else
                                      echo "SUCCESSFUL."
                                 fi     
                                 echo "#                                                            #" 
                                 fi
                                 
                              done
                            
                            }
                            dataloader
                            Hope this gives you a head start.
                            Regards

                            Edited by: OrionNet on Apr 29, 2009 9:37 PM
                            • 26. Re: sql loader script
                              sunil_dba
                              I know it is a old thread.

                              Just want to know, how to achieve the same in the WINDOWS environment.

                              ie., to dynamically pass the file name to the infile value in the sql*loader contolfile.
                              What scripting is required for the same ?

                              Does anybody has any script for this ?

                              Regards
                              Sunil
                              • 27. Re: sql loader script
                                SomeoneElse
                                ie., to dynamically pass the file name to the infile value in the sql*loader contolfile.
                                There is no need to do that.

                                You can specify the infile on the command line.
                                • 28. Re: sql loader script
                                  sunil_dba
                                  someoneelse,

                                  sorry i didn't get you.


                                  Every day i am getting multiple csv files into a folder, and my sql*loader should read the files and insert into db.

                                  How can i achieve this ? This is on WINDOWS.

                                  thx
                                  sunil
                                  • 29. Re: sql loader script
                                    SomeoneElse
                                    Every day i am getting multiple csv files into a folder
                                    Do all the files have the same layout?

                                    If so, you only need 1 .ctl file but leave out the input file specifier, you'll name it on the command line.

                                    Then write a simple DOS script to loop through all your .csv files and pass the file name on the command line.