首页 \ 问答 \ 从SQL Server 2005执行Oracle存储过程(Executing an Oracle Stored Procedure from SQL Server 2005)

从SQL Server 2005执行Oracle存储过程(Executing an Oracle Stored Procedure from SQL Server 2005)

 我们如何从SQL Server 2005执行Oracle存储过程？ 作为SQL Server脚本的一部分，我们需要执行Oracle 10g存储过程并将数据下载到SQL Server 2005 

how do we execute an Oracle Stored Procedure from SQL Server 2005? As part of a SQL Server scripts we need to execute an Oracle 10g Stored Procedure and download data to SQL Server 2005 

原文：https://stackoverflow.com/questions/1651864
更新时间：2022-10-21 07:10

最满意答案

 当您处理复杂或大型数据文件时，将XML转换为哈希值并不是一个好主意，因为与搜索XML相比，遍历哈希不是很方便。 使用像Nokogiri这样的合适工具解析XML非常简单。 
 

 基于你的XML： 
 

require 'nokogiri'

xml = "
<Events>
    <Event>
        <System>
            <Provider Name='Service Control Manager' Guid='{555908d1-a6d7-4695-8e1e-26931d2012f4}' EventSourceName='Service Control Manager'/>
            <EventID Qualifiers='16384'>7036</EventID>
            <Version>0</Version>
            <Level>4</Level>
            <Task>0</Task>
            <Opcode>0</Opcode>
            <Keywords>0x8080000000000000</Keywords>
            <TimeCreated SystemTime='2013-03-25T05:00:38.021800000Z'/>
            <EventRecordID>17629</EventRecordID>
            <Correlation/>
            <Execution ProcessID='476' ThreadID='3028'/>
            <Channel>System</Channel>
            <Computer>AMAZONA-ONIST5V</Computer>
            <Security/>
        </System>
        <EventData>
            <Data Name='param1'>Windows Modules Installer</Data>
            <Data Name='param2'>stopped</Data>
            <Binary>540072007500730074006500640049006E007300740061006C006C00650072002F0031000000</Binary>
        </EventData>
    </Event>
    <Event>
        <System>
            <Provider Name='Service Control Manager' Guid='{555908d1-a6d7-4695-8e1e-26931d2012f4}' EventSourceName='Service Control Manager'/>
            <EventID Qualifiers='16384'>7040</EventID>
            <Version>0</Version>
            <Level>4</Level>
            <Task>0</Task>
            <Opcode>0</Opcode>
            <Keywords>0x8080000000000000</Keywords>
            <TimeCreated SystemTime='2013-03-25T05:00:37.741000000Z'/>
            <EventRecordID>17628</EventRecordID>
            <Correlation/>
            <Execution ProcessID='476' ThreadID='3028'/>
            <Channel>System</Channel>
            <Computer>AMAZONA-ONIST5V</Computer>
            <Security UserID='S-1-5-18'/>
        </System>
        <EventData>
            <Data Name='param1'>Windows Modules Installer</Data>
            <Data Name='param2'>auto start</Data>
            <Data Name='param3'>demand start</Data>
            <Data Name='param4'>TrustedInstaller</Data>
        </EventData>
    </Event>
</Events>"

 

 这是我如何获取数据的开始： 
 

doc = Nokogiri::XML(xml)
pp doc.search('Event').map { |event|

  system_provider_node = event.at('System Provider')

  system = {
    provider: {
      name: system_provider_node['Name'],
      guid: system_provider_node['Guid'],
      event_source_name: system_provider_node['EventSourceName']
    }
  }

  event_data = event.search('EventData Data').map{ |data|
    {
      name: data['Name'],
      text: data.text
    }
  }

  {
    system: system,
    event_data: event_data
  }    

}

 

 结果如下： 
 

[{:system=>
  {:provider=>
    {:name=>"Service Control Manager",
      :guid=>"{555908d1-a6d7-4695-8e1e-26931d2012f4}",
      :event_source_name=>"Service Control Manager"}},
  :event_data=>
  [{:name=>"param1", :text=>"Windows Modules Installer"},
    {:name=>"param2", :text=>"stopped"}]},
{:system=>
  {:provider=>
    {:name=>"Service Control Manager",
      :guid=>"{555908d1-a6d7-4695-8e1e-26931d2012f4}",
      :event_source_name=>"Service Control Manager"}},
  :event_data=>
  [{:name=>"param1", :text=>"Windows Modules Installer"},
    {:name=>"param2", :text=>"auto start"},
    {:name=>"param3", :text=>"demand start"},
    {:name=>"param4", :text=>"TrustedInstaller"}]}]

 

 您不必构建哈希数组。 对于每个<Event>您可以剥离元素并在数据库的各个表中构建单独的行。 这真的取决于你脑子里的作用。 

Converting XML to hashes is not a good idea when you're dealing with complex or large data files because walking a hash isn't very convenient in comparison to searching the XML. Parsing XML is really simple using the right tools like Nokogiri.
 

Basing off your XML:
 

require 'nokogiri'

xml = "
<Events>
    <Event>
        <System>
            <Provider Name='Service Control Manager' Guid='{555908d1-a6d7-4695-8e1e-26931d2012f4}' EventSourceName='Service Control Manager'/>
            <EventID Qualifiers='16384'>7036</EventID>
            <Version>0</Version>
            <Level>4</Level>
            <Task>0</Task>
            <Opcode>0</Opcode>
            <Keywords>0x8080000000000000</Keywords>
            <TimeCreated SystemTime='2013-03-25T05:00:38.021800000Z'/>
            <EventRecordID>17629</EventRecordID>
            <Correlation/>
            <Execution ProcessID='476' ThreadID='3028'/>
            <Channel>System</Channel>
            <Computer>AMAZONA-ONIST5V</Computer>
            <Security/>
        </System>
        <EventData>
            <Data Name='param1'>Windows Modules Installer</Data>
            <Data Name='param2'>stopped</Data>
            <Binary>540072007500730074006500640049006E007300740061006C006C00650072002F0031000000</Binary>
        </EventData>
    </Event>
    <Event>
        <System>
            <Provider Name='Service Control Manager' Guid='{555908d1-a6d7-4695-8e1e-26931d2012f4}' EventSourceName='Service Control Manager'/>
            <EventID Qualifiers='16384'>7040</EventID>
            <Version>0</Version>
            <Level>4</Level>
            <Task>0</Task>
            <Opcode>0</Opcode>
            <Keywords>0x8080000000000000</Keywords>
            <TimeCreated SystemTime='2013-03-25T05:00:37.741000000Z'/>
            <EventRecordID>17628</EventRecordID>
            <Correlation/>
            <Execution ProcessID='476' ThreadID='3028'/>
            <Channel>System</Channel>
            <Computer>AMAZONA-ONIST5V</Computer>
            <Security UserID='S-1-5-18'/>
        </System>
        <EventData>
            <Data Name='param1'>Windows Modules Installer</Data>
            <Data Name='param2'>auto start</Data>
            <Data Name='param3'>demand start</Data>
            <Data Name='param4'>TrustedInstaller</Data>
        </EventData>
    </Event>
</Events>"

 

Here's the start of how I'd grab the data:
 

doc = Nokogiri::XML(xml)
pp doc.search('Event').map { |event|

  system_provider_node = event.at('System Provider')

  system = {
    provider: {
      name: system_provider_node['Name'],
      guid: system_provider_node['Guid'],
      event_source_name: system_provider_node['EventSourceName']
    }
  }

  event_data = event.search('EventData Data').map{ |data|
    {
      name: data['Name'],
      text: data.text
    }
  }

  {
    system: system,
    event_data: event_data
  }    

}

 

Which results in:
 

[{:system=>
  {:provider=>
    {:name=>"Service Control Manager",
      :guid=>"{555908d1-a6d7-4695-8e1e-26931d2012f4}",
      :event_source_name=>"Service Control Manager"}},
  :event_data=>
  [{:name=>"param1", :text=>"Windows Modules Installer"},
    {:name=>"param2", :text=>"stopped"}]},
{:system=>
  {:provider=>
    {:name=>"Service Control Manager",
      :guid=>"{555908d1-a6d7-4695-8e1e-26931d2012f4}",
      :event_source_name=>"Service Control Manager"}},
  :event_data=>
  [{:name=>"param1", :text=>"Windows Modules Installer"},
    {:name=>"param2", :text=>"auto start"},
    {:name=>"param3", :text=>"demand start"},
    {:name=>"param4", :text=>"TrustedInstaller"}]}]

 

You don't have to build an array of hashes. For each <Event> you could peel off the elements and build separate rows in various tables in a database. It's really up to what works in your head.

相关问答

更多

  • 在Ruby中解释哈希(Explaining hashes in Ruby)[2024-03-07]

    嵌套散列很好，是一个嵌套散列。 给出的示例@cached[key][prefix][name][partial]向您显示特定值的“路径”，因此在这种情况下，哈希可能如下所示： @cache = { key => { prefix => { name => { partial => "value" } } } } 对于以数组为键的简单散列，他们使用该4元素数组作为散列中的一个键。 @cache = { [key, prefix, name, ...

  • Ruby：同时循环两个哈希值，其中一个是嵌套哈希值(Ruby: looping over two hashes simultaneously with one being a nested hash)[2023-10-09]

    rides = [ { driver_id: "DR0004", date: "3rd Feb 2016", cost: 5, rider_id: "RD0022", rating: 5 }, { driver_id: "DR0001", date: "3rd Feb 2016", cost: 10, rider_id: "RD0003", rating: 3 }, { driver_id: "DR0002", date: "3rd Feb 2016", cost: 25, rider_id: ...

  • Ruby嵌套哈希 - 确定单独的事件(Ruby Nested Hashes - Determining separate events)[2023-07-30]

    当您处理复杂或大型数据文件时，将XML转换为哈希值并不是一个好主意，因为与搜索XML相比，遍历哈希不是很方便。 使用像Nokogiri这样的合适工具解析XML非常简单。 基于你的XML： require 'nokogiri' xml = "