SET sql_mode=ORACLE;
#
# MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR
#
#
# Aliasing
#
CREATE PROCEDURE p1(task VARCHAR) AS
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
v0 INT;
v1 INT;
BEGIN
OPEN c0 FOR SELECT 100 FROM DUAL UNION SELECT 101 FROM DUAL;
c1:= c0;
FETCH c0 INTO v0; -- fetch 100
FETCH c1 INTO v1; -- fetch 101
DBMS_OUTPUT.PUT_LINE('actual=' || v0 || ' ' || v1 || ' ' || 'expected=100 101');
IF task LIKE '%close_c0%' THEN
CLOSE c0;
END IF;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' ||
'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
OPEN c1 FOR SELECT 200 FROM DUAL UNION SELECT 201 FROM DUAL;
FETCH c0 INTO v0; -- fetch 200 from the new OPEN c1
FETCH c1 INTO v1; -- fetch 201 from the new OPEN c1
DBMS_OUTPUT.PUT_LINE('actual=' || v0 || ' ' || v1 || ' ' || 'expected=200 201');
IF task LIKE '%close_c1%' THEN
CLOSE c1;
END IF;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' ||
'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
END;
/
CALL p1('');

actual=100 101 expected=100 101

c0%ISOPEN=true c1%ISOPEN=true

actual=200 201 expected=200 201

c0%ISOPEN=true c1%ISOPEN=true
CALL p1('close_c0');

actual=100 101 expected=100 101

c0%ISOPEN=false c1%ISOPEN=false

actual=200 201 expected=200 201

c0%ISOPEN=true c1%ISOPEN=true
CALL p1('close_c1');

actual=100 101 expected=100 101

c0%ISOPEN=true c1%ISOPEN=true

actual=200 201 expected=200 201

c0%ISOPEN=false c1%ISOPEN=false
CALL p1('close_c0 close_c1');

actual=100 101 expected=100 101

c0%ISOPEN=false c1%ISOPEN=false

actual=200 201 expected=200 201

c0%ISOPEN=false c1%ISOPEN=false
DROP PROCEDURE p1;
CREATE PROCEDURE p1(task VARCHAR) AS
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
v INT;
BEGIN
OPEN c0 FOR SELECT 1 FROM DUAL;
CLOSE c0;
CASE task
WHEN 'c0:=c1' THEN c0:=c1; -- Expect: Cursor is not open
WHEN 'c1:=c0' THEN c1:=c0; -- Expect: v is set to 2
ELSE NULL;                 -- Expect: Cursor is not open
END CASE;
OPEN c1 FOR SELECT 2 FROM DUAL;
FETCH c0 INTO v;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('v=' || v);
END;
/
CALL p1('');
ERROR 24000: Cursor is not open
CALL p1('c0:=c1');
ERROR 24000: Cursor is not open
CALL p1('c1:=c0');

v=2
DROP PROCEDURE p1;
#
# Aliasing: different variable scope
#
CREATE PROCEDURE p1 AS
c0 SYS_REFCURSOR;
v INT;
BEGIN
DECLARE
c1 SYS_REFCURSOR;
BEGIN
OPEN c1 FOR SELECT 1 AS c FROM DUAL;
c0:= c1;
END;
-- Although c1 who opened the cursor goes out of the scope here,
-- the alias still works:
FETCH c0 INTO v;
DBMS_OUTPUT.PUT_LINE('v='||v);
END;
/
CALL p1;

v=1
DROP PROCEDURE p1;
#
# A comprex script with many OPEN, FETCH, CLOSE statements
#
CREATE OR REPLACE PROCEDURE p1 AS
c0 SYS_REFCURSOR;
c1 SYS_REFCURSOR;
v0 VARCHAR(32);
v1 VARCHAR(32);
BEGIN
DBMS_OUTPUT.PUT_LINE('test1');
OPEN c0 FOR SELECT '10' FROM DUAL UNION SELECT '11' FROM DUAL;
c1:= c0;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
DBMS_OUTPUT.PUT_LINE('test1a');
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
BEGIN
FETCH c0 INTO v0;
EXCEPTION
WHEN OTHERS THEN v0:='<FETCH c0 FAILED>';
END;
BEGIN
FETCH c1 INTO v1;
EXCEPTION
WHEN OTHERS THEN v1:='<FETCH c1 FAILED>';
END;
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
DBMS_OUTPUT.PUT_LINE('test2:');
OPEN c1 FOR SELECT '20' FROM DUAL UNION SELECT '21' FROM DUAL UNION SELECT '22' FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' || bool_to_char(c1%ISOPEN));
BEGIN
FETCH c0 INTO v0;
EXCEPTION
WHEN OTHERS THEN v0:='<FETCH c0 FAILED>';
END;
BEGIN
FETCH c1 INTO v1;
EXCEPTION
WHEN OTHERS THEN v1:='<FETCH c1 FAILED>';
END;
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
DBMS_OUTPUT.PUT_LINE('test2a');
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' ||bool_to_char(c1%ISOPEN));
BEGIN
FETCH c0 INTO v0;
EXCEPTION
WHEN OTHERS THEN v0:='<FETCH c0 FAILED>';
END;
BEGIN
FETCH c1 INTO v1;
EXCEPTION
WHEN OTHERS THEN v1:='<FETCH c1 FAILED>';
END;
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
DBMS_OUTPUT.PUT_LINE('test3');
OPEN c0 FOR SELECT '30' FROM DUAL UNION SELECT '31' FROM DUAL;
DBMS_OUTPUT.PUT_LINE('c0%ISOPEN=' || bool_to_char(c0%ISOPEN) || ' ' || 'c1%ISOPEN=' ||bool_to_char(c1%ISOPEN));
FETCH c0 INTO v0;
FETCH c1 INTO v1;
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
DBMS_OUTPUT.PUT_LINE('test4');
OPEN c0 FOR SELECT 'c0-40' FROM DUAL UNION SELECT 'c0-41' FROM DUAL;
OPEN c1 FOR SELECT 'c1-40' FROM DUAL UNION SELECT 'c1-41' FROM DUAL;
FETCH c0 INTO v0;
FETCH c1 INTO v1;
DBMS_OUTPUT.PUT_LINE('v0=' || v0 || ' v1=' || v1);
END;
/
CALL p1;

test1

c0%ISOPEN=true c1%ISOPEN=true

test1a

c0%ISOPEN=false c1%ISOPEN=false

v0=<FETCH c0 FAILED> v1=<FETCH c1 FAILED>

test2:

c0%ISOPEN=true c1%ISOPEN=true

v0=20 v1=21

test2a

c0%ISOPEN=false c1%ISOPEN=false

v0=<FETCH c0 FAILED> v1=<FETCH c1 FAILED>

test3

c0%ISOPEN=true c1%ISOPEN=true

v0=30 v1=31

test4

v0=c1-40 v1=c1-41
DROP PROCEDURE p1;
DROP PACKAGE DBMS_OUTPUT;
DROP FUNCTION bool_to_char;