1
 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 ?

5
  • 1
    Your code works fiddle and the issue cannot be reproduced. Please edit the question and include a minimal reproducible example including details of: which user owns the fpp2 function; which user is running the PL/SQL block; the CREATE TABLE statement for table t1 (including details of the owner); details of grants/synonyms on the function to the calling user; the full error message; and the expected output. Commented Jun 17, 2024 at 8:30
  • 1
    Does your user own fpp2, or is it in some other schema with default definer rights? Commented Jun 17, 2024 at 12:09
  • 2
    Change the definition of fpp2 to CREATE OR REPLACE FUNCTION fpp2(cur IN INTEGER) RETURN INTEGER AUTHID CURRENT_USER and see where that gets you. Commented Jun 17, 2024 at 19:12
  • thanks , function fpp2 need AUTHID CURRENT_USER Commented Jun 19, 2024 at 8:28
  • Feel free to post the solution as an answer. (You can answer your own question. ) Commented Jun 24, 2024 at 0:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.