Solved

oracle trace file

Posted on 2015-01-07
6
216 Views
Last Modified: 2015-01-09
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
Comment
Question by:anumoses
  • 4
  • 2
6 Comments
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
utl_http.set_transfer_timeout(60);    
v_return_c := utl_http.request(url);

I made this change and will check for errors.
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now