0

As my title. I'm in trouble with this error. My code below:

create or replace PROCEDURE     TEST(
        P_CONTRACT VARCHAR2,
        p_result OUT SYS_REFCURSOR
) AS

cursor_name INTEGER := DBMS_SQL.OPEN_CURSOR;
ROWS_PROCESSED INTEGER ;
v_string VARCHAR2(1000);

BEGIN
    v_string:=' SELECT *
    FROM CONTRACT C 
    WHERE 1 = 1 ';
    
    IF(P_CONTRACT IS NOT NULL)  THEN
        v_string := v_string || ' AND C.CONTRACT_NAME = :P_CONTRACT ';
    END IF;

    v_string: = v_string || ' AND ROWNUM <= 10';

    DBMS_SQL.PARSE(CURSOR_NAME,v_string,DBMS_SQL.NATIVE);

    IF  (P_CONTRACT IS NOT NULL) THEN
        DBMS_SQL.BIND_VARIABLE(CURSOR_NAME, ':P_CONTRACT', P_CONTRACT);         
    END IF;

    ROWS_PROCESSED := DBMS_SQL.EXECUTE(cursor_name);        
    p_result := DBMS_SQL.TO_REFCURSOR(cursor_name);

EXCEPTION 
    WHEN OTHERS THEN 
        IF DBMS_SQL.IS_OPEN(cursor_name) THEN 
        DBMS_SQL.CLOSE_CURSOR(cursor_name); 
    END IF; 
END ;

And I execute store like this:

set serveroutput on;
VARIABLE p_result REFCURSOR ;
    Exec TEST('HNLT00014',:p_result);
print p_result;

I've always got this error :

Error starting at line : 10 in command -
BEGIN TEST('HNLT00014',:p_result); END;
Error report -
ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 1098
ORA-06512: at "TEST", line 6
ORA-06512: at line 1
29471. 00000 -  "DBMS_SQL access denied" 
*Cause:    DBMS_SQL access was denied due to security concerns.
*Action:   Check the alert log and trace file for more information.

I tried close and re-open newly established session like @Nick Krasnov recommended Link

But it didn't work. Someone can help me? I'm using Oracle 12C.

Thanks!

Nguyen An
  • 13
  • 3
  • The error message you've shown refers to line 6, which is the `open_cursor` call, which seems odd. But the code you posted has at least one typo, and cannot throw *any* error because of the `when others`. Please edit your question to show exact code that you run and the error you get. The code you have shown seems to work in later versions, so it could be a bug in your version - which exact version and patch level do you use? - but could be an error in your real code. – Alex Poole Jul 02 '20 at 11:56
  • @AlexPoole I checked again and do not see any typo in my code. This nearly code which I ran, just SELECT some column instead of SELECT * and declare two more parameters. My patch is 12.2.0.1.0 – Nguyen An Jul 03 '20 at 02:13

0 Answers0