Revalidating utl_mail package [message #306983] |
Mon, 17 March 2008 07:21 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
AHMADF124
Messages: 30 Registered: January 2008 Location: KSA
|
Member |
![ahmadf124%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 #307825 is a reply to message #307768] |
Thu, 20 March 2008 01:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
AHMADF124
Messages: 30 Registered: January 2008 Location: KSA
|
Member |
![ahmadf124%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/67467.jpg) |
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS 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 #310023 is a reply to message #310001] |
Mon, 31 March 2008 00:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
AHMADF124
Messages: 30 Registered: January 2008 Location: KSA
|
Member |
![ahmadf124%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 740 times)
[Updated on: Mon, 31 March 2008 00:15] Report message to a moderator
|
|
|
|
|
|
|