utPLSQL
ut.expect for refcursor causes ORA-02047 error
Describe the bug A clear and concise description of what the bug is.
I am trying to compare two refcursor results. However when I do so, I receive this error:
ORA-02047: cannot join the distributed transaction in progress
The code called reaches out over DB link to a MS SQL Server, and stores the results in a local table.
Then, I open 2 refcursors against a single table (each refcursor has a where clause for different rows), and call ut.expect against those.
Since they are refcursors against local data, I do not understand why the ut.expect would cause an issue with distributed transaction that is likely opened against the linked database.
Provide version info Information about utPLSQL and Database version,
19.0.0.0.0
19.0.0
PL/SQL procedure successfully completed.
UT_VERSION
------------------------------------------------------------
v3.1.10.3349
BANNER BANNER_FULL BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production 0
Version 19.12.1.0.0
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR HH24.MI.SS
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT yyyy-mm-dd"T"hh24:mi:ssxff
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
PORT_STRING
------------------------------------------------------------
x86_64/Linux 2.4.xx
Information about client software
Run as a script in Oracle SQL Developer 21.4.3.063.0100, running Java(TM) Platform 1.8.0_311
set serveroutput on
begin
ut.run('test_my_package');
end;
To Reproduce Steps to reproduce the behavior:
If ut.expect is called against the refcursors, the error occurs.
If it is commented out, no error occurs (seems to be rolled back ok)
Expected behavior A clear and concise description of what you expected to happen.
I would expect no error to be thrown and ut.expect to compare the refcursors
Example code If applicable, add sample code to help explain your problem.
--%test(Check tape route is inventory but scrap route error)
PROCEDURE check_refcursors_match
IS
l_actual sys_refcursor;
l_expected sys_refcursor;
BEGIN
--this procedure contains several SELECT against MS SQL server to insert data to LOCAL_TABLE
my_package.generate_data(data_key => 1);
-- rows in LOCAL_TABLE with data_key column = 0 are the expected values
OPEN l_expected FOR
SELECT * FROM LOCAL_TABLE
WHERE data_key = 0;
-- rows in LOCAL_TABLE with data_key column = 1 are the values inserted by procedure above
OPEN l_actual FOR
SELECT * FROM LOCAL_TABLE
WHERE data_key = 1;
-- if ut.expect is commented out, the test succeeds with no errors
ut.expect( l_actual ).to_equal( l_expected );
END;
Additional context Add any other context about the problem here.
See the error output below
Failures:
1) check_refcursors_match
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 113
ORA-06512: at "UT3.UT_EXPECTATION_PROCESSOR", line 145
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 70
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "UT3.UT_EXPECTATION_PROCESSOR", line 129
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 47
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 92
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 22
ORA-06512: at "UT3.UT", line 83
ORA-06512: at "MY_SCHEMA.TEST_MY_PACKAGE", line 269
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 113
ORA-06512: at "UT3.UT_EXPECTATION_PROCESSOR", line 145
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 70
ORA-06512: at "UT3.UT_EXPECTATION_PROCESSOR", line 129
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 47
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 92
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 22
ORA-06512: at "UT3.UT", line 83
ORA-06512: at "MY_SCHEMA.TEST_MY_PACKAGE", line 269
ORA-06512: at line 6
Finished in 3.960272 seconds
12 tests, 0 failed, 1 errored, 0 disabled, 0 warning(s)
Hi, Can you fetch data as part of autonomous transaction and commit? Then use a compare?
Hi @lwasylow ,
Thank you for the reply!
The original code that I wrote in "my_package" did attempt to save the data from MS SQL server to the Oracle database using an autonomous transaction. However, it did not function correctly and I would receive errors:
ORA-01010: invalid OCI operation ORA-02063: preceding line from MY_LINKED_SERVER
I asked my Oracle DBA at the time why the code was not working (he used to work for Oracle and seems trustworthy in my experience) and his reply was:
This MOS article,
Oracle Gateways (DG4ODBC, DG4MSQL, DG4SYBS, DG4DRDA, DG4IFMX) and Autonomous Transactions (Doc ID 1916351.1)
describes it. There is a workaround that might help: if it were possible to add a COMMIT after every SELECT, to terminate the distributed transaction that the SELECT started (as we demonstrated yesterday). But overall, what you are doing is not guaranteed to work because autonomous transactions are not supported by the heterogeneous services layer.
To make it reliable, you probably have to remove all the autonomous transactions.
For the code I wrote, commit didn't seem ideal for every single use... perhaps with a better understanding of transactions I could go back and rewrite it. However, the code DOES work at this point so I would prefer not to rewrite it just to test it.
I guess what I need to know - does this seem like a bug? Is ut.expect performing some extra transaction/commit/rollback when comparing refcursors? If the refcursor is the cause maybe I could select into collections and compare those instead.
My current workaround uses a combination of (row counts, MINUS to compare the 2 datasets, and some ut.expect on the rows returned) to determine if the test should pass or fail. But I miss out on the lovely feature of ut.expect to print out in detail what rows/columns were different when the test fails.
Thank you, Doug
The problem seems unrelated to ut.expect but related to heterogenous services and autonomous-transactions.
if instead of running:
set serveroutput on
begin
ut.run('test_my_package');
end;
You run:
set serveroutput on
begin
test_my_package.the_failing_test_procedure;
end;
Or you run:
set serveroutput on
begin
ut.run('test_my_package', a_force_manual_rollback => true);
end;
Do you still face the same problem?
The a_force_manual_rollback flag disables autonomous transaction. See this section of documentation.
Hopefully this will let you solve your problem.
