This discussion is archived
6 Replies Latest reply: Jan 5, 2012 5:06 AM by 908634 RSS

oracle export sql inserts how to commit every N rows

908634 Newbie
Currently Being Moderated
I have a table with 5,000,000 records and I want select several fields out and insert to a MYSQL database table . So I concat a SQL to insert . But there are too many records MYSQL can't import one time (The file is more than 700M). So I should commit every N rows . I want to find a good method to solve this problem .
I use PL/SQL to execute my SQL statement . I also find when I export a table using PL/SQL I can select commit every N rows . I think if I know how PL/SQL implement it I will know how to do .
Any answer is appreciative .
  • 1. Re: oracle export sql inserts how to commit every N rows
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    What are you using for the connection between Oracle and MySQL ? Is the Database Gateway for ODBC (DG4ODBC) ? If not, then what are you using ?
    If you are using PL/SQL for the insert statements then you could add a counter for every insert statement then issue a commit when the counter reaches a certain value.
    If this doesn't make sense then post the PL/SQL you are using and we maybe able to help further.

    Regards,
    Mike
  • 2. Re: oracle export sql inserts how to commit every N rows
    908634 Newbie
    Currently Being Moderated
    Hi,
    There is no connection between Oracle and MySQL . I want to use PL/SQL to connect to Oracle and select & concat out lots of insert statements to a SQL file and then import the SQL file to MySQL by mysql command .
    Yes , I am using PL/SQL and it has a Export Tables tool . But I find it can only export all the table fields and the insert SQL can not be defined .
    Of cause , I can modify the SQL file by Unix command , but I wonder if I can get a better method .
    Thanks.
    Regards.

    WangHao
  • 3. Re: oracle export sql inserts how to commit every N rows
    mkirtley-Oracle Expert
    Currently Being Moderated
    WangHao,
    Thanks for the clarification.
    As this is a problem using PL/SQL itself it would be better to ask the question in the PL/SQL forum here -

    SQL and PL/SQL

    They will have more experience of using PL/SQL itself to do what you want.

    Regards,
    Mike
  • 4. Re: oracle export sql inserts how to commit every N rows
    908634 Newbie
    Currently Being Moderated
    Hi Mike,

    Thanks for your patient reply .
    Do you have a good method to export data with commit every N rows in SQLPLUS ? I think it's better to implement in SQLPLUS .
    Thanks .

    Regards,
    WangHao
  • 5. Re: oracle export sql inserts how to commit every N rows
    mkirtley-Oracle Expert
    Currently Being Moderated
    WangHao,
    If I understand correctly this is what you want to do -

    - create insert statements from data in Oracle tables
    - run these insert statements directly in MySQL using the MySQL equivalent of SQLPLUS
    - you want the insert statements to have a commit after 'x' number of rows

    If this is the case I still think the best option is to raise it in the SQL-PL/SQL forum.
    The issue is creating the insert statements and adding an 'commit' statement after a number of rows.
    I would have thought depending how you write the insert statements it should be possible to have some of counter and add a 'commit' statement after a number of rows.
    Try the SQL-PL/SQL forum for further advice on the best way to do this.

    Regards,
    Mike
  • 6. Re: oracle export sql inserts how to commit every N rows
    908634 Newbie
    Currently Being Moderated
    Hi Mike,

    Thanks for your generous answers . Maybe Oracle or SQL is not good at this work .
    I think it's also a good method to finish the work using Unix command .
    I will also try to find out how the PL/SQL implements it .
    Thanks.

    Best Regards,
    WangHao

Legend

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