?
Solved

oracle trace file

Posted on 2015-01-07
6
Medium Priority
?
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 40536496
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40536507
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
ID: 40537754
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40537770
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
ID: 40538275
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
ID: 40540559
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 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