none
Oracle Client in powershell RRS feed

  • Question

  • Hi all,

    I have to fetch the data from notepad and have to modify it into an insert query and have to insert to a table.

    Normally it is running fine. The code that is running fine is 

    Add-Type -Assembly System.Data.OracleClient
     
    $connectionString = "Data Source=XXXXXXXXX:1521/sms ; User ID=SXXXXX; Password=XXXXX"
    
    $connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
    $connection.Open()
    
    
    $datas = gc 'D:\MPAYdatatill(6-JAN-2016).txt'
    
    foreach($data in $datas)
    {
    
    $values= $data.Split('|')
    [string]$text = ""
    for($i =0 ; $i -lt $values.Count;$i++)
    {
    if ($i -eq ($values.Count - 1)) {
    $text = $text +  "'" + $values[$i] + "'"
    break
    }
    $text = $text +  "'" + $values[$i] + "'" + "," 
    
    
    }
    $SQLQuery = 'insert into MPAY_USER_LIST(MOB_NO,CUST_ID,ACNO1,DATE_REG,STATUS)VALUES(' + $text + ')'
    
      try
    {
    
    $SQLCommand = New-Object System.Data.OracleClient.OracleCommand($SQLQuery,$connection)
    
    $SQLReader = $SQLCommand.ExecuteReader()
    }
    catch
    {
    Write-Host $_
    
    }
      }
    
    
      $connection.Close()
    
    
    
    '
    
    
    
    
    
    What i found from my collegues is that it is taking time to finish as it is doing autocommit for each insert command. The yare suggesting to use begintransaction and commit transaction. I have tried googling but didnot found how to do it for powershell. Any help is most appreciable


    Naveen Basati

    • Moved by Bill_Stewart Wednesday, February 10, 2016 5:43 PM This is not third-party software support forum
    Friday, January 8, 2016 11:54 AM

All replies

  • transactions are part of Oracle and have nothing to do with PowerShell.

    You can more easily and quickly use the bulf insert utility of the Oracle client.

    ExecuteReader does not insert data.  You need to rethink what you are doing.

    https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.executenonquery%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396


    \_(ツ)_/


    • Edited by jrv Friday, January 8, 2016 3:05 PM
    Friday, January 8, 2016 3:01 PM
  • Here is how to start a transaction: https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.begintransaction(v=vs.110).aspx

    Here is how to do a transacted and batched insert:

    Add-Type -Assembly System.Data.OracleClient
    
    $connectionString = "Data Source=XXXXXXXXX:1521/sms ; User ID=SXXXXX; Password=XXXXX"
    $connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
    $connection.Open()
    
    $query = 'insert into MPAY_USER_LIST(MOB_NO,CUST_ID,ACNO1,DATE_REG,STATUS)VALUES('@MOB_NO','@CUST_ID','@ACNO1','@DATE_REG','@STATUS')'
    $cmd = $connection.CreateCommand()
    $cmd.CommandText=$query $mob_no=$cmd.Parameters.Add('@MOB_NO') $cust_id=$cmd.Parameters.Add('@CUST_ID') $acno=$cmd.Parameters.Add('@ACNO1') $date_reg=$cmd.Parameters.Add('@DATE_REG') $status=$cmd.Parameters.Add('@STATUS') # to transact $trans=$connection.BeginTransaction() $data = Import-Csv 'D:\MPAYdatatill(6-JAN-2016).txt' -Delimiter '|' -Header MOB_NO, CUST_ID, ACNO1, DATE_REG, STATUS try { foreach($row in $data){ $mob_no.Value=$row.MOB_NO # ... place rest of assignments here: $status.Value=$row.Status #execute insert $cmd.ExecuteNonQuery() } $trans.Commit() } catch{ $trans.Rollback() Write-Host $_ } $connection.Close()

    If you use text it will recompile the insert every insert. This is very slow and prone to bad behaviors.

    The exact creation of the parameters may have onto be adjusted for data types.


    \_(ツ)_/


    • Edited by jrv Friday, January 8, 2016 3:28 PM
    Friday, January 8, 2016 3:23 PM