Home » RDBMS Server » Server Administration » Emailing from pl/sql on Oracle9i - Running initplsj.sql in oracle 9i
Emailing from pl/sql on Oracle9i - Running initplsj.sql in oracle 9i [message #110187] Thu, 03 March 2005 23:03 Go to next message
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 Go to previous messageGo to next message
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 #110208 is a reply to message #110187] Fri, 04 March 2005 02:49 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks for replying to the query. But I am aware of how to use SMTP package. Only problem we are facing is installing initplsj.sql file as we can't find this file in Oracle9i installation. As described in the attached file for installation for SMTP package, it works fine with Oracle8i but not in Oracle9i.


As mentioned earlier

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

Reply awaited.
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 Go to previous message
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
Previous Topic: Changing Database Owners
Next Topic: Message Queues
Goto Forum:
  


Current Time: Wed Dec 17 23:56:32 CST 2014

Total time taken to generate the page: 0.08480 seconds