Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5356
  • Last Modified:

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
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;

Open in new window

0
KathysFriend
Asked:
KathysFriend
  • 6
  • 5
1 Solution
 
MarkusIdCommented:
Try:

SELECT owner, name FROM ALL_OBJECTS WHERE
object_name = 'UTL_MAIL';

Thus you will find out, if it's really in the sys-schema.
0
 
KathysFriendDBAAuthor Commented:


OWNER      OBJECT_NAME      OBJECT_TYPE
SYS      UTL_MAIL                           PACKAGE
SYS      UTL_MAIL                           PACKAGE BODY
PUBLIC      UTL_MAIL                           SYNONYM
0
 
MarkusIdCommented:
What procedure of the package are you trying to execte?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
KathysFriendDBAAuthor Commented:
exec utl_mail.send(sender=>'piercecl@gov.ca',recipients=>'piercecl@gov.ca', subject=>'Subject ', message => 'Message Body');

and...

grant execute on sys.utl_mail to uschema;  ...or...
grant execute on utl_mail to ushema;

0
 
MarkusIdCommented:
Hi,

Could it be that the package compiled with errors?

show errors package UTL_MAIL
show errors package body UTL_MAIL
0
 
KathysFriendDBAAuthor Commented:
There are no errors.
The package compiled fine.

0
 
MarkusIdCommented:
Did you try to run the package from the sys-schema or only from your schema?
0
 
KathysFriendDBAAuthor Commented:
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>
0
 
MarkusIdCommented:
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.
0
 
KathysFriendDBAAuthor Commented:
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.
0
 
MarkusIdCommented:
Well, have a look in the alert.log-file on the db-server. Maybe there's
an error that can tell you what went wrong.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now