java - Bulk import from Informix into Oracle -

We need to pull a table from an Infrastructure SE database, Tablet table on Oracle 10G, and then Informix data.

Does a Bulk Import Work? What is the data type conflict?

I want to use a simple Java executable, which we can schedule daily. Can Java Program Import Bulk? Is there an example you can provide? Thank you.

Interesting scenario!

There are many issues to worry about: <

What is the correct format for the DATE and DATETIME values? P> P> Practically (and based on experience with Informix instead of Oracle), Bulk loading. Instead of reducing the tables before, I will load the data into newly created tables (a relatively time-consuming process), then arrange to replace the old tables with the new

Old table junk table

Rename the old table in the old table

<

• Old table drop
• Rename the new table in the old table

If the operation is done in this manner, then the 'down time' for the table is reduced Less than, 'Load Table' in comparison with 'Thru Table'.

Oracle is like SE - its DDL statement is non-transaction (opposite IDS, where you can make a transaction, a table drops, creates a new one, and After that, the operations of the whole operation back).

How to export the data?

It depends on how flexible the Oracle loader is. If they can adapt to standard output formats of Informix (for example, UNOFF format), then the landing environment is trivial to ensure that the date value has been recognized by Oracle, you may have to set the DBDAT environment variable. Believe that ' DBDATE = "Y4MD -" ' is likely to be accepted; This is the signal for SQL Standard 2009-12-02 2 ^nd December 2009.

The default UNOF format can be summarized as 'pipe-delimited areas, with the backslash embedded newline, the backslash and the symbol of the pipe':

  Abc | 123 | 2009-12-02 | One \ | B \\ c \ d ||  

This is a record string, a number, a date, and another character string ('a', '|', 'b', '\', 'c', new line and 'D') and an empty field. Trailing spaces are removed from character strings; An empty but non-empty character field is empty in the unload file.

If Oracle can not be handled easily, then consider whether there could be a toolset for using Perl + DBi + DBD :: Informics + DBD :: Oracle - it will give you Oracle and Allows to connect to the Infrastructure (SE) database and transfer data between the two.

Alternatively, you need to check alternative unloaders for the SE. A program that might be worth the test unless you are not using Windows ( Unbiased Warning: in the author's bias ) It has a very powerful set of output formatting options and maybe a lesson Can create formats that are acceptable to Oracle (for example CSV).

INSERT statements will be generated for a final fallback. The selected data I think it may be useful as an additional for SQLCMD, but it is not yet. So, you have to use it:

  include in the select target (Col1, Col2) VALUES ('' Col1 || '', '' Col2 '' '' '); From Source ' 

This generates a simple INSERT statement. The problem with this is that it is not strong if the quote is included in Col2 (a letter string) (and new lines can also cause problems at the end). You have to evaluate whether it is acceptable.