Re: P/L SQL Exception

Amine Y. Tarhini wrote:
>
> 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.

How are you checking whether the user is allowed to delete? Are you perhaps selecting from a table for which said user doesn't have even SELECT privilege? (The full error message should make it clear)

Surely it would be easier to grant / not grant the DELETE privilege explicitly to the user(s). Received on Tue Feb 18 1997 - 00:00:00 CST