Title: Oracle TNSPING using TNS Packets and TCP
Question: I recently had the need to do an Oracle TNSPING programatically. After a bit of research I found that this is accomplished via TNS Packets to the Listener. An interesting side effect of this is that the Oracle Version number is returned in te response packet. This means that one can programatically determine the Oracle Version without a UserName/Passord logged in connection. Here is a class that implements TNSPING and Oracle Version using TNS Packets.
Answer:
In this simplified article I have only implemented TCP connections, and the HOST and PORT is required to be known. I have a larger class the overloads these calls to look up this info from TNSNAMES.ORA as an option as well as additional TNS features ...
function Ping(const AHost : string;
APort : integer) : integer; overload;
function Ping(const ATNSName : string) : integer; overload;
function OracleVersion(const AHost : string;
APort : integer) : string; overload;
function OracleVersion(const ATNSName : string) : string; overload;
procedure GetTNSConnections(AList : TStrings);
procedure GetTNSConnectData(const ATNSName : string;
out AProtocol,AHost : string;
out APort : integer);
property Error : string read FError;
property TNSPath : string read _GetTNSPath;
I can E-Mail this new class to interested parties.
Sorry guys, I am still unable to enter the NOT-EQUAL, GREATER-THAN and
SMALLER-THAN signs in this article. (WEB SITE ADMINISTRATOR TAKE NOTE)
Thus if you see NE or GT or LT in the code you know what to substitute
them with.
unit OracleTNS;
interface
// ==========================================================================
// Mike Heydon 2008
//
// Oracle TNS Component
//
// Methods Ping() and OracleVersion() are supported.
// Property Error stores last error message
//
// Written for Delphi 2006 and up.
// To Convert to 7 just move the types and constants out of the class
// back to the implementation section
// See notes for Indy 9
//
// In the response TNS packet of the tnsping command values (VSNNUM=?) and
// (ERR=?) values are held. An example of VSNNUM is 169869568. If we convert
// this number to hexadecimal we get $A200100. This is the Oracle version
// number in disguise, in this case 10.2.0.1.0
//
// Example of Input and Output packets of a TNS ping command
//
// Local Port (1521) opened
// Waiting for connections
// 308: Client connected; 10.0.1.94:2733
// 308: Connecting to Server
// 308: Connected to Server
// 308: Client to Server (87 bytes)
// 0000 00 57 00 00 01 00 00 00 01 36 01 2C 00 00 08 00 .W.......6.,....
// 0010 7F FF A3 0A 00 00 01 00 00 1D 00 3A 00 00 00 00 ...........:....
// 0020 00 00 00 00 00 00 00 00 00 00 00 00 0B C4 00 00 ................
// 0030 00 0D 00 00 00 00 00 00 00 00 28 43 4F 4E 4E 45 ..........(CONNE
// 0040 43 54 5F 44 41 54 41 3D 28 43 4F 4D 4D 41 4E 44 CT_DATA=(COMMAND
// 0050 3D 70 69 6E 67 29 29 =ping))
// 308: Server to Client (73 bytes)
// 0000 00 49 00 00 04 00 00 00 22 00 00 3D 28 44 45 53 .I......"..=(DES
// 0010 43 52 49 50 54 49 4F 4E 3D 28 54 4D 50 3D 29 28 CRIPTION=(TMP=)(
// 0020 56 53 4E 4E 55 4D 3D 31 36 39 38 36 39 35 36 38 VSNNUM=169869568
// 0030 29 28 45 52 52 3D 30 29 28 41 4C 49 41 53 3D 4C )(ERR=0)(ALIAS=L
// 0040 49 53 54 45 4E 45 52 29 29 ISTENER))
// 308: Server disconnected
// 308: Disconnected from Client
//
// Example ...
//
// procedure TFMain.Button1Click(Sender: TObject);
// var oOraTNS : TOracleTNS;
// begin
// oOraTNS := TOracleTNS.Create;
// Memo1.Lines.Add(IntToStr(oOraTNS.Ping('10.0.1.10',1521)));
// Memo1.Lines.Add(oOraTNS.OracleVersion('10.0.1.10',1521));
// FreeAndNil(oOraTNS);
// end;
//
// ==========================================================================
uses SysUtils, DateUtils, IdGlobal, IdTCPClient;
type
{ TOracleTNS Class }
TOracleTNS = class(TObject)
strict private const
TNSCONNECT = 1;
TNSPING = '(CONNECT_DATA=(COMMAND=ping))';
MAXVER = $0139;
MINVER = $012C;
SDUMAX = $0800;
TDUMAX = $7FFF;
strict private type
TTNSPacket = array [0..1999] of char;
TTnsHeader = packed record
Length,PacketChecksum : word;
PacketType,Flags : byte;
HeaderChecksum : word;
end;
TTnsConnect = packed record
Version,MinVersion,
GlobalServiceOptions,
SessionDataUnit,
TransportDataUnit,
Characteristics,
MaxPacketsBeforeAck,
ByteOrder,Length,Offset : word;
MaxRecv : longword;
AdditionalNetworkOptions : word;
Buffer : array [0..23] of char;
end;
strict private var
FTNSPacket : TTNSPacket;
FTCP : TIdTcpClient;
FPacketLength : word;
FError,FVersion : string;
strict private
function _SwapBytes(AValue : longword) : longword;
function _SwapWords(AValue : longword) : longword;
procedure _InitPacket(const AData : string);
public
constructor Create;
destructor Destroy; override;
function Ping(const AHost : string;
APort : integer) : integer; overload;
function OracleVersion(const AHost : string;
APort : integer) : string; overload;
property Error : string read FError;
end;
// -----------------------------------------------------------------------------
implementation
constructor TOracleTNS.Create;
begin
inherited Create;
FTCP := TIdTcpClient.Create(nil);
end;
destructor TOracleTNS.Destroy;
begin
FreeAndNil(FTCP);
inherited Create;
end;
// Internal functions to implement BIG-ENDIAN storage
function TOracleTNS._SwapBytes(AValue : longword) : longword;
begin
asm
xor eax,eax
mov eax,AValue
bswap eax
shr eax,16
mov AValue,eax
end;
Result := AValue;
end;
function TOracleTNS._SwapWords(AValue : longword) : longword;
begin
asm
xor eax,eax
mov eax,AValue
bswap eax
mov AValue,eax
end;
Result := AValue;
end;
// Initialise and format the TNS packet with the TNSPING command
// NOTE : Word and DWord are stored as BIG-ENDIAN
procedure TOracleTNS._InitPacket(const AData : string);
var iLength : word;
rTNSHeader : TTNSHeader;
rTNSConnect : TTNSConnect;
begin
ilength := length(AData);
FPacketLength := iLength + $003A;
FillChar(rTNSHeader,SizeOf(TTNSHeader),0);
FillChar(rTNSConnect,SizeOf(TTNSConnect),0);
FillChar(FTNSPacket,SizeOf(TTNSPacket),0);
rTNSHeader.Length := _SwapBytes(FPacketLength);
rTNSHeader.PacketType := TNSCONNECT;
rTNSConnect.Version := _SwapBytes(MAXVER);
rTNSConnect.MinVersion := _SwapBytes(MINVER);
rTNSConnect.SessionDataUnit := _SwapBytes(SDUMAX);
rTNSConnect.TransportDataUnit := _SwapBytes(TDUMAX);
rTNSConnect.Characteristics := _SwapBytes($860E);
rTNSConnect.ByteOrder := 1;
rTNSConnect.Length := _SwapBytes(iLength);
rTNSConnect.Offset := _SwapBytes($003A);
rTNSConnect.MaxRecv := _SwapWords($000007F8);
rTNSConnect.AdditionalNetworkOptions := $0C0C;
move(rTNSHeader,FTNSPacket[0],SizeOf(TTNSHeader));
move(rTNSConnect,FTNSPacket[SizeOf(TTNSHeader)],50);
move(AData[1],FTNSPacket[$003A],iLength);
end;
// Do the TNS Ping and return Milliseconds
//
// NOTE : Version is also stored in this call so it can also be
// used by OracleVersion() method
//
// Indy 10 is used in this version.
//
// For Indy 9 - Add uses IdTCPConnection and implement
// Remove references to pBufferIn and pBufferOut
// Remove IdGlobal from uses
//
// FTCP.Connect(0);
//
// try
// FTCP.WriteBuffer(FTNSPacket,FPacketLength);
// FTCP.ReadBuffer(arrResponse,70);
// finally
// FTCP.Disconnect;
// end;
//
function TOracleTNS.Ping(const AHost : string;
APort : integer) : integer;
var arrResponse : array [0..199] of char;
pResponse,pData : PChar;
sVersion : string;
iResult,iPos : integer;
dtStart : TDateTime;
pBufferIn,pBufferOut : TIdBytes;
begin
FError := '';
sVersion := '';
iResult := -1;
FillChar(arrResponse,SizeOf(arrResponse),0);
dtStart := Now();
FTCP.Host := AHost;
FTCP.Port := APort;
_InitPacket(TNSPING);
pBufferIn := RawToBytes(FTNSPacket,FPacketLength);
try
FTCP.Connect;
try
FTCP.IOHandler.Write(pBufferIn);
FTCP.IOHandler.ReadBytes(pBufferOut,70,false);
BytesToRaw(pBufferOut,arrResponse,70);
finally
FTCP.Disconnect;
end;
pResponse := @arrResponse[13];
iPos := pos('VSNNUM',pResponse); // Look for (VSNNUM=xxx..) in buffer
// Get Oracle Version
if iPos NE 0 then begin
inc(iPos,6);
pData := @pResponse[iPos];
iPos := pos(')',pData);
if iPos NE 0 then begin
dec(iPos);
sVersion := IntToHex(StrToInt(copy(pData,1,iPos)),7);
FVersion := IntToStr(StrToInt('$' + sVersion[1])) + '.' +
IntToStr(StrToInt('$' + sVersion[2])) + '.' +
IntToStr(StrToInt('$' + sVersion[3])) + '.' +
IntToStr(StrToInt(copy(sVersion,4,2))) + '.' +
IntToStr(StrToInt(copy(sVersion,6,2)));
// Check for (ERR=0)
if pos('(ERR=0)',pData) NE 0 then
iResult := MilliSecondsBetween(Now(),dtStart);
end;
end;
except
on E : exception do FError := E.Message;
end;
Result := iResult;
end;
// Get the oracle version
function TOracleTNS.OracleVersion(const AHost : string;
APort : integer) : string;
begin
Ping(AHost,APort);
Result := FVersion;
end;
end.