• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1750
  • Last Modified:

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,
0
Gonzalo Becerra
Asked:
Gonzalo Becerra
  • 10
  • 8
1 Solution
 
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 BecerraAuthor Commented:
Is the application server dedicated for scripts.

The database server and the email server are in the same network.
0
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Gonzalo BecerraAuthor 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
 
slightwv (䄆 Netminder) 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
 
Gonzalo BecerraAuthor 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 BecerraAuthor 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 BecerraAuthor 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 BecerraAuthor 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 BecerraAuthor 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 BecerraAuthor 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 BecerraAuthor 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 BecerraAuthor Commented:
It's Working, Thank you very much for your help! :)
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now