KathysFriend
asked on
utl_mail - can't access this procedure
I ran the code below to set up utl_mail on our database. It succesfully created the package and public synonym for utl_mail.
I'm logged on as sysman and I can browse the schema in Toad and see the package and synonym. When I try to exec the package with or without the schema prefix. I get PLS-00201: identifier 'SYS.UTL_MAIL' must be declared. Then I tried to grant persmission for my schema
grant execute on sys.utl_mail to uschema; and I got the following error:
ORA-00942: table or view does not exist
I'm logged on as sysman and I can browse the schema in Toad and see the package and synonym. When I try to exec the package with or without the schema prefix. I get PLS-00201: identifier 'SYS.UTL_MAIL' must be declared. Then I tried to grant persmission for my schema
grant execute on sys.utl_mail to uschema; and I got the following error:
ORA-00942: table or view does not exist
sqlplus / as sysdba
@utlmail.sql
@prvtmail.plb
Set smtp_server information in init.ora or spfile.ora
alter system set smtp_out_server = 'SMTP_SERVER_IP_ADDRESS:SMTP_PORT' scope=both;
25 = Default SMTP Port
show parameter spfile;
alter system set smtp_out_server = 'xxx.xxx.xx.x:25' scope=both;
ASKER
OWNER OBJECT_NAME OBJECT_TYPE
SYS UTL_MAIL PACKAGE
SYS UTL_MAIL PACKAGE BODY
PUBLIC UTL_MAIL SYNONYM
What procedure of the package are you trying to execte?
ASKER
exec utl_mail.send(sender=>'pie rcecl@gov. ca',recipi ents=>'pie rcecl@gov. ca', subject=>'Subject ', message => 'Message Body');
and...
grant execute on sys.utl_mail to uschema; ...or...
grant execute on utl_mail to ushema;
and...
grant execute on sys.utl_mail to uschema; ...or...
grant execute on utl_mail to ushema;
Hi,
Could it be that the package compiled with errors?
show errors package UTL_MAIL
show errors package body UTL_MAIL
Could it be that the package compiled with errors?
show errors package UTL_MAIL
show errors package body UTL_MAIL
ASKER
There are no errors.
The package compiled fine.
The package compiled fine.
Did you try to run the package from the sys-schema or only from your schema?
ASKER
There are no errors - the package compiled successfully
SQL> show errors package UTL_MAIL
No errors.
SQL> show errors package body UTL_MAIL
No errors.
SQL>
SQL> show errors package UTL_MAIL
No errors.
SQL> show errors package body UTL_MAIL
No errors.
SQL>
Hi,
Well, that does not necessarily be too conclusive if the db does
not find the package.
(For example:
TEST> show errors package test
No errors.
TEST> select distinct name from all_source
where upper(name) = 'TEST';
No rows selected.
Therefore, the question was if you only tried to run the send-command
from uschema or also logged in as SYS.
Well, that does not necessarily be too conclusive if the db does
not find the package.
(For example:
TEST> show errors package test
No errors.
TEST> select distinct name from all_source
where upper(name) = 'TEST';
No rows selected.
Therefore, the question was if you only tried to run the send-command
from uschema or also logged in as SYS.
ASKER
Your last comment made me realize that I was running as sysman and although he had permission to see the package he couldn't execute or grant permission. I signed in as sys and granted all the permissions. Thanks. But of couse now it can execute the package but it is not sending mail - back to the drawing board.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT owner, name FROM ALL_OBJECTS WHERE
object_name = 'UTL_MAIL';
Thus you will find out, if it's really in the sys-schema.