Home » RDBMS Server » Server Administration » Revalidating utl_mail package (FORMS,6i)
icon5.gif  Revalidating utl_mail package [message #306983] Mon, 17 March 2008 07:21 Go to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Dear All,
Really, I need your help in the following I am trying to use mail procedure which is created on 10g DB through forms 6i, but each time an error appears saying that:
“Not executed, package body "XXXX.UTL_SMTP" does not exist
PL/SQL: could not find program unit beingcalled: "XXX.UTL_SMTP"

but when I searched for UTL_SMTP I face the problem of that the package specification already created and can be edited but for the same package body it not applicable to read it.

for sending email I am using the following code to call mail procedure,
email.mail
      (sender     => 'sender name',
       recipients => :mailid,
       subject    => 'Change Request '|| crnumdisp || ' REJECTED',
       message    => mess);


Also the mail procedure code like follow:
PROCEDURE mail
  (
  sender      IN VARCHAR2,
  recipient   IN VARCHAR2,
  ccrecipient IN VARCHAR2,
  subject     IN VARCHAR2,
  message     IN VARCHAR2
  ) IS
  crlf VARCHAR2(2):= UTL_TCP.CRLF;
  connection utl_smtp.connection;
  mailhost VARCHAR2(30) := '200.200.200.8';
  header VARCHAR2(1000);
BEGIN
  -- Start the connection.
  connection := utl_smtp.open_connection(mailhost,25);
  header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
     'From: '||sender||''||crlf||
  'Subject: '||subject||crlf||
       'To: '||recipient||crlf||
       'CC: '||ccrecipient;
  -- Handshake with the SMTP server
  utl_smtp.helo(connection, mailhost);
  utl_smtp.mail(connection, sender);
  utl_smtp.rcpt(connection, recipient);
  utl_smtp.rcpt(connection, ccrecipient);
  utl_smtp.open_data(connection);
  -- Write the header
  utl_smtp.write_data(connection, header);
  utl_smtp.write_data(connection, crlf ||message);
  utl_smtp.close_data(connection);
  utl_smtp.quit(connection);
EXCEPTION
  WHEN UTL_SMTP.INVALID_OPERATION THEN
    dbms_output.put_line(' Invalid Operation in SMTP transaction.');
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    dbms_output.put_line(' Temporary problems with sending email - try again later.');
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    dbms_output.put_line(' Errors in code for SMTP transaction.');   
END;


Please help me to find a solution for this.

Regards,,,

Upd: Title was "Sending EMAIL FROM 6I FORMS USING 10g DB"

[Updated on: Thu, 03 April 2008 18:15] by Moderator

Report message to a moderator

Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #307519 is a reply to message #306983] Wed, 19 March 2008 01:14 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Are you using the same USERID when you run the form as you used when you compile the form?

David
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #307627 is a reply to message #307519] Wed, 19 March 2008 05:34 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Yes I am using the same user name and password .
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #307768 is a reply to message #307627] Wed, 19 March 2008 18:17 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I googled 'Not executed package body does not exist' and found http://www.experts-exchange.com/Database/Oracle/Q_20731639.html (you have to go to the very bottom for the answers.

Are both the spec and body of UTL_SMTP valid?

Can you get any of the routines in UTL_SMTP to run at the SQL*Plus prompt?

David
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #307825 is a reply to message #307768] Thu, 20 March 2008 01:51 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

The main problem that if I use 9i forms it will work, but the 6i forms can not deal with 10g DB, like the following:
if you go to DATA BASE OBJECTS IN 6i FORMS and then to the DB for example XX then select stored program unit, then select UTL_MAIL if you go to edit mode you can see the source code for package specifications, but for body you will see the following:
PACKAGE BODY utl_mail wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
b
4c41 125c
1Eb6Lb2bpjL14YTzHKu1kHekt2kwg826EiAFYNOdnYE+MxgcFmYJJz706wOKpo4N8UJjWo0a
bjZXqWl2Y1zusTGeUKY6pXj4yv6hGBqkDkTmFGGgaEKKiiAXn8mx1XK1lbpwIfAXu7PBc1w/
7TFMa8rNgS+ehhH3TwsgQLfPHI+4QORX8oKWl2kvATA3tvXAQEoPLg6W5BPUHhGLbLvpZJmW
CHLtkWgsnXufoQ8XFgCWFyT4hpwXOs6V5Co/KfCHWcaWh+1BvLgbAP9QE0OHwlSWf6XmzpDQ
6pMvgKJoxQhvDK6gQzWOkRBYcKlAhqrQ0IDRhtRXwqoKFDMIfpBwoN0xxd0iufgeFPtmkkSV
9mt7ZiQAJNYt6FeEh9Ii/uv77uBCZS9KAyEMrBH7G/z/FpUiiFzjjOmf3FfpHkOOSdFYBEuZ
hzbzbMN3OfjoHj5au9kjdFARDpvHSQCEMcWjXPcXEaL0UiETFm+QZO+D0AQhDyM1da7tFqxr
CaG9/zyhFFY1WzfIaGSilxHkgpSo1HUGL4ln/9Opvu2ALLzKwR7qMX5xUtTefKs299I9NUra
dr7FAtY8k1qB9RGCDq08jlHNThSHFWRtLJa48RWlEYYFx3SFNaHkbxtD9VmyzErM4DgqiTNk
oLoExp3Y6jp7kjKkFzCntgvSOASixB0vwu9aI+ROFI0xUwLCCnzXbQKKLa0IARXACYe+7BFQ
ig8aRCKCfERlkNuc7UTOdzQCOp7JV7DvW0nTB7Rtw+dNTKYKx5ZZCEad77Lv+QJarGR0ZNup
dop89jXjzCRnsNI7DFa8/JRhTmNEprVbFgTKua11X63I8ntTFtrG90t6QrvvpSAkV5IKI0P6
1mqgGrVk8EEvRJEILhS/+1DhLdMT6XTKSqWYX6ywz2uBHqQ8VLF4DDxTO3bXuHArrBX7m0zS
OcnZBBSAkXAB6qCQQafgmjQpWDzu6+la+oqbSxRTLI2KdzKTxHz0hE7JAzaw3k/k/2rK1/Wa
uYNxdo3pWnd8FjsfAWzS8tucRllvN4IwhmMvD5vKZH5KjIqtZ/9wBo5z+nF1i7DFJYDY+/T7
otCmsr6GeCGC+FsBiQ6F1MKOyhYSozGty0mOA5pwTXyX6xvWw4FcXGKpjvSx//2ANKADO3B+
tFlgA+RYakBpPcZcdjp/2PrToYKWTstAOcQb5PL/DX1n4OAG7RC5Et6nFy0vG9rvm8hKWWvB
z8UrS7qFNN7jQpg1rHS5pVhKFTJVYISWkrfMasHA+q8XEKIlMFPsXpaVxpaZQ020YOYxi8Rr
DTvUKxLTYOmP2G4HvBrOZVTgvB2cjb8TftGjtQOTpPj7itGMVs1J+cUMmODczfKTJp+peXaK
egN6XxCIKptv6UY6tAfwIAPiPn4L+6eiUX2jZviVzoEI9sTcmODX0/ZHwMnW4nHTdAVq547x
OqCLFYzMQQaNAaKPT4XrMKIYEKMp5jF9MPe0gQE8phMbCsEvuBSnZc9XYlQ2exct4bKyb4AC
DEjheLOThdfewip7V8dvXNW+8CxqO2pZPLgLjNnrXASRsrEMHVVUrIOQ/QFJmJfinX7UZrO6
z5BUr8MNc8jnbUznsEnaOY0HAf5pa9ygy4QthmGprpcrkUQR6JZtdhhi45SflM+OGwmr2zkB
5bYaFhiC5WEtA2QEUatwLkHCwxucwfVvP9Eu/NLglOuiYkt3xYzfqnPyeC9YnSO5Se5uz7/I
gveAG5/EntTFpcchzJqc+YDyDwXdrHoh9OlNK9EFJexvHMgIdEYcuaKGRpo3mTAMfk37iNTg
9SvkQJQrY7QJ4FyBDKqOKYQ4V7Tt74TzleLlGXL12oJwPuWxnYhDU9J0JojXe6QjJiw26a/Y
6V87hzbgiv4TEweuBfiovL40c1OYOs027flLBQpe6zlX
AX5XZ9NMRgLzKQ/4fDebrsfqJRD6sXT+qk8g/gXQapj4Vdc/Tu4nHnZXYnPUn7NkX8f9vi6u
mArYEYqJ519DdKXwhdu6Z9jJkxwUKjxPCePhuyreW4RbVX7ixHdE3wOUrw5xGAZnie3oe78y
nfUS8q1Fb0PsalOUSCq6Pv0asPM6OE5LAD/e5wn+iGjLNKu0J32BEhoJAzG57rkzOMSaVRlu
Z1m+g1V+shp6CY6SoJiDpxHlaqFkauB+Kq1MX8Ixu3icnQ+E+Jr/fJscrul3jOqn7ro9JRMF
EZ9ajdisljq6Hw35i6PUPfYaJxMWjPhwM0yfcIOkQUEVdyiGjhOUtmNyZB7K6xmbbt3AQQJd
htjRsL3ACIwdvvX59eN2ZR6+xMuTm+CGbKTJAOyc9U2BEYcl7jyGQiok+K1xOtFGrfjNT/hP
ueLfmBO9ReQQx7VLVU8QPwRIHNG/2sboQPVZ6Bz3jXoPUCReJZbAhUlNng/772Mx/o1kXs0S
zBApWDwrBZ4KPjuVyolgaqWk/YmdLOv/yL7WVhdkIB2Phq5YT4/IvkOIxxNskhTko0EAxJPl
rmpkVVj1A2rlSZNhcaQeJQ338ltcTK/DQrlx8huqWw/pOhpBu1LJfkIU4g7bzXOk7VF1cyNy
sFmJCEAuvLNxdcI32RSqGORVuj/+p4lPAtY65OluAZUQQZ3lDGhoVUzN0XgUhHMpM8CdVar+
1tItrXoTC8/FJFo/+Ya1EFNWQw==


I think this is the problem that the 6i Forms can not retrieve the source code for the package body, before we use 10g DB it was working with 9i DB, so what is your opinion in this case.

Finally, I appreciate your help to me and thank you very much..


Regards,,

[Updated on: Tue, 25 March 2008 22:27] by Moderator

Report message to a moderator

Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #308905 is a reply to message #307825] Tue, 25 March 2008 22:43 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
The package body is 'wrapped' meaning that you can't see the source code. IF you can see the source code of the body via Forms 9i builder and not through Forms 6i builder then I think you are looking at two different databases.

Please confirm that you have the 10g database on a separate machine and that you have Forms 9i, Forms 6i, and PL/SQL on your PC. Therefore, you will have at least three 'tnsnames.ora' files. Please search your PC for 'tnsnames.ora'. Use either the 'ifiles' or 'tns_admin' method to point to just one copy of the 'tnsnames.ora' file. (Search this forum for fuller explanations.)

Do you have TOAD or SQL Developer or DBA Studio on your PC? If so, use them to tell you whether the body or whole package is invalid. If you don't have anything like these packages try:
select   object_name, object_type, status, last_ddl_time, object_id
    from sys.dba_objects
   where owner = 'IRISPROD'
     and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
order by 1, 2
and see if the 'body' of 'utl_mail' is 'invalid'.

David

Upd: Change 'tnsadmin' to 'tns_admin'.

[Updated on: Wed, 26 March 2008 19:15]

Report message to a moderator

Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #309816 is a reply to message #308905] Sat, 29 March 2008 01:11 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Really I am using just one DB and it is 10g located in separate machine(server), and with 6i forms on my PC,
also just the package body is invalid, I check it through SQL Navigator.

so what is your openion???

Regards,,,

[Updated on: Sat, 29 March 2008 03:34]

Report message to a moderator

Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #310001 is a reply to message #309816] Sun, 30 March 2008 23:17 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If you can see 'utl_mail' via SQL Navigator and just the body is invalid, then recompile it!!

David
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #310023 is a reply to message #310001] Mon, 31 March 2008 00:14 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Hi David,
Really , I did this before but I think the major problem that the error in the package body code it self, look at the attached print screen through SQL Navigator, it seems to be junk in the code not understandable thing, I think if I can get the package body code or as an exported file it will be solved??

so what do you think???
  • Attachment: ERR.JPG
    (Size: 107.41KB, Downloaded 218 times)

[Updated on: Mon, 31 March 2008 00:15]

Report message to a moderator

Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #310281 is a reply to message #310023] Mon, 31 March 2008 17:47 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If you want I will move this thread to the 'database' forum. You need to talk with your DBA and get that person to run the 'utl_mail' creation script again.

David
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #310405 is a reply to message #310281] Tue, 01 April 2008 02:16 Go to previous messageGo to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Hi,
You mean I can get the "UTL_MAIL" script code from the DBA forum,
If this applicable no problem, also if you know a specific place to find this script please inform me.

Finally; I am appreciating your help for me thank you very much.

Ahmad,,,
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #311256 is a reply to message #310405] Thu, 03 April 2008 18:13 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please leave this thread in the database forum.

The OP is trying to get his 'utl_mail' package validated.

David
Re: Sending EMAIL FROM 6I FORMS USING 10g DB [message #311257 is a reply to message #311256] Thu, 03 April 2008 18:16 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Thump thread so that 'Post Notification' is active.

David
Previous Topic: Error while installing asmlib @vmware
Next Topic: (LOCAL=NO)
Goto Forum:
  


Current Time: Sat Dec 10 12:49:36 CST 2016

Total time taken to generate the page: 0.06452 seconds