Home » Developer & Programmer » Forms » Sending email from a form (Oracle Forms, 9i, XP)
Sending email from a form [message #311512] Fri, 04 April 2008 12:12 Go to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
I have the following code working in sql plus 9i however when i move the code into oracle forms i receive an error:

Quote:
Error 512-Implementation Restriction: 'UTL_TCP.CRLF: cannot directly access remote package variable or cursur


I dont have the code for the package UTL_TCP so what can i do?

Any suggestions would be great

Thanks


PROCEDURE school_det_sp 
  (
   b_type IN behaviour.beh_type%TYPE,
   s_id IN behaviour.be_stu_id%TYPE,
   c_id IN behaviour.be_class_id%TYPE,
   d_id IN behaviour.date_recieved%TYPE
	)
 IS
  lv_beh_total NUMBER (8);
  lv_punish VARCHAR2(40);	
  conn UTL_SMTP.connection;
  eheader VARCHAR2(1000);
  sender VARCHAR2(50) := 'studentmonitorsys@thirdyearprojects.com';
  rec1 VARCHAR2(50) := 'studentmonitorsys@thirdyearprojects.com';
  server VARCHAR2(50) := 'thirdyearprojects.com';
  subject VARCHAR2(50) := 'Behaviour Alert';
  crlf VARCHAR2(2);
  emessage VARCHAR2(500);
  crlf VARCHAR2(2) := UTL_TCP.crlf;
BEGIN
INSERT INTO behaviour (BEHAVIOUR_ID, BEH_TYPE, BE_STU_ID , BE_CLASS_ID, DATE_RECIEVED) 
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,b_type, s_id, c_id, d_id);
SELECT COUNT(behaviour_id)
   INTO lv_beh_total
   FROM behaviour 
   WHERE be_stu_id =s_id
   AND  be_class_id = c_id
   AND  date_recieved =d_id;
IF lv_beh_total = 2 THEN
	lv_punish:='Lunch Time Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
	MESSAGE('Lunch Time Detention for '||'Student'||s_id);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,1,s_id,c_id, d_id);
ELSIF lv_beh_total = 3 THEN
 	lv_punish:='After School Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
	MESSAGE('After School Detention for '||'Student'||s_id);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,2,s_id,c_id, d_id);
ELSIF lv_beh_total = 5 THEN
 	lv_punish:='Lunch Time Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
	MESSAGE('Lunch Time Detention for '||'Student'||s_id);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,1,s_id,c_id, d_id);
ELSIF lv_beh_total = 6 THEN
 	lv_punish:='After School Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
	MESSAGE('After School Detention for '||'Student'||s_id);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,2,s_id,c_id, d_id);
		
	eheader:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy  hh24:mi:ss')||crlf||
            'From: '||sender||''||crlf||
            'Subject: '||subject||crlf||
            'To: '||rec1;
	emessage := 'Student '||s_id||' has recieved'||lv_beh_total||
                ' behaviours today resulting in 2 Lunctime and 2 Afterschool Detentions!';
--Start Connection
   conn := utl_smtp.open_connection(server);
   utl_smtp.helo(conn, server);
   utl_smtp.mail(conn, sender);
   utl_smtp.rcpt(conn, rec1);
   utl_smtp.open_data(conn);
   utl_smtp.write_data(conn,eheader);
   utl_smtp.write_data(conn, crlf || emessage);
   utl_smtp.close_data(conn);
   utl_smtp.quit(conn);
ELSIF lv_beh_total = 8 THEN
 	lv_punish:='Lunch Time Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
	MESSAGE('Lunch Time Detention for '||'Student'||s_id);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,1,s_id,c_id, d_id);
ELSIF lv_beh_total = 9 THEN
 	lv_punish:='After School Detention';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
	MESSAGE('After School Detention for '||'Student'||s_id);
INSERT INTO DETENTION_BEHAVIOUR(BEH_BEHAVIOUR_ID, DET_DETENTION_ID, DB_STU_ID, DB_CLASS_ID,DATE_RECIEVED)
		VALUES(:BEHAVIOUR.BEHAVIOUR_ID,2,s_id,c_id, d_id);
END IF;
EXCEPTION
  WHEN UTL_SMTP.INVALID_OPERATION THEN
    dbms_output.put_line(' Invalid Op in transaction.');
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
    dbms_output.put_line(' Temporary prob - try later.');
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
    dbms_output.put_line(' Errors in code.');  
END;

Re: Sending email from a form [message #311546 is a reply to message #311512] Fri, 04 April 2008 13:55 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Try this...

   ...
   crlf            varchar2(2)  := chr(10)||chr(13);
   ...
Previous Topic: How to create Push Buttons in a block at runtime.....??
Next Topic: How can i create an LOV that should not run if item field is not NULL
Goto Forum:
  


Current Time: Sat Dec 10 08:53:08 CST 2016

Total time taken to generate the page: 0.08594 seconds