CODEWITHSUNDEEP
oracle-databasecsvpowershellpowershell-2.0
user4691770
user4691770

Reputation:

Powershell and Oracle, exporting SQL data to csv

I have an Oracle SQL table that contains email addresses. I need to extract this data and insert it into an array, and export to a csv file. I'm not too sure where to go from here. My code so far is:

$odpAssemblyName = "Oracle.DataAccess, Version=2.112.3.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
[System.Reflection.Assembly]::Load($odpAssemblyName)

$con = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=HR;Password=oracle;Data Source=XE")
$cmd=$con.CreateCommand()
$cmd.CommandText= @"

SELECT DISTINCT email FROM 
hr.emails WHERE acct_enabled = 'Y' 
AND UPPER(email) NOT LIKE 'AIS%'ORDER BY email
"@

$con.Open()

$rdr=$cmd.ExecuteReader()
$columnNames=$rdr.GetSchemaTable() | Select-Object -ExpandProperty ColumnName

$resultSet=@()

while ($rdr.Read()) {

    $intData=$rdr.GetOracleString(0)

    "{0,0}" -f $intData.Value

}

$con.Close()

Can anybody help me with my While loop (I'm new to programming mostly) and help me add the result set to an array, and export the result set to a nice little csv file?

Thanks in advance for any help/pointers

James

Upvotes: 1

Views: 2583

Answers (1)

ATek
ATek

Reputation: 825

Try something like this, although I cant exactly test it based on no oracle DB available on my side.

$odpAssemblyName = "Oracle.DataAccess, Version=2.112.3.0,
Culture=neutral, PublicKeyToken=89b483f429c47342"
[System.Reflection.Assembly]::Load($odpAssemblyName)

$con = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=HR;Password=oracle;Data Source=XE")
$cmd=$con.CreateCommand()
$cmd.CommandText= @"

SELECT DISTINCT email FROM 
hr.emails WHERE acct_enabled = 'Y' 
AND UPPER(email) NOT LIKE 'AIS%'ORDER BY email
"@

$con.Open()

 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $cmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)

$emaillist = $DataSet.Tables[0].Email



$con.Close()

$emaillist | Export-csv "C:\Somefolder\OracleEmails.csv" -NoTypeInformation

Upvotes: 0

Related Questions