Re: SQL Loader + ORA-01031

On 28 Feb 2003 01:41:25 -0800, milanvallecha_at_hotmail.com (milan vallecha) wrote:

>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

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 Received on Fri Feb 28 2003 - 12:51:54 CST