Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL Loader + ORA-01031

371 views
Skip to first unread message

milan vallecha

unread,
Feb 28, 2003, 4:41:25 AM2/28/03
to
Hi All,

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

unread,
Feb 28, 2003, 1:51:54 PM2/28/03
to
On 28 Feb 2003 01:41:25 -0800, milanv...@hotmail.com (milan
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

0 new messages