Laurent Schneider

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 or document or login screen,
[⊞+R] [e] [enter] [CTRL+V]
4 keystrokes (+ 2 mod)

Explanation :

C:\>powershell -noprofile
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\> $o=New-Object DirectoryServices.DirectorySearcher;
### new object to browse Microsoft AD
PS C:\> $o.SearchRoot=New-Object DirectoryServices.DirectoryEntry;
### the base dn
PS C:\> $o.Filter='samaccountname='+$ENV:USERNAME;
### the search filter, your Username
PS C:\> $o.FindOne().Properties.mail
### find one (not necessarly 100% safe) and print the mail property
laurentschneider@example.com

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 ename from emp",$connection)
$reader = $command.ExecuteReader()
$a = @()
while ($reader.Read()) {
  $a = $a + $reader.GetString(0)
}
$connection.close()

last, let’s create a simple window (a Form) with a list (a List box) where you can select an item with a doubleclick.

Initialize the list with the array from EMP

$form = New-Object Windows.Forms.Form
$form.Text = "Select employee !"
$form.Size = New-Object Drawing.Size(640,480)
$form.StartPosition = "CenterScreen"
$listbox = New-Object Windows.Forms.ListBox
$listbox.Location = New-Object Drawing.Point(10,10)
$listbox.Size = New-Object Drawing.Size(620,460)
$listbox.Items.AddRange($a)
$listbox.Add_DoubleClick({$form.Close();})
$form.Controls.Add($listbox)
$form.Topmost = $True
$form.Add_Shown({$form.Activate()})
[void] $form.ShowDialog()

Show the result (or use it in your powershell scripts)

PS> $listbox.SelectedItems[0]
SCOTT

Pretty cool! No compiler needed, directly run from the powershell prompt

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 sysdba" @?/rdbms/admin/csminst
csscan "'sys/sys as sysdba'" full=y tochar=we8mswin1252 array=1024000 process=5
sqlplus "/ as sysdba" @?/rdbms/admin/csalter.plb

It is not always that straight forward, check output from csscan (scan.*) carefully before running csalter.

Ok, now retry

H:\>set NLS_LANG=american_america.we8pc850

H:\>sqlplus.exe scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Thu May 10 11:28:01 2012

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

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

SQL> select chr(128) from dual;

C
-
■

Not good!

Obviously, the PC850 client character is not good enough. Let’s switch to mswin1252 on the client.

H:\>chcp 1252
Active code page: 1252

H:\>set NLS_LANG=american_america.we8mswin1252

H:\>sqlplus.exe scott/tiger

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

SQL> select chr(128) from dual;

C
-
Ç

Well, what’s missing now? The font ! Let’s change it from “Raster Fonts” to “Lucida Console”. Either by clicking on the command com properties, or even dynamically with that gem (tested on XP) !

H:\>type Lucida.cs
using System;
using System.Runtime.InteropServices;

public class Lucida
{
  const int STD_OUT_HANDLE = -11;

  [DllImport("kernel32.dll", SetLastError = true)]
  static extern int SetConsoleFont(IntPtr hOut, uint dwFontSize);

  [DllImport("kernel32.dll", SetLastError = true)]
  static extern IntPtr GetStdHandle(int dwType);

  public static void Main()
  {
    SetConsoleFont(GetStdHandle(STD_OUT_HANDLE), 6);
  }
}

H:\>csc Lucida.cs
Microsoft (R) Visual C# 2010 Compiler version 4.0.30319.1
Copyright (C) Microsoft Corporation. All rights reserved.
H:\>Lucida

H:\>sqlplus.exe scott/tiger

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

SQL> select chr(128) from dual;

C
-
€

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 a for a Machine-wide setting and USER is for a USER-wide setting.

I hope it will be of some use

Completly unrelated, Enterprise Manager 12c for AIX is out. Do not wait it for HPUX, it is not planned. For Windows 64bit, it will come “between March and May 2012″ according to note 793512.1 on metalink

I have never been a Microsoft fanatic nor an anti-microsoft terrorist, but today I could not believe that large compressed folders got corrupted in Windows !

I have send a relatively small zip file (5gb, peanuts) from AIX to Windows per sftp and in Windows Explorer, some files in the compressed folder (read zip) were just pointing to the wrong content.

I had some issues with large zip files on unix, but this was last century! Howcome could a modern filesystem/operating system have such issues?

I have found a few bugs on support.microsoft.com.

Ex: Compressed folder becomes corrupted when larger than 2 gigabytes
Workaround : make sure that you limit the size of a compressed folder to 2 GB or less

Amazing!

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 file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

Windows:

SQL>  @.[%NLS_LANG%]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8ISO8859P1]"

It could well be that both return the same answer, but not necessarly, as shown above.

The unix discrepancy is related to the subshell created by HOST. The subshell may read some .profile and overwrite the value of NLS_LANG

In Windows, the NLS_LANG setting may be set by sqlplus according to some registry entries

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> $res

hello world

Pretty cool

I have been using Vista for about 3 months and finally found a way to start my databases with a single click

The method I used until yesterday was a .BAT file that starts the services, I had then to right click on the shortcut, run as administrator, confirm the UAC warning. 3 Clicks. Way to much…

I have googled quite a lot and found Vista setuid – How to elevate without prompting

Ok, here we go :

1) run mmc
2) file – add snapin
3) Task Scheduler Library – Create Task
4) Name: startoracle
Run with highest privileges
Actions – New – Start a program – Program:net – Arguments: start OracleOraDb11g_home1TNSListener
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC01
Actions – New – Start a program – Program:net – Arguments: start OracleServiceLSC02
…
5) create a batch file that runs : schtasks /run /tn startoracle

Same for stoporacle

It seems to work fine. Do not play too much with mmc if you do not know it. It is a powerful tool

It is quite a while I have not posted about Linux. The reason is I have bought in new notebook three months ago with Vista and did not have a program to resize the partition…

Do I need a program?
NO, I do not !!!

Vista has disk management that can resize my online partition.

Run: compmgmt.msc /s
Or go to control panel.

And shrink/extend your volumes!