Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Im getting insufficient privileges error while executing the following oracle stored procedure. Im 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;

Im using the post Update oracle sql database from CSV to build this SP. I could compile this stored procedure sucessfully. I have all the rights for the oracle user because im the admin. I have given all possible rigts.

But when i execute the SP im 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, its executing without any problem.

What could be the reason for this? How can i resolve the issue?

share|improve this question
    
What Oracle version? –  FreshPhilOfSO Sep 12 '13 at 13:10
    
I'm using the version Oracle 11g. –  suren Sep 12 '13 at 13:16
    
Why drop the table? Why not just truncate it? –  Colin 't Hart Dec 11 '13 at 16:46
add comment

2 Answers

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

share|improve this answer
    
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 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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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