Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

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
Avatar of anumoses
anumoses
Flag of United States of America image

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
utl_http.set_transfer_timeout(60);    
v_return_c := utl_http.request(url);

I made this change and will check for errors.
thanks