Insert to oracle table using powershell

Question

I want to write a oracle table using powershell. I have written the following code.

    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connectionString = "User Id=XXX;Password=XXX;Data Source=YYY;"
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)

$app_id="SP_ERROR_ALERT"
$cat="CAT151"
$stat="I"
$phoneno="0123456789"
$body="Hard drive free space running low - SP"
$outid="SLIC%20LIFE"
$desc="Free space"

$connection.Open()

$queryString = "insert into DB.TABLE (APPLICATION_ID, JOB_CATEGORY, SMS_TYPE, MOBILE_NUMBER, TEXT_MESSAGE, SHORT_CODE, JOB_OTHER_INFO) VALUES (:one, :two, :three, :four, :five, :six, :seven) "

$command.Parameters.Add("one", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["one"].Value = $app_id

$command.Parameters.Add("two", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["two"].Value = $cat

$command.Parameters.Add("three", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["three"].Value = $stat

$command.Parameters.Add("four", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["four"].Value = $phoneno

$command.Parameters.Add("five", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["five"].Value = $body

$command.Parameters.Add("six", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["six"].Value = $outid

$command.Parameters.Add("seven", [System.Data.OracleClient.OracleType]::VarChar)
$command.Parameters["seven"].Value = $desc

$command = New-Object System.Data.OracleClient.OracleCommand
$command.Connection = $connection
$command.CommandType = [System.Data.CommandType]::Text
$command.CommandText = $queryString
$command.ExecuteNonQuery()

$connection.Close()

When I execute the script I get an error saying "Exception calling "ExecuteNonQuery" with "0" argument(s): "ORA-01008: not all variables bound". What am I doing wrong here.

Thanks in advance

I think you will find the answer in this pos: http://stackoverflow.com/questions/11048910/oraclecommand-sql-parameters-binding — CeOnSql, Oct 24 '16 at 06:16
It's .Net Framework basically. You should take a look at how to add a parameter: `Parameters.Add(new OracleParameter("variableName", theValue));` — CeOnSql, Oct 24 '16 at 06:43
I added the parameters like i used to do in .NET $pone=New-Object System.Data.OracleClient.OracleParameter $pone.ParameterName = "one" $pone.Value = $app_id $command.Parameters.Add($pone) Still no luck — Anshu, Oct 24 '16 at 07:21
`$command = New-Object System.Data.OracleClient.OracleCommand` is in wrong place. Put it after `queryString`. — Arkadiusz Łukasiewicz, Oct 24 '16 at 07:21
Already you create your command after your parameters creation, that's wrong — Esperento57, Oct 24 '16 at 07:22
As Arka... mentiond. First the command, then its parameters... Also, why no size for the Varchar2 data items? You should have a size corresponding to the target column width e.g. $command.Parameters.Add("one", [System.Data.OracleClient.OracleType]::VarChar), 32) or whatever the size is. I solve this with a function that forces me to provide a size if the OracleDbType of the parameter is Varchar2... — Allen, Jan 06 '21 at 15:24

score 4 · Accepted Answer · answered Oct 24 '16 at 09:23

you can reduct your code like this:

   [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

   $connectionString = "User Id=XXX;Password=XXX;Data Source=YYY;"
   $connection = $null
   $command = $null

   Try
   {
       $queryString = "insert into SMS.SMS_GATEWAY (APPLICATION_ID, JOB_CATEGORY, SMS_TYPE, MOBILE_NUMBER, TEXT_MESSAGE, SHORT_CODE, JOB_OTHER_INFO) VALUES (:one, :two, :three, :four, :five, :six, :seven) "

       $connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
       $command = New-Object System.Data.OracleClient.OracleCommand -ArgumentList $queryString, $connection  

       $connection.Open()

       $command.Parameters.Add("one", "SP_ERROR_ALERT")
       $command.Parameters.Add("two", "CAT151")
       $command.Parameters.Add("three", "I")
       $command.Parameters.Add("four", "012345679")
       $command.Parameters.Add("five", "Hard drive free space running low - SP")
       $command.Parameters.Add("six", "SLIC%20LIFE")
       $command.Parameters.Add("seven", "Free space")

       $command.ExecuteNonQuery()
   }
   Finally
   {
       if ($connection -ne $null) 
       {
          $connection.Close()
          $connection.Dispose()
       }

       if ($command -ne $null) 
       {
          $command.Dispose()
       }

   }

I am sorry but I cannot see why ignoring the (max/limit) size of the varchar2 field and relying on the actual data hard-wired is a good idea? Please clarify. — Allen, Jan 06 '21 at 15:25

Anshu · Answer 2 · 2016-10-27T04:15:26.753

With the help of comments I was able to fix the issue i was having.

Here's my working code.

    [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")

$connectionString = "User Id=XXX;Password=XXX;Data Source=YYY;"
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)

Try
{
$queryString = "insert into SMS.SMS_GATEWAY (APPLICATION_ID, JOB_CATEGORY, SMS_TYPE, MOBILE_NUMBER, TEXT_MESSAGE, SHORT_CODE, JOB_OTHER_INFO) VALUES (:one, :two, :three, :four, :five, :six, :seven) "
    $command = New-Object System.Data.OracleClient.OracleCommand
    $command.Connection = $connection
    $command.CommandType = [System.Data.CommandType]::Text
    $command.CommandText = $queryString



    $app_id="SP_ERROR_ALERT"
    $cat="CAT151"
    $stat="I"
    $phoneno="012345679"
    $body="Hard drive free space running low - SP"
    $outid="SLIC%20LIFE"
    $desc="Free space"

    $connection.Open()


    $pone=New-Object System.Data.OracleClient.OracleParameter
    $pone.ParameterName = "one"
    $pone.Value = $app_id

    $ptwo=New-Object System.Data.OracleClient.OracleParameter
    $ptwo.ParameterName = "two"
    $ptwo.Value = $cat

    $pthree=New-Object System.Data.OracleClient.OracleParameter
    $pthree.ParameterName = "three"
    $pthree.Value = $stat

    $pfour=New-Object System.Data.OracleClient.OracleParameter
    $pfour.ParameterName = "four"
    $pfour.Value = $phoneno

    $pfive=New-Object System.Data.OracleClient.OracleParameter
    $pfive.ParameterName = "five"
    $pfive.Value = $body

    $psix=New-Object System.Data.OracleClient.OracleParameter
    $psix.ParameterName = "six"
    $psix.Value = $outid

    $pseven=New-Object System.Data.OracleClient.OracleParameter
    $pseven.ParameterName = "seven"
    $pseven.Value = $desc

    $command.Parameters.Add($pone)
    $command.Parameters.Add($ptwo)
    $command.Parameters.Add($pthree)
    $command.Parameters.Add($pfour)
    $command.Parameters.Add($pfive)
    $command.Parameters.Add($psix)
    $command.Parameters.Add($pseven)

    $command.ExecuteNonQuery()
}
Catch 
{

}
Finally
{
    $connection.Close()
}

Insert to oracle table using powershell

2 Answers2