OceanBase stored procedure error: ORA-00942: table or view does not exist

I’m encountering permission and object recognition issues when creating and executing stored procedures in OceanBase Database. Here’s my environment:

• OceanBase Version: 4.2.1 (Enterprise Edition - Oracle mode)

Steps to Reproduce

1. Create a test table

CREATE TABLE test_proc (  
    id NUMBER PRIMARY KEY,  
    name VARCHAR2(50)  
);

2. Create a simple stored procedure

CREATE OR REPLACE PROCEDURE insert_test(  
    p_id IN NUMBER,  
    p_name IN VARCHAR2)  
IS  
BEGIN  
    INSERT INTO test_proc VALUES(p_id, p_name);  
    COMMIT; 
END;  
/

3. Execution fails with

EXEC insert_test(1, 'test1');  
-- Error: ORA-00942: table or view does not exist  

Attempted Solutions

1. Confirmed the table exists

SELECT * FROM test_proc;  -- Works fine  

2. Granted all privileges to the current user

GRANT ALL PRIVILEGES TO current_user;

3. Tried adding the schema prefix

INSERT INTO current_user.test_proc VALUES(...);  
-- Still fails with the same error  

Question

Why can't existing tables be recognized in stored procedures? I’ve reviewed OceanBase’s official PL/SQL documentation but couldn’t resolve this.

I understand that this issue is about the permissions or scope of stored procedures

In OceanBase’s Oracle-compatible mode, stored procedures by default run using the permissions of the person who created them — not the person who calls them.

That means:

• The procedure executes with the privileges of its owner (the one who wrote or created it).
• And when it refers to tables or other objects, it looks for them in the owner’s schema, not the caller’s.

You might want to try adding an explicit schema name. Here’s an example:

CREATE OR REPLACE PROCEDURE insert_test(
    p_id IN NUMBER,
    p_name IN VARCHAR2)
IS
BEGIN
    INSERT INTO YOUR_SCHEMA_NAME.test_proc VALUES(p_id, p_name);
    COMMIT;
END;
/