oracle procedure ORA-00942

Question

I'm trying to execute a Merge procedure Iv'e created but I encounter the following error:

Could not get schema Object:java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

I read in forum about the error and I made sure I'm on the right scheme. In addition, the merge query works properly.

any suggestion?

procedure

create or replace procedure insert_or_update_Account
IS
BEGIN
MERGE INTO  CRMAS_ODS_RAW_DATA.ACCOUNT T
      USING CRMAS_ODS_RAW_DATA.ACCOUNT_TMP S
      ON ( T.TCCID=S.TCCID)
    WHEN MATCHED THEN
    UPDATE 
    SET 
      T.BATCH_ID              =S.BATCH_ID                  ,
  T.SOURCE_SYSTEM       =S.SOURCE_SYSTEM       ,
  T.UPDATE_DATE         =S.UPDATE_DATE         ,
  T.MLI_LOCALID         =S.MLI_LOCALID          
    WHERE 
  T.BATCH_ID                  <>S.BATCH_ID              OR
  T.SOURCE_SYSTEM       <>S.SOURCE_SYSTEM       OR
  T.MLI_LOCALID         <>S.MLI_LOCALID         
  WHEN NOT MATCHED THEN
    INSERT  (T.BATCH_ID,T.SOURCE_SYSTEM,T.UPDATE_DATE,T.TCCID,T.MLI_LOCALID,T.MLI_LOCALSYSTEMNAME)
    VALUES  (S.BATCH_ID,S.SOURCE_SYSTEM,S.UPDATE_DATE,S.TCCID,S.MLI_LOCALID,S.MLI_LOCALSYSTEMNAME);
    commit;
END insert_or_update_Account;

Answer 1

The procedure owner should be granted privileges on CRMAS_ODS_RAW_DATA.ACCOUNT table directly, not via role.

The fact that MERGE works in SQL (but not in PL/SQL) sounds exactly like that.