Oracle 10g - UTL_MAIL package

Question

Hi everyone,

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;

Answer 2

That could be workable, thanks.

However, I'd really prefer it if I could figure out a way to access this package in the same way as any of the other Oracle-supplied packages (without any special prefix or synonyms).

I'm trying to understand what is different from say, the UTL_SMTP package which is installed by default and doesn't require a prefix, and the UTL_MAIL package, which is installed manually and does need the prefix. Is it the fact that it's installed manually that causes this difference? Do you know if there's a different way to install it, or a difference privilege to grant, which would remove the need for this SYSMAN. prefix?

Thanks for your help, much appreciated.

Answer 3

I'm pretty sure that public synonyms will be the only difference.

SELECT * FROM ALL_SYNONYMS WHERE OWNER = 'PUBLIC' and table_name LIKE 'UTL%'

will confirm or deny

Answer 4

Great, you were right. Those other packages were all listed there as synonyms of SYS..

Cheers mate, very much appreciated.

Answer 5

logon as sys and run the scripts

Answer 6

try ALTER SYSTEM SET smtp_out_server='mymailserver.fake:25' SCOPE=BOTH; as the user you are running the procedure not as sys.

ie. connect to MYTABLESPACE as MYUSER and run the alter session Hope am clear

Answer 7

Can somebody please advice using special character in message body is allowed or not? I am using a word "month's" in the nessage body but it is getting replaced by "month?s" in the email. Please HELP!!!!