none
System.Data.OracleClient and powershell RRS feed

  • Question

  • I'm trying to use powershell to retrieve some data from a local oracle server. However when i try to use the OracleDataAdaptor, to fill a data set, I get an error that I need to have oracle cliet software version 8.1.7. I can fix this by installing the ODP.Net, however it is a huge installation. I need this scrip to be portal. I have also looked into using the Oracle Instant Client, however from a C# tutorial I found it looks like these need to be included in the same directory as an executable. Since powershell does not operate the same way, can anyone give me some advice as to how I can deploy a portable script that can connect to oracle 10g without any type of software installation?

    				[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
    
    				# Connection information
    				$ConnectionString = "Data Source=serverip;User Id=user_name;Password=password"
    
    				#Standard SQL Query Syntax
    				$QueryString = "SELECT * FROM Table"
    
    				$OracleConnection = New-Object System.Data.OracleClient.OracleConnection($ConnectionString)
    				$dtSet = New-Object System.Data.DataSet
    				$OracleAdapter = New-Object System.Data.OracleClient.OracleDataAdapter($QueryString, $OracleConnection)
    
    				[void]$OracleAdapter.Fill($dtSet)
    				
    				
    Exception calling "Fill" with "1" argument(s): "System.Data.OracleClient requires Oracle client software version 8.1.7
    or greater."
    At line:1 char:42
    +                 [void]$OracleAdapter.Fill <<<< ($dtSet)
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : DotNetMethodException
    Wednesday, August 12, 2009 9:31 PM

Answers

  • Hello!
    It would be better to use Oracle Data Provider for .NET (ODP.NET):

    $AssemblyFile = "C:\Oracle\product\10.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
    $ConnectionString = "User ID=Username;Password=Password;Data Source=Server;Persist Security Info=True"
    $CommandText = "SELECT * FROM Table"
    [Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
    $OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
    $OracleConnection.ConnectionString = $ConnectionString
    $OracleConnection.Open()
    $OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
    $OracleCommand.CommandText = $CommandText
    $OracleCommand.Connection = $OracleConnection
    $OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
    $OracleDataAdapter.SelectCommand = $OracleCommand
    $DataSet = New-Object -TypeName System.Data.DataSet
    $OracleDataAdapter.Fill($DataSet) | Out-Null
    $OracleDataAdapter.Dispose()
    $OracleCommand.Dispose()
    $OracleConnection.Close()
    $DataSet.Tables[0]


    Thursday, December 3, 2009 2:51 PM

All replies

  • Trying to help, but might be beyond my abilities with Oracle...

    I would have to say that you should go with using something more generic like ODBC or simply using the Oracle command-line tools.

    Now, when I search for "oracle powershell", I see different strategies, to me, to once the connection is made.  Are there more generic ADO.NET features available for you to load data?
    Thursday, August 13, 2009 1:28 AM
    Moderator
  • It seems that when you google this topic, all of them are using the ODP.net or system.data.oracleclient with ODP.net installed. As long as Oracle Client or ODP.net is installed, you can use system.data.oracleclient without a problem. I am not aware of generics as this is the solution i'm searching for. I'm hoping to find a stand alone assembly that i can include with the script that can retrieve table data.
    Thursday, August 13, 2009 1:34 AM
  • Hi,

    You may have to build your own solution. I suggest that you initial a new post in the MSDN form to get further support there. They are the best resource for development related problems.

    For your convenience, I have list the link as followed.

    MSDN Forum
    http://forums.microsoft.com/MSDN/default.aspx?SiteID=1

    Thanks.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, August 13, 2009 9:52 AM
  • Hello!
    It would be better to use Oracle Data Provider for .NET (ODP.NET):

    $AssemblyFile = "C:\Oracle\product\10.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"
    $ConnectionString = "User ID=Username;Password=Password;Data Source=Server;Persist Security Info=True"
    $CommandText = "SELECT * FROM Table"
    [Reflection.Assembly]::LoadFile($AssemblyFile) | Out-Null
    $OracleConnection = New-Object -TypeName Oracle.DataAccess.Client.OracleConnection
    $OracleConnection.ConnectionString = $ConnectionString
    $OracleConnection.Open()
    $OracleCommand = New-Object -TypeName Oracle.DataAccess.Client.OracleCommand
    $OracleCommand.CommandText = $CommandText
    $OracleCommand.Connection = $OracleConnection
    $OracleDataAdapter = New-Object -TypeName Oracle.DataAccess.Client.OracleDataAdapter
    $OracleDataAdapter.SelectCommand = $OracleCommand
    $DataSet = New-Object -TypeName System.Data.DataSet
    $OracleDataAdapter.Fill($DataSet) | Out-Null
    $OracleDataAdapter.Dispose()
    $OracleCommand.Dispose()
    $OracleConnection.Close()
    $DataSet.Tables[0]


    Thursday, December 3, 2009 2:51 PM
  • Very Nice! Thank you!
    Monday, December 7, 2009 11:16 PM
  • I'm running Powershell V2 on a Windows Server 2008 R2 standard installation which has Oracle 11.2 Server and Client installed; I am trying to run the following lines in a Powershell window:

    $assembly="C:\Oracle\product\11.2.0\dbhome_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"

    [Reflection.Assembly]::LoadFile($assembly)

    which results in the following error message:

    Exception calling "LoadFile" with "1" argument(s): "Could not load file or assembly 'Oracle.DataAccess, Version=2.112.2 at line:1 char:36, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. An attempt was made to load a program with an incorrect format"

    .

    ----- MethodInvocationException ----- DotNetMethodException

    I have .NET v4.0.30319

    Can you please tell me what I am doing wrong? Google shows plenty of similar examples which all appear to work.

    
    
    
    
    Tuesday, February 25, 2014 11:50 PM