| Emailing from pl/sql on Oracle9i - Running initplsj.sql in oracle 9i [message #110187] |
Thu, 03 March 2005 23:03  |
himang
Messages: 282
Registered: March 2005
Location: Bangalore
|
Senior Member |
|
|
hi,
I have a technical query related to email on Oracle9i.
To send email through pl/sql package using UTL_FILE we need to run the initplsj.sql file on the database. For this around 200+ classes get loaded into the database.
While this runs well on Oracle8i but gives problem on 9i.
As one of the steps we need to run initplsj.sql file and this file is not available with installation of Oracle9i.
The possibilities could be:
1. 9i installation at our end is not complete and this file is missing
2. Feature provided by this file’s installation is already there in 9i and hence no need to install it explicitly.
3. This step has been changed in 9i and some other file/step needs to be run in 9i
Could anyone throw some light on the problem.
|
|
|
|
| Re: Emailing from pl/sql on Oracle9i - Running initplsj.sql in oracle 9i [message #110206 is a reply to message #110187] |
Fri, 04 March 2005 02:32  |
anmol1947
Messages: 34
Registered: February 2005
Location: Gurgaon
|
Member |
|
|
For this use UTL_SMTP package ,see the example below
but to execute this u should have privaleges on this package too.
CREATE or replace PROCEDURE MAILFROMDB ( MESSAGE IN VARCHAR) AS
cursor c1 is select send_recpt,mailids from mailid;
conn UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 4000 );
mesg_body varchar2(4000);
AdminMailid varchar2(30);
BEGIN
/* Open connection */
conn:= utl_smtp.open_connection( 'mail.Test.com', 25 );
/* Hand Shake */
utl_smtp.helo( conn, 'mail.Test.com' );
/* Loop for configure sender and recipient to UTL_SMTP */
for c1rec in c1 loop
if c1rec.send_recpt = 'SENDER' then
utl_smtp.mail( conn,c1rec.mailids);
else
utl_smtp.rcpt( conn,c1rec.mailids );
end if;
end loop;
/* making a message buffer */
mesg:=
'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From: Oracle Database'|| crlf ||
'Subject: Mail Through ORACLE Database' || crlf ||
'To: All the Recipients '|| crlf
|| crlf ||crlf||
' This is Mail from Oracle Database By Using UTL_SMTP Package'||crlf||
'It is very easy to configure Tell me if you face any problems'||crlf||message ;
/* Configure sending message */
utl_smtp.data( conn, mesg );
/* closing connection */
utl_smtp.quit( conn );
END;
/
SQL> execute mailfromdb('Send Feedback at Wadhwa_s@hotmail.com');
|
|
|
|
|
|
| Re: Emailing from pl/sql on Oracle9i - Running initplsj.sql in oracle 9i [message #110211 is a reply to message #110208] |
Fri, 04 March 2005 03:20 |
anmol1947
Messages: 34
Registered: February 2005
Location: Gurgaon
|
Member |
|
|
You need 8i or a higher version with the Java Virtual Machine (JVM) installed to use UTL_SMTP. In addition, you have to have plsql.jar loaded into your database. Otherwise, you'll get the following exception when you call the UTL_SMTP APIs to send an e-mail: ORA-29540: class oracle/plsql/net/TCPConnection does not exist.
By default, the $ORACLE_HOME/javavm/install/initjvm.sql main script (which installs JVM) does not run the initplsj.sql script that loads the plsql.jar file into your database. Run the $ORACLE_HOME/RDBMS/ADMIN/initplsj.sql script manually as a SYS or INTERNAL user to resolve the problem. If you don't have this script available, you can either request it from Oracle Support or as a workaround simply load plsql.jar directly using the loadjava utility:
loadjava -user sys/password@database -resolve plsql/jlib/plsql.jar
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
