Category Archives: windows

Dump TNSNAMES.ORA from ActiveDirectory

Having all connections string in ActiveDirectory is nice, but maybe you need sometimes to push it to an external system (e.g. DMZ or Linux). echo “# AD” > tnsnames.ora $o = New-Object DirectoryServices.DirectorySearcher $o.Filter = ‘objectclass=orclNetService’ foreach ($p in $o.FindAll().Properties) { [String]($p.name+”=”+$p.orclnetdescstring) >> tnsnames.ora } goodies

Oracle 18c/19c and ActiveDirectory

With Oracle 18c and even better in Oracle 19c, you can manage your Oracle database users in Active Directory. This was supposed to be a very nice new feature as many of us struggle with many thousand users spread over many versions, environments, platforms and even cloud or exadata. Is this going to help you? […]

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. […]

ODBC 32bits for Windows 64bits

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS. You will see there is another powershell, another registry, another ODBC tool, another Oracle client. %SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe First, we run powershell(x86) Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME ORACLE_HOME ORACLE_HOME_NAME ———– —————- C:\oracle\product\11.2.0\client_32 client32bit_11203 Only the Oracle 32bit client is displayed cmd /c “%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn “”Oracle […]

Oracle ODBC hello world with powershell

Demo : cmd /c “odbcconf.exe /a {configdsn “”Oracle in OraClient11g_home1″” “”DSN=helloworld|SERVER=DB01″”}” Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home. It is easy to get the Oracle Home name with Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME ORACLE_HOME ORACLE_HOME_KEY ———– ————— C:\oracle\product\11.1.0\client_1 OraClient11g_home1 C:\oracle\product\11.2.0\client_1 OraClient11g_home2 Then we create the connection (as […]

Default Oracle Home in Windows

In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry. Remember, NT means New Technology 🙂 There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure […]

[Windows] Email of current user

I hate having to type my email address, so I created a long one-liner to do the trick of getting my email from Exchange and copying it in my clipboard powershell -noprofile -command “$o=New-Object DirectoryServices.DirectorySearcher; $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;$o.Filter=’samaccountname=’+$ENV:USERNAME;write-host ($o.FindOne().Properties.mail)” | clip save this as “C:\WINDOWS\E.BAT” Then, when you have to enter your email in a form […]

My first .NET gui in Powershell

I managed to interface Oracle and a GUI via powershell. First, load the Oracle and the .NET assemblies [void] [Reflection.Assembly]::LoadFile(“C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll”) [void] [Reflection.Assembly]::LoadWithPartialName(“Drawing”) [void] [Reflection.Assembly]::LoadWithPartialName(“Windows.Forms”) Now, let’s retrieve EMP in a powershell array. I hope one of my reader will advise me on a better way 🙂 $connection=New-Object Oracle.DataAccess.Client.OracleConnection(“Data Source=DB01; User Id=scott; password=tiger”) $connection.open() $command=new-object Oracle.DataAccess.Client.OracleCommand(“select […]

EURO symbol, sqlplus, cmd.exe and various issues

One customer reported a not-correctly displayed Euro Symbol (€) in the database from sqlplus (msdos). Why? First, the character set did not support it. select * from v$nls_parameters where PARAMETER like ‘%CHARACTERSET%’; PARAMETER VALUE —————————— ————— NLS_CHARACTERSET WE8ISO8859P1 NLS_NCHAR_CHARACTERSET AL16UTF16 If you are still using WE8ISO8859P1, consider migrating to WE8MSWIN1252 using csalter sqlplus “/ as […]

change system wide PATH variable in Windows

This is utterly simple and mostly you did it with the mouse. To do it with command line and powershell, proceed this way. Open a powershell window powershell or if you have no right to edit the machine-wide environment, runas admin runas /user:yourdomain\youruserplusadmin powershell then access the environment assembly [environment]::setEnvironmentVariable(“PATH”,”c:\oracle\product\11.2.0\client_1\bin;C:\WINDOWS\;C:\WINDOWS\system32;C:\Program Files\Putty;C:\Program Files\Perforce;C:\Program Files\TortoiseSVN\bin;C:\WINDOWS\system32\WindowsPowerShell\v1.0″,”MACHINE”) MACHINE is […]

What is the current setting of NLS_LANG in sqlplus?

I just learnt a neat trick from Oracle Support. How do you see the current value of NLS_LANG in SQLPLUS ? HOST is not the right answer. E.g.: Unix: SQL> host echo $NLS_LANG AMERICAN_SWITZERLAND Windows: SQL> HOST ECHO %NLS_LANG% %NLS_LANG% The correct setting is revealed by @.[%NLS_LANG%] E.g.: Unix: SQL> @.[$NLS_LANG] SP2-0310: unable to open […]

What’s your favorite shell in Windows?

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC. PS> $stmt = “set hea off`n” PS> $stmt += “select ‘hello world’ from dual;” PS> $res = ($stmt | sqlplus -s scott/tiger) PS> […]