11 Replies Latest reply: Jun 16, 2010 4:35 AM by Girish Sharma RSS

    SQL Loader with row terminator

    user645399
      Dear buddies,

      I am using Oracle 10g R2.

      Need to load some records from one text file to Oracle.

      This is my control file:
      LOAD DATA 
      INFILE 'D:\load\dat\FinNote.dat' 
      BADFILE 'D:\load\bad\FinNote.bad' 
      DISCARDFILE 'D:\load\dat\discard\FinNote.dsc' 
      INSERT INTO TABLE FinNote FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING 
      NULLCOLS
      ( IDNo, RegDate date 'YYYY-MM-DD HH24:MI:SS', Col1, Remark, UserID, Status)
      {code}
      
      My text file has semicolon as a row delimiter and | as column delimiter. Now, how can I add the semicolon as row delimiter. Please guide me.
      
      I followed the method given in this site: 
      http://www.exforsys.com/tutorials/oracle-10g/oracle-10g-sql-loader-input-data-and-datafiles.html
      
      
      *INFILE datafile_name ["str terminator_string"]*
      
      {code}
      LOAD DATA 
      INFILE 'D:\load\dat\CRS_FinNote.dat' ";"
      BADFILE 'D:\load\bad\CRS_FinNote.bad' 
      DISCARDFILE 'D:\load\dat\discard\CRS_FinNote.dsc' 
      INSERT INTO TABLE CRS_FinNote FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING 
      
      NULLCOLS
      ( StudentNo, IDate date 'YYYY-MM-DD HH24:MI:SS', Topic, Remark, UserID, Status)
      and received error:

      SQL*Loader-500: Unable to open file (D:\load\dat\CRS_FinNote.dat)
      SQL*Loader-555: unrecognized processing option
      SQL*Loader-509: System error: The operation completed successfully.
      SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
      SQL*Loader-513: Unable to close file (D:\load\dat\CRS_FinNote.dat)
      SQL*Loader-559: error closing file
      SQL*Loader-509: System error: The storage control block address is invalid.

      Thanks in advance.
      Nith

      Edited by: user645399 on Jun 16, 2010 10:35 AM
        • 1. Re: SQL Loader with row terminator
          AlokKumar
          Can you paste your sample data on the borad ?


          hare krishna
          • 2. Re: SQL Loader with row terminator
            baskar.l
            hi,

            Can you try this..

            Fields terminated by ";" Optionally enclosed by ' "'

            thanks,
            baskar.l
            • 3. Re: SQL Loader with row terminator
              Girish Sharma
              But, i did'nt found any error, using your control file. See below : (Using 10.2.0.1 on Windows XP)
              SCOTT@orcl> create table FinNote (
                2  Idno number(4),
                3  RegDate Date,
                4  Col1 Varchar2(10),
                5  Remark Varchar2(10),
                6  Userid Varchar2(10),
                7  Status Varchar2(10));
              
              Table created.
              How my D:\CSV\CRS_FinNote.dat look like:
              1245|2000-05-16|Oracle|The Best|AAA|Online
              294|2000-04-03|Dotnet|Best|BBB|Pending
              300|2010-03-28|C++|The Best|CCC|Offline
              And Hows my D:\CSV\user645399.ctl looks like:
              LOAD DATA 
              INFILE 'd:\csv\CRS_FinNote.dat' 
              BADFILE 'd:\csv\FinNote.bad' 
              DISCARDFILE 'd:\csv\FinNote.dsc' 
              INSERT INTO TABLE FinNote FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING 
              NULLCOLS
              ( IDNo, RegDate date 'YYYY-MM-DD HH24:MI:SS', Col1, Remark, UserID, Status)
              And i run sqlldr as below :
              C:\Documents and Settings\Girish Sharma>sqlldr scott/tiger@orcl control=d:\csv\user645399.ctl log=user645399.log
              
              SQL*Loader: Release 10.2.0.1.0 - Production on Wed Jun 16 08:44:02 2010
              
              Copyright (c) 1982, 2005, Oracle.  All rights reserved.
              
              Commit point reached - logical record count 2
              Commit point reached - logical record count 3
              
              C:\Documents and Settings\Girish Sharma>sqlplus
              
              SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 16 08:44:13 2010
              
              Copyright (c) 1982, 2005, Oracle.  All rights reserved.
              
              Enter user-name: scott/tiger
              
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options
              
              SCOTT@orcl> select * from finnote;
              
                    IDNO REGDATE              COL1       REMARK     USERID     STATUS
              ---------- -------------------- ---------- ---------- ---------- ----------
                    1245 16-may-2000 00:00:00 Oracle     The Best   AAA        Online
                     294 03-apr-2000 00:00:00 Dotnet     Best       BBB        Pending
                     300 28-mar-2010 00:00:00 C++        The Best   CCC        Offline
              
              SCOTT@orcl>
              Since you are getting "SQL*Loader-500: Unable to open file (D:\load\dat\CRS_FinNote.dat)"; so please check the availability of file and permission (if any).

              HTH
              Girish Sharma

              Edited by: Girish Sharma on Jun 16, 2010 8:52 AM
              • 4. Re: SQL Loader with row terminator
                Girish Sharma
                And, if your CRS_FinNote.dat look like below (row/record delimited by ; then)
                1245|2000-05-16|Oracle|The Best|AAA|Online;
                294|2000-04-03|Dotnet|Best|BBB|Pending;
                300|2010-03-28|C++|The Best|CCC|Offline;
                SCOTT@orcl> select utl_raw.cast_to_raw (';') as "hexidecimal for ;" from dual;

                hexidecimal for ;
                --------------------------------------------------------------------------------
                3B


                Then, i will change user645399.ctl with below :
                LOAD DATA 
                INFILE 'd:\csv\CRS_FinNote.dat' "str X'3B'"
                BADFILE 'd:\csv\FinNote.bad' 
                DISCARDFILE 'd:\csv\FinNote.dsc' 
                INSERT INTO TABLE FinNote FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' TRAILING 
                NULLCOLS
                ( IDNo, RegDate date 'YYYY-MM-DD HH24:MI:SS', Col1, Remark, UserID, Status)
                Idea got from http://www.orafaq.com/forum/t/140062/0/ link.
                C:\Documents and Settings\Girish Sharma>sqlldr scott/tiger@orcl control=d:\csv\user645399.ctl log=user645399.log
                
                Enter user-name: scott/tiger
                
                Connected to:
                Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                With the Partitioning, OLAP and Data Mining options
                
                SCOTT@orcl> select * from finnote;
                
                      IDNO REGDATE              COL1       REMARK     USERID     STATUS
                ---------- -------------------- ---------- ---------- ---------- ----------
                      1245 16-may-2000 00:00:00 Oracle     The Best   AAA        Online
                       294 03-apr-2000 00:00:00 Dotnet     Best       BBB        Pending
                       300 28-mar-2010 00:00:00 C++        The Best   CCC        Offline
                
                SCOTT@orcl>
                HTH
                Girish Sharma
                • 5. Re: SQL Loader with row terminator
                  user645399
                  Dear Baskar,

                  Thanks a bunch for your reply but fields are terminated by commas and only rows are terminated by semicolon.

                  Any other suggestion?

                  Nith
                  • 6. Re: SQL Loader with row terminator
                    baskar.l
                    hi,

                    Have u tried what girish said..

                    thanks,
                    baskar.l
                    • 7. Re: SQL Loader with row terminator
                      user645399
                      Thanks a lot Girish Sharma!

                      That worked but still has one small issue. One of my column contains very long text. VARCHAR2(4000) is not enough.

                      I tried replacing it with LONG but getting error.

                      SQL> CREATE TABLE FinNote
                      2 ( IDNo NUMBER(31,0),
                      3 RegDate date,
                      4 Col1 VARCHAR2(120 CHAR),
                      5 Remark LONG(5000 CHAR),
                      6 UserID VARCHAR2(50 CHAR),
                      7 Status VARCHAR2(1 CHAR));
                      Remark LONG(5000 CHAR),
                      *
                      ERROR at line 5:
                      ORA-00907: missing right parenthesis

                      Please guide me.

                      Thanks a lot.
                      Nith

                      Edited by: user645399 on Jun 16, 2010 4:25 PM
                      • 8. Re: SQL Loader with row terminator
                        user645399
                        Baskar,

                        Thanks once again. I just tried and posted further errors. Just noticed your second message as well.

                        Thanks a lot.

                        Nith
                        • 9. Re: SQL Loader with row terminator
                          user645399
                          Dear buddies,

                          Actually I am migrating from Sql Server 2005 to oracle 10g R2.

                          The column remark has only 3000 char (varchar) in sql server but even if I use CLOB its not enough in Oracle to load that column. How can I go about this?

                          Please guide me.

                          Thanks in advance.

                          Nith
                          • 10. Re: SQL Loader with row terminator
                            user645399
                            Hi buddies!

                            Managed to find the solution:

                            Followed the advice given in this link: http://www.orafaq.com/forum/t/61849/2/

                            Thanks a bunch to all of you.

                            Really appreciate all of your support.

                            Cheers till we meet in another thread.

                            Nith
                            • 11. Re: SQL Loader with row terminator
                              Girish Sharma
                              Nith,

                              Now your requirement has been changed. So please check below link the do as per mentioned, i am sure; you can successfully proceed; otherwise mention here what you did and where you stuck. I hope, you will get perfect answer (if needed).

                              http://www.oracle-base.com/articles/10g/LoadLobDataUsingSqlLoader.php

                              HTH
                              Girish Sharma