Remotely connect to oracle database by powershell

Question

Below is my Oracle query which is running via PowerShell. It is working fine with no error if I am running it locally on that machine.

[Reflection.Assembly]::LoadFile("E:\oracle\product\11.2.0\ODP.NET\bin\2.x\Oracle.DataAccess.dll")

$constr = "User Id=system;Password=pass;Data Source=API"
$conn= New-Object Oracle.DataAccess.Client.OracleConnection($constr)
$conn.Open()
$sql="select * from dba_users"
$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$reader=$command.ExecuteReader()

while($reader.Read()){
  $reader.GetString(0)
}

$conn.Close()

I want to run same query on other machine from this machine. I can say I want to run it remotely.

When I run it I am getting this error:

Exception calling "Open" with "0" argument(s): "ORA-12541: TNS:no listener"

I can't add entry in Tnsora file.

Can anybody advise me any alternative way to achive this?

Martin Brandl · Accepted Answer · 2015-08-12 07:25:31Z

1

If you can't modify the tnsora, you have to use either EZConnect or the connect descriptor as your connection string. Try this:

$userId = 'system'
$password = 'pass'
$host = 'ip or hostname'
$port = '1521'
$serviceName = 'Your service name'

$constr = "User Id=$userId;Password=$password;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))(CONNECT_DATA=(SERVICE_NAME=$serviceName)))"

answered Aug 12, 2015 at 7:25

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Roxx Over a year ago

Thanks for your answer. I will try and let you know.

Roxx Over a year ago

One more question $serviceName = 'Your service name'. What do i need to put in service name. It would be database name or something else

Martin Brandl Over a year ago

Yes, the database name. For further information about the connect descriptor I suggested you to use, look at the link I provided you ;-)

Collectives™ on Stack Overflow

Remotely connect to oracle database by powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related