Author Posts

October 25, 2016 at 2:36 pm

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!!!!

October 26, 2016 at 12:45 pm

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

October 27, 2016 at 7:36 pm

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!!!

October 28, 2016 at 8:55 am

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!!!

October 28, 2016 at 1:39 pm

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.

November 1, 2016 at 2:31 pm

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!!!!

November 1, 2016 at 3:26 pm

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()

November 1, 2016 at 4:23 pm

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

November 27, 2017 at 2:45 pm

I want to use this code for multiple SQL queries and want to store the results in different CSV files.
I tried creating different queries but the result shows only the first query result.
Need your urgent help ! thanks !

I managed to get "Oracle.ManagedDataAccess.dll" on my Windows machine and below code worked

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()

November 28, 2017 at 5:30 pm

Hi Shreyas,
I think the problem you have is that you are writing the results to the file name.
Try adding a verable to the CSV file name like a date/time or just another name like Dataset1.csv and Dataset2.csv

Hope that helps.