This discussion is archived
7 Replies Latest reply: Jun 14, 2012 10:30 AM by 84959 RSS

Migration slq server to oracle :  empty string '' convert to null

Moonlight Newbie
Currently Being Moderated
Hello,
I'm stuck on the migration from sql server 2000 to Oralce. I am using oracle sql developer and I want an empty string '' in sql server is migrated as a null in oracle .

Any idea please I'm stuck...and I may lose the project migration :(

please help
Thank u in advance
  • 1. Re: Migration slq server to oracle :  empty string '' convert to null
    84959 Explorer
    Currently Being Moderated
    Have you setting the preference under SQLDeveloper -->Tools--> Preferences-->Migration-->Data Move Options-->Oracle Representation for zero length string?. That should help.

    Regards

    Prakash
  • 2. Re: Migration slq server to oracle :  empty string '' convert to null
    Moonlight Newbie
    Currently Being Moderated
    I'm using oracle sql developer 1.2.1 i don't find the oracle Representation for zero length string!!

    u know where i can find it with this version of oracle sql developer ?

    Thank's for your replay and help..
  • 3. Re: Migration slq server to oracle :  empty string '' convert to null
    Moonlight Newbie
    Currently Being Moderated
    I can identifie my problem:
    this my control file for LISTS table:

    load data
    infile '[migration].[td].[LISTS].dat' "str '<EORD>'"
    into table homo_mig.LISTS
    fields terminated by '<EOFD>'
    trailing nullcols
    (
    ITEM_ID ,
    ITEM_DESCRIPTION CHAR(255) "DECODE(:ITEM_DESCRIPTION, CHR(00), ' ', :ITEM_DESCRIPTION)",
    ITEM_SYSTEM "DECODE(:ITEM_SYSTEM, CHR(00), ' ', :ITEM_SYSTEM)",
    ITEM_MEMO CHAR(2000000)
    )

    the type of the colone ITEM_MEMO in oracle is CLOB, and in Ms SQL is Text.
    My problem, this colone contains empty string, but i want to take null value.

    I tried to modifie the controle file, generated by oracle sql developer, i add this :

    ITEM_MEMO CHAR(2000000) "DECODE(:ITEM_MEMO, CHR(00), null, :ITEM_MEMO)",

    OR

    ITEM_MEMO CHAR(2000000) "REPLACE(:ITEM_MEMO, CHR(00), null)",

    But i got a error msg :( in log file :
    SQL*Loader-309: Aucune chaîne SQL autorisée en tant que partie de la spécification de champ ITEM_MEMO

    How can'i do to rectifie this problem!!!

    It's so urgent
  • 4. Re: Migration slq server to oracle :  empty string '' convert to null
    84959 Explorer
    Currently Being Moderated
    I tried a simple test case as follows:

    1. Control file =

    load data
    infile 't.dat'
    into table test
    fields terminated by '|'
    trailing nullcols
    (
    ITEM_ID ,
    ITEM_DESCRIPTION CHAR(255) "DECODE(:ITEM_DESCRIPTION, CHR(00), ' ', :ITEM_DESCRIPTION)",
    ITEM_SYSTEM "DECODE(:ITEM_SYSTEM, CHR(00), ' ', :ITEM_SYSTEM)",
    ITEM_MEMO CHAR(2000000)
    )

    2. Data file (t.dat) =

    1000| | | |

    Notice the gaps between '|' symbols. They are all one empty space. I suppose your data file looks like that i.e. instead of NULLs you have one empty space in it for those fields.

    3. Table 'test' was created as follows:

    SQL> desc test;
    Name Null? Type
    ----------------------------------------- -------- --------------------------

    ITEM_ID NUMBER(10)
    ITEM_DESCRIPTION VARCHAR2(255)
    ITEM_SYSTEM VARCHAR2(100)
    ITEM_MEMO CLOB


    Seems to work for me. If you can give me one record from your datafile then I can test it (just cut and paste).

    Regards

    Prakash

    PS: Why don't you use the latest SQL Developer 3.1.0.7 (http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-3.1.07.42.zip) . Why are you using 1.2.1?.
  • 5. Re: Migration slq server to oracle :  empty string '' convert to null
    84959 Explorer
    Currently Being Moderated
    Some more information on loading data into CLOBs in Oracle : SQL Loader: Load empty clob field as null Seems like we will always load an empty_clob() instead of just NULL.

    Regards

    Prakash
  • 6. Re: Migration slq server to oracle :  empty string '' convert to null
    Moonlight Newbie
    Currently Being Moderated
    Hi,

    Thank you for your answers, but does not work anymore, there's always an empty string in my oracle database.

    I tested NULLIF clause, DEFAULTIF clause, but no luck :(, i think with clob type it's have a problem, i don't know what's it !!!

    I have worked with both versions oracle sql developer 1.2 and 3.0, i find 1.2 version of migration more better than 3.0 for the following reasons:
    - Both capture and conversion phases are separated, in case of error you can know at what stage does not go
    - Easy renaming of schema (I so need it)
    - In *.bat file, parameters are entered only once, rewrite these parameters to each implementation including test cases, risk of error and it is too painful
    - Conversion of ddl is more effective, in fact, with 3.0 i have got additional indexes that I do not need,buit with 1.2 migration of ddl it's correct 100%..
    - a separate tab for Migration, you can easily identify repository, capture and all the necessary operation for migration (in my case, I'm working on migrating of more than 80 database MS SQL)

    Here is my opinion and my experience I share with you ;)

    Thank you again, and I hope I continue migration projects, but it is very difficult if I not solve problems!!

    Have a nice day
  • 7. Re: Migration slq server to oracle :  empty string '' convert to null
    84959 Explorer
    Currently Being Moderated
    Hello

    For CLOB datatype, we initialize (empty_clob()) it hence it will not be NULL. If the data in the Text column is always less than 4000 bytes then you can use VARCHAR2 instead.

    With respect to your feedback on the comparison between 1.2 and 3.0, I would like seek some clarification on your observations.
    1. Both capture and conversion phases are separated, in case of error you can know at what stage does not go.
    Answer: In 3.X, you also have this facility. In the migration wizard UI after definining the capture settings (off-line,on-line) and selecting the database, click on the 'Proceed to Summary' check box at the bottom of the screen. This will let you do only capture (not convert, data migration etc.). You can do the same thing after convert, generate and data move options as well. The 'Proceed to Summary' option will allow you to skip subsequent stages in the migration process.

    2. Easy renaming of schema (I so need it)
    Answer: In 2.x, you could directly rename the converted schema in migration project explorer. In 3.x, it is provided as an option in the object naming section of the 'data type mapping' UI where you typically edit the default data type mappings. However it will not show up in the Migration Wizard UI. Once you finish 'capture' and the project appears in the explorer window on the bottom left side, you click on it and it will show you options for convert etc. Click on 'Convert' and then it will have that option along with data type mappings.

    I simply update the MD_SCHEMAS table in the repository(which is ofcourse not recommended for all users ;) ).

    3. In *.bat file, parameters are entered only once, rewrite these parameters to each implementation including test cases, risk of error and it is too painful

    Answer: I assume this is related to the data loading scripts. Is that correct?. Can you give an example to understand the issue you are running into?.

    4. Conversion of ddl is more effective, in fact, with 3.0 i have got additional indexes that I do not need,buit with 1.2 migration of ddl it's correct 100%..

    Answer: Can you shed some light on the additional indexes in 3.0?. I think they are associated with Unique/foriegn keys and stuff. Is that so?.

    5. a separate tab for Migration, you can easily identify repository, capture and all the necessary operation for migration (in my case, I'm working on migrating of more than 80 database MS SQL)

    Answer: There is a separete 'migration' option in the tools option but not a complete tab like it used to be before. But in 3.0 with the command line options, you can actually script the complete migration process for all the databases without having to go through each database at a time and clicking through the wizard. So 3.0 is actually more useful in mass migrations.

    Drop me an email and we can discuss more about your requirements and see where we can help.

    Regards

    Prakash

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points