Script Run Oracle Query

Is the database server on the same network as an email relay or email server?  You can have the database do it all using UTL_MAIL for simple emails or UTL_SMTP for more complex emails.

Is the application server dedicated for scripts.

The database server and the email server are in the same network.

>>Is the application server dedicated for scripts.

I'm not sure what you are asking here.

>>The database server and the email server are in the same network.

Can you use UTL_MAIL for what you are wanting to do or do you still need some script?

I still need an script to connect from Powershell to Oracle and run a Query, because we will re use the information of the query also.

Thank you, I just read the articles but the query in oracle is not working yet.



This is the script from Powershell that I'm trying to use:

$connectionString = "Data Source=RTED;User Id=USER;Password=Password;
Integrated Security=no"
[System.Reflection.Assembly]::
   LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object
   System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT * from Res"
$command = new-Object
   System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo "Number of employees: "$employeesNames
$connection.Close()

But I received the following error:


Unexpected token '::' in expression or statement.
At C:\WINDOWS\TEMP\9ff4dec2-4843-4130-b83f-aaffaee98d4c.ps1:3 char:31
+ [System.Reflection.Assembly]:: <<<< 
    + CategoryInfo          : ParserError: (:::String) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken

Is it a line wrap issue?  Try moving it onto the same line.

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

I've not done a lot with Powershell but I think it is pretty harsh when it comes to syntax.

yep, now I'm receiving the following prompt:

This is the script now:

$connectionString = "Data Source=Test;User Id=User;Password=Password;
Integrated Security=no"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object
   System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT Res_ID from Res"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo "Resources: "$employeesNames
$connection.Close()

Select allOpen in new window

This is the prompt:

I just moved some lines and it's working now, the only remaining think is if I do Select * from Table I'm only receiving the first result of the table. How can I do to see the complete results from the select?


This is the script now:

$connectionString = "Data Source=DataSource;User Id=User;Password=Password;
Integrated Security=no"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT Res_ID from Res"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo "Resources: "$employeesNames
$connection.Close()

Select allOpen in new window

executescalar returns one value from one row.

To loop through them all, you need a data reader and a loop.  Check the second link.

I just read the the second article and I added the Reader, I think I'm still missing something to make it work. Thanks a lot for your help :)

What isn't working?

Also, I don't think you need the for loop, just the reader.

I removed the loop, I leave just the Reader but I cannot see the fields

This is the powershell result all like this:



This is the script:

$connectionString = "Data Source=DataSource;User Id=User;Password=Password;
Integrated Security=no"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT Res_ID from Res"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteReader()
echo "Resources: "$employeesNames
$connection.Close()

Select allOpen in new window

>>I removed the loop

I meant the 'for' loop, not the reader loop...

Remove this code:
for ($i=0;$i -lt $reader.FieldCount;$i++) {
    Write-Host  $reader.GetName($i) $reader.GetDataTypeName($i)
}


Keep this code:
# Write out the results
while ($reader.read()) {
    $username=$reader.GetString(0)  
    $userid=$reader.GetDecimal(1)
    $createDate=$reader.GetDateTime(2)
   
    Write-Host "$userid $username $createDate "
}

thank you, I leave the script:

$connectionString = "Data Source=DataSource;User Id=User;Password=Password;
Integrated Security=no"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT Res_ID from Res"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$resources = $command.ExecuteReader()
# Write out the results 
Write-Host $reader
while ($reader.read()) {
    $username=$reader.GetString(0)  
    $userid=$reader.GetDecimal(1)
    $createDate=$reader.GetDateTime(2)
    
    Write-Host "$userid $username $createDate "
}
echo "Resources: "$Resources
$connection.Close()

Select allOpen in new window

But I'm still receiving the results as my previous image:

FieldCount : 1


FieldCount : 1


FieldCount : 1


FieldCount : 1

Question regarding this part:


while ($resources.read()) {
    $username=$resources.GetDecimal(1)
    Write-Host "$username "
}

The field that I'm trying to get is APP_ID the value is decimal for example: 101583



What I should add here? is ok?  the field name is APP_ID

$username=$resources.GetDecimal(1)

I'm receiving the following error now:

Exception calling "GetDecimal" with "1" argument(s): "Index was outside the bounds of the array."
At C:\WINDOWS\TEMP\9ff4dec2-4843-4130-b83f-aaffaee98d4c.ps1:11 char:36
+     $username=$resources.GetDecimal <<<< (1)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException


This is the script:

$connectionString = "Data Source=DataSource;User Id=User;Password=Password;
Integrated Security=no"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = "SELECT res_id from Res"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$resources = $command.ExecuteReader()
# Write out the results 
while ($resources.read()) {
    $username=$resources.GetDecimal(1)
    Write-Host "$username "
}
echo "Resources: "$Resources
$connecti

Select allOpen in new window

on.Close()

>>$username=$resources.GetDecimal(1)

The reader returns an array based on the columns selected.  0 is the first column returned, 1 the second, etc...

If you only return one column and it is a decimal:
$username=$resources.GetDecimal(0)


I encourage you to take a few minutes and understand what the code is doing and not just copy/paste/run.  Once you understand what it is doing, it will make the project easier.

It's Working, Thank you very much for your help! :)