anumoses
asked on
oracle trace file
I am getting some errors . Have scheduled jobs in oracle
Uploading trace file. Need help in identifying the error.
/oracle/admin/phbc/bdump/p hbc_j002_1 3839.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
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;
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;
Uploading trace file. Need help in identifying the error.
/oracle/admin/phbc/bdump/p
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
ORA-06512: at line 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
utl_http.set_transfer_time out(60);
v_return_c := utl_http.request(url);
I made this change and will check for errors.
v_return_c := utl_http.request(url);
I made this change and will check for errors.
ASKER
thanks
ASKER
Open in new window