当前位置：文江博客话题详情

Oracle PL/SQL 加载 XML

发布于 2024-10-22 03:29:38 字数 1518 浏览 25 评论 0原文

我不知道问这个问题的最佳方式，但让我解释一下这个问题，它可能会有所帮助。

目前，我们有一个使用 Oracle OAI 中心分发给我们的数据源。数据通过 DBLinks 提供给我们。

该信息提供商将升级到 Oracle ODI，并且出于多种原因要求所有数据传输均通过 SFTP 使用加密的 XML 文件进行。

现在，这给我们作为数据接收者带来了一个新问题，因为我们现在需要修改我们的系统，将 XML 数据加载到曾经由 DBLink 填充的表中。

我们当前的设置是：

Oracle 10g (10.2.0.4)
- Oracle 在 Unix (HP-UX) 上运行
众多 Win2k3 服务器控制接口/ETL 流程。

目前，OAI 中心会将数据放入 ETL 层的多个表中。例如说“人”。我认为该表的结构不相关...

当数据加载到 PERSON 表中时，OAI 交付会将文件结束标记传输到数据库，该标记存储在表“EOF”中 - 它保存“PERSON”中预期收到的记录计数。

Windows 服务器有一个批处理进程，每 30 秒轮询一次，检查是否存在 EOF 记录，如果存在，则启动我们的 ETL 处理。

我想尽可能避免更改系统的这一部分，所以我建议的解决方案是解析新的 XML 文件并将它们加载到 Oracle 数据库中，问题是执行此操作的最佳方法是什么：

XML文件将被传送到 Windows 服务器
首选是使用 PL/SQL 加载数据

将数据加载到 Oracle (Unix) 数据库（源数据位于 Windows 端）的最佳方法是什么。

如果可能的话，我想避免使用任何 Unix 脚本 - 因为我的开发团队没有足够的 Unix 经验来让他们在这方面放松。

任何建议将不胜感激。

XML 格式如下 - 元素名称映射到数据库上的列名称：

<PERSON>
  <HEADER>
    <Creator>~</Creator>
    <DigitalSigniture>~</DigitalSigniture>
    <Owner>~</Owner>
    <Title>~</Title>
    <Marking>~</Marking>
   </HEADER>

   <PERSONS>
     <EMPLOYEE_NUMBER>~</EMPLOYEE_NUMBER>
     <FIRST_NAME>~</FIRST_NAME>
     <LAST_NAME>~</LAST_NAME>
     ......
   </PERSONS>
<PERSON>

编辑：我还意识到数量，在正常（平均）一天，我将处理大约 80,000 条 XML 记录，在特殊的一天，我将处理多达 300,000 条记录（通常每年一次）。

原文

I don't know the best way to ask this, but let me explain the issue and it may help.

We currently have a feed of data that is distributed to us using an Oracle OAI hub. The data is fed to us using DBLinks.

The information provider is going to be upgrading to Oracle ODI, and for numerous reasons are mandating that all data transfer is conducted using encrypted XML files over SFTP.

Now this introduces a new issue for ourselves as data recipient, as we now need to amend our systems to load XML data into the tables that once were populated by DBLinks.

The set-up we currently have is:

Oracle 10g (10.2.0.4)
- Oracle is running on Unix (HP-UX)
Numerous Win2k3 servers controlling interface / ETL flow.

So currently, the OAI hub will place data into a number of tables in our ETL layer. Say for example "PERSON". The structure of this table i dont believe is relevant...

When the data has been loaded into the PERSON table, the OAI delivery will transmit an End-of-file marker to the database, this is stored in table "EOF" - it holds a count of recieved records expected in "PERSON".

The windows server has a batch process that polls every 30 seconds, this checks to see if an EOF record exists, if it does then it kicks off our ETL processing.

I want to avoid changing this part of the system if possible, so what i am proposing as a solution is to parse the new XML files and load them into the Oracle database, the question is what is the best way to do this:

The XML files will be delivered to the Windows Servers
Preference would be to use PL/SQL to load the data

What is the best way to load the data into the Oracle (Unix) database, with the source data being on the Windows side.

I want to avoid having to use any Unix scripting if possible - as my development team don't have enough Unix experience to let them loose on this.

Any suggestions will be grateful.

The XML format is as below - and element names map to column names on DB:

<PERSON>
  <HEADER>
    <Creator>~</Creator>
    <DigitalSigniture>~</DigitalSigniture>
    <Owner>~</Owner>
    <Title>~</Title>
    <Marking>~</Marking>
   </HEADER>

   <PERSONS>
     <EMPLOYEE_NUMBER>~</EMPLOYEE_NUMBER>
     <FIRST_NAME>~</FIRST_NAME>
     <LAST_NAME>~</LAST_NAME>
     ......
   </PERSONS>
<PERSON>

EDIT:
I am also conscious of volumes, on a normal (average) day i will be processing about 80,000 XML records, and on an exceptional day i will processing up to 300,000 (typically once per year).

分享到QQ

分享到微博