4

We are migrating a MySQL database to an Oracle 12c database using SQL Developer 4.0.3.16.

After creating a repository we had an error (unable to create repository because it still exists, delete it first.). There was no repository so we just tried again and it worked, repository was created.

Now we are connected to our source database (MySQL), our target database (Oracle) (see picture) and we have another connection with our migrating user (migrepo) to our target database.

enter image description here

Now we are having the following error over and over again..:

enter image description here

(English: ORA-01400: Can't insert NULL into ("MIGREPO"."MD_PROJECTS"."ID"))

Can anyone help us?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user2323240
  • 1,607
  • 2
  • 13
  • 15
  • are you moving data or schema structure and data? – kevinskio Mar 31 '15 at 12:52
  • schema structure and data – user2323240 Mar 31 '15 at 12:53
  • If that column is a primary key, or even if it just has a `NOT NULL` constraint on it, you won't be able to insert a `NULL` into it. Take a look at the table's constraints, you might have to drop one or more to accomplish what you're trying to accomplish. – David Faber Mar 31 '15 at 13:08
  • Another possibility is that you are trying to insert not an explicit `NULL` value, but an empty string. Oracle considers the empty string `''` to be the same as `NULL`. MySQL, on the other hand, does support empty strings as distinct from `NULL`s. http://stackoverflow.com/questions/1267999/mysql-better-to-insert-null-or-empty-string – David Faber Mar 31 '15 at 13:10
  • I am not trying to insert anything but just trying to migrate a database.. if i drop the constraint on primary key 'id' it gives only more errors. – user2323240 Mar 31 '15 at 13:27
  • `I am not trying to insert anything but just trying to migrate a database` - if you're migrating the data you're inserting. Given that `id` is a primary key, you're either inserting a `NULL` or an empty string (which Oracle views as a `NULL`). – David Faber Mar 31 '15 at 15:05
  • This poor old question might finally have an answer: [migration from mysql to oracle hits ora-01400](https://stackoverflow.com/questions/67638360/migration-from-mysql-to-oracle-hits-ora-01400) – jlemley Dec 03 '21 at 21:22

3 Answers3

0

Your table MIGREPO.MD_PROJECTS has a column named ID on it which is either a primary key or has a NOT NULL constraint on it (or perhaps both). Something in the code being run is trying to put NULL into this ID column, which the constraint(s) will not allow.

Best of luck.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
0

Solved, I was trying to migrate it as sysdba. Created new user and migrated without errors.

user2323240
  • 1,607
  • 2
  • 13
  • 15
-1

Better try changing your Oracle column as NOT NULL, and while you are converting, select the option as APPEND for what if table exist. It will solve the problem.

Drenmi
  • 8,492
  • 4
  • 42
  • 51
Jothi Kiran
  • 1