I'm getting an insufficient privileges error while executing the following oracle stored procedure. I'm using Oracle Database 10g Express Edition.

PROCEDURE sp_update_acounts(
    accounts_file_dir  IN VARCHAR2,
    accounts_file_name IN VARCHAR2)

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';

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
  SELECT entity_account_id,
  FROM crm_account_stage) acc_stage
ON (acc_stage.entity_account_id = acc.pkey)
  UPDATE SET acc.crm_id = acc_stage.crm_id;


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?


The pl/sql execution context does not include Role. So your risght have been delegated by a role. The Pl/SQL doesn't see it. So, first, you can try to give the right to alter the user directly not via a role. 2nd, i would try this option on the package definition (auhtid) which is explained here

  • 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, its executing without any problem. I'm still not able to understand. – Suren Raju Sep 12 '13 at 13:14
  • you are modifying your user by granting it to read/write on the directory. – Nicolas Durand Sep 12 '13 at 13:25

Some like that is possible in MSSQL but not in Oracle. Simply PL/SQL is compiled language, but not scripting one. Oracle "silently" compiles it into bytecode on background. Your procedure's sp_update_acounts bytecode depends on definition of the table crm_account_stage (in fact it depends on it's object_id). Your "eval" - dynamic sql - drops this object thus it invalidates all the depending objects. They have to be recompiled. But they can not be, because the procedure is running.

This approach is wrong from Oracle's perspective.

For Oracle the rule of thumb is: "Avoid using DDL for business logic".

Try to use: alter table instead of drop and create. Or use execute immediate also for merge statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.