thunguo commented on PR #1057:
URL:
https://github.com/apache/incubator-seata-go/pull/1057#issuecomment-4031450957
It appears that `oracle_xa_connection.go` needs to be rewritten. `go-ora`
does not natively support XA — it must be implemented via the `DBMS_XA` PL/SQL
package.
## Test Environment
| **Item** | **Details** |
|:---:|:---:|
| Database | Oracle Database 21c Express Edition (21.3.0.0.0) |
| Container Name | oracle-xa-test |
| Connection String | system/testpwd123@localhost:1521/XEPDB1 |
## Execution Command
### Simulating go-ora ExecContext calls via EXECUTE IMMEDIATE
```bash
docker cp /tmp/xa_test.sql oracle-xa-test:/tmp/xa_test.sql && \
docker exec oracle-xa-test sqlplus -S
system/testpwd123@localhost:1521/XEPDB1 @/tmp/xa_test.sql
```
## Test SQL File (xa_test.sql)
```sql
SET SERVEROUTPUT ON SIZE UNLIMITED
SET ECHO OFF
SET FEEDBACK OFF
-- TEST A: XA START via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA START ''test_xid_001''';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST B: XA END via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA END ''test_xid_001''';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST C: XA PREPARE via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA PREPARE ''test_xid_001''';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST D: XA COMMIT via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA COMMIT ''test_xid_001''';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST E: XA ROLLBACK via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA ROLLBACK ''test_xid_001''';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST F: XA RECOVER via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA RECOVER';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST G: XA FORGET via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA FORGET ''test_xid_001''';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- TEST H: XA COMMIT ONE PHASE via EXECUTE IMMEDIATE
BEGIN
EXECUTE IMMEDIATE 'XA COMMIT ''test_xid_001'' ONE PHASE';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ORA-ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('ORA-ERROR MSG: ' || SQLERRM);
END;
/
-- CONTROL: DBMS_XA (the correct Oracle way)
DECLARE
l_xid DBMS_XA_XID := DBMS_XA_XID(1,
UTL_RAW.CAST_TO_RAW('test_xid_ctrl'));
l_ret PLS_INTEGER;
BEGIN
l_ret := DBMS_XA.XA_START(l_xid, DBMS_XA.TMNOFLAGS);
DBMS_OUTPUT.PUT_LINE('DBMS_XA.XA_START returned: ' || l_ret);
l_ret := DBMS_XA.XA_END(l_xid, DBMS_XA.TMSUCCESS);
DBMS_OUTPUT.PUT_LINE('DBMS_XA.XA_END returned: ' || l_ret);
l_ret := DBMS_XA.XA_ROLLBACK(l_xid);
DBMS_OUTPUT.PUT_LINE('DBMS_XA.XA_ROLLBACK returned: ' || l_ret);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('DBMS_XA ERROR CODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('DBMS_XA ERROR MSG: ' || SQLERRM);
END;
/
EXIT
```
## Output Results
```
==========================================
TEST A: XA START via EXECUTE IMMEDIATE
(simulates go-ora driver ExecContext)
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST B: XA END via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST C: XA PREPARE via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST D: XA COMMIT via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST E: XA ROLLBACK via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST F: XA RECOVER via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST G: XA FORGET via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
TEST H: XA COMMIT ONE PHASE via EXECUTE IMMEDIATE
==========================================
ORA-ERROR CODE: -900
ORA-ERROR MSG: ORA-00900: invalid SQL statement
==========================================
CONTROL: DBMS_XA (the correct Oracle way)
==========================================
DBMS_XA.XA_START returned: 0
DBMS_XA.XA_END returned: 0
DBMS_XA.XA_ROLLBACK returned: 0
```
## Analysis
### oracle_xa_connection.go — All 8 Methods Failed
| **Test** | **SQL Statement** | **Corresponding Method** | **Result** |
|:---:|:---:|:---:|:---:|
| A | XA START 'test_xid_001' | Start() | ❌ ORA-00900 |
| B | XA END 'test_xid_001' | End() | ❌ ORA-00900 |
| C | XA PREPARE 'test_xid_001' | XAPrepare() | ❌ ORA-00900 |
| D | XA COMMIT 'test_xid_001' | Commit() | ❌ ORA-00900 |
| E | XA ROLLBACK 'test_xid_001' | Rollback() | ❌ ORA-00900 |
| F | XA RECOVER | Recover() | ❌ ORA-00900 |
| G | XA FORGET 'test_xid_001' | Forget() | ❌ ORA-00900 |
| H | XA COMMIT 'test_xid_001' ONE PHASE | Commit(onePhase=true) | ❌
ORA-00900 |
### Correct Oracle XA Approach (DBMS_XA) — All Succeeded
| **Call** | **Return Value** | **Meaning** |
|:---:|:---:|:---:|
| DBMS_XA.XA_START | 0 | XA_OK |
| DBMS_XA.XA_END | 0 | XA_OK |
| DBMS_XA.XA_ROLLBACK | 0 | XA_OK |
## Conclusion
The implementation in `oracle_xa_connection.go` directly copies MySQL's XA
SQL syntax. Oracle Database has **never supported** this syntax — whether
executed directly in sqlplus or via `EXECUTE IMMEDIATE` (which is equivalent to
the `go-ora` driver's `ExecContext` call path) — and consistently returns:
```
ORA-00900: invalid SQL statement
```
**The correct implementation must use the** **`DBMS_XA`** **PL/SQL package**
(`DBMS_XA.XA_START`, `DBMS_XA.XA_END`, `DBMS_XA.XA_PREPARE`,
`DBMS_XA.XA_COMMIT`, `DBMS_XA.XA_ROLLBACK`, etc.) to perform XA operations.
This is the only SQL-level XA interface officially provided by Oracle.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
