Hi,
I am running a procedure which is within a package on dev(8.0.5) but it just does not come back. It hangs with no errors. It works fine on QC environment(8.1.6).
I checked v$lock and it gives me 'CU' in 'Type' column. Any ideas what this is about? How do I resolve this issue?
Cu lock type is for cursor bind. Ref. V$lock in oracle documentation. I can not say much about your problem, unless I have more details of your procedure etc.
The procedure is as follows: Can you help me? It's urgent!!!
Thanks.
Procedure: SP_Tran_Summary
-- Modification History
-- Person Date Comments
-- -------------------------------------------------
-- Krish 8/19/99 Initial Coding
-- Laya 9/13/1999 Changed Pcurrency parameter to pccy_acct
--------------------------------------------------------------------------------------------------------
Procedure SP_Tran_Summary(
pbranch IN varchar2 := '*',
pcustomer IN varchar2 := '*',
paccount IN varchar2 := '*',
pacctgrp IN varchar2 := '*',
ENTRY_DATE_RANGE IN varchar2 := '*',
pvdaterange IN varchar2 := '*',
PAMTRANGE IN varchar2 := '*',
PDEBITCREDIT IN varchar2 := '*',
prodtype IN varchar2 := '*',
-- ptrantyp IN varchar2 := '*',
SYSCLIENTID IN varchar2 := '*',
SYSLANGUAGE IN varchar2 := 'en_US',
SYSORACHARSET IN varchar2 := '*',
pccy_acct IN varchar2 := '*',
SYSBASECURRENCY IN varchar2 := '*',
pccy_tran IN varchar2 := '*',
PTRANDESC IN varchar2 := '*',
SYSUSERID IN varchar2 := '*',
PCHQONLY IN varchar2 := '*',
Tran_Summ_Cur In Out btr_types.Tran_Summ_Curr);
Pragma RESTRICT_REFERENCES(SP_Tran_Summary, WNDS);
Procedure: SP_Tran_Summary
-- Modification History
-- Person Date Comments
-- -------------------------------------------------
-- Krish 8/17/99 Initial Coding
-- Krish 9/08/99 Modified joins for type_values
-- Laya 9/13/1999 Modified to Change parameter from Currency to Account Currency
--------------------------------------------------------------------------------------------------------
Procedure SP_Tran_Summary(
pbranch IN varchar2 := '*',
pcustomer IN varchar2 := '*',
paccount IN varchar2 := '*',
pacctgrp IN varchar2 := '*',
ENTRY_DATE_RANGE IN varchar2 := '*',
pvdaterange IN varchar2 := '*',
PAMTRANGE IN varchar2 := '*',
PDEBITCREDIT IN varchar2 := '*',
prodtype IN varchar2 := '*',
-- ptrantyp IN varchar2 := '*',
SYSCLIENTID IN varchar2 := '*',
SYSLANGUAGE IN varchar2 := 'en_US',
SYSORACHARSET IN varchar2,
pccy_acct IN varchar2 := '*',
SYSBASECURRENCY IN varchar2 := '*',
pccy_tran IN varchar2 := '*',
PTRANDESC IN varchar2 := '*',
SYSUSERID IN varchar2 := '*',
PCHQONLY IN varchar2 := 'INC_CHQ',
Tran_Summ_Cur In Out btr_types.Tran_Summ_Curr) is
select TO_NUMBER(rpt_lbl_des)
into row_limit
from report_label
where rpt_lbl_nam = 'LBL_TXN_SUMM_RPT_LMT'
and lang_id = 'en_US';
-- row_limit := 1000;
if substr(pCustomer,1,2) = '*:' Then
vc_pCustomer := substr(pCustomer,3);
end if;
if substr(pAccount,1,2) = '*:' Then
vc_pAccount := substr(pAccount,3);
end if;
if Entry_Date_Range = '*' and pvdaterange = '*' and
pccy_tran = '*' and prodtype = '*' and PCHQONLY = '*' and
pamtrange = '*' and PDEBITCREDIT = '*' then
vc_tran_sel_req := 'N';
else
vc_tran_sel_req := 'Y';
end if;
if Entry_Date_Range = '*' and pvdaterange = '*' and
pccy_tran = '*' and prodtype = '*' and PCHQONLY = '*' and
pamtrange = '*' and PDEBITCREDIT = '*' and ptrandesc = '*' then
open tran_summ_cur for
SELECT /*+ ORDERED FIRST_ROWS PUSH_SUBQ index(asd asd_ndx_3) index(s1 rds_statement_idx_5) */
null as brn_nm,
asd.br_cod as brn_no,
null as bnk_nm,
asd.acct_no as acct_no,
null as acct_typ,
null as acct_id,
nvl(asd.ap1_acct_fam_nam,asd.ap2_acct_fam_nam) as acct_nm,
null as acct_grp,
null as acct_grp_desc,
null as cust_no,
null as cust_nm,
null as cr_dr_mrk,
s1.stmt_dt as ent_dt, -- 5/31 changed postdt to stmt_dt for short rpt
null as prod_typ,
null as prod_typ_des,
null as tran_no,
null as tran_des,
null as swift_tran_cod,
null as val_dt,
null as bnk_ref,
null as cust_ref,
null as ft_confirm_ref,
null as tran_ccy,
null as acct_ccy,
s1.cr_cnt_posn as tran_ccy_decimal_cnt, -- bogus field for rpt 5/31
s1.dr_cnt_posn as acct_ccy_decimal_cnt,-- bogus field for rpt 5/31
null as base_ccy_decimal_cnt,
0 as tran_amt,
null as by_ord_of,
0 as row_num,
null as Conv_rt,
null as Math_Actn
from
acct_static_details asd,
rds_statement s1
Where
asd.acct_id = s1.acct_id and
asd.acct_sta_typ = s1.acct_sta_typ and
s1.actv_ind = 'Y' and -- added 2/25/2000
(asd.clnt_id = SYSCLIENTID) and
(pbranch = '*' or
(asd.br_cod in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pbranch and
rp.user_id = sysuserid and
rp.field_name = 'PBRANCH'))) and
(vc_pcustomer ='*' or
(asd.gfcid_id in(Select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_pcustomer and
rp.user_id = sysuserid and
rp.field_name = 'PCUSTOMER')) or
(asd.cust_bas_no_id in(Select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_pcustomer and
rp.user_id = sysuserid and
rp.field_name = 'PCUSTOMER'))) and
(vc_paccount = '*' or
(asd.acct_id in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_paccount and
rp.user_id = sysuserid and
rp.field_name = 'PACCOUNT'))) and
(pccy_acct = '*' or
(asd.acct_ccy in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pccy_acct and
rp.user_id = sysuserid and
rp.field_name = 'PCCY_ACCT'))) and
(pacctgrp = '*' or
(asd.acct_id in (select cl.acct_id from account a1,client_group_accounts cl
where a1.acct_id = cl.acct_id and cl.acct_grp_id in
(select field_value_item from RPT_PARAM rp
where rp.rpt_nam = pacctgrp and
rp.user_id = sysuserid and
rp.field_name = 'PACCTGRP'))));
else
open tran_summ_cur for
SELECT /*+ ORDERED FIRST_ROWS PUSH_SUBQ index(asd asd_ndx_3) index(s1 rds_statement_idx_5) */
null as brn_nm,
asd.br_cod as brn_no,
null as bnk_nm,
asd.acct_no as acct_no,
null as acct_typ,
null as acct_id,
nvl(asd.ap1_acct_fam_nam,asd.ap2_acct_fam_nam) as acct_nm,
null as acct_grp,
null as acct_grp_desc,
null as cust_no,
null as cust_nm,
null as cr_dr_mrk,
s1.stmt_dt as ent_dt, -- 5/31 changed postdt to stmt_dt for short rpt
null as prod_typ,
null as prod_typ_des,
null as tran_no,
null as tran_des,
null as swift_tran_cod,
null as val_dt,
null as bnk_ref,
null as cust_ref,
null as ft_confirm_ref,
null as tran_ccy,
null as acct_ccy,
s1.cr_cnt_posn as tran_ccy_decimal_cnt, -- bogus field for rpt 5/31
s1.dr_cnt_posn as acct_ccy_decimal_cnt,-- bogus field for rpt 5/31
null as base_ccy_decimal_cnt,
0 as tran_amt,
null as by_ord_of,
0 as row_num,
null as Conv_rt,
null as Math_Actn
from
acct_static_details asd,
rds_statement s1
Where
asd.acct_id = s1.acct_id and
asd.acct_sta_typ = s1.acct_sta_typ and
s1.actv_ind = 'Y' and -- added 2/25/2000
(asd.clnt_id = SYSCLIENTID) and
(pbranch = '*' or
(asd.br_cod in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pbranch and
rp.user_id = sysuserid and
rp.field_name = 'PBRANCH'))) and
(vc_pcustomer ='*' or
(asd.gfcid_id in(Select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_pcustomer and
rp.user_id = sysuserid and
rp.field_name = 'PCUSTOMER')) or
(asd.cust_bas_no_id in(Select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_pcustomer and
rp.user_id = sysuserid and
rp.field_name = 'PCUSTOMER'))) and
(vc_paccount = '*' or
(asd.acct_id in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_paccount and
rp.user_id = sysuserid and
rp.field_name = 'PACCOUNT'))) and
(pccy_acct = '*' or
(asd.acct_ccy in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pccy_acct and
rp.user_id = sysuserid and
rp.field_name = 'PCCY_ACCT'))) and
(pacctgrp = '*' or
(asd.acct_id in (select cl.acct_id from account a1,client_group_accounts cl
where a1.acct_id = cl.acct_id and cl.acct_grp_id in
(select field_value_item from RPT_PARAM rp
where rp.rpt_nam = pacctgrp and
rp.user_id = sysuserid and
rp.field_name = 'PACCTGRP')))) and
s1.stmt_no in
( select
t1.stmt_no from rds_transaction t1 where
t1.backval_ind is null and
((ENTRY_DATE_RANGE ='*' ) or
((t1.tran_post_dt) between Entry_FromDate and Entry_ToDate)) and
((pvdaterange ='*' ) or
((t1.tran_val_dt) between Value_FromDate and Value_ToDate)) and
((PCHQONLY = 'NO_CHQ' AND T1.SWIFT_TRAN_COD <> 'CHK') OR
(PCHQONLY = 'ONLY_CHQ' AND T1.SWIFT_TRAN_COD = 'CHK') OR
(PCHQONLY = 'INC_CHQ' ) ) and
(PTRANDESC = '*' OR
(nvl(decode(t1.tran_cod,'MSC',NULL,convert((btr_util.get_tran_desc(t1.tran_cod,SYSLANGUAGE,asd.brn_i d)),SYSORACHARSET)),
t1.fthr_ref_data) = ptrandesc) ) and
((PAMTRANGE = '*') or
(DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
'RC', -1.0*t1.tran_amt,
t1.tran_amt) between Amt_From and Amt_To)) and
(pccy_tran = '*' or
(t1.iso_ccy_cod in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pccy_tran and
rp.user_id = sysuserid and
rp.field_name = 'PCCY_TRAN'))) and
(prodtype = '*' or
(t1.prod_typ in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = prodtype and
rp.user_id = sysuserid and
rp.field_name = 'PRODTYPE'))) and
((PCHQONLY = 'NO_CHQ' AND T1.SWIFT_TRAN_COD <> 'CHK') OR
(PCHQONLY = 'ONLY_CHQ' AND T1.SWIFT_TRAN_COD = 'CHK') OR
(PCHQONLY = 'INC_CHQ' ) ) and
((PDEBITCREDIT = '*') OR
(PDEBITCREDIT = 'B' AND (CR_DR_MRK = 'C' OR CR_DR_MRK = 'RC' OR CR_DR_MRK = 'D' OR CR_DR_MRK = 'RD')) OR
(PDEBITCREDIT = 'C' AND (CR_DR_MRK = 'C' OR CR_DR_MRK = 'RD')) OR
(PDEBITCREDIT = 'D' AND (CR_DR_MRK = 'D' OR CR_DR_MRK = 'RC'))) and
asd.brn_nm as brn_nm,
asd.br_cod as brn_no,
asd.bnk_nm as bnk_nm,
asd.acct_no as acct_no,
nvl(convert(atm.acct_typ_des,SYSORACHARSET),ate.acct_typ_des) as acct_typ,
asd.acct_id as acct_id,
nvl(asd.ap1_acct_fam_nam,asd.ap2_acct_fam_nam) as acct_nm,
cl.acct_grp_id as acct_grp,
cag.acct_grp_des as acct_grp_desc,
asd.cust_ref_id as cust_no,
asd.lgl_nam as cust_nm,
t1.cr_dr_mrk as cr_dr_mrk,
t1.tran_post_dt as ent_dt,
t1.prod_typ as prod_typ,
nvl(convert(tvm_1.typ_val_des,SYSORACHARSET),tve_1.typ_val_des) as prod_typ_des,
t1.tran_no as tran_no,
nvl(decode(t1.tran_cod,'MSC',NULL,convert((btr_util.get_tran_desc(t1.tran_cod,SYSLANGUAGE,asd.brn_id )),SYSORACHARSET)),
nvl(t1.fthr_ref_data,nvl(convert(tvm_2.typ_val_des,SYSORACHARSET),tve_2.typ_val_des))) as tran_des,
t1.swift_tran_cod as swift_tran_cod,
t1.tran_val_dt as val_dt,
CONVERT(t1.othr_ref_no,SYSORACHARSET) as bnk_ref,
CONVERT(t1.ref_no,SYSORACHARSET) as cust_ref,
nvl(CONVERT(tp2.ptcp_bank_rte_sys_id,SYSORACHARSET),CONVERT(tp3.ptcp_bank_rte_sys_id,SYSORACHARSET)) as ft_confirm_ref,
t1.iso_ccy_cod as tran_ccy,
-- CONVERT(tp2.ptcp_bank_rte_sys_id,SYSORACHARSET) as ft_fed_imad_no,
-- CONVERT(tp3.ptcp_bank_rte_sys_id,SYSORACHARSET) as ft_chips_ssn,
asd.acct_ccy as acct_ccy,
tc.ccy_deci_plac as tran_ccy_decimal_cnt,
ac.ccy_deci_plac as acct_ccy_decimal_cnt,
ec.ccy_deci_plac as base_ccy_decimal_cnt,
DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
'RC', -1.0*t1.tran_amt,
t1.tran_amt) as tran_amt,
-- NVL(CONVERT(tp1.ptcp_pty_nam,SYSORACHARSET), CONVERT(tp4.ptcp_pty_nam,SYSORACHARSET)) as by_ord_of,
DECODE(t1.CR_DR_MRK,'C ', CONVERT(tp4.ptcp_pty_nam,SYSORACHARSET),'RD', CONVERT(tp4.ptcp_pty_nam,SYSORACHARSET),
'D ', CONVERT(tp1.ptcp_pty_nam,SYSORACHARSET),'RC', CONVERT(tp1.ptcp_pty_nam,SYSORACHARSET),' ')
as by_ord_of,
--row_num needed to make rows with same tran_amt unique on reports
ROWNUM as row_num,
NVL(Decode(e4.math_actn,'M',
(decode(e3.math_actn,'M',1 / e3.conv_rt,1 * e3.conv_rt))* e4.conv_rt,
(decode(e3.math_actn,'M',1 / e3.conv_rt,1 * e3.conv_rt))/ e4.conv_rt)
,
(select pty_id as c2_pty_id,pty_sta_typ as c2_pty_sta_typ,
pty_role_typ as c2_pty_role_typ,acct_id as c2_acct_id,
acct_sta_typ as c2_acct_sta_typ,acct_pty_sta_typ as c2_acct_pty_sta_typ,
min(acct_grp_id)AS c2_acct_grp_id from
client_group_accounts c2
where acct_grp_sta_typ = 'PROCESSED'
group by pty_id,pty_sta_typ,pty_role_typ,
acct_id, acct_sta_typ,acct_pty_sta_typ)
Where
asd.acct_id = s1.acct_id and
asd.acct_sta_typ = s1.acct_sta_typ and
-- s1.acct_id = t1.acct_id and
s1.stmt_no = t1.stmt_no and
s1.actv_ind = 'Y' and -- added 2/25/2000
t1.backval_ind is null and
asd.clnt_id = c2_pty_id(+) and
asd.ap_pty_sta_typ = c2_pty_sta_typ(+) and
asd.ap_pty_role_typ = c2_pty_role_typ(+) and
asd.acct_id = c2_acct_id(+) and
asd.ap_acct_sta_typ = c2_acct_sta_typ(+) and
asd.ap_acct_pty_sta_typ = c2_acct_pty_sta_typ(+) and
asd.clnt_id = cl.pty_id(+) and
asd.ap_pty_sta_typ = cl.pty_sta_typ(+) and
asd.ap_pty_role_typ = cl.pty_role_typ(+) and
asd.acct_id = cl.acct_id(+) and
asd.ap_acct_sta_typ = cl.acct_sta_typ(+) and
asd.ap_acct_pty_sta_typ = cl.acct_pty_sta_typ(+) and
cl.acct_grp_sta_typ(+) = 'PROCESSED' and
cl.acct_grp_sta_typ = cag.acct_grp_sta_typ(+) and
cl.acct_grp_id = cag.acct_grp_id(+) and
t1.tran_no = tp1.tran_no(+) and
tp1.tpty_typ(+) = 'BENE' and
t1.tran_no = tp4.tran_no(+) and
tp4.tpty_typ(+) = 'ORDPTY' and
t1.tran_no = tp2.tran_no(+) and
tp2.ptcp_bank_rte_sys_cod(+) = 'FW' and
t1.tran_no = tp3.tran_no(+) and
tp3.ptcp_bank_rte_sys_cod(+) = 'CH' and
t1.prod_typ = tvm_1.typ_val(+) and
tvm_1.typ_def(+) = 'PROD_TYP' and
t1.prod_typ = tve_1.typ_val(+) and
tve_1.typ_def(+) = 'PROD_TYP' and
tve_1.lang_id(+) = 'en_US' and
t1.swift_tran_cod = tvm_2.typ_val(+) and
tvm_2.typ_def(+) = 'SWIFTTRAN' and
t1.swift_tran_cod = tve_2.typ_val(+) and
tve_2.typ_def(+) = 'SWIFTTRAN' and
tve_2.lang_id(+) = 'en_US' and
asd.brn_id = ate.pty_id(+) and
ate.pty_sta_typ(+) = 'PROCESSED' and
asd.acct_typ = ate.acct_typ(+) and
ate.lang_id(+) = 'en_US' and
asd.brn_id = atm.pty_id(+) and
atm.pty_sta_typ(+) = 'PROCESSED' and
asd.acct_typ = atm.acct_typ(+) and
atm.lang_id(+) = SYSLANGUAGE and
tc.iso_ccy_cod(+) = t1.iso_ccy_cod and
ac.iso_ccy_cod(+) = asd.acct_ccy and
e1.iso_ccy_cod(+) = t1.iso_ccy_cod and
ec.iso_ccy_cod(+) = SYSBASECURRENCY and
e1.bas_ccy_cod(+) = 'USD' and
e2.bas_ccy_cod(+) = 'USD' and
e1.prod_src_typ(+) = 'MCP' and
e2.prod_src_typ(+) = 'MCP' and
e1.actv_ind(+) = 'Y' and
e2.actv_ind(+) = 'Y' and
e2.iso_ccy_cod(+) = ec.iso_ccy_cod and
e3.bas_ccy_cod(+) = 'EUR' and
e3.prod_src_typ(+) = 'EMU' and
e3.actv_ind(+) = 'Y' and
e3.iso_ccy_cod(+) = ec.iso_ccy_cod and
e4.bas_ccy_cod(+) = 'EUR' and
e4.prod_src_typ(+) = 'EMU' and
e4.actv_ind(+) = 'Y' and
e4.iso_ccy_cod(+) = t1.iso_ccy_cod and
-- CHECK NYRS IF MCP MISSING 6/27/01 CHANGE T. HINES
e5.iso_ccy_cod(+) = t1.iso_ccy_cod and
e5.bas_ccy_cod(+) = 'USD' and
e5.prod_src_typ(+) = 'NYRS' and
e5.exch_rt_typ(+) = 'SPOT' and
e5.actv_ind(+) = 'Y' and
e6.bas_ccy_cod(+) = 'USD' and
e6.prod_src_typ(+) = 'NYRS' and
e6.exch_rt_typ(+) = 'SPOT' and
e6.actv_ind(+) = 'Y' and
e6.iso_ccy_cod(+) = ec.iso_ccy_cod and
(asd.clnt_id = SYSCLIENTID) and
(pbranch = '*' or
(asd.br_cod in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pbranch and
rp.user_id = sysuserid and
rp.field_name = 'PBRANCH'))) and
(vc_pcustomer ='*' or
(asd.gfcid_id in(Select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_pcustomer and
rp.user_id = sysuserid and
rp.field_name = 'PCUSTOMER')) or
(asd.cust_bas_no_id in(Select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_pcustomer and
rp.user_id = sysuserid and
rp.field_name = 'PCUSTOMER'))) and
(vc_paccount = '*' or
(asd.acct_id in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = vc_paccount and
rp.user_id = sysuserid and
rp.field_name = 'PACCOUNT'))) and
(pccy_acct = '*' or
(asd.acct_ccy in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pccy_acct and
rp.user_id = sysuserid and
rp.field_name = 'PCCY_ACCT'))) and
(pccy_tran = '*' or
(t1.iso_ccy_cod in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = pccy_tran and
rp.user_id = sysuserid and
rp.field_name = 'PCCY_TRAN'))) and
((ENTRY_DATE_RANGE ='*' ) or
((t1.tran_post_dt) between Entry_FromDate and Entry_ToDate )) and
((pvdaterange ='*' ) or
((t1.tran_val_dt) between Value_FromDate and Value_ToDate)) and
((PCHQONLY = 'NO_CHQ' AND T1.SWIFT_TRAN_COD <> 'CHK') OR
(PCHQONLY = 'ONLY_CHQ' AND T1.SWIFT_TRAN_COD = 'CHK') OR
(PCHQONLY = 'INC_CHQ' ) ) and
((PAMTRANGE = '*') or
(DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
'RC', -1.0*t1.tran_amt,
t1.tran_amt) between Amt_From and Amt_To)) and
((PDEBITCREDIT = '*') OR
(PDEBITCREDIT = 'B' AND (t1.CR_DR_MRK = 'C' OR t1.CR_DR_MRK = 'RC' OR t1.CR_DR_MRK = 'D' OR t1.CR_DR_MRK = 'RD')) OR
(PDEBITCREDIT = 'C' AND (t1.CR_DR_MRK = 'C' OR t1.CR_DR_MRK = 'RD')) OR
(PDEBITCREDIT = 'D' AND (t1.CR_DR_MRK = 'D' OR t1.CR_DR_MRK = 'RC'))) AND
(PTRANDESC = '*' OR
(nvl(decode(t1.tran_cod,'MSC',NULL,convert((btr_util.get_tran_desc(t1.tran_cod,SYSLANGUAGE,asd.brn_i d)),SYSORACHARSET)),
nvl(t1.fthr_ref_data,nvl(convert(tvm_2.typ_val_des,SYSORACHARSET),tve_2.typ_val_des))) = ptrandesc) ) and
(prodtype = '*' or
(t1.prod_typ in (select field_value_item
from RPT_PARAM rp
where rp.rpt_nam = prodtype and
rp.user_id = sysuserid and
rp.field_name = 'PRODTYPE'))) and
(pacctgrp = '*' or
(asd.acct_id in (select cl.acct_id from acct_static_details asd1,client_group_accounts cl
where asd1.acct_id = cl.acct_id and cl.pty_id = SYSCLIENTID and cl.acct_grp_id in
(select field_value_item from RPT_PARAM rp
where rp.rpt_nam = pacctgrp and
rp.user_id = sysuserid and
rp.field_name = 'PACCTGRP')))) and
/*
((pacctgrp = '*' and
cl.acct_grp_id = c2_acct_grp_id or
cl.acct_grp_id is null) or
(pacctgrp <> '*')) and
(pacctgrp = '*' or
(cl.acct_grp_id in
(select field_value_item from RPT_PARAM rp
where rp.rpt_nam = PACCTGRP and
rp.user_id = sysuserid and
rp.field_name = 'PACCTGRP'))) and
-- combined both below
*/
((pacctgrp = '*' and
cl.acct_grp_id = c2_acct_grp_id or
cl.acct_grp_id is null) or
( cl.acct_grp_id in
(select field_value_item from RPT_PARAM rp
where rp.rpt_nam = PACCTGRP and
rp.user_id = sysuserid and
rp.field_name = 'PACCTGRP')) ) and
tvm_1.lang_id(+) = SYSLANGUAGE and
tvm_2.lang_id(+) = SYSLANGUAGE ;
Not sure if I can help much, but you may want to trace the session that's running the procedure to see where the hang issue is occurring. I beleive the CU is an enqueue wait lock. Here is an article I found on Metalink that may be able to help you out. Good luck.
Note:102925.1
Subject: Tracing sessions: waiting on an enqueue
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 21-MAR-2000
Last Revision Date: 25-APR-2001
PURPOSE
-------
How to monitor and trace locking problems (enqueues).
SCOPE
-----
DBA's and people working with technical support.
APPLICATION
-----------
This bulletin will assist you when:
1. Requiring to figure out if an instance has had locking problems with
enqueues. See section "Has locking been a problem".
2. Are there sessions just now in the instance that are waiting on
enqueues and who are the blockers. See "Is locking a problem just now".
Abstract
--------
What are locks, enqueues and latches?
What are the common types of enqueues?
Has locking been a problem?
Is locking a problem just now?
All those questions will be answered in this document.
To get better performance (get a better flow), one approach is to
eliminate the time a session is waiting to get a resource (for example
an enqueue). You don’t want sessions that are waiting on other sessions,
because those waiting sessions may also hold enqueues and resources, which
will make other sessions wait for them - which can end up with a deadlock.
What are locks, enqueues and latches
-----------------------------------
Often the word "lock" is synonymous to enqueue. In this document, "enqueue"
will be used for the locking mechanism that you can find in V$LOCK. "locking"
will be used when talking about requesting an enqueue, waiting on an enqueue
and blocking others while holding the enqueue.
A lock is a resource that you need to hold to get access to the resource.
Oracle has two kind of locks: enqueues and latches. This bulletin will not
talk about latches. A latch is a fast, inexpensive and non-sophisticated
lock. A latch is used when you need serial access to operations/functions
in Oracle. There is no ordered queue for the waiters on a latch - first to
grab it gets it.
Enqueues are sophisticated locks for managing access to shared resources
(like tables, rows, jobs, redo threads). An enqueue can be requested in
different levels/mode: null, row share, row exclusive, share, share row
exclusive or exclusive. If a session holds an enqueue in share mode, other
sessions can then also take the enqueue in share mode (for the same resource).
If a session holds an enqueue in exclusive mode, other sessions that wants to
get it - independently in which level - they have to wait.
When access is required by a session, a lock structure is obtained and a
request is made to acquire access to the resource at a specific level (mode)
is made. The lock structure is placed on one of three linked lists which hang
off of the resource, called the OWNER (if the enqueue could be acquired),
WAITER (if the session is waiting to acquiring the enqueue) and CONVERTER
(the session is holding the enqueue in one level but wants to convert it to
another) lists.
An enqueue is uniquely defined by it's TYPE, ID1 and ID2 (which are columns
in the V$LOCK view). For example can there only be one enqueue for user
SCOTT's EMP table (identified by TYPE=TM, ID1=
Reply With Quote