Solved

oracle trace file

Posted on 2015-01-07
6
233 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
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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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
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
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

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.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

789 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