Oracle 10g - UTL_MAIL package

4
votes

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

4
oracleemail
in addition to adding a PUBLIC synonym, its preferred that you install it using SYSDBA as advised by Oracle Experts. Read more here: dba-oracle.com/oracle_tips_10g_utl_mail.htm oracle-base.com/articles/10g/plsql-enhancements-10g#UTL_MAIL - Migs Isip

4 Answers

5
votes

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

4
votes

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

CREATE PUBLIC SYNONYM UTL_MAIL FOR SYSMAN.UTL_MAIL;
1
votes

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

-2
votes

logon as sys and run the scripts