OracleDataConvertion简单方法

文章作者 100test 发表时间 2007:11:28 13:31:19
来源 100Test.Com百考试题网

Oracle EBS各模块之间及与处部系统之间的数据转换和传递都是经过Interface表来完成的，上线前需要做Data Convertion工作，比起常规的API、DataLoad等方式，用“PL/SQL Developer”的文本导入器功能来实现更简单。

　　“PL/SQL Developer”的提供多种导入功能，其中“工具——>文本导入器”，可以导入CVS等文本数据，提供字段对应功能，可以生成导入脚本或直接导入，还可以保存Import Definition为一个文件来反复修改，有多种参数设置，十分方便。

　　EBS有哪些接口表？

0select * 　　from dba_objects db 　　where db.object_type = TABLE 　　and db.object_name like %INTERFACE% 　　0select * 　　from dba_objects db 　　where db.object_type = TABLE 　　and db.object_name like %INTERFACE% and owner like PO 　　0select * 　　from dba_objects db 　　where db.object_type = TABLE 　　and db.object_name like %INTERFACE%

　　建议去研究一下系统的标准并行程序，会了解在系统内部数据是如何抛转的，这里面用了很多接口表。

　　Oracle EBS 数据导入方法比较

　　API， INTERFACE，MANUAL TYPING， DATALOAD

　　dataload是免费可以使用的~，大家不要寄希望于这种工具，它只是让用户把键盘输入动作和鼠标坐标编成一张类似于Excel的指令表，然后没有任何判断地傻傻地一边读一边执行，作为使用者，你必须蹲在边上看着它完成，如果出错，也不会停下来，必须要你手动干预，极易出错！！！可就想不通，ORACLE的顾问怎么就这么喜欢向用户推荐这种工具，如果我有时间的话，甚至用VB就可以编写一个类似的工具！！！

　　这只能说明一点，ORACLE那边太刁蛮，根本看不起用户的工作，根本就不考虑用户在实施工作中对历史数据的迁移！！！再次鄙视！

　　Dataload is not an Oracle tool. Well， that sells， which means if you can come up with a better tool， you may sell it and big money will be waiting ；-D

　　Oracle provides some tools to help data migration.

　　API

　　1. Rocjing mentioned ——提及的 API， and said it is the fastest. That is true. API is not a tool， but a term， i.e. an abbreviation ——缩写 for Application Procedure（？） Interface. An API is really a （hook） function/procedure provided in Oracle code to create data in Oracle. For example， if you want to create a "user" in Oracle， you will call FND_USER_PVT.create_user（……） （a lot of parameters）。 This procedure is an API. Using API is really for you to code a sql script， and call the APIs in your script. In another word， this is coding and programming.

　　INTERFACE

　　2. Another popular method is through interface tables. Almost all Oracle products provide interface tables for high volumn transaction data. For example， AP has ap_invoice_interface， GL has GL_journal_interface， etc. （Invoices are the basic transactions in AP， journals are the basic transactions for GL， etc.） AP may also have an interface table for suppliers， which are also very common for AP. How do you populate these interface tables？ Mostly through uploading spreadsheets， i.e. xsl files （MicroSoft Excel）。 The key here is a spreadsheet ——电子表格软件 and its target interface table have the same column structure （including column names， column type， column count， etc.）。

　　Oracle also provides tools like ADI to help you to upload data from spreadsheets to interface tables.

　　Usually after you load your data into an interface table， you will submit a concurrent program to validate the data and transfer that into the real transaction table.

　　Hence ——因此， this is really a spreadsheet-ADI-interface table-import process combination ——结合 联合 method.

　　MANUAL TYPING

　　3. It is pretty hard to deal with every high volumn data issue in an ideal ——理想 way. So manual typing is sometimes the only way. Even in this case， users should find ways to use， say， batches， default values， existing auto triggers （like distribution sets in AP）， etc. to reduce the amount of manual typing.

　　DATALOAD

　　4. Dataload is also kind of tools that help， but of course， it is not ideal in every case. Dataload is very similar to Oracles Regression Test tool， which was very popular even inside Oracle years ago. Nowadays it is not that attractive any more. People just have to live that.

　　Hope this is of a little help for you guys.

　　What you said was like Interface Tables， which were in Oracle for years. If you have not used that， you should find a chance to try. I would say all interface tables were created to support spreadsheets （XSL files）， and I believe that is still the best solution.