On 18th May 2003, a man named Ian Redfern published a paper documenting the  TNS protocol internals. The paper, entitled Oracle Protocol, is clearly the result of painstaking research.

Over the years, the paper became a classic in our field. It is widely referenced by security professionals and performance-monitoring experts, both of whom need to perform detailed analysis of the data Oracle communicates over the network.

The original paper, however, became nearly impossible to find. It seemed to have only been published on the ukcert website, and after it was removed from their servers, the only place to find it was web.archive.org. Web Archive is wonderful, but it is a very unreliable way to preserve one of the most important papers published in our field.

Fortunately, Ian Redfern released his paper into the public domain. I can now reproduce it here in full to prevent it from disappearing forever:

Oracle Protocol

This document is an attempt to document the network protocol used by Oracle
database clients to communicate with Oracle database servers in order to allow
developers to decode this traffic and construct new, interoperable client and
server software.

The network protocol is known variously as SQL*Net, Net8, TNS and TTC7 – I
shall refer to it as Net8. It can be run over a number of transports, but I
shall only discuss the TCP/IP variant. I believe the details are valid for all
Oracle versions since Oracle 7.2

Basics

All Net8 traffic goes over an ordinary TCP connection to port 1521 on the
server, although this can be overridden. After logging in, multiple transactions
are carried over the connection until it is closed after logout.

Every packet begins with a length, a checksum, a type and a flags byte. Like
all Net8 integers, these are Big-Endian. The maximum length of a packet is the
SDU (Session Data Unit), which is at most 4086 bytes. By default the SDU is 4086
and the TDU (Transport Data Unit) is 32767 (also its maximum) – the TDU is never
smaller than the SDU.

XX XX Packet Length (8..4086)
00 00 Packet Checksum
XX Type (0..19)
00 Flags (unused)
00 00 Header checksum

Possible packet types are:

Packet type	Meaning
1	Connect
2	Accept
3	Acknowledge
4	Refuse
5	Redirect
6	Data
7	Null
9	Abort
11	Resend
12	Marker
13	Attention
14	Control Information

The checksum is either the ones complement of the sum of the packet header or
whole packet (like an IP checksum) or – in reality – zero.

Connect

A Connect packet is of type 1. Its length is 34 unless there is connection
data. Connection data is a string of the form
(SOURCE_ROUTE=yes)(HOP_COUNT=0)(CONNECT_DATA=((SID=)CID=(PROGRAM=)(HOST=)(USER=)))
or similar.

If the connection data is longer than 221 bytes, it is carried immediately
after the CONNECT packet and the CONNECT packet length is 34 bytes, as if there
were no connection data.

00 bb Packet Length
00 00 Packet Checksum
01 Type Connect
00 Flags(Unused)
00 00 Header Checksum
01 36 Packet version number (01 34 also used)
01 2c Lowest compatible version number
0c 01 Global service options supported
08 00 SDU
7f ff TDU
43 80 Protocol Characteristics (4f 98 also used)
00 00 Max packets before ACK
01 00 1 in hardware byte order
00 81 Connect Data length
00 3a Connect Data offset
00 00 08 00 Max connect data that can be received
01 01 ANO Flags
00 00 00 00 00 00
00 00 00 00 7d 8b 00 00
00 18 00 00 00 00 00 00 00 00
[..] Connection Data

Sample connection data:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ahost)(Port=1521))
(CONNECT_DATA=(SID=test)(CID=(PROGRAM=)(HOST=ahost)(USER=redferni))))

The response to this is a packet of type Accept (2), Redirect (5) or Refuse
(4). A Redirect carries a new set of connection information as its payload.
Resend (11) should be ignored.

Accept

An Accept packet has the following form:

00 20 Overall Length
00 00 Checksum
02 Type Accept
00 Flags
00 00 Header Checksum
01 36 Version
08 01 Global Service Options
08 00 SDU
7f ff TDU
01 00 Hardware Byte Order
00 00 Data Length
00 20 Data Offset
01 Flag0
01 Flag1
00 00 00 00 00 00 00 00 Data

Refuse

A Refuse packet has the following form:

00 20 Overall Length
00 00 Checksum
02 Type Refuse
00 Flags
00 00 Header Checksum
01 Application Reason for Refusal
00 System Reason for Refusal
00 10 Data Length
[..] Data

Data

All traffic after the initial handshake is sent as Data packets, with the
exception of Marker packets, used for interruption.

A Data packet has the following form:

00 91 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)
03 5e
1d Packet Counter
61 80
00 00 00 00 00 00 fc bf 12
08 18 00 00 00 f8 61 12
08 09 00 00 00 00 00 00
00 1c 62 12 08 00 00 00
00 01 00 00 00 00 00 00
00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00
00 1e 62 12 08
73 65 6c 65 63 74 20 2a 20 66 72	select * fr
6f 6d 20 76 24 73 65 73 73 69 6f 6e     om v$session
0a 01 00 00
00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00
00 01 00 00 00 00 00 00 00

The connection is terminated by a Data packet with DataFlags = 0x0040:

00 0a Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Padding
00 40 Data Flags (bit 0x0040 set for EOF)

Additional Network Option Negotiation

After the Accept packet is received, there may be an optional ANO
negotiation, where the client and server indicate which ANO drivers they want to
use.

00 8f Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

de ad be ef ANO Negotiation header
00 85 overall data size = 13 + 20*4 + individual driver contents
08 00 50 00 Version 8.0.5
00 04 #Services: "Authentication", "Encryption", "DataIntegrity", "Supervisor"
00 Desired options flag

00 04 Service (Supervisor)
00 03 Service sub-packets
00 00 00 00 Marker

00 04 Version
00 05 Version
08 00 50 00 

00 08 Drivers
00 01 Type = UB2 Array(1)
00 00 7d 8b l[] Current PID (32139)
50 82 28 d1 Junk

00 12 Object length = 10 + length * 2
00 01 Type = UB2 Array(1)
de ad be ef
00 03 Array marker
00 00 00 04 Array length
00 04 Array entry selectedDrivers[0]
00 01 Array entry selectedDrivers[1]
00 01 Array entry selectedDrivers[2]
00 02 Array entry selectedDrivers[3]

00 01 Service (Authentication)
00 03 Service sub-packets
00 00 00 00 Marker

00 04 Version
00 05 Version
08 00 50 00 

00 02
00 03 UB2
e0 e1 Constant

00 02
00 06 Status
fc ff Constant

00 02 Service (Encryption)
00 02 Service sub-packets
00 00 00 00 Marker

00 04 Version
00 05 Version
08 00 50 00 

00 01
00 01 UB2Array
00 AlgID (0=none)

00 03 Service (DataIntegrity)
00 02 Service sub-packets
00 00 00 00 Marker

00 04 Version
00 05 Version
08 00 50 00 

00 01 Drivers
00 01 UB2Array
00

Potential data types are String(0), UB2Array(1), UB1(2), UB2(3), UB4(4),
Version(5), Status(6)

The response is:

00 7f Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

de ad be ef ANO negotiation header
00 75 Overall data length
08 00 50 00 Version
00 04 #Services
00 Services to be used

00 04 Service (Supervisor)
00 03 Service sub-packets
00 00 00 00 Error

00 04 Version
00 05 Version
08 00 50 00 

00 02
00 06 Status
00 1f Error if status != 31

00 0e
00 01 UB2Array
de ad be ef
00 03 Array Marker
00 00 00 02 Array Length
00 04 Array Value selectedDrivers[0]
00 01 Array Value selectedDrivers[1]

00 01 Service (Authentication)
00 02 Service sub-packets
00 00 00 00 Error

00 04 Version
00 05 Version
08 00 50 00 

00 02
00 06 Status
fb ff 

00 02 Service (Encryption)
00 02 Service sub-packets
00 00 00 00 Error

00 04 Version
00 05 Version
08 00 50 00 

00 01
00 02 UB1
00 AlgID (0=none)

00 03 Service (DataIntegrity)
00 02 Service sub-packets
00 00 00 00 Error

00 04
00 05 Version
08 00 50 00 

00 01
00 02 UB1
00 On/Off

It should be acceptable to use these canned packets for negotiations – they
simply disable all ANO facilities.

Types and marshalling

This is not a true self-descriptive mechanism like ASN.1 or XML, but it does
deal with variable-length binary data, and so it has a marshalling mechanism for
doing so.

There are four native types: B1, B2, B4 and PTR. Each one can be shipped as
native, universal, LSB or (universal and LSB). Native values are big-endian,
universal ones are length-byte-preceeded and LSB ones are little-endian.

By default, B1 types (signed and unsigned bytes) are native, B2, B4 and PTR
are universal. Universal types are a length followed by the non-zero bytes of
data, so 0 is represented as just as zero byte. Negative values are indicated by
setting the high bit of the length.

The following types fit into this scheme:

• UB1, unsigned byte length 1 (B1)
• SB1, signed byte length 1, never negative, B1
• UB2, unsigned byte length 2 (B2)
• SB2, signed byte length 2 (B2)
• UB4, unsigned byte length 4 (B4)
• SB4, signed byte length 4 (B4)
• UWORD, unsigned word length 4 (B4)
• SWORD, signed word length 4 (B4)
• RefCusror, signed word length 4 (B4)
• B1Array, array of B1, written as native
• UB4Array, array of UB4, written as multiple UB4s
• Ptr, pointer, byte 0 if null, otherwise byte 1
• O2U, boolean, byte 0 if false, byte 1 if true
• NULLPTR, byte 0
• PTR, byte 1
• CHR, character array, written as native or CLR if conversion
• CLR, byte array
• DALC, byte array, either 0 (if null/empty) or SB4 length followed by CLR
• UCS2, single unicode character
• TEXT, 0-terminated array of B1

A CLR is a byte array in 64-byte blocks. If its length <=64, it is just
length-byte-preceeded and written as native. Null arrays can be written as the
single bytes 0x0 or 0xff. If length >64, first a LNG byte (0xfe) is written,
then the array is written in length-byte-preceeded chunks of 64 bytes (although
the final chunk can be shorter), followed by a 0 byte. A chunk preceeded by a
length of 0xfe is ignored.

A UCS2 character is (if B2 is universal, as is usual) prefixed by a byte of 1
or 2. The character then follows in one or two bytes, reversed if B2 is LSB
(which it usually isn’t).

In this document I will not mark B1 types as they are always raw bytes.

Logon

First we get the v8 TTI protocol negotiation. The client passes in its client
type and a list of versions – presumably those it is compatible with. The TTI7
client handles up to version 4, sqlplus up to 5 and the JDBC client up to 6.

I shall document the latest protcol, version 6, as used by the JDBC client,
as it is the current version.

00 21 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

01 TTIpro
06 05 04 03 02 01 00 Acceptable protocol versions
4a 61 76 61 5f 54 54 43 2d 38 2e 32 2e 30 00 "Java_TTC-8.2.0\0"

The response is:

00 90 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

01 TTIpro
05 Version (4=7.2.3, 5=8.0.3, 6=8.1.0)
00 Ignored
4c 69 6e 75 78 69 33 38 36 2f 4c 69  TEXT, server string
6e 75 78 2d 32 2e 30 2e 33 34 20 00  (Linuxi386/Linux-2.0.34 \0)
01 00 UB2 native LSB, Server charset
00 Server flags
00 00 UB2 native LSB, Server charset graph elements
   (followed by 5 bytes per element)
00 64 UB2 native, fdoLength, followed by fdoLength bytes of fdo
00 00 00 60 data length?
01 unknown
1f Length of first part
0f Length of second part
05 0b 0c 03 0c 0c 05 04 05 0d 06 09 07 08 05 0e Unknown
05 06 05 0f 02 ec eb ed 05 0a 05 05 05 05 05 Unknown

08 23 43 23 23 08 11 23 08 11 41 b0 23 00 83 unknown

00 01 00
01 03 NCHAR_CHARSET
00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

If Version >= 6, this is followed by two length-byte-preceeded byte
arrays.

The next stage sorts out any differences in type representation:

00 10 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

02 TTIdty
01 00 UB2 native LSB, charset in
01 00 UB2 native LSB, charset out
00 conversion flags
01 01 01 00 02 02 0a 00  08 08 01 00 0c 0c 0a 00 type representations
17 17 01 00 18 18 01 00  19 19 01 00 1a 1a 01 00
1b 1b 01 00 1c 1c 01 00  1d 1d 01 00 1e 1e 01 00
1f 1f 01 00 20 20 01 00  21 21 01 00 0a 0a 01 00
0b 0b 01 00 22 22 01 00  23 23 01 00 24 24 01 00
25 25 01 00 26 26 01 00  28 28 01 00 29 29 01 00
2a 2a 01 00 2b 2b 01 00  2c 2c 01 00 2d 2d 01 00
2e 2e 01 00 2f 2f 01 00  30 30 01 00 31 31 01 00
32 32 01 00 33 33 01 00  34 34 01 00 35 35 01 00
36 36 01 00 37 37 01 00  38 38 01 00 39 39 01 00
3a 3a 01 00 3b 3b 01 00  3c 3c 01 00 3d 3d 01 00
3e 3e 01 00 3f 3f 01 00  40 40 01 00 41 41 01 00
42 42 01 00 43 43 01 00  47 47 01 00 48 48 01 00
49 49 01 00 4b 4b 01 00  4d 4d 01 00 4e 4e 01 00
4f 4f 01 00 50 50 01 00  51 51 01 00 52 52 01 00
53 53 01 00 54 54 01 00  55 55 01 00 56 56 01 00
57 57 01 00 59 59 01 00  5a 5a 01 00 5c 5c 01 00
5d 5d 01 00 62 62 01 00  63 63 01 00 67 67 01 00
6b 6b 01 00 75 75 01 00  78 78 01 00 7c 7c 01 00
7d 7d 01 00 7e 7e 01 00  7f 7f 01 00 80 80 01 00
81 81 01 00 82 82 01 00  83 83 01 00 84 84 01 00
85 85 01 00 86 86 01 00  87 87 01 00 88 88 01 00
89 89 01 00 8a 8a 01 00  8b 8b 01 00 8c 8c 01 00
8d 8d 01 00 8e 8e 01 00  8f 8f 01 00 90 90 01 00
91 91 01 00 94 94 01 00  95 95 01 00 96 96 01 00
97 97 01 00 9d 9d 01 00  9e 9e 01 00 9f 9f 01 00
a0 a0 01 00 a1 a1 01 00  a2 a2 01 00 a3 a3 01 00
a4 a4 01 00 a5 a5 01 00  a6 a6 01 00 a7 a7 01 00
a8 a8 01 00 a9 a9 01 00  aa aa 01 00 ab ab 01 00
ad ad 01 00 ae ae 01 00  af af 01 00 b0 b0 01 00
b1 b1 01 00 b4 b4 01 00  b5 b5 01 00 b6 b6 01 00
b7 b7 01 00 e7 e7 01 00  03 02 0a 00 04 02 0a 00
05 01 01 00 06 02 0a 00  07 02 0a 00 09 01 01 00
0d 00 0e 00 0f 17 01 00  10 00 11 00 12 00 13 00
14 00 15 00 16 00 27 78  01 5d 01 26 01 00 3a 00
44 02 0a 00 45 00 46 00  4a 00 4c 00 58 00 5b 02
0a 00 5e 01 01 00 5f 17  01 00 60 60 01 00 61 60
01 00 64 00 65 00 66 66  01 00 68 00 69 00 6a 6a
01 00 6b 00 6c 6d 01 00  6d 6d 01 00 6e 6f 01 00
6f 6f 01 00 70 70 01 00  71 71 01 00 72 72 01 00
73 73 01 00 74 66 01 00  76 00 77 00 79 00 7a 00
7b 00 88 00 92 92 01 00  93 00 98 02 0a 00 99 02
0a 00 9a 02 0a 00 9b 01  01 00 9c 0c 0a 00 ac 02
0a 00 ae
00 00 terminator

If the server version is at least 6, this is foll,owed by two more
length-byte-preceeded byte arrays, CTcap (17×0, 3, 0, 0, 0) and RTcap (2).

The response is:

00 0b Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

02 TTIdty
[..] type representations - safe to ignore these
00 00 array teminator

Authentication step

00 59 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode
52 FunCode
00 seq number
01 PTR user
01 06 SB4 user length
00 PTR password
00 SB4 password length
00 SB4 audit flag
00 UB4 connect flag
00 SB4 revision level
00 O2U padding
01 PTR terminal
01 07 SB4 terminal length
01 PTR machine
01 05 SB4 machine length
01 PTR sysusername
01 08 SB4 sysusername length
02 10 00 SB4 size of UCAUAC
00 PTR PID
00 SB4 PID length
01 PTR progname
01 10 SB4 progname length
00 PTR server attributes
00 SB4 server attributes length
00 PTR server data
00 SB4 server data length
01 PTR server info
01 10 SB4 server info length
01 O2U return
73 79 73 74 65 6d user="system"
[password]
75 6e 6b 6e 6f 77 6e terminal="unknown"
77 69 6c 6d 61 machine="wilma"
72 65 64 66 65 72 6e 69 sysusername="redferni"
[processid]
4a 44 42 43 20 54 68 69 6e 20 43 6c 69 65 6e 74 progname="JDBC Thin Client"

The response is:

00 34 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

08 OK
01 10 UB2 encrypted session key length
41 41 34 33 42 36 31 44 34 32 34 32 36 39 44 32 AA43B61D424269D2
04 00 00 00 00 00 00 00 00 00 00 oer, ignored
00 00 00 00 00 00 00 00 00 00 00 00

Standard error

An error object has the following structure:

UB4 current row number
UB2 return code
UB2 array element with error
UB2 array element error number
UB2 current cursor ID
SB2 error position
UB1 SQL type
SB1 fatal
SB2 flags
SB2 user cursor options
UB1 UPI parameter
UB1 warning flag
UB4 rid.ti5.rba
UB2 rid.ti5.partition ID
UB1 rid.ti5.table ID
UB4 rid.block number
UB2 rid.slot number
SWORD OS error
UB1 statement number
UB1 call number
UB2 padding
UB4 successful iterations
CLR for REFS [error message if return code != 0]

The authentication step is now repeated, but this time with the password
encrypted with the session key.

00 6b Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode
51 FunCode - second phase login
00 seq number
01 PTR user
01 06 SB4 user length
01 PTR password
01 11 SB4 password length
00 SB4 audit flag
00 UB4 connect flag
00 SB4 revision level
00 O2U padding
01 PTR terminal
01 07 SB4 terminal length
01 PTR machine
01 05 SB4 machine length
01 PTR sysusername
01 08 SB4 sysusername length
02 10 00 SB4 size of UCAUAC
00 PTR PID
00 SB4 PID length
01 PTR progname
01 10 SB4 progname length
00 PTR server attributes
00 SB4 server attributes length
00 PTR server data
00 SB4 server data length
01 PTR server info
01 10 SB4 server info length
01 O2U return
73 79 73 74 65 6d user="system"
45 42 35 33 43 44 30 30 46 45 36 33 36 45 37 36 31 password="manager"
75 6e 6b 6e 6f 77 6e terminal="unknown"
77 69 6c 6d 61 machine="wilma"
72 65 64 66 65 72 6e 69 sysusername="redferni" [processid]
4a 44 42 43 20 54 68 69 6e 20 43 6c 69 65 6e 74 progname="JDBC Thin Client"

The response is:

00 21 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

04 OK
00 00 00 00 00 00 00 00 00 00 00 00 00 oer, ignored
00 00 00 00 00 00 00 00 00

It is possible to receive a warning:
0f Warning UB2 return code UB2
warning flag CLR [warning message if return code != 0]

Version

The client then asks the server for its version details:

00 13 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTI function
3b version function
00 seq number
01 O2U rdbms version
02 01 00 SWORD buffer length
01 O2U return version length
01 O2U return version number

The response is:

00 5f Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

08 OK
01 4c UB2 length
String "Oracle8 Release 8.0.5.0.0 -  Production\n
        PL/SQL Release 8.0.5.0.0 - Production"
04 08 00 50 00 UB4 version 8.0.5.0
09 END

Auto-commit

The next thing the driver does is set auto-commit to ‘on’:

00 13 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTI function
0c set autocommit on
00

The response is:

00 0b Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

09 OK

At this point, the client may send the following SQL:

ALTER SESSION SET NLS_LANGUAGE = 'ENGLISH'
ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'
SELECT VALUE FROM NLS_INSTANCE_PARAMETERS WHERE PARAMETER ='NLS_DATE_FORMAT'
ALTER SESSION SET NLS_DATE_FORMAT = '[value]' (optional)

We have now logged in.

Password algorithm

The Oracle password encryption mechanism is based on DES, and uses a random
challenge from the server which the client must encrypt. The algorithm is quite
complex, and is most easily described in the attached Perl source
– you will need Crypt::DES and Crypt::CBC to use it.
There is now also a C version, orapasswd.c
by Xue Yong Zhi, which requires OpenSSL.

SQL

First, it is necessary to open a statement:

00 0f Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode function
02 function = Oopen
00 seqnumber
01 O2U cid
00 UWORD opesiz

The response is:

00 0e Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

08 OK
01 01 SWORD cid
09 END

Here is a SQL query:

00 42 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode function
47 function = Oall7
00 seqnumber
02 80 21 UB4 options NOPLSQL | EXECUTE | PARSE
01 01 SWORD cursor
01 PTR sqlStmt
01 17 SB4 SQL statment length
00 NULLPTR dbLink
00 SB4 dbLink length
01 PTR inVector
01 07 SB4 inVector length
01 O2U outVector
01 02 SB4 outVector length
00 O2U outVecRet
00 NULLPTR defCols
00 SWORD defCols
00 NULLPTR binCols
00 SWORD binCols
73656c656374 20 2a 20 66726f6d 20 762473657373696f6e select * from v$session
[dbLink]
01 01 01 01 00 00 00 00 00 UB4Array inVector

Bitmap for options:

1	PARSE
8	BIND
16	DEFINE
32	EXECUTE
64	FETCH
128	CANCEL
256	COMMIT
512	EXACTFE
1024	SNDIOV
32768	NOPLSQL

The value of options is constructed in the following manner:
If call is
parse_execute, options = NOPLSQL|EXECUTE|PARSE. If call is fetch, options =
NOPLSQL|FETCH.

If call is execute_fetch, check the SQL. If select or with, options =
NOPLSQL|EXECUTE_FETCH. If begin, call or declare, options = SNDIOV|EXECUTE (if
binds depth nonzero) otherwise 32. If insert, delete, update or other, options =
NOPLSQL|EXECUTE. If call is parse_execute_fetch, options is same as
execute_fetch plus PARSE.

In all cases, if binds depth is nonzero, set the BIND bit, and if defines
depth is nonzero, set the DEFINE bit.

inVector[0] is always 1. inVector[1] is 1 if not NOPLSQL. If (EXECUTE and not
FETCH), inVector[1] = binds depth (or 1 if binds depth is 0). If FETCH,
inVector[1] = defines depth.

The response is:

00 29 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

08 Response code - 8 = RPA
01 02 UB2 OutVecRet - length of output vector
00 00 UB4Array outVector[]
04 Code - 4 = OER
00 UB4 current row number
00 UB2 return code
00 UB2 array element with error
00 UB2 array element error number
01 01 UB2 current cursor ID
00 SB2 error position
03 UB1 SQL type
00 SB1 fatal
00 SB2 flags
00 SB2 user cursor options
00 UB1 UPI parameter
00 UB1 warning flag
00 UB4 rid.ti5.rba
00 UB2 rid.ti5.partition ID
00 UB1 rid.ti5.table ID
00 UB4 rid.block number
00 UB2 rid.slot number
00 SWORD OS error
00 UB1 statement number
00 UB1 call number
00 UB2 padding
01 01 UB4 successful iterations

If there are binds or defines, these are now marshalled and sent to the
server. I shall describe these at a later date.

This is followed by a version 8 array request (for Oracle 8.0.3 and above) to
describe the columns coming back:

00 16 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode function
62 function = v8Odscrarr
00 seqnumber
07 operation flags (always 7)
01 01 SWORD cursor ID
00 NULLPTR SQL text
00 SB4 SQL text length
01 02 UB4 SQL parse version (always 2)
01 O2U UDS array
01 O2U num UDS
[SQL text]

The response is:

04 c0 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

08 OK
01 29 UB2 numUDs

There follows a series of UDSes, each of which describes a column. Each UDS
begins with a v7OAC:

17 v7oacdty
00 v7oacflg
00 v7oacpre
00 v7oacscl (UB2 if oadty==NUMBER)
[padding byte if oadty==TIMESTAMP, TIMESTAMPTZ, INTERVALDS, TIMESTAMPLTZ]
01 04 SB4 v7oacmxl max length
00 SB4 v7oacmal
00 SB4 v7oacfl2

If v7oacdty==NUMBER, v7oacmxl=22. If v7oacdty==DATE, v7oacmxl=7. If
v7oacdty==TIMESTAMPTZ, v7oacmxl=13.

This is followed by the rest of a v8oac:
00 DALC v8oactoid 00 UB2 v8oacvsn
00 UB2 v8ncs (charset) 00 v8FormOfUse

We then get the rest of the UDS:

01 null_allowed
05 udscnl
01 05 05 DALC column name
5341 4444 52 "SADDR"
00 DALC schema name
00 DALC type name

The next UDS looks like:

02 v7oacdty
00 v7oacflg
00 v7oacpre
00 v7oacscl
01 16 SB4 v7oacmxl
00 SB4 v7oacmal
00 SB4 v7oacfl2
00 DALC v8oactoid
00 UB2 v8oacvsn
00 UB2 v8ncs
00 v8FormOfUse
01 null_allowed
03 udscnl
01 03 03 DALC column name
53 49 44 "SID"
00 DALC schema name
00 DALC type name

These continue until:

01 v7oacdty
80 v7oacflg
00 v7oacpre
00 v7oacscl
01 03 SB4 v7oacmxl
00 SB4 v7oacmal
00 SB4 v7oacfl2
00 DALC v8oactoid
00 UB2 v8oacvsn
01 01 UB2 v8ncs
01 v8FormOfUse
01 null_allowed
0b udscnl
01 0b 0b DALC column name
46 41 49 4c 45 44 5f 4f 56 45 52 "FAILED_OVER"
00 DALC schema name
00 DALC type name

This is then followed by an oer:

04 oer
00 UB4 current row number
00 UB2 return code
00 UB2 array element with error
00 UB2 array element error number
01 01 UB2 current cursor ID
00 SB2 error position
03 UB1 SQL type
00 SB1 fatal
00 SB2 flags
00 SB2 user cursor options
00 UB1 UPI parameter
00 UB1 warning flag
00 UB4 rid.ti5.rba
00 UB2 rid.ti5.partition ID
00 UB1 rid.ti5.table ID
00 UB4 rid.block number
00 UB2 rid.slot number
00 SWORD OS error
00 UB1 statement number
00 UB1 call number
00 UB2 padding
01 01 UB4 successful iterations

Valid database types are:

Type #	Type
1	VARCHAR
2	NUMBER
6	VARNUM
8	LONG
11	ROWID (deprecated), equiv to 104
12	DATE
23	RAW
24	LONG_RAW
96	CHAR
102	RESULT_SET
104	ROWID
109	NAMED_TYPE
111	REF_TYPE
112	CLOB
113	BLOB
114	BFILE
180	TIMESTAMP
181	TIMESTAMPTZ
182	INTERVALYM
183	INTERVALDS
231	TIMESTAMPLTZ
998	PLSQL_INDEX_TABLE
999	FIXED_CHAR

After the execute follows the fetch:

02 40 Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode function
47 function = Oall7
00 seqnumber
02 80 50 UB4 options NOPLSQL | FETCH | DEFINE
01 01 SWORD cursor
00 NULLPTR sqlStmt
00 SB4 SQL statment length
00 NULLPTR dbLink
00 SB4 dbLink length
01 PTR inVector
01 07 SB4 inVector length
01 O2U outVector
01 02 SB4 outVector length
00 O2U outVecRet
01 PTR defCols
01 29 SWORD defCols
00 NULLPTR binCols
00 SWORD binCols
[SQL statement]
[dbLink]
01 01 01 0a 00 00 00 00 00 UB4Array inVector=[1,10,0,0,0,0,0]

This is followed by all the column definitions as oacs:

17 v7oacdty
01 v7oacflg (default 1)
00 v7oacpre
00 v7oacscl
01 04 SB4 v7oacmxl (max length)
00 SB4 v7oacmal
00 SB4 v7oacfl2
00 DALC v8oactoid
00 UB2 v8oacvsn
01 01 UB2 v8ncs (character set)
00 v8FormOfUse

Here, note that if oacdty==CHAR, oacflag=33. If oacdty==FIXED_CHAR, instead
use CHAR. If oadty==ROWID (either 11 or 104), use VARCHAR. If oacdty==RESULTSET,
oacmxl=1.

The reply to this is the rows:

05 3e Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

06 rxh (Row header)
02 rxh.flags=FLEOR
01 29 UB2 rxh.numRqsts
00 rxh.iterNum (high byte of numRqsts)
01 0a UB2 rxh.numItersThisTime
00 UB2 rxh.uacBufLength

07 rxd (Row data)
04 20 03 23 cc CLR
00 SB2 indicator
02 c1 02 CLR
00 indicator

This continues over many packets until all the columns and rows are
returned.

We end a query result set with a cancel:

00 0f Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode function
14 function = cancel
00 seqnumber
01 01 SWORD cid

The response is:

00 0b Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

We end a statement with a close:
00 0f Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

03 TTCCode function
8 function = close
00 seqnumber
01 01 SWORD cid

The response is:

00 0b Overall Length
00 00 Checksum
06 Type Data
00 Flags
00 00 Header Checksum
00 00 Data Flags (bit 0x0040 set for EOF)

09 END

---

Release 0.9 – 18th May 2003Ian Redfern
(Ian.Redfern@LogicaCMG.com)

This document and its accompanying source code samples are in
the public domain, and you may do anything with them that you
wish. The author takes no responsibility for the accuracy of their
contents. Some of the terms in this document are trademarks of Oracle
and other companies. No trade secrets or other privileged information
has been used in its compilation, and the author has no relationship
with Oracle.