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