3 Replies Latest reply on Feb 25, 2011 10:51 AM by Kgronau-Oracle

    SQL Loader question.


      I'm having a problem that is making me going crazy, and need some help about it.

      I'm importing an informix database 9 to oracle 11, and informix uses | as separator.

      So far so good ... but in some cases I have certains tables which columns have the | character (sometimes multiple times within that field).
      I realise that Informix puts the backlash as a Escape Character but I am not being able to use it with SQL Loader.

      Example of value: (with just 2 columns)

      Where "xxx\|zzz\|aaa\|bbb)" is the value of the last field within the exported file. In informix database, that value is "xxx|zzz|aaa|bbb)" wich means that informix is creting the escape character correctly.
      The problem is that I am not being able to use the Escape Character to identify that \| is just an |...

      Can anyone help me on this?

        • 1. Re: SQL Loader question.
          I did a quick test:
          create table forum (col1 int, col2 varchar(20))
          insert into forum values (1,'abc|def')

          When I now use dbaccess to unload the data using the generated unload script:
          UNLOAD TO 'FORUM.dat' DELIMITER '▒' SELECT * FROM nolog:forum ;

          I get this dump:

          Using Unix od (or any other hext tool) I'm able to determine the delimeter is the hex value "ce"

          => so after correcting the SQL+Loader control file:

          load data
          infile 'FORUM.dat'
          into table FORUM
          fields terminated by X'ce' OPTIONALLY ENCLOSED BY "'"
          trailing nullcols
          COL2 CHAR(20))

          => I'm able to successfully load the data into the Oracle db:
          SQL> select * from FORUM;

          COL1 COL2
          ---------- ----------------------------------------
          1 abc|def

          => Which utility (and version) are you using, what's your Informix unload statement and what's the control file content of the SQL*Loader ctl file?
          • 2. Re: SQL Loader question.
            Hi kgronau,

            First of all, thanks for your quick answer.

            The Statemente I use to export the Informix data is simply "dbexport <database> -ss"

            What you are suggesting is making an unload instead of dbexport, right? Using a new delimeter instead Informix default, right?

            I didn't understood exactly X'ce' usage ... can you explain it better, please?

            • 3. Re: SQL Loader question.
              for a migration of certain Informix databases to Oracle there is a utility for free which can assist you during the migration:

              This utility is also creating offline scripts that allow you to unload data from Informix into flat files and to load them into Oracle using SQL*Loader.

              It uses dbaccess & unload to "export" data from Informix into flat files - see my previous post (UNLOAD TO 'FORUM.dat' DELIMITER '▒' SELECT * FROM nolog:forum ; ).

              As field terminator it uses a special character '▒' which makes it easier to import the dat file again.

              For example for the table & its content it creates this flat file.
              more FORUM.dat

              The fields are separated by '▒'.

              There are often conversation issues between the various operating systems and their character set settings. So it would be better to specify the hex value of the field terminator rather then specifying its string '▒'.

              On Unix you can use for example "od" to figure out the hex value of the delimeter - on Windows you can use any ascii/hex editor.

              od -x FORUM.dat
              0000000 ce31 6261 7c63 6564 ce66 000a
              => so in my example the '▒' charcater is represented by the hex value 'ce'.

              Finally I now have to tell the SQL*Loader to use X'ce' as field terminator:
              fields terminated by X'ce'