This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Aug 21, 2013 3:29 PM by jgarry RSS

Import from MySQL via CSV in Oracle, Key MUL

David_Pasternak Newbie
Currently Being Moderated
Hi @ everybody,

i have a MySQL Table, that i want to import into Oracle by a csv data. In the describing of the table in MySQL i saw, that some fields hat the Key - value "MUL". How can i realize that at Oracle site?

For your Info: Oracle is a 11gR2 (11.2.0.3.0) database.

Thanks a lot and best regards,
David
  • 1. Re: Import from MySQL via CSV in Oracle, Key MUL
    Kh$n Journeyer
    Currently Being Moderated
    table structure and column names are same in both DBS?>
    provide table structure
    in SQL Loader control file just add a clause optionally enclosed in double quotes will help you out
    i.e

    LOAD DATA
    INFILE * APPEND INTO TABLE emp
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    (empno, ename,job,deptno)

    Edited by: Asad99 on Mar 28, 2013 3:42 AM
  • 2. Re: Import from MySQL via CSV in Oracle, Key MUL
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    ...In the describing of the table in MySQL i saw, that some fields hat the Key - value "MUL".
    What's a field ? What does MUL means ?
    ...How can i realize that at Oracle site?
    It depends of what means MUL on MySQL side.

    Nicolas.
  • 3. Re: Import from MySQL via CSV in Oracle, Key MUL
    John Stegeman Oracle ACE
    Currently Being Moderated
    If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column of a nonunique index or a unique-valued index that can contain NULL values.
    You realise it in Oracle the same way you realise it in MySQL, by creating a non-unique index or a unique index that can contain null values.
  • 4. Re: Import from MySQL via CSV in Oracle, Key MUL
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    John Stegeman wrote:
    If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column of a nonunique index or a unique-valued index that can contain NULL values.
    John, where is that coming from ? I don't see it from the above posts.

    Nicolas.
  • 5. Re: Import from MySQL via CSV in Oracle, Key MUL
    John Stegeman Oracle ACE
    Currently Being Moderated
    Nicolas,

    Sorry - didn't post my source from the MySQL documentation :)

    http://dev.mysql.com/doc/refman/5.0/en/show-columns.html
  • 6. Re: Import from MySQL via CSV in Oracle, Key MUL
    David_Pasternak Newbie
    Currently Being Moderated
    But here is my problem, i'm not the creator of the MySQL side, so i can't tell, if there are non-unique or unique indexes are defined at that fields.

    And yes, i'm trying to create the table at Oracle side in the same way it is created at MySQL side.

    Here the table script:
    CREATE TABLE AccountingPrimary
    (
      JobID varchar(16) NULL,
      Status char(2) NULL,
      Year varchar(4) NULL,
      Month char(2) NULL,
      Day char(2) NULL,
      Sender varchar(16) NULL,
      ObjectClass varchar(16) NULL,
      Receiver varchar(16) NULL,
      JobName varchar(16) NULL,
      ProcessingTimeComplete varchar(16) NULL,
      ExecutionTimeOfAll varchar(16) NULL,
      NoOfAllExecutedFunctionCalls varchar(16) NULL,
      NoOfAllIncorrectFunctionCalls varchar(16) NULL,
      ObjectID varchar(16) NULL,
      NoOfComponents varchar(16) NULL,
      AccumulatedCompSize varchar(16) NULL,
      SizeOfEachComp varchar(128) NULL,
      YearMonth varchar(6) NULL,
      RecNR int NOT NULL PRIMARY KEY
    )
    TABLESPACE DUPONT_STAT;
  • 7. Re: Import from MySQL via CSV in Oracle, Key MUL
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    John Stegeman wrote:
    Nicolas,

    Sorry - didn't post my source from the MySQL documentation :)
    I was wondering if I missed something and/or David edited his post.

    Nicolas.
  • 8. Re: Import from MySQL via CSV in Oracle, Key MUL
    David_Pasternak Newbie
    Currently Being Moderated
    I've looked now for the indexes in the source table at MySQL side. Why it is defined so, i can't tell. My step is just to migrate the data from the "old" MySQL Server to the "new" ORacle 11gR2 Server without to ask about the data logig at MyySQL side... no comment to that.

    Now, here is the output from MySQL side about the indexes from the table:
    +-------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | Table             | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    +-------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    | AccountingPrimary |          0 | PRIMARY     |            1 | RecNR       | A         |     2557126 |     NULL | NULL   |      | BTREE      |         |
    | AccountingPrimary |          1 | Date        |            1 | Year        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | Date        |            2 | Month       | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | Date        |            3 | Day         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | Sender      |            1 | Sender      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | Receiver    |            1 | Receiver    | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | ObjectClass |            1 | ObjectClass | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | Status      |            1 | Status      | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | JobName     |            1 | JobName     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    | AccountingPrimary |          1 | YearMonth   |            1 | YearMonth   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
    +-------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    And herre is the describing of the table at MySQL side:
    +-------------------------------+------------------+------+-----+---------+----------------+
    | Field                         | Type             | Null | Key | Default | Extra          |
    +-------------------------------+------------------+------+-----+---------+----------------+
    | JobID                         | varchar(16)      | YES  |     | NULL    |                |
    | Status                        | char(2)          | YES  | MUL | NULL    |                |
    | Year                          | varchar(4)       | YES  | MUL | NULL    |                |
    | Month                         | char(2)          | YES  |     | NULL    |                |
    | Day                           | char(2)          | YES  |     | NULL    |                |
    | Sender                        | varchar(16)      | YES  | MUL | NULL    |                |
    | ObjectClass                   | varchar(16)      | YES  | MUL | NULL    |                |
    | Receiver                      | varchar(16)      | YES  | MUL | NULL    |                |
    | JobName                       | varchar(16)      | YES  | MUL | NULL    |                |
    | ProcessingTimeComplete        | varchar(16)      | YES  |     | NULL    |                |
    | ExecutionTimeOfAll            | varchar(16)      | YES  |     | NULL    |                |
    | NoOfAllExecutedFunctionCalls  | varchar(16)      | YES  |     | NULL    |                |
    | NoOfAllIncorrectFunctionCalls | varchar(16)      | YES  |     | NULL    |                |
    | ObjectID                      | varchar(16)      | YES  |     | NULL    |                |
    | NoOfComponents                | varchar(16)      | YES  |     | NULL    |                |
    | AccumulatedCompSize           | varchar(16)      | YES  |     | NULL    |                |
    | SizeOfEachComp                | varchar(128)     | YES  |     | NULL    |                |
    | YearMonth                     | varchar(6)       | YES  | MUL | NULL    |                |
    | RecNR                         | int(10) unsigned |      | PRI | NULL    | auto_increment |
    +-------------------------------+------------------+------+-----+---------+----------------+
    The primary key on field RecNr should be no problem. But can you give me some hints how to create the indexes at Oracle side like on MySQL side?
  • 9. Re: Import from MySQL via CSV in Oracle, Key MUL
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Looks like there's a unique index on RecNR.
    Looks like there a non-unique index on Year,Month,Day (3 columns, one index).
    Looks like there's a non-unique index on Sender, Receiver,ObjectClaa,Status,JobName,YearMonth (1 column, 1 index).
    Are you sure an index on MySQL should have the same structure on Oracle ? It's up to you after testing.
    But can you give me some hints how to create the indexes at Oracle side like on MySQL side?
    Why not looking in the doc for the syntax ?

    Nicolas.
  • 10. Re: Import from MySQL via CSV in Oracle, Key MUL
    David_Pasternak Newbie
    Currently Being Moderated
    I was just thinking about the logical side, which field has which index. But that should it be.

    The non-unique index will be created normally with
    CREATE INDEX test on Tablename;
    right?
  • 11. Re: Import from MySQL via CSV in Oracle, Key MUL
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    David_Pasternak wrote:
    I was just thinking about the logical side, which field has which index. But that should it be.

    The non-unique index will be created normally with
    CREATE INDEX test on Tablename;
    Within the column name you want to create the index on. IS it really complicated to have a look in the doc for the syntax?
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5012.htm#SQLRF01209
    Ok, just for you and because it's Easter weekend, I would do the following (as far as I understand the MySQL table's description):
    create unique index u_idx on mysql_tbl(RecNR);
    create index idx1 on mysql_tbl(Year,Month,Day);
    create index idx2 on mysql_tbl (Sender);
    create index idx3 on mysql_tbl (Receiver);
    create index idx4 on mysql_tbl (ObjectClass);
    create index idx5 on mysql_tbl (Status);
    create index idx6 on mysql_tbl (JobName);
    create index idx7 on mysql_tbl (YearMonth);
    Nicolas.

    Edited by: N Gasparotto on Mar 28, 2013 4:15 PM
  • 12. Re: Import from MySQL via CSV in Oracle, Key MUL
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    I've looked now for the indexes in the source table at MySQL side. Why it is defined so, i can't tell. My step is just to migrate the data from the "old" MySQL Server to the "new" ORacle 11gR2 Server without to ask about the data logig at MyySQL side... no comment to that.
    MySQL has export dump utility - mysqldump - that you can use to get CREATE DDLs and INSERT DMLs.
    I believe you can run these scripts on Oracle with some minor modifications, like changing ` quotes to " quotes, replacing some data types, and like that.

    Edited by: Mark Malakanov (user11181920) on Mar 28, 2013 12:09 PM

    Also, SQL Developer (Oracle free tool) has Migration Wizard. You can add platform (MySQL JDBC driver), connect to your MySQL DB and perform your migration.

    Edited by: Mark Malakanov (user11181920) on Mar 28, 2013 12:15 PM
  • 13. Re: Import from MySQL via CSV in Oracle, Key MUL
    David_Pasternak Newbie
    Currently Being Moderated
    Thanks for that tipp. Before i've tried to migrate by csv i've tried it by sql developer. But here i've the problem, that SQL Developer can't create the migration repository at the Oracle instance.

    I've tried to go through like here:

    [SQL Developer: Online Migration|http://www.youtube.com/watch?v=W1QaEHpNw6Y]

    Edited by: David_Pasternak on 02.04.2013 04:07
  • 14. Re: Import from MySQL via CSV in Oracle, Key MUL
    995974 Newbie
    Currently Being Moderated
    sorry frined this method not working.

    Edited by: 992971 on Apr 2, 2013 4:33 AM
1 2 Previous Next

Legend

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