CREATE OR REPLACE FUNCTION fpp2(cur IN INTEGER) RETURN INTEGER
IS
rows_processed INTEGER;
BEGIN
dbms_output.PUT_LINE('cursor_fp2:' || cur);
rows_processed := DBMS_SQL.EXECUTE(cur);
RETURN rows_processed;
END;
DECLARE
cur INTEGER;
rows_processed INTEGER;
BEGIN
cur := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cur, 'select * from t1',
DBMS_SQL.NATIVE);
-- rows_processed:= DBMS_SQL.EXECUTE(cursor_name);
dbms_output.PUT_LINE('cursor_nam:' || cur);
rows_processed := fpp2(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
dbms_output.PUT_LINE('1234');
EXCEPTION
WHEN OTHERS THEN
dbms_output.PUT_LINE('123'|| sqlerrm);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
result is ORA-29470 effective user or roles are not the same when the cursor was parsed
how to fix fpp2 function ? oracle dbms_sql not support cursor as parameter ?
fpp2function; which user is running the PL/SQL block; theCREATE TABLEstatement for tablet1(including details of the owner); details of grants/synonyms on the function to the calling user; the full error message; and the expected output.fpp2toCREATE OR REPLACE FUNCTION fpp2(cur IN INTEGER) RETURN INTEGER AUTHID CURRENT_USERand see where that gets you.