Categories
c# powershell sqlnet

specify TNSNAMES for one program

Monday I wrote on tnsping.exe inconsistencies. Actually there is one good thing in having Oracle Client on Windows looking in the current directory first : you can set one tnsnames for a specific shortcut ! It is quite a viable alternative to .bat files with set TNS_ADMIN=path.

Demo :

First I create a small EXE in C#

HelloWorld.cs:

using System;
using System.Threading;
using Oracle.DataAccess.Client;

class HelloWorld
{
static void Main() {
OracleConnection connection=
new OracleConnection("Data Source=DB01; User Id=scott; password=tiger");
try {
connection.Open();
Console.WriteLine("Msg: " + (new OracleCommand(
"select * from global_name",connection)).ExecuteScalar());
connection.Close();
} catch(Exception e) {
Console.WriteLine("Exception Occured :{0}",e.Message);
} finally {
connection.Dispose();
}
Thread.Sleep(5000);
}
}

Compile

C:\Windows\Microsoft.NET\Framework\v4.0.*\csc.exe /R:C:\oracle\product\11.2.0\client_1\odp.net\bin\4\Oracle.DataAccess.dll HelloWorld.cs

Create a specific tnsnames and sqlnet

Tnsnames.ora

DB01.example.com=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DB01)(PORT=1521)))(CONNECT_DATA=(SID=DB01)))

SQLNET.ora

NAMES.DIRECTORY_PATH=TNSNAMES
NAMES.DEFAULT_DOMAIN=EXAMPLE.COM

test

C:\TEMP> HelloWorld
Msg: DB01.EXAMPLE.COM

To create a desktop icon with the correct WorkingDirectory with powershell

PS> $ws = New-Object -comObject WScript.Shell
PS> $desktop = [Environment]::GetFolderPath("Desktop")
PS> $s = $ws.createshortcut($desktop+"\HelloWorld.lnk")
PS> $s.TargetPath = "C:\TEMP\HelloWorld.exe"
PS> $s.WorkingDirectory = "C:\TEMP"
PS> $s.Save()

Categories
dba windows

sqlnet.ora, sqlplus.exe and tnsping.exe inconsistencies

if you use tnsping.exe and sqlplus.exe, the way the sqlnet.ora and tnsnames.ora are located differs

Let’s take the following setup


C:\tmp>type dir1\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.COM)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir1\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.com)(PORT=1521))(CONNECT_DATA=(SID=db01)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv1.example.org)(PORT=1521))(CONNECT_DATA=(SID=db01)))

C:\tmp>type dir2\sqlnet.ora
NAMES.DEFAULT_DOMAIN = (EXAMPLE.ORG)
NAMES.DIRECTORY_PATH = (TNSNAMES)

C:\tmp>type dir2\tnsnames.ora
db.example.com=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
db.example.org=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.org)(PORT=1521))(CONNECT_DATA=(SID=db02)))

You set TNS_ADMIN to dir1 and your current directory is dir2.

Let’s try TNSPING.EXE first

C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>tnsping db

TNS Ping Utility for 64-bit Windows: Version 12.1.0.1.0 - Production on 25-NOV-2013 15:47:31

Copyright (c) 1997, 2013, Oracle. All rights reserved.

Used parameter files:
C:\tmp\dir1\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.example.com)(PORT=1521))(CONNECT_DATA=(SID=db02)))
OK (0 msec)

TNSPING.EXE is using the sqlnet.ora in %TNS_ADMIN% directory (EXAMPLE.COM domain) and the tnsnames.ora in the current directory (db02)

Let’s try with sqlplus

C:\tmp>cd dir2

C:\tmp\dir2>set TNS_ADMIN=C:\tmp\dir1

C:\tmp\dir2>sqlplus -L system@db

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 25 16:01:15 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------
DB02.EXAMPLE.ORG

SQLPLUS.EXE is using the sqlnet.ora in the current directory (EXAMPLE.ORG) and the tnsnames.ora in the current directory (db02)

This does not reproduce on Linux

Categories
powershell

unreadable output file in powershell

If you redirect a not-completly-string output (like a spfile) to a file in powershell, you may not see the same in the file as in the output

  • without redirection

    PS C:\> Select-String "compatible" .\spfileDB01.ora
    spfileDB01.ora:13:*.compatible='11.2.0.4.0'
  • with redirection

    PS> Select-String "compatible" .\spfileDB01.ora > compatible.txt
    PS> vim -b .\compatible.txt
    ÿþ^M^@
    ^@s^@p^@f^@i^@l^@e^@D^@B^@0^@0^@1^@.^@o^@r^@a^@:^@1^@3^@:^@*^@.^@c^@o^@m^@p^@a^@t^@i^@b^@l^@e^@=^@'^@1^@1^@.^@2^@.^@0^@.^@4^@.^@0^@'^@^M^@
    ^@
  • With redirection and conversion to ascii

    PS> Select-String "compatible" .\spfileDB01.ora |
    Out-File -Encoding ASCII .\compatible.txt

    PS> vim .\compatible.txt

    spfileDB01.ora:13:*.compatible='11.2.0.4.0'

With Out-File (instead of >), you can specify the encoding, which does the trick