{"id":1399,"date":"2016-03-17T17:28:54","date_gmt":"2016-03-17T22:28:54","guid":{"rendered":"http:\/\/osric.com\/chris\/accidental-developer\/?p=1399"},"modified":"2016-03-17T17:28:54","modified_gmt":"2016-03-17T22:28:54","slug":"querying-an-oracle-database-from-powershell","status":"publish","type":"post","link":"https:\/\/osric.com\/chris\/accidental-developer\/2016\/03\/querying-an-oracle-database-from-powershell\/","title":{"rendered":"Querying an Oracle database from Powershell"},"content":{"rendered":"<p>I needed to query Oracle for information to use in a Powershell script. I found the following comprehensive blog post with details on how to do so using ODP.NET: <a href=\"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2012\/12\/04\/use-oracle-odp-net-and-powershell-to-simplify-data-access\/\">Use Oracle ODP.NET and PowerShell to Simplify Data Access<\/a><\/p>\n<p>The article is extremely long and goes into a lot of depth. The following is a short summary of the first steps, with just enough to get started:<\/p>\n<ol>\n<li>Download <a href=\"http:\/\/www.oracle.com\/technetwork\/topics\/dotnet\/index-085163.html\">Oracle Data Provider for .NET (ODP.NET)<\/a>. (If that link doesn&#8217;t work just search for &#8220;Oracle ODP.NET&#8221;.)\n<ul>\n<li>Select &#8220;Download the latest ODP.NET production release.&#8221;<\/li>\n<li>Select &#8220;64-bit ODAC Downloads&#8221;<\/li>\n<li>Select &#8220;ODP.NET_Managed_ODAC12cR4.zip&#8221;<\/li>\n<\/ul>\n<\/li>\n<li>Extract the ZIP file to <code>C:\\<\/code>, which creates <code>C:\\ODP.NET_Managed_ODAC12cR4<\/code>.<\/li>\n<li>Run <code>cmd<\/code> as administrator, navigate to C:\\ODP.NET_Managed_ODAC12cR4, and run:<br \/>\n<code>install_odpm.bat C:\\oracle\\instantclient_10_2 both<\/code><\/li>\n<\/ol>\n<p>In Powershell, add the DLL and set up a database connection and a query:<\/p>\n<p><code>Add-Type -Path \"C:\\Users\\chris\\ODP.NET_Managed_ODAC12cR4\\odp.net\\managed\\common\\Oracle.ManagedDataAccess.dll\"<br \/>\n$username = Read-Host -Prompt \"Enter database username\"<br \/>\n$password = Read-Host -Prompt \"Enter database password\"<br \/>\n$datasource = Read-Host -Prompt \"Enter database TNS name\"<br \/>\n$query = \"SELECT first_name, last_name FROM users.user WHERE first_name = 'Chris' ORDER BY last_name\"<br \/>\n$connectionString = 'User Id=' + $username + ';Password=' + $password + ';Data Source=' + $datasource<br \/>\n$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connectionString)<br \/>\n$connection.open()<br \/>\n$command=$connection.CreateCommand()<br \/>\n$command.CommandText=$query<br \/>\n$reader=$command.ExecuteReader()<br \/>\nwhile ($reader.Read()) {<br \/>\n    $reader.GetString(1) + ', ' + $reader.GetString(0)<br \/>\n}<br \/>\n$connection.Close()<\/code><\/p>\n<p>Output of the above:<\/p>\n<p><code>Brown, Chris<br \/>\nCarter, Chris<br \/>\nJones, Chris<br \/>\nSmith, Chris<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I needed to query Oracle for information to use in a Powershell script. I found the following comprehensive blog post with details on how to do so using ODP.NET: Use Oracle ODP.NET and PowerShell to Simplify Data Access The article is extremely long and goes into a lot of depth. The following is a short &hellip; <a href=\"https:\/\/osric.com\/chris\/accidental-developer\/2016\/03\/querying-an-oracle-database-from-powershell\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Querying an Oracle database from Powershell<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[282,393],"tags":[274,228],"class_list":["post-1399","post","type-post","status-publish","format-standard","hentry","category-oracle-2","category-powershell","tag-oracle","tag-powershell"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1399","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=1399"}],"version-history":[{"count":11,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1399\/revisions"}],"predecessor-version":[{"id":1410,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/1399\/revisions\/1410"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=1399"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=1399"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=1399"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}