SQL Loader + ORA-01031
milan vallecha
System : Windows 2000. Oracle Release 8.1.7.0.0 - Production + SQL
Loader
My database has the following table revolving around this problem:
1) TEMP
When I try to insert a ROW in the TEMP table.
The following trigger is fired:
CREATE OR REPLACE TRIGGER delete_before_insert
BEFORE INSERT ON temp
FOR EACH ROW
BEGIN
DELETE FROM TEMP;
END;
The requirement is to empty/delete the existing row in the TEMP table.
i.e. at any given point of time there should be only one row in the
TEMP table.
The error message says:
SQL*Loader-926: OCI error while executing delete/truncate (due to
REPLACE/TRUNCATE keyword) for table "POC"."TEMP"
ORA-01031: insufficient privileges
When I execute the command :
DELETE FROM TEMP; at the SQL prompt, I do not get any error. However
when this happens through the trigger, I get the above error.
Could anyone suggest me the privileges, We need to grant to this
Oracle user to have sufficient rights to perform a DELETE through a
trigger on the TEMP table ?
Would really appreciate a prompt reply.
TIA
Milan
Sybrand Bakker
vallecha) wrote:
A few remarks
1 The trigger above fires for every row. Hence you will never get
*anything* in your table.
2 You have set up the control file to delete all rows in your table,
but you don't have sufficient privileges to truncate the table,
you need to have the DELETE ANY TABLE privilege for that purpose.
3 If you GRANT DELETE ANY TABLE privilege to the user connecting in
the loader session, the trigger becomes completely redundant
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address