5 Replies Latest reply: Dec 13, 2012 8:26 AM by AlbertoFaenza RSS

    ORACLE SQL-Loader

    928466
      Hi...
      i am having Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.
      -----
      Question is:
      we have a table 'T' in ABC database.
      we need to load data from txt file to tabel T.
      here the requirement is we need to skip few columns in this process.
      let me go in clear....

      data:

      A,1234,ap,xyz
      B,2345,TN,WER
      C,4567,KA,We
      D,5678,MH,show

      now i want to laod data into T like

      col1, col2, col3
      A, 1234, ap
      B,2345, TN
      .........
      .........


      i need to skip the data after last comma.(at the same time we should not fill with nulls)
      How come we achieve?


      Thanks,
      Pram
        • 1. Re: ORACLE SQL-Loader
          Mahir M. Quluzade
          Load full table and update to NULL table column witch is you are need ignore.
          • 2. Re: ORACLE SQL-Loader
            Hoek
            Have you considered using an external table instead of SQL*Loader?
            Using external tables gives you the power to treat a file as if it were a table, you can query the file, using the power of (PL/)SQL to insert the data as desired.

            Examples/explanations:
            http://www.oracle-base.com/articles/9i/external-tables-9i.php
            http://www.oracle-developer.net/display.php?id=204

            There's also a dedicated forum for SQL*Loader and External Tables, see: Export/Import/SQL Loader & External Tables
            • 3. Re: ORACLE SQL-Loader
              928466
              No.
              -----
              hope this is not the exact thing am looking for.
              Can someone will go how we acheive this?
              ------
              Thanks Mahir.
              • 4. Re: ORACLE SQL-Loader
                971895
                Try with Filler option in sql loader
                LOAD DATA
                INFILE *
                INSERT INTO TABLE TEST
                Fields terminated by ";" Optionally enclosed by '"'
                (
                  ENAME,
                  EMPNO FILLER,
                  SAL 
                )
                BEGINDATA
                "JONES";7566;3405
                "BLAKE";7698;2980
                "CLARK";7782;8000
                "SCOTT";7788;3430
                "KING";7839;6430
                "JONES";7566;3405
                "JONES";7566;3405
                "BLAKE";7698;2980
                "CLARK";7782;8000
                "SCOTT";7788;3430
                "KING";7839;6430 
                {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: ORACLE SQL-Loader
                  AlbertoFaenza
                  Hi,

                  I've answered a similar questions here: {message:id=10707955}.

                  Just specify the first 3 columns in your control file. Rest of columns in your data file are ignored.

                  Regards.
                  Al