Oracle Error ORA-06550 & PLS-00201: DBMS_CLOUD_NOTIFICATION Must Be Declared

ORA-06550: line 2, column 4:

PLS-00201: identifier ‘DBMS_CLOUD_NOTIFICATION’ must be declared

ORA-06550: line 2, column 4:

PL/SQL: Statement ignored

https://docs.oracle.com/error-help/db/ora-06550/

Error at Line: 7 Column: 0

The error occurs when executing the PL/SQL block:

ORA-06550: General PL/SQL compilation or execution error.

PLS-00201: The DBMS_CLOUD_NOTIFICATION package is not recognized.

Causes for this error

Package Does Not Exist: The DBMS_CLOUD_NOTIFICATION package might not be installed in your database.
Insufficient Privileges: The user does not have the required permissions to execute the package.
Incorrect Syntax or Spelling: A typo in the package name can cause this error.
Incompatible Database Version: The package might not be available in the current Oracle Cloud Infrastructure (OCI) database version.

Solutions & Fixes

Solution 1: Check Database Version Compatibility

Run the following command to check your database version:

SELECT * FROM V$VERSION;

If using an older version of Oracle, DBMS_CLOUD_NOTIFICATION may not be available.
Fix: Upgrade your database or use an alternative method (e.g., UTL_SMTP).

Solution 2: Check If the Package Exists

-> Run the following SQL query to verify whether DBMS_CLOUD_NOTIFICATION is available:

SELECT * FROM DBA_PROCEDURES WHERE OBJECT_NAME = ‘DBMS_CLOUD_NOTIFICATION’;

-> If no rows are returned, the package is missing.

-> If the package exists but is invalid, it needs recompilation.

Fix: Ensure the Required Packages Are Installed

-> If DBMS_CLOUD_NOTIFICATION is missing, check whether DBMS_CLOUD is installed:

SELECT * FROM DBA_PROCEDURES WHERE OBJECT_NAME = ‘DBMS_CLOUD’;

-> If missing, install or enable DBMS_CLOUD by running:

GRANT EXECUTE ON DBMS_CLOUD TO <your_user>;

Solution 3: Grant Required Privileges

The package may exist, but the user may not have permission to execute it. Run the following command as an admin user (SYSDBA):

GRANT EXECUTE ON DBMS_CLOUD_NOTIFICATION TO <your_user>;

Replace <your_user> with the correct username.

Solution 4: Alternative Method Using SMTP (If DBMS_CLOUD_NOTIFICATION is Unavailable)

If the package is unavailable, use SMTP to send emails manually:

Step 1: Allow SMTP Access

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(

host => ‘smtp.email.us-phoenix-1.oci.oraclecloud.com’,

lower_port => 587,

upper_port => 587,

ace => xs$ace_type(privilege_list => xs$name_list(‘SMTP’),

principal_name => ‘ADMIN’,

principal_type => xs_acl.ptype_db)

);

END;

Step 2: Use UTL_SMTP to Send Emails

DECLARE

mail_conn UTL_SMTP.CONNECTION;

mail_host VARCHAR2(100) := ‘smtp.email.us-phoenix-1.oci.oraclecloud.com’;

sender VARCHAR2(100) := ‘you@example.com’;

recipient VARCHAR2(100) := ‘recipient@example.com’;

message VARCHAR2(4000) := ‘Hello, this is a test email from Oracle.’;

BEGIN

mail_conn := UTL_SMTP.OPEN_CONNECTION(mail_host, 587);

UTL_SMTP.HELO(mail_conn, mail_host);

UTL_SMTP.MAIL(mail_conn, sender);

UTL_SMTP.RCPT(mail_conn, recipient);

UTL_SMTP.DATA(mail_conn, message);

UTL_SMTP.QUIT(mail_conn);