I'm getting an insufficient privileges error while executing the following oracle stored procedure. I'm using Oracle Database 10g Express Edition.
CREATE OR REPLACE PROCEDURE sp_update_acounts( accounts_file_dir IN VARCHAR2, accounts_file_name IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ext_accounts_dir AS '''||accounts_file_dir||''''; EXECUTE IMMEDIATE 'grant read, write on directory ext_accounts_dir to myuser'; EXECUTE IMMEDIATE 'drop table crm_account_stage'; EXECUTE IMMEDIATE 'CREATE TABLE crm_account_stage (entity_account_id NUMBER(19,0), crm_id VARCHAR2(255 CHAR)) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_accounts_dir ACCESS PARAMETERS (FIELDS TERMINATED BY '','' ( entity_account_id CHAR(225), crm_id CHAR(225))) LOCATION ('''||accounts_file_name||''''||') )'; MERGE INTO ua_crm_accounts acc USING ( SELECT entity_account_id, crm_id FROM crm_account_stage) acc_stage ON (acc_stage.entity_account_id = acc.pkey) WHEN MATCHED THEN UPDATE SET acc.crm_id = acc_stage.crm_id; END;
I'm using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure successfully. I have all the rights for the oracle user because I'm the admin. I have given all possible rights.
But when I execute the SP I'm getting error like:
Error starting at line 13 in command: execute sp_update_acounts('C:\Users\surenr\Desktop\UA\Intitial-Conversion','acc_data.csv') Error report: ORA-01031: insufficient privileges ORA-06512: at "myuser.SP_UPDATE_ACOUNTS", line 7 ORA-06512: at line 1 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login. *Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.
Update: I'm not trying to update user or password. But the error message says as i'm trying to modify user details. When I try the same code step by step outside the stored procedure, it's executing without any problem.
What could be the reason for this? How can I resolve the issue?