Home » SQL & PL/SQL » SQL & PL/SQL » ORA-04042 issue when trying to grant the access (Oracle)
ORA-04042 issue when trying to grant the access [message #680192] Mon, 27 April 2020 23:29 Go to next message
raghuguru
Messages: 6
Registered: April 2020
Junior Member
Hi all,

I am very new to the Oracle and now got a new task and it is to send an email through the PL/SQL and looking for help on this one.

1. I installed the SQL Developer and user name i kept as sys at the time of installation.
2. I looked at the packages for the

C:\oracle18c\rdbms\admin\utlmail.sql and when i am trying to grant the permissions to the user i am getting an error.

I loged into SQLPLUS and as SYS AS SYSDBA and and used the global password as sysdba and it logged in.

SHOW USER ;

it is showing as SYS

SQL> grant execute on utl_mail to sys;
grant execute on utl_mail to sys
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

Can someone please help me how to grant the user with the perimission.. please or if any idea that will be really helpful for my project

Re: ORA-04042 issue when trying to grant the access [message #680193 is a reply to message #680192] Tue, 28 April 2020 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

There are many errors in your post:
1/ You don't have to grant a privilege to the owner of an object: the onwer of an object has all privileges on it
2/ You don't have to grant a privilege to SYS: SYS has all privileges
3/ The error shows you didn't execute the script or you didn't execute it as SYS which is mandatory.
4/ Use SQL*Plus and copy and paste your session, the WHOLE session otherwise we can't know what you have done wrong.
5/ You can't do DBA stuff without some knowledge of Oracle

Database Concepts
2 Day DBA
Administrator's Guide

Re: ORA-04042 issue when trying to grant the access [message #680194 is a reply to message #680192] Tue, 28 April 2020 01:09 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
You need to create the package and package body. Some more reading for you:

https://docs.oracle.com/database/121/ARPLS/u_mail.htm#ARPLS384

(be sure to read MC's links first Smile )
Re: ORA-04042 issue when trying to grant the access [message #680207 is a reply to message #680193] Tue, 28 April 2020 23:11 Go to previous messageGo to next message
raghuguru
Messages: 6
Registered: April 2020
Junior Member
Thanks for responding Michel.
1/ You don't have to grant a privilege to the owner of an object: the onwer of an object has all privileges on it
2/ You don't have to grant a privilege to SYS: SYS has all privileges

I am clear with the above two things, that i don't have to grant a privilege as SYS will have all the privileges


3/ The error shows you didn't execute the script or you didn't execute it as SYS which is mandatory.

I did executed the script but

BEGIN
UTL_MAIL.send(sender => 'abcd.study@.com',
recipients => 'abcd.study@.com',

subject => 'UTL_MAIL Test',
message => 'If you get this message it worked!');
END;
/

ORA-06550: line 2, column 3:
PLS-00201: identifier 'UTL_MAIL.SEND' must be declared
ORA-06550: line 2, column 3:

I am getting the above error message.

Thank you for sharing the useful documents.


Re: ORA-04042 issue when trying to grant the access [message #680210 is a reply to message #680207] Tue, 28 April 2020 23:48 Go to previous messageGo to next message
raghuguru
Messages: 6
Registered: April 2020
Junior Member
Michel..

I installed the following packages.


@C:\oracle18c\rdbms\admin\prvtmail.plb

@C:\oracle18c\rdbms\admin\utlmail.sql

Then i executed the following code.

BEGIN
UTL_MAIL.send(sender => 'abcd.study@.com',
recipients => 'abcd.study@.com',

subject => 'UTL_MAIL Test',
message => 'If you get this message it worked!');
END;
/


I am still getting the below error

Error report -
ORA-29279: SMTP permanent error: 501 <abcd.study@.com>: domain missing or malformed
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 142
ORA-06512: at "SYS.UTL_SMTP", line 476
ORA-06512: at "SYS.UTL_MAIL", line 444
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2
29279. 00000 - "SMTP permanent error: %s"
*Cause: A SMTP permanent error occurred.
*Action: Correct the error and retry the SMTP operation.

Please let me know how can i resolve this issue.. please.
Re: ORA-04042 issue when trying to grant the access [message #680211 is a reply to message #680194] Tue, 28 April 2020 23:50 Go to previous messageGo to next message
raghuguru
Messages: 6
Registered: April 2020
Junior Member
Hi John..

Thank you for responding to the post and i installed the packages and still getting an error and also posted the error below and also to Michel


BEGIN
UTL_MAIL.send(sender => 'abcd.study@.com',
recipients => 'abcd.study@.com',

subject => 'UTL_MAIL Test',
message => 'If you get this message it worked!');
END;
/


I am still getting the below error

Error report -
ORA-29279: SMTP permanent error: 501 <abcd.study@.com>: domain missing or malformed
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 142
ORA-06512: at "SYS.UTL_SMTP", line 476
ORA-06512: at "SYS.UTL_MAIL", line 444
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2
29279. 00000 - "SMTP permanent error: %s"
*Cause: A SMTP permanent error occurred.
*Action: Correct the error and retry the SMTP operation.


Please let me know .. how to resolve this type of issue if you came across.. please

Re: ORA-04042 issue when trying to grant the access [message #680213 is a reply to message #680211] Wed, 29 April 2020 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

(Note that the order of the execution if the scripts is first "utlmail.sql" and then "prvtmail.plb".)

The error is obvious (and has nothing with Oracle): ".com" is not a valid domain name; you should first try to send a message from your usual (not Oracle) mail tool and then when it works try it in PL/SQL.

Re: ORA-04042 issue when trying to grant the access [message #680214 is a reply to message #680210] Wed, 29 April 2020 00:56 Go to previous messageGo to next message
raghuguru
Messages: 6
Registered: April 2020
Junior Member
Hi Michel,

I came to know the domain name is missing after '@' iam missing @gmail

So i entered and then i started getting the new error message.



BEGIN
UTL_mail.send(sender => 'abcd.study@gmail.com',
recipients => 'abcd.study@gmail.com',

subject => 'UTL_MAIL Test',
message => 'If you get this message it worked!');
END;
/




Error report -
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 432
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2
29278. 00000 - "SMTP transient error: %s"
*Cause: A SMTP transient error occurred.
*Action: Correct the error and retry the SMTP operation.


Do you happen to have any idea on the error message, please
Re: ORA-04042 issue when trying to grant the access [message #680215 is a reply to message #680211] Wed, 29 April 2020 00:57 Go to previous messageGo to next message
raghuguru
Messages: 6
Registered: April 2020
Junior Member
Hi John,


I came to know the domain name is missing after '@' iam missing @gmail

So i entered and then i started getting the new error message.



BEGIN
UTL_mail.send(sender => 'abcd.study@gmail.com',
recipients => 'abcd.study@gmail.com',

subject => 'UTL_MAIL Test',
message => 'If you get this message it worked!');
END;
/




Error report -
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 432
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2
29278. 00000 - "SMTP transient error: %s"
*Cause: A SMTP transient error occurred.
*Action: Correct the error and retry the SMTP operation.


Do you happen to have any idea on the error message, please
Re: ORA-04042 issue when trying to grant the access [message #680216 is a reply to message #680215] Wed, 29 April 2020 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you set a SMTP server?
To use gmail SMTP server you need to give credentials.
This has already address several times here, please do search.

Please format your post as per:

Michel Cadot wrote on Tue, 28 April 2020 07:23

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
...

[Updated on: Wed, 29 April 2020 01:03]

Report message to a moderator

Re: ORA-04042 issue when trying to grant the access [message #680217 is a reply to message #680215] Wed, 29 April 2020 01:10 Go to previous messageGo to next message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
THat is the message you receive if you have not set your smtp server correctly, as detailed in the docs you should have read by now. For example,
orclz> alter system set smtp_out_server='nowhere.at.all:25';

System altered.

orclz> exec UTL_mail.send(sender => 'abcd.study@gmail.com',recipients => 'abcd.study@gmail.com',subject => 'UTL_MAIL Test', message => 'If you get this message it worked!')
BEGIN UTL_mail.send(sender => 'abcd.study@gmail.com',recipients => 'abcd.study@gmail.com',subject => 'UTL_MAIL Test', message => 'If you get this message it worked!'); END;

*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 432
ORA-06512: at "SYS.UTL_SMTP", line 57
ORA-06512: at "SYS.UTL_SMTP", line 140
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 1


orclz>
Re: ORA-04042 issue when trying to grant the access [message #680230 is a reply to message #680211] Wed, 29 April 2020 08:02 Go to previous message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
raghuguru wrote on Tue, 28 April 2020 23:50
Hi John..

Thank you for responding to the post and i installed the packages and still getting an error and also posted the error below and also to Michel
There is no need to post twice. You do not post 'to Michael', you post 'to' the message thread. Everyone reading the thread can see every reply. Posting the same message twice, just to post 'to' multiple individuals, only clutters the thread and makes it that much harder to follow.
Previous Topic: "ORA-00942: table or view does not exist" when creating view via role's privilege
Next Topic: drithsx
Goto Forum:
  


Current Time: Fri Sep 18 06:19:25 CDT 2020