CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Dinu Nicolae
Dinu Nicolae

Reputation: 1301

How to throw an exception in PL SQL?

I have this procedure:

CREATE OR REPLACE PROCEDURE SCHEMA.check_privs(
can_grant OUT NUMBER
)IS
BEGIN
    SELECT count(*) INTO can_grant
            FROM USER_SYS_PRIVS usp
            WHERE PRIVILEGE = 'CREATE DATABASE LINK';
         
           
           EXCEPTION
           WHEN can_grant = 0
DBMS_OUTPUT.PUT_LINE('error');

    END;

How would I throw an exception in case can_grant is 0? The exception part does not work.

Upvotes: 1

Views: 1056

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

The easiest way would be this one:

BEGIN
   SELECT count(*) INTO can_grant
   FROM USER_SYS_PRIVS usp
   WHERE PRIVILEGE = 'CREATE DATABASE LINK';
         
   IF can_grant = 0 THEN
      DBMS_OUTPUT.PUT_LINE('error');
      RAISE_APPLICATION_ERROR(-20000, 'Count is zero');
   END IF;
END;

Note, there could be other privileges (e.g. CREATE PUBLIC DATABASE LINK or DBA ROLE) which would raise the error but permission to create a DB-Link is actually granted.

Upvotes: 2

Related Questions