Script Run Oracle Query

Hi,

I would like to know how can a execute a script to run a query in Oracle and then send an email. Can we do with Powershell? could you help me to know how to do it?

PowerShell V2


Thanks in advance,
LVL 1
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
There are a many examples out there on connecting to Oracle from Powershell:
http://guyharrison.typepad.com/oracleguy/2008/01/accessing-oracl.html
http://dba-oracle.com/t_windows_powershell_scripting.htm

Have you looked around already?
What have you tried?
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
Is the application server dedicated for scripts.

The database server and the email server are in the same network.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
slightwv (䄆 Netminder) Commented:
>>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?
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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.
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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()

Open in new window


This is the prompt:
Prompt
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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()

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
executescalar returns one value from one row.

To loop through them all, you need a data reader and a loop.  Check the second link.
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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 :)

Added code
0
 
slightwv (䄆 Netminder) Commented:
What isn't working?

Also, I don't think you need the for loop, just the reader.
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
I removed the loop, I leave just the Reader but I cannot see the fields

This is the powershell result all like this:

Image

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()

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>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 "
}
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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()

Open in new window


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

FieldCount : 1


FieldCount : 1


FieldCount : 1


FieldCount : 1
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
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

Open in new window

on.Close()
0
 
slightwv (䄆 Netminder) Commented:
>>$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.
0
 
Gonzalo BecerraSharePoint - Technical Lead for Operations & Engineering Team - Superrvising AssociateAuthor Commented:
It's Working, Thank you very much for your help! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.