{"id":876,"date":"2006-02-10T23:04:00","date_gmt":"2006-02-10T23:04:00","guid":{"rendered":"\/blogs\/bobb\/post\/New-samples3b-Calling-Oracle-from-SQLCLR.aspx"},"modified":"2013-01-04T00:00:17","modified_gmt":"2013-01-04T08:00:17","slug":"new-samples-calling-oracle-from-sqlclr","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/","title":{"rendered":"New samples; Calling Oracle from SQLCLR"},"content":{"rendered":"

\nI think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website<\/a>. I have (thought I had) the December update, this one actually says "Date published 1\/4\/2006" and its 20mb instead of the old once (just under 16mb).\n<\/p>\n

\nThe site says: Since its original release, new samples have been added for the following technologies: CLR, SMO, Integration Services, Replication, and Reporting Services. See the SQLServerDatabasesAndSamplesOverview.htm file for descriptions of the new and original samples.\n<\/p>\n

\nWell, the samples folks (thanks Bonnie and everyone) have really outdone themselves this time. Thanks!\n<\/p>\n

\nOne of the samples that particularly intruiged me was a TVF that returned a resultset from Oracle using System.Data.OracleClient. I was surprised because, although System.Data.OracleClient is on the "approved assemblies" list, the DDL cataloged the assembly as UNSAFE. Hmmm…thought I'd only need EXTERNAL_ACCESS.\n<\/p>\n

\nSo I fired up my combined VPC of SQL Server 2005\/Oracle 10g and decided to see for myself. When cataloged as EXTERNAL_ACCESS I got:\n<\/p>\n

\nMsg 10314, Level 16, State 11, Line 1
\nAn error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
\nSystem.IO.FileLoadException: Could not load file or assembly 'OracleTVF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=65071e111a9fd000' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417) —> System.Security.Policy.PolicyException: Required permissions cannot be acquired.\n<\/p>\n

\nWhen cataloged as UNSAFE, it worked fine. Well, after I changed it to access the SCOTT.DEPT table. I don't have the "titles" table the sample referred to.\n<\/p>\n

\nThe reason for this (UNSAFE) is most likely that System.Data.OracleClient (which is approved) needs to load Oracle's client libraries. These are written in unmanged code, which makes them unsafe to load, even if System.Data.OracleClient isn't unsafe. BTW, I didn't try ODP.NET. This would have required cataloging my DLL as UNSAFE because Oracle.OracleClient.dll provider isn't on the approved list. But I'm running UNSAFE already. Oh.<\/p>\n","protected":false},"excerpt":{"rendered":"

I think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website. I have (thought I had) the December update, this one actually says "Date published 1\/4\/2006" and its 20mb instead of the old once (just under 16mb). The site says: Since its original release, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,38],"tags":[],"class_list":["post-876","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sqlclr"],"yoast_head":"\nNew samples; Calling Oracle from SQLCLR - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New samples; Calling Oracle from SQLCLR - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"I think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website. I have (thought I had) the December update, this one actually says "Date published 1\/4\/2006" and its 20mb instead of the old once (just under 16mb). The site says: Since its original release, […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2006-02-10T23:04:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T08:00:17+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/\",\"name\":\"New samples; Calling Oracle from SQLCLR - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2006-02-10T23:04:00+00:00\",\"dateModified\":\"2013-01-04T08:00:17+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Data Access\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"New samples; Calling Oracle from SQLCLR\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"New samples; Calling Oracle from SQLCLR - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/","og_locale":"en_US","og_type":"article","og_title":"New samples; Calling Oracle from SQLCLR - Bob Beauchemin","og_description":"I think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website. I have (thought I had) the December update, this one actually says "Date published 1\/4\/2006" and its 20mb instead of the old once (just under 16mb). The site says: Since its original release, […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/","og_site_name":"Bob Beauchemin","article_published_time":"2006-02-10T23:04:00+00:00","article_modified_time":"2013-01-04T08:00:17+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/","name":"New samples; Calling Oracle from SQLCLR - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2006-02-10T23:04:00+00:00","dateModified":"2013-01-04T08:00:17+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/new-samples-calling-oracle-from-sqlclr\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Data Access","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/data-access\/"},{"@type":"ListItem","position":3,"name":"New samples; Calling Oracle from SQLCLR"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/876","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=876"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/876\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=876"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=876"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=876"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}