Error when initiating New-Oracle_connection

Jul 8, 2010 at 6:50 PM
Hi, Apologies in advance if this has been covered or i just missed the info, but I couldn't find anything off of the Documentation tab re: any prereq's for the oracleclient or oracleISE usage. I'm receiving the following after loading the modules and initiating a new oracle connection: PS C:\Workspace\#[DISCONNECTED]: > New-Oracle_connection -tns xxxxxx.world -user xxxxxx_user -password xxxxxx Exception calling "Open" with "0" argument(s): "" At \\xxxxxx\lavoieph$\My Documents\WindowsPowerShell\Modules\OracleClient\new-connection.ps1:34 char:12 + $conn.Open <<<< () + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException ClientId : ConnectionString : Data Source=xxxxxx.world;User ID=xxxxxx_user;Password=xxxxxx ConnectionTimeout : 15 Database : DataSource : xxxxxx.world ServerVersion : State : Closed Site : Container : I seem to have the GAC entries as follows: [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") GAC Version Location --- ------- -------- True v2.0.50727 C:\WINDOWS\assembly\GAC_32\Oracle.DataAccess\2.102.2.20__89b483f429c47342\Oracle.DataAccess.dll True v2.0.50727 C:\WINDOWS\assembly\GAC_32\System.Data.OracleClient\2.0.0.0__b77a5c561934e089\System.Data.OracleClient.dll I'm not sure if this assists, but plsql, tnsping, tnsnames, toad, etc are all operational. any guidance as to what i'm missing or doing wrong would be greatly appreciated as this would prove to be an awesome tool & thanks for all of the great sqlpsx work.
Developer
Jul 9, 2010 at 9:56 PM
Edited Jul 10, 2010 at 10:59 AM
Wellcome, you are the first who gives me any feedback on the oracle module. Thanks for your interest in the Oracle version of SQLPSX. Oracle.DataAccess v2.0.50727 is the version I'm using too. Please try the following minimal script (with your true credentials of course) and tell me if it works. [System.Reflection.Assembly]::LoadWithPartialName("Oracle.DataAccess") $ConnectionString = "Data Source=xxxxxx.world;User ID=xxxxxx_user;Password=xxxxxx" $conn=new-object Oracle.DataAccess.Client.OracleConnection $conn.ConnectionString=$ConnectionString $conn.Open() $conn For me the result is somethinglike: ClientId : ConnectionString : Data Source=xxxxxx.world;User ID=xxxxxx_user; ConnectionTimeout : 15 Database : DataSource : xxxxxx.world ServerVersion : 9.2.0.7.0 State : Open Site : Container : If the the open () succeeds, you can close the connection with $conn.close() BTW you can reach me on twitter: @bernd_k Bernd Edited 10/07/2010: With this code I get specific error messages, when I mistype the tnsname, or the user or password: Exception calling "Open" with "0" argument(s): "ORA-12154: TNS: Angegebener Connect Identifier konnte nicht aufgelöst werden" resp. Exception calling "Open" with "0" argument(s): "ORA-1017: Benutzername/Kennwort ungültig; Anmeldung abgelehnt" Hope this helps Bernd
Developer
Jul 13, 2010 at 9:38 PM

Yes I start believing that something is missing.

Here follows another test script, this time not depending on ODAC, but on System.Data.OracleClient. Please try it and tell me the result.

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$ConnectionString = "Data Source=xxxxxx.world;User ID=xxxxxx_user;Password=xxxxxx"
$conn=new-object System.Data.OracleClient.OracleConnection
$conn.ConnectionString=$ConnectionString
$conn.Open()
$sql = "SELECT upper(user) || '@' || global_name global_name FROM global_name"
$cmd=new-object System.Data.OracleClient.OracleCommand($sql,$conn)

# $cmd.CommandTimeout=$timeout

$ds=New-Object system.Data.DataSet
$da=New-Object System.Data.OracleClient.OracleDataAdapter($cmd)
$da.fill($ds)

$conn.close()

$ds.tables[0]

Jul 15, 2010 at 5:41 PM

This was after my re-installation of the oracle 10g ODT with ODAC from OTN to try and thwart any oracle ugliness that may have bled into my system. Apologies if this is a witch-hunt, if there were prescribed steps you did to set up your system, I can just follow those. Thanks for all the help Bernd!

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
$ConnectionString = "Data Source=xxxxxx.world;User ID=xxxxxx_user;Password=xxxxxx"

$conn=new-object System.Data.OracleClient.OracleConnection
$conn.ConnectionString=$ConnectionString
$conn.Open()
$sql = "SELECT upper(user) || '@' || global_name global_name FROM global_name"
$cmd=new-object System.Data.OracleClient.OracleCommand($sql,$conn)

# $cmd.CommandTimeout=$timeout

$ds=New-Object system.Data.DataSet
$da=New-Object System.Data.OracleClient.OracleDataAdapter($cmd)
$da.fill($ds)

$conn.close()

$ds.tables[0]

GAC    Version        Location                                                                                                   
---    -------        --------                                                                                                   
True   v2.0.50727     C:\WINDOWS\assembly\GAC_32\System.Data.OracleClient\2.0.0.0__b77a5c561934e089\System.Data.OracleClient.dll
Exception calling "Open" with "0" argument(s): "OCIEnvCreate failed with return code -1 but error message text was not available.
"
At line:5 char:11
+ $conn.Open <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 
Exception calling "Fill" with "1" argument(s): "OCIEnvCreate failed with return code -1 but error message text was not available.
"
At line:13 char:9
+ $da.fill <<<< ($ds)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Developer
Jul 16, 2010 at 12:17 PM

Going to try ODT next days. My main installation is based on win32_11gR1_client. Yesterday i succeed to to install ODAC1110720Xcopy.zip in a XP SP3  VM. it was a bit tricky  MSVCP71.dll was missing

Developer
Jul 23, 2010 at 7:03 AM
Today tried ODT from 11g. on XP Sp3 in Virtual Box. Only basics to run PowerShell V2 install, than added ODAC. Configured TNSnames on and it run. Sorry I have no more ideas.
Sep 15, 2010 at 4:38 AM

There's no way to connect to oracle without ODAC? This is something I've been asking for a while. I have several scripts that accesses my company's oracle database but every time i give someone the script they have to download ODAC and install it. It's not a big deal to install it but it's nicer if there's a installer-less solution.

Developer
Sep 21, 2010 at 11:39 AM

I believe it that it is possible to use OLEDB instead. But I'm rather sure that that needs some installation of Orcale components too. I do not think, that there ever be installer-less connection to Oracle DB s from Windows.

 

Coordinator
Sep 21, 2010 at 11:55 PM

Some type of Oracle client installation is necessary for the connectivity components, although I've seen JDBC based solutions that do not require an install like SQLPAL http://sqlpal.com

I'm not sure if you can connect a .NET based (PowerShell) app via JDBC.