ElephantProtocol

From C4 Wiki
Jump to: navigation, search

elephant Protocol

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

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 (17x0, 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 "elephant8 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 elephant 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 elephant 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

--hello