Oracle 10g - UTL_MAIL Package

Question

I was having problems with the UTL_MAIL package in Oracle 10g and I was wondering if anyone has 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 installed an SMTP server,

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

I give the user the required permission;

GRANT execute ON utl_mail TO MYUSER;

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

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

If I prefix it with SYSMAN (SYSMAN.UTL_MAIL.SEND), it works, but I do not want to do this, because this procedure containing this call does not know about the table space in which the scripts are installed.

Is there a way to set these scripts so that they are accessible everywhere and do not require the SYSMAN prefix to be executed?

Greetings

Chris

Answer 1

I am sure that public synonyms will be the only difference.

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

confirm or reject

Answer 2

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

 CREATE PUBLIC SYNONYM UTL_MAIL FOR SYSMAN.UTL_MAIL; 

Answer 3

try ALTER SYSTEM SET smtp_out_server='mymailserver.fake:25' SCOPE=BOTH; as the user with whom you are executing the procedure, and not as sys.

T. Connect to MYTABLESPACE as MYUSER and start a change session. Hope is clear.

Answer 4

log in as sys and run scripts