Replace SID to SERVICE_NAME in Oracle connnection string

Can you try regsub instead of regexp?

 http://www.tcl.tk/man/tcl8.4/TclCmd/regsub.htm
regsub -all {(SID=MYSID)} [TCP::payload] SERVICE_NAME=MYSERVICENAME $new_payload
TCP::payload replace 0 [TCP::payload length] $new_payload

Aaron

I didn't test this against an Oracle environment, but the following should get you closer:

when CLIENT_ACCEPTED {
    TCP::collect
}
when CLIENT_DATA {
    set sid_match 0
    if { [TCP::payload] contains "CONNECT_DATA=" } {
        set sid_match 1
        log local0. "original payload = [TCP::payload]"
        set service_name "SERVICE_NAME=MYSERVICENAME"
        if { [regsub -all -nocase "SID=MYSID" [TCP::payload] "$service_name" newdata] } {
            TCP::payload replace 0 [TCP::payload length] ""
            TCP::payload replace 0 0 $newdata
            log local0. "replaced payload = $newdata"
        }
    }
    TCP::release   
    TCP::collect
}

You'll need to re-add your pool logic, but you shouldn't have to do any of the payload length manipulation. Output from test using an HTTP POST:

original payload = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.110.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYSID)(CID=(PROGRAM=sqlplus)(HOST=xxxxxxx)(USER=xxxxxx))))
replaced payload = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.11.110.10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYSERVICENAME)(CID=(PROGRAM=sqlplus)(HOST=xxxxxxx)(USER=xxxxxx))))

I'm getting closer in that the payload is succesfully updated and the correct pool is chosen and when I run a tcpdump on the pool member from "other_pool" I can see the updated payload is being received and it replies but for some reason I'm getting a connection closed error from the client (sqlplus) when testing. I can also see from a tcpdump on the client that replies are coming in correctly so it's not a route/SNAT issue.

ERROR: ORA-12537: TNS:connection closed

when CLIENT_ACCEPTED {
    log local0. "in client_accepted event"
    TCP::collect
}
when CLIENT_DATA {
    log local0. "in client_data event"
    set sid_match 0
    log local0. "orig payload length = [TCP::payload length]"
    if { [TCP::payload] contains "CONNECT_DATA=" } {
        log local0. "original payload = [TCP::payload]"
        set service_name "SERVICE_NAME=MY_SERVICE_NAME"
        if { [regsub -all -nocase "SID=MYSID" [TCP::payload] "$service_name" newdata] } {
            log local0. "found a SID match in tcp payload - replacing with $service_name"
            TCP::payload replace 0 [TCP::payload length] ""
            TCP::payload replace 0 0 $newdata
            log local0. "replaced payload = $newdata"
   set sid_match 1
        }
        log local0. "new payload length = [TCP::payload length]"
        if { $sid_match != 0 } {
             log local0. "sending to pool other_pool"
             LB::detach
    pool other_pool
             TCP::release 
        } else {
    do nothing further - found no SID match - use the VS default_pool
    log local0. "No SID match - use the VS default_pool"
             TCP::release 
        }
    }

    TCP::release
    TCP::collect
}

when LB_SELECTED {
    log local0. "selected pool member [LB::server addr]"
}

Can you open up the tcpdump to look at the complete transaction? When I tested with a cURL client and a POST request, the first packet contained the headers so they didn't match the criteria and went to the default pool. I assumed, because of the way you had written the previous version, that the sqlplus client didn't have that problem. So please look to see if ALL of the data from the client is being directed to the same pool (and not just the payload).