时间戳

首页 > 解决方案 > 如何使用powershell从oracle表中删除数据

.net - 如何使用powershell从oracle表中删除数据

问题描述

我想在使用 powershell 插入数据之前从 oracle 表中删除整个数据， 

Add-Type -Path C:\Users\sankar\ODP.NET_Managed_ODAC122cR1\odp.net\managed\common\Oracle.ManagedDataAccess.dll
#Database Connection String & Establish connection
$compConStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx)(PORT=xxxxx)))(CONNECT_DATA=(SID =xxxxxxxx)));User Id=spcl_init;Password=xxxxxx;Pooling=False"
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($compConStr)
$connection.open()
$Grouplists = Import-Csv "\\XXX\Techsupportgroups1.csv"
#Deleate existing data from oracle table
 $query = "DELETE FROM TableName"

            $command=$connection.CreateCommand()
            $command.CommandText=$query
            #$command.ExecuteNonQuery()
            $command.ExecuteReader()
#Insert memebers details into oracle Table
foreach ($Group in $Grouplists){
$MembersList=(Get-QADGroupMember $Group.Group_DL |Get-QADuser -Properties * |where {$_.Type -ne "group" -and $_.AccountIsDisabled -ne "False" -and $_.Samaccountname -notmatch "unixlinuxops"} | Select-Object |Select-Object @{n='Group_Name';e={$Group.Group_Name}},Samaccountname,NTAccountName,UserPrincipalName)

        foreach($Member in $MembersList)
        {
            $GroupName = $Member.Group_Name
            $SamAccountName=$Member.SamAccountName
            $NTAccountName = $Member.NTAccountName
            $UserPrincipleName =$Member.userprincipalname

            $query = "INSERT INTO IVISTA_RBAC (Group_Name, SamAccountName,NTAccountName,UserPrincipalName)  
                                        VALUES ('$GroupName','$SamAccountName','$NTAccountName', '$UserPrincipleName')"

            $command=$connection.CreateCommand()
            $command.CommandText=$query
            $ds = New-Object system.Data.DataSet
            $da = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
            [void]$da.fill($ds)
        } 
}
$connection.Close()

上面的代码会将数据插入表中，但在插入之前不会从表中删除数据，

标签： .netoraclepowershellodac

解决方案

在此代码段中，您已将ExecuteNonQuery注释掉，但这是您需要使用的命令，而不是ExecuteReader.

 $query = "DELETE FROM TableName"
 $command=$connection.CreateCommand()
 $command.CommandText=$query
 #$command.ExecuteNonQuery()
 $command.ExecuteReader()

所以它应该是这样的：

 $query = "DELETE FROM TableName"
 $command=$connection.CreateCommand()
 $command.CommandText=$query
 $command.ExecuteNonQuery()

推荐阅读