PS script to get data from Oracle DB and export to CSV

This topic contains 7 replies, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 month, 1 week ago.

  • Author
    Posts
  • #56020
    Profile photo of Nathan W
    Nathan W
    Participant

    Hello again.

    I am working on a project that I need to automate the creation of a CSV file based on a SQL query to a oracle DB.
    I have written the SQL script to make the query I need and saved that off as a .sql file.
    I have also done my research and downloaded the ODP.NET_Managed_ODAC12cR4 and installed it. I have also tested the ability to load in the DLL to PS via PS C:\Windows\System32\WindowsPowerShell\v1.0> Add-Type -Path C:\oracle\instantclient_10_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll.
    So now I just need to create the connection to the DB, execute the .sql script and export the results to a CSV.
    I am continuing to search the internet for a solution but I always find that someone here has the answer long before I find it out in the wild.

    As always Thanks for your assistance!!!!

  • #56095
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    The Oracle Client is typically required because it installs the Oracle ODBC drivers that allow you to connect to Oracle. You can check ConnectionStrings.com to get examples of the strings you'll need to make the connection. A search for Powershell Oracle turned up a lot of examples like this: http://guyharrison.typepad.com/oracleguy/2008/01/accessing-oracl.html

  • #56285
    Profile photo of Nathan W
    Nathan W
    Participant

    Thanks for the links. It has been quite helpful.
    I have made a lot of progress but I am getting an error when I try to run the script.
    Here is the secipt

    Add-Type -Path C:\oracle\instantclient_10_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll
    $query = "select info from DATABASE.TABLE"
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=user;Password=password;Data Source=server.domain.com:PORT/DBINSTANCE")
    $connection.open()
    $command=$connection.CreateCommand()
    $command.CommandText=$query
    $reader=$command.ExecuteReader()
    while ($reader.Read()) {
    $reader.GetString(0)
    }
    $connection.Close()
    

    And the error

    Exception calling "ExecuteReader" with "0" argument(s): "ORA-00936: missing expression"
    At C:\Folder\script.ps1:19 char:1
    + $reader=$command.ExecuteReader()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : OracleException
    
    You cannot call a method on a null-valued expression.
    At C:\Folder\script.ps1:20 char:8
    + while ($reader.Read()) {
    +        ~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull
    

    Now it seems like the CommandText is not being passed to the execute command but when I brake the code down and enter it one line at a time I can enter $command and I get this result

    AddRowid               : False
    AddToStatementCache    : True
    ArrayBindCount         : 0
    ArrayBindRowsAffected  :
    BindByName             : False
    UseEdmMapping          : False
    CommandText            : select info from DATABASE.TABLE
    CommandTimeout         : 0
    CommandType            : Text
    ImplicitRefCursors     :
    XmlCommandType         : None
    XmlQueryProperties     : Oracle.ManagedDataAccess.Client.OracleXmlQueryProperties
    XmlSaveProperties      : Oracle.ManagedDataAccess.Client.OracleXmlSaveProperties
    Connection             : Oracle.ManagedDataAccess.Client.OracleConnection
    Transaction            :
    DesignTimeVisible      : True
    FetchSize              : 131072
    RowSize                : 0
    InitialLOBFetchSize    : 0
    InitialLONGFetchSize   : 0
    UpdatedRowSource       : Both
    Parameters             : {}
    Notification           :
    NotificationAutoEnlist : True
    Site                   :
    Container              :
    

    As always thanks for your assistance!!!

  • #56348
    Profile photo of Nathan W
    Nathan W
    Participant

    Ok. I was able to figure out what I was doing wrong above and now the script is executing and I am getting the data I want displayed in the console. Now I just need to get the data out to a csv file. I have been noodling with it for a while tonight and not making the progress I want.
    I was toying with using Set-Content but I am having trouble getting the data to be added to the file. And to make it more interesting I need to customize the column headers.
    here is my code that gets all the data I need. Just need help piping it out to a csv.

    $query = "SELECT DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA6, DATA7 FROM DBO.Test WHERE NOT REGEXP_LIKE (DATA4, '@domain.com','i') order by DATA2"
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("User Id=userid;Password=password;Data Source=host:port/Instance")
    $connection.open()
    $command=$connection.CreateCommand()
    $command.CommandText=$query
    $reader=$command.ExecuteReader()
    while ($reader.Read()) {
    $reader.GetString(0) + ', ' +,
    $reader.GetString(1) + ', ' +,
    $reader.GetString(2) + ', ' +,
    $reader.GetString(3) + ', ' +,
    $reader.GetValue(4) + ', ' +,
    $reader.GetValue(5) + ', ' +,
    $reader.GetString(6) + ', ' +,
    $reader.GetString(7)
    
    }
    $connection.Close()
    

    Thanks in advance!!!

  • #56374
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Read this Scripting Guy blog, which talks in depth about how to connect to Oracle, performance, etc. as well as functions to get the data.

    Most of the reader() (Get-OracleResultRdr)solutions are generating a PSObject, which is what you want to do. The Get-OracleResultDa function fills a DataTable, so you will need to convert it to a PSObject. Regardless, of the function you choose, you should do something like this:

    $results = Get-OracleResultDa  $conString $sqlString
    $results | Select Data1, Data2, Data3 | Export-CSV C:\MyOracleData.csv -NoTypeInformation
    

    The Select(-Object) will create a PSObject so that you can easily export it to a CSV.

  • #56588
    Profile photo of Nathan W
    Nathan W
    Participant

    Thanks again Rob!!
    I have been tinkering with this all morning and starting to bang my head on my desk.
    I did try your suggestion and I am getting a cmdlet error

    Get-OracleResultDa : The term 'Get-OracleResultDa' is not recognized as the name of a cmdlet, function, script file,
    or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and
    try again.
    

    I have also tried several other variations.
    I will continue to search the web but thought I would post again so that if you or someone more knowledgeable may find a solution faster than I do.

    Thanks a bunch!!!!

  • #56597
    Profile photo of Nathan W
    Nathan W
    Participant

    HA HA!!
    I did it.
    Just in case anyone would like to know how

    Add-Type -Path C:\oracle\instantclient_10_2\odp.net\managed\common\Oracle.ManagedDataAccess.dll
    
    $username = "USERID"
    $password = "Password"
    $datasource = "HOST:PORT/Instance"
    $connectionString = "User Id=$username;Password=$password;Data Source=$datasource"
    $query = "SELECT DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8 FROM TABLE WHERE NOT REGEXP_LIKE (EMAIL_ID, '@domain.com','i') order by DATA2"
    $connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("$connectionString")
    $connection.open()
    $command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
    $command.Connection = $connection
    $command.CommandText = $query
    $ds = New-Object system.Data.DataSet
    $da = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
    [void]$da.fill($ds)
    return $ds.Tables[0] | SELECT DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8 | Export-CSV "C:\test.csv" -NoTypeInformation
    $connection.Close()
    
  • #56608
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Glad you got it working. The error you received in the post before, I wanted to show you how to make modular code. Using functions, you could re-use this code again, call it multiple times, etc. versus having the script read line be line. Here is an example for you to play with if you're interested:

    function Get-OracleResultDa{
        [CmdLetBinding()]
        param(
            [Parameter(Mandatory=$true)]
            [ValidateScript({$_-match'\bdatasource\b'})]
            [string]$conString,
            [ValidateScript({$_-match'\bselect\b'})]
            [Parameter(Mandatory=$true)]
            [string]$sqlString
        )
        begin {
            $resultSet=@()
        }
        process {
            try{
                Write-Verbose ("Connection String: {0}" -f $conString)
                Write-Verbose ("SQL Command: `r`n {0}" -f $sqlString)
                $con=New-ObjectOracle.ManagedDataAccess.Client.OracleConnection($conString)
                $cmd=$con.CreateCommand()
                $cmd.CommandText=$sqlString
    
                $da=New-ObjectOracle.ManagedDataAccess.Client.OracleDataAdapter($cmd);
    
                $resultSet=New-ObjectSystem.Data.DataTable
    
                [void]$da.fill($resultSet)
            }catch{
                Write-Error($_.Exception.ToString())
            }finally{
                if($con.State-eq'Open'){$con.close()}
            }
        }
        end {
            $resultSet
        }
    
    }
    
    $userName = "user123"
    $password = "Password123"
    $datasource = "ORASRV123"
    $connectionString="UserId=$username;Password=$password;DataSource=$datasource"
    $query=@"
    SELECT DATA1
          ,DATA2
          ,DATA3
          ,DATA4
          ,DATA5
          ,DATA6
          ,DATA7
          ,DATA8
    FROM TABLE 
    WHERE NOT REGEXP_LIKE (EMAIL_ID,'@domain.com','i')
    Order By DATA2
    "@
    
    $results = Get-OracleResultDa -conString $connectionString -sqlString $query -Verbose
    $results | SELECT DATA1, DATA2, DATA3, DATA4, DATA5, DATA6, DATA7, DATA8 | Export-CSV "C:\test.csv" -NoTypeInformation
    

You must be logged in to reply to this topic.