• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

oracle trace file

I am getting some errors . Have scheduled jobs in oracle
DECLARE
    x   NUMBER;
BEGIN
    DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'wb_donors_elig_for_conv_proc;'
   ,next_date =>  to_date('31-Jan-2014 06:30','dd-Mon-yyyy hh24:mi') 
   --,interval  => 'SYSDATE + 1'
   ,interval => 'TRUNC(CASE WHEN TO_CHAR(SYSDATE, ''Dy'') = ''Sat'' THEN SYSDATE + 1 ELSE SYSDATE END) + 1 + 6.5 / 24' 
   ,no_parse  => FALSE
  );
    COMMIT;
END;

DECLARE
    x   NUMBER;
BEGIN
    DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'wb_dnrs_elig_for_conv_cnts;'
   ,next_date =>  to_date('31-Jan-2014 06:30','dd-Mon-yyyy hh24:mi') 
   ,interval => 'TRUNC(CASE WHEN TO_CHAR(SYSDATE, ''Dy'') = ''Sat'' THEN SYSDATE + 1 ELSE SYSDATE END) + 1 + 6.5 / 24' 
   --,interval  => 'SYSDATE + 1'
   ,no_parse  => FALSE
  );
    COMMIT;
END;

Open in new window


CREATE OR REPLACE PROCEDURE wb_donors_elig_for_conv_proc IS

  Cursor get_rep_param_cur  is
    select sch_date,sch_time,hds.donor_id,gender,
           to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI AM') as convert_date,
           site_id,hds.first_name,hds.last_name,mc.description
      from donors_don d,hbc_donor_schedule hds,master_codes mc
     where SCH_DATE = trunc(sysdate)
       AND hds.STATUS = 'CNFRM'
       AND PROC_CODE = 'WB'
       AND DRIVE_ID IS NULL 
       and donation_type = 'AL'
       and mc.code_type = 'ABO'
       and mc.udf1 = to_char(hds.blood_type) 
	   and d.donor_id = hds.donor_id
       and hds.donor_id in (select donor_id 
                              from ht_bc_don_elig_vw hbdev
						     where hds.donor_id = hbdev.donor_id 
                               and (PLATELET_DATE is null
							    or PLATELET_DATE <= trunc(sysdate) 
                                or  PLASMA_DATE is null
								or PLASMA_DATE <= trunc(sysdate)))
             and (gender = 'M'
       or
(gender = 'F' and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null) ))
and site_id not in ('LOCN201489','LOCN201102')	
and hds.site_id not in (select site_id from WB_SITE_EMAIL tmp
                               where hds.site_id = tmp.site_id)		
union
select sch_date,sch_time,hds.donor_id,gender,
           to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI AM') as convert_date,
           site_id,hds.first_name,hds.last_name,mc.description
      from donors_don d,hbc_donor_schedule hds,master_codes mc
     where SCH_DATE = trunc(sysdate)
       AND hds.STATUS = 'CNFRM'
       AND DRIVE_ID IS NULL 
       and donation_type = 'AL'
       and mc.code_type = 'ABO'
       and mc.udf1 = to_char(hds.blood_type) 
	   and d.donor_id = hds.donor_id
       and hds.donor_id in (select donor_id 
                              from ht_bc_don_elig_vw hbdev
						     where hds.donor_id = hbdev.donor_id 
                               and (PLATELET_DATE is null
							    or PLATELET_DATE <= trunc(sysdate) 
                                or  PLASMA_DATE is null
								or PLASMA_DATE <= trunc(sysdate)))
             and (gender = 'M'
       or
(gender = 'F' and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null) ))
and site_id not in ('LOCN201489','LOCN201102')	
and hds.site_id not in (select site_id from WB_SITE_EMAIL tmp
                               where hds.site_id = tmp.site_id)	;						   							   							   						   	 						   	
   

  Cursor get_email_cur(cp_Site_id in varchar) is
   select subcenter_email,site_name
     from sites 
    where lt_location_id = cp_site_id;
	 -- and inactive_flag = 'N';
 


  v_letter Varchar(50);  
  v_count number;
  req UTL_HTTP.REQ; 
  resp UTL_HTTP.RESP; 
  url VARCHAR2 (2000); 
  val varchar2(4000); 

  v_db_conn_str_c hbc_system_parameters.parameter_value%TYPE;
  v_server_c varchar2(100);
  v_report_db_c varchar2(10); 
  v_conn_str_c varchar2(30);   

BEGIN 
  
  		delete from WB_SITE_EMAIL
		where trunc(date_entered) <= trunc(sysdate-1);
		
		commit;
  v_letter := 'r_wb_dns_elig_for_conv_email'; 
 
--Set the application server.  


  v_server_c := 'http://hbc-yellow:8090/reports/rwservlet';
  
--Determine the database that is being used.

  v_db_conn_str_c := hbc_attendance_issues_pkg.get_database;
  
  if v_db_conn_str_c is null then
          
      v_report_db_c := 'DEV';
      v_conn_str_c := 'cmdkey=dev_connection';
            
    elsif lower(substr(v_db_conn_str_c,1,4)) = 'qhbc' then
          
      v_report_db_c := 'DEV';
      v_conn_str_c := 'cmdkey=dev_connection';
            
    else 
             
      v_report_db_c := 'QA';
      v_conn_str_c := 'cmdkey=prod_connection';
             
  end if;    

-- SET PROXY

 
 for get_rep_param_rec in get_rep_param_cur loop
 

     
	 
      for get_email_rec in get_email_cur(get_rep_param_rec.SITE_id) loop

    		
 
     select count(*) into v_count
	     from WB_SITE_EMAIL
		where site_id = get_rep_param_rec.site_id;

  If v_count = 0 then	
	
  	     UTL_HTTP.SET_PROXY (null,null); 



     url := 
     utl_url.ESCAPE 
     (v_server_c || '?' || v_conn_str_c ||
       '&desformat=PDF&ENVID=' || v_report_db_c ||
       '&subject=' ||'WB Donors Eligible For Conversion -'||get_email_rec.site_name ||
	   '&destype=mail' ||
       '&FROM=adwaram@heartlandbc.org&desname=' ||get_email_rec.subcenter_email ||
       '&report='||v_letter||'&p_site_id='||get_rep_param_rec.site_id); 
   
       req := UTL_HTTP.BEGIN_REQUEST (url); 
       UTL_HTTP.SET_HEADER (req, 'User-Agent', 'Mozilla/4.0 Oracle'); 
         resp := UTL_HTTP.GET_RESPONSE (req); 
       UTL_HTTP.READ_LINE (resp, val, TRUE); 
       UTL_HTTP.END_RESPONSE (resp);
              


		      
			insert into WB_SITE_EMAIL
	         (site_id,date_entered)
	       values (get_rep_param_rec.site_id,trunc(sysdate));
		    
		  commit;
  End if;
  end loop;	     
  end loop; 
END;

Open in new window


Uploading trace file. Need help in identifying the error.

/oracle/admin/phbc/bdump/phbc_j002_13839.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle/product/9.2.0.8
System name:      HP-UX
Node name:      heart1
Release:      B.11.11
Version:      U
Machine:      9000/800
Instance name: phbc
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 13839, image: oracle@heart1 (J002)

*** SESSION ID:(35.36449) 2015-01-06 06:31:09.900
*** 2015-01-06 06:31:09.900
ORA-12012: error on auto execute of job 274
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1227
ORA-29276: transfer timeout
ORA-06512: at "HBC_DATA.WB_DNRS_ELIG_FOR_CONV_CNTS", line 156
ORA-06512: at line 1
0
anumoses
Asked:
anumoses
  • 4
  • 2
2 Solutions
 
anumosesAuthor Commented:
CREATE OR REPLACE PROCEDURE wb_dnrs_elig_for_conv_cnts IS

  Cursor get_rep_param_cur  is
    select sch_date,sch_time,hds.donor_id,gender,
           to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI AM') as convert_date,
           site_id,hds.first_name,hds.last_name,mc.description
      from donors_don d,hbc_donor_schedule hds,master_codes mc
     where SCH_DATE = trunc(sysdate)
       AND hds.STATUS = 'CNFRM'
       AND PROC_CODE = 'WB'
       AND DRIVE_ID IS NULL 
       and donation_type = 'AL'
       and mc.code_type = 'ABO'
       and mc.udf1 = to_char(hds.blood_type) 
	   and d.donor_id = hds.donor_id
       and hds.donor_id in (select donor_id 
                              from ht_bc_don_elig_vw hbdev
						     where hds.donor_id = hbdev.donor_id 
                               and (PLATELET_DATE is null
							    or PLATELET_DATE <= trunc(sysdate) 
                                or  PLASMA_DATE is null
								or PLASMA_DATE <= trunc(sysdate)))
             and (gender = 'M'
       or
(gender = 'F' and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null) ))
and site_id not in ('LOCN201489','LOCN201102')	
and hds.site_id not in (select site_id from WB_SITE_EMAIL_CNT tmp
                               where hds.site_id = tmp.site_id)									   							   						   	 						   
union
select sch_date,sch_time,hds.donor_id,gender,
           to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI AM') as convert_date,
           site_id,hds.first_name,hds.last_name,mc.description
      from donors_don d,hbc_donor_schedule hds,master_codes mc
     where SCH_DATE = trunc(sysdate)
       AND hds.STATUS = 'CNFRM'
      -- AND PROC_CODE = 'WB'
       AND DRIVE_ID IS NULL 
       and donation_type = 'AL'
       and mc.code_type = 'ABO'
       and mc.udf1 = to_char(hds.blood_type) 
	   and d.donor_id = hds.donor_id
       and hds.donor_id in (select donor_id 
                              from ht_bc_don_elig_vw hbdev
						     where hds.donor_id = hbdev.donor_id 
                               and (PLATELET_DATE is null
							    or PLATELET_DATE <= trunc(sysdate) 
                                or  PLASMA_DATE is null
								or PLASMA_DATE <= trunc(sysdate)))
             and (gender = 'M'
       or
(gender = 'F' and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null) ))
and site_id not in ('LOCN201489','LOCN201102')	
and hds.site_id not in (select site_id from WB_SITE_EMAIL_CNT tmp
                               where hds.site_id = tmp.site_id);
--order by to_date(sch_time,'HH24:MI:SS'); 	
   

  Cursor get_email_cur(cp_Site_id in varchar) is
   select subcenter_email,site_name
     from sites 
    where lt_location_id = cp_site_id;
 


  v_letter Varchar(50);  
  v_count number;
  req UTL_HTTP.REQ; 
  resp UTL_HTTP.RESP; 
  url VARCHAR2 (2000); 
  val varchar2(4000); 

  v_db_conn_str_c hbc_system_parameters.parameter_value%TYPE;
  v_server_c varchar2(100);
  v_report_db_c varchar2(10); 
  v_conn_str_c varchar2(30);   

BEGIN 
  
  		delete from WB_SITE_EMAIL_CNT
		where trunc(date_entered) <= trunc(sysdate-1);
		
		commit;
		
  v_letter := 'r_wb_dns_elig_for_conv_email_for_cnts'; 
 
--Set the application server.  


  v_server_c := 'http://hbc-yellow:8090/reports/rwservlet';
  
--Determine the database that is being used.

  v_db_conn_str_c := hbc_attendance_issues_pkg.get_database;
  
  if v_db_conn_str_c is null then
          
      v_report_db_c := 'DEV';
      v_conn_str_c := 'cmdkey=dev_connection';
            
    elsif lower(substr(v_db_conn_str_c,1,4)) = 'qhbc' then
          
      v_report_db_c := 'DEV';
      v_conn_str_c := 'cmdkey=dev_connection';
            
    else 
             
      v_report_db_c := 'QA';
      v_conn_str_c := 'cmdkey=prod_connection';
             
  end if;    

-- SET PROXY

 
 for get_rep_param_rec in get_rep_param_cur loop
 

     
	 
      for get_email_rec in get_email_cur(get_rep_param_rec.SITE_id) loop

    		
 
     select count(*) into v_count
	     from WB_SITE_EMAIL_CNT
		where site_id = get_rep_param_rec.site_id;

  If v_count = 0 then	
	
  	     UTL_HTTP.SET_PROXY (null,null); 




     url := 
     utl_url.ESCAPE 
     (v_server_c || '?' || v_conn_str_c ||
       '&desformat=PDF&ENVID=' || v_report_db_c ||
	   '&subject=' ||'WB Donors Eligible For Conversion -'||get_email_rec.site_name ||
       '&destype=mail' ||
       '&FROM=adwaram@heartlandbc.org&desname=' ||'nstrang@heartlandbc.org,ahardesty@heartlandbc.org,kdaggett@heartlandbc.org,mwilliams@heartlandbc.org,nwhite@heartlandbc.org,vkopulos@heartlandbc.org'||
       '&report='||v_letter||'&p_site_id='||get_rep_param_rec.site_id); 
   
       req := UTL_HTTP.BEGIN_REQUEST (url); 
       UTL_HTTP.SET_HEADER (req, 'User-Agent', 'Mozilla/4.0 Oracle'); 
         resp := UTL_HTTP.GET_RESPONSE (req); 
       UTL_HTTP.READ_LINE (resp, val, TRUE); 
       UTL_HTTP.END_RESPONSE (resp);
              


		      
	
			insert into WB_SITE_EMAIL_CNT
	         (site_id,date_entered)
	       values (get_rep_param_rec.site_id,trunc(sysdate));
		    
		  commit;
  End if;
  end loop;	     
  end loop; 
END;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I think the important error message in that stack trace is:ORA-29276: transfer timeout


Check the resolution here:
http://ora-29276.ora-code.com/
0
 
anumosesAuthor Commented:
Is there a way or example that the procedure can use  utl_smtp instead of utl_http? That may be another solution? Please let me know your thought on this.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
slightwv (䄆 Netminder) Commented:
SMTP = Simple Mail Transfer Protocol
HTTP = Hypertext Transfer Protocol

Don't really see how the two are interchangeable.

Even if there was a way I don't see how changing the method of pulling the data will fix the issue when the error points to a timeout issue.  It would talk across the same wire that is generating the timeout.
0
 
anumosesAuthor Commented:
utl_http.set_transfer_timeout(60);    
v_return_c := utl_http.request(url);

I made this change and will check for errors.
0
 
anumosesAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now