GRANT to EXECUTE execute immediate truncate table in Oracle-CodePudding

My user is owner of a simple schema in an instance of Oracle in my Job, let´s call my USER E, with some resctinct privileges. Also I have an USER E_ETL, to receive information of another database with ETL techonology. My user E is the owner of some tables and a procedure DO_TRUNCATE (E.DOCUMENT_TASKS and E.DO_TRUNCATE), and the user E_ETL uses every day the procedure E.DO_TRUNCATE to clean all data inside my E.DOCUMENT_TASKS and insert the flash new information. But I´m having problems to GRANT user E_ETL to execute immediate the function to truncate table E.DOCUMENT_TASKS, the code and the error is those:

E.DOCUMENT_TASKS

  CREATE TABLE "E"."DOCUMENT_TASKS" 
   (    
    "DOCUMENT" VARCHAR2(20 BYTE), 
    "REVISION" VARCHAR2(5 BYTE), 
    "TITLE" VARCHAR2(300 BYTE), 
    "STATUS" VARCHAR2(50 BYTE), 
    "TASK" VARCHAR2(120 BYTE), 
    "ETL_DATE" TIMESTAMP (6) DEFAULT SYSDATE NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "E_D_01" ;

The E.DO_TRUNCATE procedure code is:

create PROCEDURE DO_TRUNCATE ( ptname in varchar2)
as
begin
execute immediate 'truncate table '||upper(ptname);
end;

I alread gave some grants for E_ETL user:

GRANT SELECT, DELETE, INDEX, INSERT, REFERENCES, UPDATE ON E.DOCUMENT_TASKS TO E_ETL;
GRANT EXECUTE ON DO_TRUNCATE TO E_ETL;

But I still have this error information:

Database driver error...
Function Name : executeDirect
SQL Stmt : call DO_TRUNCATE ('DOCUMENT_TASKS')
Oracle Fatal Error
Database driver error...
Function Name : ExecuteDirect

Thanks all!!!

CodePudding user response：

The error Database driver error... suggests that you might have an error connecting to the database; if so then you will need to sort that (but since you have not provided the error message then we cannot make suggestions).

Other issues could be:

Change CALL to Oracle's BEGIN END syntax:

BEGIN DO_TRUNCATE ('DOCUMENT_TASKS'); END;

If you are not connecting as the E user who owns the procedure and tables then make sure you include the schema name:
```
BEGIN E.DO_TRUNCATE ('E.DOCUMENT_TASKS'); END;
```

CodePudding user response：

This is not Oracle error. Also it is saying about "executeDirect". Please elaborate more about the error