{"id":6007,"date":"2017-09-29T11:55:43","date_gmt":"2017-09-29T02:55:43","guid":{"rendered":"http:\/\/excelwork.info\/excel\/?p=6007"},"modified":"2017-09-29T11:55:43","modified_gmt":"2017-09-29T02:55:43","slug":"databaseoracleoo4o","status":"publish","type":"post","link":"https:\/\/excelwork.info\/excel\/databaseoracleoo4o\/","title":{"rendered":"\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08Oracle\uff09\u306b\u63a5\u7d9a\u3059\u308b\uff08oo4o\uff09"},"content":{"rendered":"<h2>oo4o \u63a5\u7d9a<\/h2>\n<div class=\"m30-l\">\n<p><strong>OO4O\uff08Oracle Objects for OLE\uff09<\/strong>\u306fWindows\u7528\u30a2\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u5411\u3051\u306bOracle\u304c\u63d0\u4f9b\u3057\u3066\u3044\u308b\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u63a5\u7d9a\u30df\u30c9\u30eb\u30a6\u30a8\u30a2\u3067\u3001DAO\u306b\u985e\u4f3c\u3057\u305f\u30a4\u30f3\u30bf\u30fc\u30d5\u30a7\u30fc\u30b9\u3092\u5229\u7528\u3057\u307e\u3059\u3002<br \/>\n    11gR2 \u3092\u6700\u5f8c\u306b 12c \u4ee5\u964d\u306f\u63d0\u4f9b\u3055\u308c\u306a\u304f\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<h3>oo4o \u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\uff08Oracle\uff09\u3078\u306e\u63a5\u7d9a<\/h3>\n<br \/>\n<div class=\"kkbox\"><strong>\u3010oo4o \u63a5\u7d9a\u3011<\/strong><\/p>\n<p>    <span class=\"red\"><strong>\uff1c\u53c2\u7167\u8a2d\u5b9a\uff1e<\/strong><\/span><br \/>\n    <b>Dim<\/b> <em>OraSession<\/em> <b>As New OraSessionClass<\/b><br \/>\n    <b>Dim<\/b> <em>OraDatabase<\/em> <b>As OraDatabase<\/b><\/p>\n<p>    <b>Set<\/b> <em>OraDatabase<\/em> = <em>OraSession<\/em>.<b>OpenDatabase<\/b>(<em>NetServiceName<\/em>, <em>User&#038;Pass<\/em>, <em>Options<\/em>)<\/p>\n<p>    <span class=\"red\"><strong>\uff1cCreateObject\u95a2\u6570\uff1e<\/strong><\/span><br \/>\n    <b>Dim<\/b> <em>OraSession<\/em> <b>As Object<\/b><br \/>\n    <b>Dim<\/b> <em>OraDatabase<\/em> <b>As Object<\/b><\/p>\n<p>    <b>Set<\/b> <em>OraSession<\/em> = <b>CreateObject<\/b>(&#8220;<strong>OracleInProcServer.XOraSession<\/strong>&#8220;)<br \/>\n    <b>Set<\/b> <em>OraDatabase<\/em> = <em>OraSession<\/em>.<b>OpenDatabase<\/b>(<em>NetServiceName<\/em>, <em>User&#038;Pass<\/em>, <em>Options<\/em>)<\/p><\/div>\n<br \/>\n<h4>\u5f15\u6570\u30fb\u623b\u308a\u5024<\/h4>\n<div class=\"m30-l\">\n<ul style=\"margin-top:0;margin-bottom:0;padding-top:0;padding-bottom:0;\">\n<li>OraSession&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30bb\u30c3\u30b7\u30e7\u30f3<\/li>\n<li>OraDatabase&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9<\/li>\n<li>NetServiceName&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30cd\u30c3\u30c8\u30b5\u30fc\u30d3\u30b9\u540d\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002<\/li>\n<li>User&#038;Pass&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u3078\u63a5\u7d9a\u3059\u308b\u30e6\u30fc\u30b6\u30fc\u540d\u3068\u30d1\u30b9\u30ef\u30fc\u30c9\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002<br \/>\n\t\uff08\u203b\u66f8\u5f0f\u306f\u3001[\u30e6\u30fc\u30b6\u30fc\u540d]\/[\u30d1\u30b9\u30ef\u30fc\u30c9]\uff09<\/li>\n<li>Options&nbsp;&nbsp;\u30fb\u30fb\u30fb&nbsp;&nbsp;\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u30aa\u30d7\u30b7\u30e7\u30f3\u30fb\u30e2\u30fc\u30c9\u306e\u8a2d\u5b9a\u306b\u4f7f\u7528\u3059\u308b\u30d3\u30c3\u30c8\u30fb\u30d5\u30e9\u30b0\u6587\u5b57\u3092\u6307\u5b9a\u3057\u307e\u3059\u3002options = 0 \u306e\u5834\u5408\u306f\u3001\u30c7\u30d5\u30a9\u30eb\u30c8\u306e\u30e2\u30fc\u30c9\u8a2d\u5b9a\u304c\u9069\u7528\u3055\u308c\u307e\u3059\u3002<\/li>\n<\/ul>\n<br \/>\n<p class=\"caution4\">\u53c2\u7167\u8a2d\u5b9a\u3059\u308b\u5834\u5408\u3001VBE \u306e\u300c\u30c4\u30fc\u30eb\u300d-\u300c\u53c2\u7167\u8a2d\u5b9a\u300d\u3092\u30af\u30ea\u30c3\u30af\u3057\u300c\u53c2\u7167\u8a2d\u5b9a\u30c0\u30a4\u30a2\u30ed\u30b0\u30dc\u30c3\u30af\u30b9\u300d\u3092\u958b\u304d\u3001\u300cOracle InProc Server 5.0 Type Library\u300d\u306b\u30c1\u30a7\u30c3\u30af\u3092\u5165\u308c\u307e\u3059\u3002<\/p>\n<p class=\"caution4\"><em>Options<\/em> \u306b\u6307\u5b9a\u3067\u304d\u308b\u5024\uff08\u5b9a\u6570\uff09\u306e\u4e00\u89a7\u8868<\/p>\n<table>\n<tr>\n<td align = \"center\" width=\"200\" bgcolor=\"#ffffccc\"><strong>\u5b9a\u6570<\/strong><\/td>\n<td align = \"center\" width=\"50\" bgcolor=\"#ffffccc\"><strong>\u5024<\/strong><\/td>\n<td align = \"center\" width=\"400\" bgcolor=\"#ffffccc\"><strong>\u5185\u5bb9<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_DEFAULT<\/td>\n<td width=\"50\" align = \"center\">&#038;H0&#038;<\/td>\n<td width=\"400\">Visual Basic\u30e2\u30fc\u30c9(\u30c7\u30d5\u30a9\u30eb\u30c8)<\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_ORAMODE<\/td>\n<td width=\"50\" align = \"center\">&#038;H1&#038;<\/td>\n<td width=\"400\">Oracle\u30e2\u30fc\u30c9<\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_NOWAIT<\/td>\n<td width=\"50\" align = \"center\">&#038;H2&#038;<\/td>\n<td width=\"400\">\u30ed\u30c3\u30af\u975e\u5f85\u6a5f\u30e2\u30fc\u30c9<\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_NO_REFETCH<\/td>\n<td width=\"50\" align = \"center\">&#038;H4&#038;<\/td>\n<td width=\"400\">Oracle\u30e2\u30fc\u30c9(\u518d\u30d5\u30a7\u30c3\u30c1\u306a\u3057)<\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_NONBLK<\/td>\n<td width=\"50\" align = \"center\">&#038;H8&#038;<\/td>\n<td width=\"400\">\u975e\u30d6\u30ed\u30c3\u30af\u30e2\u30fc\u30c9<\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_ENLIST_IN_MTS<\/td>\n<td width=\"50\" align = \"center\">&#038;H10&#038;<\/td>\n<td width=\"400\">MTS\u30e2\u30fc\u30c9\u3067\u306e\u30ea\u30b9\u30c8<\/td>\n<\/tr>\n<tr>\n<td width=\"200\">ORADB_ENLIST_FOR_ CALLLBACK<\/td>\n<td width=\"50\" align = \"center\">&#038;H20&#038;<\/td>\n<td width=\"400\">\u30b3\u30fc\u30eb\u30d0\u30c3\u30af\u30fb\u30e2\u30fc\u30c9\u3067\u306e\u30ea\u30b9\u30c8<\/td>\n<\/tr>\n<\/table>\n<p>\n    <\/p>\n<\/p><\/div>\n<\/div>\n<br \/><br \/>\n<h2>Oracle \u63a5\u7d9a\uff08oo4o\uff09<\/h2>\n<div class=\"m30-l\">\n<h3 id=\"vba_sample\">\u30b5\u30f3\u30d7\u30ebVBA\u30bd\u30fc\u30b91<\/h3>\n<pre class=\"lang:vb mark:0 decode:true \" title=\"ConvertFormula \u30e1\u30bd\u30c3\u30c9 \u4f7f\u7528\u4f8b\" data-url=\"http:\/\/example.co.jp\" >\r\n\u3000\r\nSub Sample_oo4o_oracle1()\r\n\r\n    '\u53c2\u7167\u8a2d\u5b9a\u300cOracle InProc Server 5.0 Type Library\u300d\r\n    \r\n    Const NETSERVICENAME = \"hpdb1\"          'tnsnames.ora \u30d5\u30a1\u30a4\u30eb\u306e\u30cd\u30c3\u30c8\u30b5\u30fc\u30d3\u30b9\u540d'\r\n    Const USERPASS = \"uhpdb1\/uhpdb1\"        '\u30e6\u30fc\u30b6\u30fc\u540d\/\u30d1\u30b9\u30ef\u30fc\u30c9\r\n    \r\n    Dim OraSession As New OraSessionClass   '\u30bb\u30c3\u30b7\u30e7\u30f3\r\n    Dim OraDatabase As OraDatabase          '\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\r\n    Dim OraDynaset As OraDynaset            '\u30ec\u30b3\u30fc\u30c9\u30bb\u30c3\u30c8\r\n    \r\n    Dim row As Long\r\n    Dim col As Long\r\n    Dim strSQL As String\r\n    \r\n    'SQL\u6587\r\n    strSQL = \"select * from table01 order by id\"\r\n    \r\n    'OraDatabase\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u306e\u751f\u6210\r\n    Set OraDatabase = OraSession.OpenDatabase(NETSERVICENAME, USERPASS, ORADB_DEFAULT)\r\n    Set OraDynaset = OraDatabase.CreateDynaset(strSQL, ORADYN_DEFAULT)\r\n    \r\n    With Worksheets(\"oracle_oo4o\")\r\n        \r\n        .Cells.Clear\r\n        \r\n        '\u30d5\u30a3\u30fc\u30eb\u30c9\u540d\r\n        For col = 0 To OraDynaset.Fields.Count - 1\r\n            .Cells(1, col + 1) = OraDynaset(col).Name\r\n        Next col\r\n        \r\n        '\u30ec\u30b3\u30fc\u30c9\r\n        Do Until OraDynaset.EOF\r\n            For col = 0 To OraDynaset.Fields.Count - 1\r\n                .Cells(row + 2, col + 1) = OraDynaset(col).Value\r\n            Next col\r\n            row = row + 1\r\n            OraDynaset.MoveNext\r\n        Loop\r\n    \r\n    End With\r\n    \r\n    OraDynaset.Close\r\n    Set OraDynaset = Nothing\r\n    Set OraDatabase = Nothing\r\n    Set OraSession = Nothing\r\n    \r\nEnd Sub\r\n    \r\n  <\/pre>\n<br \/>\n<h3>\u5b9f\u884c\u7d50\u679c<\/h3>\n<p><a href=\"https:\/\/i2.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_01.jpg?ssl=1\" rel=\"lightbox[6007]\"><img loading=\"lazy\" src=\"https:\/\/i2.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_01.jpg?resize=403%2C335&#038;ssl=1\" alt=\"Oracle(oo4o)\u63a5\u7d9a\u30b5\u30f3\u30d7\u30eb\uff11\" width=\"403\" height=\"335\" class=\"alignnone size-full wp-image-6010\" srcset=\"https:\/\/i2.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_01.jpg?w=403&amp;ssl=1 403w, https:\/\/i2.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_01.jpg?resize=400%2C333&amp;ssl=1 400w\" sizes=\"(max-width: 403px) 100vw, 403px\" data-recalc-dims=\"1\" \/><\/a><\/p>\n<\/div>\n<div class=\"m30-l\">\n<h3 id=\"vba_sample\">\u30b5\u30f3\u30d7\u30ebVBA\u30bd\u30fc\u30b92<\/h3>\n<pre class=\"lang:vb mark:0 decode:true \" title=\"ConvertFormula \u30e1\u30bd\u30c3\u30c9 \u4f7f\u7528\u4f8b\" data-url=\"http:\/\/example.co.jp\" >\r\n\r\n'CreateObject\u95a2\u6570\r\n\r\nConst ORADB_DEFAULT = &H0&\r\nConst ORADB_ORAMODE = &H1&\r\nConst ORADB_NOWAIT = &H2&\r\nConst ORADB_NO_REFETCH = &H4&\r\nConst ORADB_NONBLK = &H8&\r\nConst ORADB_ENLIST_IN_MTS = &H10&\r\nConst ORADB_ENLIST_FOR_CALLLBACK = &H20&\r\n\r\nConst ORADYN_DEFAULT = &H0&\r\nConst ORADYN_NO_AUTOBIND = &H1&\r\nConst ORADYN_NO_BLANKSTRIP = &H2&\r\nConst ORADYN_READONLY = &H4&\r\nConst ORADYN_NOCACHE = &H8&\r\nConst ORADYN_ORAMODE = &H10&\r\nConst ORADYN_NO_REFETCH = &H20&\r\nConst ORADYN_N_MOVEFIRST = &H40&\r\nConst ORADYN_DIRTY_WRITE = &H80&\r\n\r\n\r\nSub Sample_oo4o_Oracle2()\r\n    \r\n    Dim OraSession As Object        '\u30bb\u30c3\u30b7\u30e7\u30f3\r\n    Dim OraDatabase As Object       '\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\r\n    Dim OraRs As Object             '\u30ec\u30b3\u30fc\u30c9\u30bb\u30c3\u30c8\r\n    Dim strSQL As String\r\n    Dim col As Long\r\n    Dim row As Long\r\n    \r\n    Set OraSession = CreateObject(\"OracleInProcServer.XOraSession\")\r\n    Set OraDatabase = OraSession.OpenDatabase(\"hpdb1\", \"uhpdb1\/uhpdb1\", ORADB_DEFAULT)\r\n    \r\n    strSQL = \"insert all into table01 values (4, '\u897f\u7530\u76f4\u7f8e') \"\r\n    strSQL = strSQL & \"into table01 values (5, '\u6771\u7530\u4e00\u884c') \"\r\n    strSQL = strSQL & \"into table01 values (6, '\u52a0\u85e4\u308c\u306a') \"\r\n    strSQL = strSQL & \"into table01 values (7, '\u5927\u91ce\u543e\u90ce') \"\r\n    strSQL = strSQL & \"select * from dual\"\r\n    \r\n    '\u30a8\u30e9\u30fc\u30c8\u30e9\u30c3\u30d7\u958b\u59cb\r\n    On Local Error GoTo ErrHandler\r\n    \r\n    '\u30a8\u30e9\u30fc\u3092\u30ea\u30bb\u30c3\u30c8\r\n    OraDatabase.LastServerErrReset\r\n    \r\n    '\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u51e6\u7406\u958b\u59cb\r\n    OraSession.BeginTrans\r\n    \r\n    'SQL\u6587\u5b9f\u884c\r\n    OraDatabase.Executesql strSQL\r\n    \r\n    '\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u7d42\u4e86\r\n    OraSession.CommitTrans\r\n    \r\n    On Local Error GoTo 0\r\n    \r\nErrHandler:\r\n    If Err <> 0 Then\r\n        MsgBox \"\u3010\u30a8\u30e9\u30fc\u756a\u53f7\uff1a\" & Err & \"\u3011\" & vbCrLf & _\r\n                Error & vbCrLf & \"\u66f4\u65b0\u3092\u30ad\u30e3\u30f3\u30bb\u30eb\u3057\u307e\u3057\u305f\"\r\n        '\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30ad\u30e3\u30f3\u30bb\u30eb\r\n        OraSession.Rollback\r\n    End If\r\n    \r\n    '\u8868\u793a\r\n    Set OraRs = OraDatabase.CreateDynaset(\"select * from table01\", ORADYN_DEFAULT)\r\n    \r\n    With Worksheets(\"oracle_oo4o\")\r\n    \r\n        .Cells.Clear\r\n    \r\n        For col = 0 To OraRs.Fields.Count - 1\r\n            .Cells(1, col + 1) = OraRs(col).Name\r\n        Next\r\n    \r\n        row = 0\r\n        Do Until OraRs.EOF\r\n            For col = 0 To OraRs.Fields.Count - 1\r\n                .Cells(row + 2, col + 1) = OraRs(col).Value\r\n            Next\r\n            OraRs.MoveNext\r\n            row = row + 1\r\n        Loop\r\n    \r\n    End With\r\n    \r\n    OraRs.Close\r\n    Set OraRs = Nothing\r\n    \r\n    Set OraDatabase = Nothing\r\n    Set OraSession = Nothing\r\n    \r\nEnd Sub\r\n    \r\n  <\/pre>\n<br \/>\n<h3>\u5b9f\u884c\u7d50\u679c<\/h3>\n<p><a href=\"https:\/\/i1.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_02.jpg?ssl=1\" rel=\"lightbox[6007]\"><img loading=\"lazy\" src=\"https:\/\/i1.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_02.jpg?resize=405%2C334&#038;ssl=1\" alt=\"Oracle(oo4o)\u63a5\u7d9a\u30b5\u30f3\u30d7\uff12\" width=\"405\" height=\"334\" class=\"alignnone size-full wp-image-6011\" srcset=\"https:\/\/i1.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_02.jpg?w=405&amp;ssl=1 405w, https:\/\/i1.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_02.jpg?resize=400%2C330&amp;ssl=1 400w\" sizes=\"(max-width: 405px) 100vw, 405px\" data-recalc-dims=\"1\" \/><\/a><\/p>\n<\/div>\n<br \/><br \/>\n","protected":false},"excerpt":{"rendered":"<p>oo4o \u63a5\u7d9a OO4O\uff08Oracle Objects for OLE\uff09\u306fWin &#8230; <\/p>\n","protected":false},"author":1,"featured_media":6010,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"amp_status":"","spay_email":"","jetpack_publicize_message":""},"categories":[948],"tags":[953,950,954,958,957,959,956,952,951,814,955,949],"jetpack_featured_media_url":"https:\/\/i2.wp.com\/excelwork.info\/excel\/wp\/wp-content\/uploads\/2017\/07\/databaseoracleoo4o_01.jpg?fit=403%2C335&ssl=1","jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4Ye9i-1yT","_links":{"self":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts\/6007"}],"collection":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/comments?post=6007"}],"version-history":[{"count":8,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts\/6007\/revisions"}],"predecessor-version":[{"id":6452,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/posts\/6007\/revisions\/6452"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/media\/6010"}],"wp:attachment":[{"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/media?parent=6007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/categories?post=6007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelwork.info\/excel\/wp-json\/wp\/v2\/tags?post=6007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}