search
Categories
Sponsors
VirtualMetric Hyper-V Monitoring, Hyper-V Reporting
Archive
Blogroll

Badges
MCSE
Community

Cozumpark Bilisim Portali
Oracle database connection and query with PowerShell
Posted in Windows Powershell | 1 Comment | 12,225 views | 04/08/2012 20:46

Here is an example to open connection to Oracle with Powershell:

1
2
3
4
5
6
7
8
9
10
11
12
13
# Load Oracle Assembly
$LoadOracle = [Reflection.Assembly]::LoadFile("D:\oracle\product\10.2.0\db_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll")
 
# Database Information
$DBName = "oracledb"
$DBUsername = "system"
$DBPassword = "password"
$DBTable = "mydatabase"
 
# Connect to Oracle
$SQLConnString = "User Id=$DBUsername;Password=$DBPassword;Data Source=$DBName"
$SQLConnection = New-Object Oracle.DataAccess.Client.OracleConnection($SQLConnString)
$SQLConnection.Open()

Now we can try a query example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# INSERTION_DATE,VPN_ID,CALLING_NUMBER,CALLED_NUMBER,SESSION_TIME,DISCONNECT_CAUSE,VPN_NAME,STARTTIME,STOPTIME
$SQLQuery = "insert into " + $DBTable + " (INSERTION_DATE,VPN_ID,CALLING_NUMBER,CALLED_NUMBER,SESSION_TIME,DISCONNECT_CAUSE,VPN_NAME,STARTTIME,STOPTIME) values "
$SQLQuery += "(to_date('" + $INSERTION_DATE + "','mm/dd/yyyy hh24:mi:ss')"
$SQLQuery += ",'" + $VPN_ID + "'"
$SQLQuery += ",'" + $CALLING_NUMBER + "'"
$SQLQuery += ",'" + $CALLED_NUMBER + "'"
$SQLQuery += ",'" + $SESSION_TIME + "'"
$SQLQuery += ",'" + $DISCONNECT_CAUSE + "'"
$SQLQuery += ",'" + $VPN_NAME + "'"
$SQLQuery += ",to_date('" + $STARTTIME + "','mm/dd/yyyy hh24:mi:ss')"
$SQLQuery += ",to_date('" + $STOPTIME + "','mm/dd/yyyy hh24:mi:ss'))"
 
# IMPORT TO DATABASE
try
{
$SQLCommand = New-Object Oracle.DataAccess.Client.OracleCommand($SQLQuery,$SQLConnection)
$SQLReader = $SQLCommand.ExecuteReader()
}
catch
{
Write-Host $_
}

It’s similar to Microsoft SQL process on Powershell.


Comments (1)

Paulo

February 8th, 2013
01:25:48

I’m having a problem when execute the $SQLConnection.Open(). I receive the error message “ORA-12504 TNS Listener was not given the Service_name in Connect_data”.
Any ideas to help me with that?
Thanks.



Leave a Reply