why is sys_refcursor after rollback closed? there are no notes in the documentation that the rollback should also close the cursor with the selected data. But in my case YES.
— open cursor to remember data modificated by old process
PROCEDURE p_get_employ_data(
in_account_prefix IN ucet_jadro.predcislie%TYPE,
in_account_number IN ucet_jadro.cislo_uctu%TYPE,
in_out_cursor IN OUT SYS_REFCURSOR
) IS
BEGIN
OPEN in_out_cursor FOR
SELECT
status_id status,
balance balance,
dispo_balance dispo_balance
FROM employ j
WHERE j.acc_pfx = in_account_prefix
AND j.acc_num = in_account_number;
— Select get rowcount = 1 into cursor
END p_get_employ_data;
…
l_expected_account SYS_REFCURSOR;
…
SAVEPOINT specimen_savepoint;
p_set_employ_data_old_process(…);
p_get_employ_data(
in_account_prefix => g_account_prefix,
in_account_number => g_account_number,
in_out_cursor => l_expected_account
);
IF l_expected_account%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('01. l_expected_account is open'); -----> IS OPEN
ELSE
DBMS_OUTPUT.PUT_LINE('01. l_expected_account is closed');
END IF;
IF l_expected_account%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('01a. l_expected_account is open'); -----> IS OPEN
ELSE
DBMS_OUTPUT.PUT_LINE('01a. l_expected_account is closed');
END IF;
-- rollback to prepare the same data by new process
ROLLBACK TO specimen_savepoint;
IF l_expected_account%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('02. l_expected_account is open');
ELSE
DBMS_OUTPUT.PUT_LINE('02. l_expected_account is closed'); ----- IS NOT OPEN!!!!
END IF;
p_set_employ_data_new_process(...);
...
Peticek is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.