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!!!
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: OracleGuy: Accessing Oracle from Powershell
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!!!
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!!!
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.
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!!!
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()
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
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()
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.