Oracle 10g - UTL_MAIL package

Question

I'm having a bit of trouble with the UTL_MAIL package in Oracle 10g, and was wondering if anyone had any solutions?

I connect to my DB as SYSMAN and load the following two scripts;

@C:\oracle\product\10.2.0\db_1\rdbms\admin\utlmail.sql

@C:\oracle\product\10.2.0\db_1\rdbms\admin\prvtmail.plb

I set up the SMTP server;

ALTER SYSTEM SET smtp_out_server='mymailserver.fake:25' SCOPE=BOTH;

I grant the user the required permission;

GRANT execute ON utl_mail TO MYUSER;

But then if I connect to the "MYTABLESPACE" (where MYUSER exists), I get the following error if I make reference to UTL_MAIL.SEND;

PLS-00201: identifier 'UTL_MAIL.SEND' must be declared

If I prefix it with SYSMAN though (SYSMAN.UTL_MAIL.SEND), it works, but I don't want to do this as this procedure that contains this call has no knowledge of the tablespace which installed the scripts.

Is there a way to install these scripts so that they are accessible universally, and do not require the SYSMAN prefix to execute?

Cheers,

Chris

Answer 1

Sounds like you need to create a PUBLIC SYNONYM for the package..

CREATE PUBLIC SYNONYM UTL_MAIL FOR SYSMAN.UTL_MAIL;