P/L SQL Exception

From: Amine Y. Tarhini <atarhini_at_ringer.cs.utsa.edu>
Date: 1997/02/15
Message-ID: <5e3kt2$jdb@ring01.cs.utsa.edu>#1/1

In order to automate the task of deleting some records, I wrote a small SQL*Plus script with a P/L SQL block to do this.

At the begining of the P/L SQL block, I check the USER variable, and based on its value, I raise an exception if the operator should not be deleting records. However, When testing the script logged in as a user who has no such privilege, I get the following errors

    ORA-06550: line .. column ..
    PLS-00904: insufficient privilege to ..
    ORA-06550: line .. column ..

    PL/SQL: SQL statement ignored.
and the script won't even run. (it does if the user has privileges).

I tried to trap this error with the WHEN OTHERS THEN clause, then by the PRAGMA EXCEPTION_INIT(my_exception,-6550), but neither method worked.

Is there a way to get around this? and why doesn't ORACLE wait until an attempt to delete and/or update is actually made to return the error? In the above script, they won't even be executed based on the initial check.

Your help is appreciated, whether Post or E-mail..

-- 
Amine.
atarhini_at_ringer.cs.utsa.edu

The above views are mine only ... etc. etc.

Received on Sat Feb 15 1997 - 00:00:00 CST