GRANT to EXECUTE execute immediate truncate table in Oracle

Question

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!!!

That's not Oracle's error stack. Which tool do you use to run that code? Is there any ORA-XXXXX error? — Littlefoot, Jul 26 '22 at 12:00
You might try changing `call DO_TRUNCATE('DOCUMENT_TASKS')` to `BEGIN DO_TRUNCATE('DOCUMENT_TASKS'); END;`. — Bob Jarvis - Слава Україні, Jul 26 '22 at 12:13
You may also have to add the schema qualifier onto the procedure name, e.g. `E.DO_TRUNCATE`. — Bob Jarvis - Слава Україні, Jul 26 '22 at 12:21

score 0 · Answer 1 · answered Jul 26 '22 at 12:07

0

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

answered Jul 26 '22 at 12:07

Sachin Padha

211
1
4

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 31 '22 at 21:28

score 0 · Accepted Answer · answered Jul 26 '22 at 12:22

0

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

answered Jul 26 '22 at 12:22

MT0

143,790
11
59
117

Dear [https://stackoverflow.com/users/1509264/mt0], it´s really runs with : BEGIN E.DO_TRUNCATE ('DOCUMENT_TASKS'); END; in Development database. But User ETL can´t use the schema ***E.*** before on they programming code. Do you have on more tip to grant the procedure wtihout mention ***E.*** ? Best Regards. – Favini Jul 26 '22 at 13:29
@Favini The ETL user could create a synonym to the procedure in the other user's schema. – MT0 Jul 26 '22 at 13:32
Thanks @mo0, I´ll create the synonym, and ask for ETL team to proceed that way. I´ll post if they are suceed! – Favini Jul 26 '22 at 13:47
Thanks all, particularly to@mod0. It works!!! – Favini Jul 26 '22 at 15:44

GRANT to EXECUTE execute immediate truncate table in Oracle

2 Answers2