Home » Fusion Middleware & Colab Suite » Weblogic & Application Server » email from oracle form and report services (10g )
email from oracle form and report services [message #555973] Wed, 30 May 2012 05:25 Go to next message
annu-agi
Messages: 238
Registered: July 2005
Location: Karachi
Senior Member

dear experts


I installed oracle report and forms services only. and its working fine. All my report and forms are working fine. Now i would like to add email facility, As i know, i can send email through configuring my application server and send my reports as attachment. But i don't know how i configure my report service.
for testing i made one form and on button press i write the following coding
begin
			WEB.SHOW_DOCUMENT('http://'||'10.10.0.12'||':7778/reports/rwservlet?report='||'D:\10gtest\Reports\'||'pr_lpc.rdf&userid='||'newpr/newpr@hpdb'||'&destype=mail&desname='||'muhammad.usman@caapak.com.;anwer.ali@caapak.com.&desformat=PDF&paramform=no
		&p_fiscal_year='|| '2011-2012'
		||'&p_loc_code='|| '450'
		||'&p_empl_no=' || '10163'
		||'&p_lpc_no='  || '2011-2012/10/450/00069'
		); 

end;



when i run this
it shows error on internet explorer
Executed successfully but there were some errors when distribute the output REP-50159: Executed successfully but there were some errors when distribute the output


Now i am stuck here, i Google this error but still i cant get the solution..but i hope i will get some help from this forum


regards


Anwer

Re: email from oracle form and report services [message #572477 is a reply to message #555973] Wed, 12 December 2012 06:11 Go to previous messageGo to next message
Medo
Messages: 17
Registered: December 2012
Location: Qatar
Junior Member

Hi ,

I did this

I know you post this long time back
if you have beter soulution share it with me

i user do not now exactly
but i made smtp and send mail public from sys user


FUNCTION Send_my_email(vac_reciption IN VARCHAR2, 
                       vac_ref       IN NUMBER) 
RETURN NUMBER 
IS 
  s_send     VARCHAR2(100) := 'Your Mail Her Or the Sender'; 
  s_rec      VARCHAR2(100) := vac_reciption; 
  rec_ref    NUMBER(20) := vac_ref; 
  vac1       NUMBER(20); -- SER_NO 
  vac2       VARCHAR2(100); --SUPPLIER NAME 
  vac3       VARCHAR2(50); -- DOCUMENT REFERENCE 
  vac4       DATE; --DOCUMENT DATE 
  vac5       NUMBER(16, 2); -- AMOUNT 
  total      NUMBER(16, 2); --TOTAL 
  loop_count NUMBER(20); 
  c          utl_smtp.connection; 
  CURSOR c1 IS 
    SELECT Count(ref_no) 
    FROM   remind_him 
    WHERE  ref_no = rec_ref; 
  CURSOR c2 IS 
    SELECT record_ser, 
           supplier_name, 
           document_reference, 
           document_date, 
           amount 
    FROM   remind_him 
    WHERE  ref_no = rec_ref; 
  PROCEDURE Send_header(l_subject  IN VARCHAR2, 
                        in_subject IN VARCHAR2, 
                        l_from     IN VARCHAR2, 
                        in_from    IN VARCHAR2, 
                        l_sent     IN VARCHAR2, 
                        in_sent    IN VARCHAR2, 
                        l_to       IN VARCHAR2, 
                        in_to      IN VARCHAR2) 
  AS 
  BEGIN 
      utl_smtp.Write_data(c, '' 
                             || l_subject 
                             || ': ' 
                             ||in_subject 
                             ||Chr(10) 
                             || '' 
                             || l_from 
                             || ': ' 
                             ||in_from 
                             ||Chr(10) 
                             || '' 
                             || l_sent 
                             || ': ' 
                             ||in_sent 
                             ||Chr(10) 
                             || '' 
                             || l_to 
                             || ': ' 
                             ||in_to 
                             ||Chr(10)); 
  END; 
BEGIN 
    IF s_rec IS NULL THEN 
      DELETE FROM remind_him 
      WHERE  ref_no = rec_ref; 

      COMMIT; 

      Message('ERROR IN PASSING MAIL ADDRESS TO THE SERVER '); 
    ELSE 
      total := 0; 

      --loop_count:= 0; 
      OPEN c1; 

      FETCH c1 INTO loop_count; 

      CLOSE c1; 

      c := utl_smtp.Open_connection('mail.domain.com'); 

      utl_smtp.Helo(c, 'mail.domain.com'); 

      utl_smtp.Mail(c, s_send); 

      utl_smtp.Rcpt(c, s_rec); 

      utl_smtp.Open_data(c); 

      Send_header('Subject', 'Pending Invoices Reminder', 'From', s_send, 'Sent' 
      , 
      To_char(SYSDATE, 'Dy DD-MON-YYYY HH24:MIam'), 'To', '<' 
      ||s_rec 
      ||'>'); 

      utl_smtp.Write_data(c, Chr(10) 
                             ||Chr(13) 
                             || 'snet on: ' 
                             ||To_char(SYSDATE, 'Dy DD-MON-YYYY HH12:MIam')); 

      utl_smtp.Write_data(c, Chr(10) 
                             ||Chr(10)); 

      utl_smtp.Write_data(c, 'Dear Sir ,' 
                             ||Chr(10) 
                             ||Chr(10) 
                             || 
      'please find below the pending invoice/s details' 
                             ||Chr(10) 
                             ||Chr(10) 
                             ||Chr(10)); 

      --if loop_count is not null then 
      OPEN c2; 

      FOR i IN 1..loop_count LOOP 
          FETCH c2 INTO vac1, vac2, vac3, vac4, vac5; 

          utl_smtp.Write_data(c, 'Serial Number  : ' 
                                 ||vac1 
                                 ||Chr(10) 
                                 || 'Supplier Name  : ' 
                                 ||vac2 
                                 ||Chr(10) 
                                 || 'invoice Number : ' 
                                 ||vac3 
                                 ||Chr(10) 
                                 || '  invoice Date : ' 
                                 ||vac4 
                                 ||Chr(10) 
                                 || '        Amount : ' 
                                 ||vac5 
                                 ||Chr(10) 
                                 || 
'========================================================================================' 
||Chr(10)); 

total := total + vac5; 
--EXIT WHEN C2%NOTFOUND; 
END LOOP; 

  CLOSE c2; 

  utl_smtp.Write_data(c, Chr(10) 
                         ||Chr(10) 
                         ||Chr(10) 
                         ||'TOTAL: ' 
                         ||total 
                         ||Chr(10) 
                         ||Chr(10)); 

  utl_smtp.Write_data(c, Chr(10) 
                         ||Chr(10) 
                         ||Chr(10) 
                         || 'Your quick replay is highly appreciated' 
                         ||Chr(10) 
                         ||Chr(10)); 

  utl_smtp.Write_data(c, 'Finance Department' 
                         ||Chr(10)); 

  utl_smtp.Write_data(c, 'DB Generated Report' 
                         ||Chr(10)); 

  utl_smtp.Write_data(c, '==============================================' 
                         ||Chr(10)); 

  utl_smtp.Close_data(c); 

  Message('sent record= ' 
          ||loop_count 
          ||' TO ' 
          ||s_rec); 

  utl_smtp.Quit(c); 
END IF; 

IF Get_item_property('DEPARTMENT.T14', visible) = 'TRUE'THEN 
  Set_item_property('DEPARTMENT.T14', visible, property_false); 
END IF; 

first_record; 
EXCEPTION 
  WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN 
             BEGIN 
                 utl_smtp.Quit(c); 
             EXCEPTION 
                 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN 
                   Message('ERROR:MAIL NOT SENT SUCCESSFULY'); 
             -- When the SMTP server is down or unavailable, we don't 
             -- have a connection to the server. The quit call will 
             -- raise an exception that we can ignore. 
             END; 
END; 


[EDITED by LF: formatted code and applied [code] tags]

[Updated on: Thu, 13 December 2012 01:09] by Moderator

Report message to a moderator

Re: email from oracle form and report services [message #572478 is a reply to message #572477] Wed, 12 December 2012 06:13 Go to previous message
Medo
Messages: 17
Registered: December 2012
Location: Qatar
Junior Member
Sorry

this my full function just look after

'========================================================================================'||CHR(10));
total:=total+vac5;
--EXIT WHEN C2%NOTFOUND;
END LOOP;

CLOSE C2;
Previous Topic: PDF reports change there formats and fonts
Next Topic: Installing Application Server on 64 bit machine and MS Operating Sytem
Goto Forum:
  


Current Time: Thu Mar 28 17:16:29 CDT 2024