Solved

Connection Reset error while executing PL/SQL script

Posted on 2010-11-23
8
616 Views
Last Modified: 2013-12-07
HI ,
I am getting the follow error while excuting the pl/sql script  
Error report:
Io exception: Connection reset

Below is my query
create or replace
PROCEDURE                        SDSECM01.Rpt_pcMAINPORTFOLIO_Test(REFCURSOR OUT SYS_REFCURSOR, DATE1 DATE,
                                                                               MANAGER VARCHAR2,
                                                                               SYMBOL VARCHAR2,
                                                                               SHOWCURRENTONLY VARCHAR2)

AS
DATE2 DATE;
BEGIN
IF (SHOWCURRENTONLY='No') THEN
SELECT DISTINCT MAX(AS_OF_TMS) INTO DATE2 FROM SDSECM01.FT_W_POSN WHERE AS_OF_TMS<=DATE1;
OPEN REFCURSOR FOR
SELECT DATE2 FROM DUAL;
END IF;
IF(SYMBOL IS NULL and MANAGER<>'ALL')
THEN
OPEN REFCURSOR FOR
SELECT     SDSECM01.FT_W_POSN.AS_OF_TMS AS "Last Held Date",
           DATE2 AS "TEST",
           SDSECM01.FT_W_POSN.AS_OF_DTDF_SOK AS "Last Held Date1",
           sdsecm01.ft_w_wpty.prty_nme as "Manager Name",
           sdsecm01.rpt_fngetmandatenames(sdsecm01.ft_w_acct.acct_sok)AS MANDATE,
           Trim(SDSECM01.FT_W_ACCT.ACCT_ID) || ' ' || trim(SDSECM01.FT_W_ACCT.ACCT_NME) AS PORTFOLIO,
           sdsecm01.ft_w_posn.qty_cqty AS quantity,
          sdsecm01.rpt_fngetpctsharesout(sdsecm01.ft_w_issu.iss_sok,sdsecm01.ft_w_posn.as_of_tms) AS "Pct Shares Out",  
           sdsecm01.ft_w_posn.local_curr_mkt_camt as "Market Value",
           '' as "Tot Mkt Val",
           round((100/sdsecm01.rpt_fngettotalmarketvalue(sdsecm01.ft_w_acct.acct_sok,sdsecm01.ft_w_posn.as_of_tms))*sdsecm01.ft_w_posn.local_curr_mkt_camt,2)  AS "Market Weight",
           sdsecm01.ft_w_posn.local_orig_cost_camt as "Total Cost",
          case  FT_W_POSN.QTY_CQTY when 0 then 0 else Round(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT / FT_W_POSN.QTY_CQTY,2) end  AS "Unit Cost",  
           sdsecm01.ft_w_posn.local_curr_unit_cprc as "Unit Price",
           ROUND(((SDSECM01.FT_W_POSN.Local_Curr_Dirty_Mkt_Camt)-(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))/((SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))*100,2)  AS "Pct Unrealized Gain Loss",
           SDSECM01.FT_W_INCL.CLASS_LVL0 AS "Sector",
           SDSECM01.FT_W_INCL.CLASS_LVL1 AS "Industry",
           SDSECM01.FT_W_INCL.CLASS_LVL2 AS "Industry Group",
           SDSECM01.FT_W_INCL.CLASS_LVL3 AS "Sub Industry" ,
           FT_W_POSN.LOCAL_CURR_UNIT_CPRC,
           sdsecm01.ft_w_acct.acct_lvl_typ,
           PREF_ISS_NME,
           PREF_ISS_DESC,
           to_char(to_date(mat_exp_tms),'dd/mm/yyyy') AS "Maturity Date",
           SDSECM01.FT_W_ISSU.TICKER_ID AS "TickerID",
           SDSECM01.FT_W_ISSU.CUSIP_ID AS "CUSIPID",
           SDSECM01.FT_W_ISSU.SEDOL_ID AS "SEDOLID",
           SDSECM01.FT_W_ISSU.ISIN_ID AS "ISINID",
           PREF_ISS_ID,
          SDSECM01.RPT_FNGETBESTID(SDSECM01.FT_W_ISSU.ISS_SOK) AS BESTID,
          PREF_ID_CTXT_TYP

FROM      
            SDSECM01.FT_W_POSN,
            SDSECM01.FT_W_ACCT,
            SDSECM01.FT_W_ISSU,
            SDSECM01.FT_W_ISCL,
            SDSECM01.FT_W_INCL,
            SDSECM01.FT_W_INCS,
            SDSECM01.FT_W_WPAC,
            SDSECM01.FT_W_WPAR,
            SDSECM01.FT_W_WPTY,
            SDSECM01.FT_W_WAGP,
            SDSECM01.FT_W_WAGR
           
         

WHERE      
          SDSECM01.FT_W_POSN.ACCT_SOK = SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_POSN.ISS_SOK = SDSECM01.FT_W_ISSU.ISS_SOK   AND
          SDSECM01.FT_W_WPAC.POSN_SOK  =SDSECM01.FT_W_POSN.POSN_SOK  AND
          SDSECM01.FT_W_ISCL.ISS_SOK  = SDSECM01.FT_W_POSN.ISS_SOK  AND
          SDSECM01.FT_W_INCL.INDUS_CLSF_SOK = SDSECM01.FT_W_ISCL.INDUS_CLSF_SOK AND
          SDSECM01.FT_W_INCS.INCS_SOK = SDSECM01.FT_W_INCL.INCS_SOK AND
          SDSECM01.FT_W_WPAR.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WPAR.WPTY_SOK=SDSECM01.FT_W_WPTY.WPTY_SOK AND
          SDSECM01.FT_W_WAGP.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WAGP.PRNT_WAGR_SOK=SDSECM01.FT_W_WAGR.WAGR_SOK
          AND FT_W_INCS.CLSF_SET_MNEM='GICS' AND FT_W_WAGR.ACCT_GRP_NME<>'PAPER Group' AND ASSETS_UNDER_MGT_IND='Y'
         and sdsecm01.ft_w_posn.as_of_tms in (date1,date2)  and acct_prt_purp_typ<>'MASACCT'
          AND sdsecm01.ft_w_wpty.prty_nme=manager
          AND FT_W_ACCT.BK_ID IN ('GIMB','PACE');
          END IF;
 
IF(SYMBOL IS NULL and MANAGER='ALL')
THEN
OPEN REFCURSOR FOR
SELECT     SDSECM01.FT_W_POSN.AS_OF_TMS AS "Last Held Date",
           DATE2 AS "TEST",
           SDSECM01.FT_W_POSN.AS_OF_DTDF_SOK AS "Last Held Date1",
           '' AS "Manager Name",
           sdsecm01.rpt_fngetmandatenames(sdsecm01.ft_w_acct.acct_sok)AS MANDATE,
           Trim(SDSECM01.FT_W_ACCT.ACCT_ID) || ' ' || trim(SDSECM01.FT_W_ACCT.ACCT_NME) AS PORTFOLIO,
           sdsecm01.ft_w_posn.qty_cqty as quantity,
          sdsecm01.rpt_fngetpctsharesout(sdsecm01.ft_w_issu.iss_sok,sdsecm01.ft_w_posn.as_of_tms) AS "Pct Shares Out",    
           SDSECM01.FT_W_POSN.LOCAL_CURR_MKT_CAMT AS "Market Value",
           '' as "Tot Mkt Val",
           round((100/sdsecm01.rpt_fngettotalmarketvalue(sdsecm01.ft_w_acct.acct_sok,sdsecm01.ft_w_posn.as_of_tms))*sdsecm01.ft_w_posn.local_curr_mkt_camt,2)  AS "Market Weight",
           SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT AS "Total Cost",
          case  FT_W_POSN.QTY_CQTY when 0 then 0 else Round(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT / FT_W_POSN.QTY_CQTY,2) end  AS "Unit Cost",  
           sdsecm01.ft_w_posn.local_curr_unit_cprc as "Unit Price",
           ROUND(((SDSECM01.FT_W_POSN.Local_Curr_Dirty_Mkt_Camt)-(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))/((SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))*100,2)  AS "Pct Unrealized Gain Loss",
           SDSECM01.FT_W_INCL.CLASS_LVL0 AS "Sector",
           SDSECM01.FT_W_INCL.CLASS_LVL1 AS "Industry",
           SDSECM01.FT_W_INCL.CLASS_LVL2 AS "Industry Group",
           SDSECM01.FT_W_INCL.CLASS_LVL3 AS "Sub Industry" ,
           FT_W_POSN.LOCAL_CURR_UNIT_CPRC,
           SDSECM01.FT_W_ACCT.ACCT_LVL_TYP,
           PREF_ISS_NME,
           PREF_ISS_DESC,
           to_char(to_date(mat_exp_tms),'dd/mm/yyyy') AS "Maturity Date",
           SDSECM01.FT_W_ISSU.TICKER_ID AS "TickerID",
           SDSECM01.FT_W_ISSU.CUSIP_ID AS "CUSIPID",
           SDSECM01.FT_W_ISSU.SEDOL_ID AS "SEDOLID",
           SDSECM01.FT_W_ISSU.ISIN_ID AS "ISINID",
           PREF_ISS_ID,
          SDSECM01.RPT_FNGETBESTID(SDSECM01.FT_W_ISSU.ISS_SOK) AS BESTID,
          PREF_ID_CTXT_TYP

FROM      
            SDSECM01.FT_W_POSN,
            SDSECM01.FT_W_ACCT,
            SDSECM01.FT_W_ISSU,
            SDSECM01.FT_W_ISCL,
            SDSECM01.FT_W_INCL,
            SDSECM01.FT_W_INCS,
            SDSECM01.FT_W_WPAC,
            --SDSECM01.FT_W_WPAR,
            --SDSECM01.FT_W_WPTY,
            SDSECM01.FT_W_WAGP,
            SDSECM01.FT_W_WAGR
           
         

WHERE      
          SDSECM01.FT_W_POSN.ACCT_SOK = SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_POSN.ISS_SOK = SDSECM01.FT_W_ISSU.ISS_SOK   AND
          SDSECM01.FT_W_WPAC.POSN_SOK  =SDSECM01.FT_W_POSN.POSN_SOK  AND
          SDSECM01.FT_W_ISCL.ISS_SOK  = SDSECM01.FT_W_POSN.ISS_SOK  AND
          SDSECM01.FT_W_INCL.INDUS_CLSF_SOK = SDSECM01.FT_W_ISCL.INDUS_CLSF_SOK AND
          SDSECM01.FT_W_INCS.INCS_SOK = SDSECM01.FT_W_INCL.INCS_SOK AND
          --SDSECM01.FT_W_WPAR.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          --SDSECM01.FT_W_WPAR.WPTY_SOK=SDSECM01.FT_W_WPTY.WPTY_SOK  AND
          SDSECM01.FT_W_WAGP.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WAGP.PRNT_WAGR_SOK=SDSECM01.FT_W_WAGR.WAGR_SOK
          AND FT_W_INCS.CLSF_SET_MNEM='GICS'
         
          and acct_prt_purp_typ<>'MASACCT' and
           sdsecm01.ft_w_posn.as_of_tms in (date1,date2)
           and ft_w_wagr.acct_grp_nme<>'PAPER Group'
           and assets_under_mgt_ind='Y'
           --AND FT_W_WPAR.PRTY_RL_TYP = 'PM'  
           --AND FT_W_WPAR.DW_STATUS_NUM = 1
           AND FT_W_ACCT.BK_ID IN ('GIMB','PACE');
          END IF;
         END Rpt_pcMAINPORTFOLIO_Test;
         
/*IF(SYMBOL IS NOT NULL AND MANAGER IS NULL)
THEN
OPEN REFCURSOR FOR
SELECT     SDSECM01.FT_W_POSN.AS_OF_TMS AS "Last Held Date",
DATE2 AS "TEST",
           SDSECM01.FT_W_POSN.AS_OF_DTDF_SOK AS "Last Held Date1",
           '' AS "Manager Name",
           sdsecm01.rpt_fngetmandatenames(sdsecm01.ft_w_acct.acct_sok)AS MANDATE,
           Trim(SDSECM01.FT_W_ACCT.ACCT_ID) || ' ' || trim(SDSECM01.FT_W_ACCT.ACCT_NME) AS PORTFOLIO,
           sdsecm01.ft_w_posn.qty_cqty AS quantity,
           sdsecm01.rpt_fngetpctsharesout(sdsecm01.ft_w_issu.iss_sok,sdsecm01.ft_w_posn.as_of_tms) AS "Pct Shares Out",
           SDSECM01.FT_W_POSN.LOCAL_CURR_MKT_CAMT AS "Market Value",
           '' as "Tot Mkt Val",
           round((100/sdsecm01.rpt_fngettotalmarketvalue(sdsecm01.ft_w_acct.acct_sok,sdsecm01.ft_w_posn.as_of_tms))*sdsecm01.ft_w_posn.local_curr_mkt_camt,2)  AS "Market Weight",
           SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT AS "Total Cost",
          case  FT_W_POSN.QTY_CQTY when 0 then 0 else Round(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT / FT_W_POSN.QTY_CQTY,2) end  AS "Unit Cost",  
           sdsecm01.ft_w_posn.local_curr_unit_cprc as "Unit Price",
           ROUND(((SDSECM01.FT_W_POSN.Local_Curr_Dirty_Mkt_Camt)-(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))/((SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))*100,2)  AS "Pct Unrealized Gain Loss",
           SDSECM01.FT_W_INCL.CLASS_LVL0 AS "Sector",
           SDSECM01.FT_W_INCL.CLASS_LVL1 AS "Industry",
           SDSECM01.FT_W_INCL.CLASS_LVL2 AS "Industry Group",
           SDSECM01.FT_W_INCL.CLASS_LVL3 AS "Sub Industry" ,
           FT_W_POSN.LOCAL_CURR_UNIT_CPRC,
           SDSECM01.FT_W_ACCT.ACCT_LVL_TYP,
           PREF_ISS_NME,
           PREF_ISS_DESC,
           to_char(to_date(mat_exp_tms),'dd/mm/yyyy') AS "Maturity Date",
           SDSECM01.FT_W_ISSU.TICKER_ID AS "TickerID",
           SDSECM01.FT_W_ISSU.CUSIP_ID AS "CUSIPID",
           SDSECM01.FT_W_ISSU.SEDOL_ID AS "SEDOLID",
           SDSECM01.FT_W_ISSU.ISIN_ID AS "ISINID",
           PREF_ISS_ID,
          SDSECM01.RPT_FNGETBESTID(SDSECM01.FT_W_ISSU.ISS_SOK) AS BESTID,
          PREF_ID_CTXT_TYP

FROM      
            SDSECM01.FT_W_POSN,
            SDSECM01.FT_W_ACCT,
            SDSECM01.FT_W_ISSU,
            SDSECM01.FT_W_ISCL,
            SDSECM01.FT_W_INCL,
            SDSECM01.FT_W_INCS,
            SDSECM01.FT_W_WPAC,
            --SDSECM01.FT_W_WPAR,
            --SDSECM01.FT_W_WPTY,
            SDSECM01.FT_W_WAGP,
            SDSECM01.FT_W_WAGR
           
         

WHERE      
          SDSECM01.FT_W_POSN.ACCT_SOK = SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_POSN.ISS_SOK = SDSECM01.FT_W_ISSU.ISS_SOK   AND
          SDSECM01.FT_W_WPAC.POSN_SOK  =SDSECM01.FT_W_POSN.POSN_SOK  AND
          SDSECM01.FT_W_ISCL.ISS_SOK  = SDSECM01.FT_W_POSN.ISS_SOK  AND
          SDSECM01.FT_W_INCL.INDUS_CLSF_SOK = SDSECM01.FT_W_ISCL.INDUS_CLSF_SOK AND
          SDSECM01.FT_W_INCS.INCS_SOK = SDSECM01.FT_W_INCL.INCS_SOK AND
          --SDSECM01.FT_W_WPAR.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          --SDSECM01.FT_W_WPAR.WPTY_SOK=SDSECM01.FT_W_WPTY.WPTY_SOK AND
          SDSECM01.FT_W_WAGP.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WAGP.PRNT_WAGR_SOK=SDSECM01.FT_W_WAGR.WAGR_SOK
          AND   SDSECM01.FT_W_POSN.AS_OF_TMS IN (DATE1,DATE2)  AND  FT_W_INCS.CLSF_SET_MNEM='GICS' AND ACCT_PRT_PURP_TYP<>'MASACCT' AND
           FT_W_WAGR.ACCT_GRP_NME<>'PAPER Group' AND ASSETS_UNDER_MGT_IND='Y' AND
         upper(symbol) in (lower(sdsecm01.ft_w_issu.cusip_id),upper(sdsecm01.ft_w_issu.cusip_id),initcap(cusip_id),lower(sdsecm01.ft_w_issu.ticker_id),upper(sdsecm01.ft_w_issu.ticker_id),initcap(ticker_id),lower(sdsecm01.ft_w_issu.sedol_id),upper(sdsecm01.ft_w_issu.sedol_id),initcap(sedol_id),lower(sdsecm01.ft_w_issu.isin_id),upper(sdsecm01.ft_w_issu.isin_id),initcap(isin_id))
         
         --AND FT_W_WPAR.PRTY_RL_TYP = 'PM'  
         --AND FT_W_WPAR.DW_STATUS_NUM = 1
         AND FT_W_ACCT.BK_ID IN ('GIMB','PACE');
         
          END IF;


IF(SYMBOL IS NOT NULL AND MANAGER IS NOT NULL)
THEN
IF(MANAGER='ALL') THEN
OPEN REFCURSOR FOR
SELECT     SDSECM01.FT_W_POSN.AS_OF_TMS AS "Last Held Date",
DATE2 AS "TEST",
           SDSECM01.FT_W_POSN.AS_OF_DTDF_SOK AS "Last Held Date1",
           SDSECM01.FT_W_WPTY.PRTY_NME AS "Manager Name",
           sdsecm01.rpt_fngetmandatenames(sdsecm01.ft_w_acct.acct_sok)AS MANDATE,
           Trim(SDSECM01.FT_W_ACCT.ACCT_ID) || ' ' || trim(SDSECM01.FT_W_ACCT.ACCT_NME) AS PORTFOLIO,
           sdsecm01.ft_w_posn.qty_cqty AS quantity,
           sdsecm01.rpt_fngetpctsharesout(sdsecm01.ft_w_issu.iss_sok,sdsecm01.ft_w_posn.as_of_tms) AS "Pct Shares Out",
           SDSECM01.FT_W_POSN.LOCAL_CURR_MKT_CAMT AS "Market Value",
           '' as "Tot Mkt Val",
           round((100/sdsecm01.rpt_fngettotalmarketvalue(sdsecm01.ft_w_acct.acct_sok,sdsecm01.ft_w_posn.as_of_tms))*sdsecm01.ft_w_posn.local_curr_mkt_camt,2)  AS "Market Weight",
           SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT AS "Total Cost",
          case  FT_W_POSN.QTY_CQTY when 0 then 0 else Round(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT / FT_W_POSN.QTY_CQTY,2) end  AS "Unit Cost",  
           sdsecm01.ft_w_posn.local_curr_unit_cprc as "Unit Price",
          ROUND(((SDSECM01.FT_W_POSN.Local_Curr_Dirty_Mkt_Camt)-(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))/((SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))*100,2)  AS "Pct Unrealized Gain Loss",
           SDSECM01.FT_W_INCL.CLASS_LVL0 AS "Sector",
           SDSECM01.FT_W_INCL.CLASS_LVL1 AS "Industry",
           SDSECM01.FT_W_INCL.CLASS_LVL2 AS "Industry Group",
           SDSECM01.FT_W_INCL.CLASS_LVL3 AS "Sub Industry" ,
           FT_W_POSN.LOCAL_CURR_UNIT_CPRC,
           SDSECM01.FT_W_ACCT.ACCT_LVL_TYP,
           PREF_ISS_NME,
           PREF_ISS_DESC,
           to_char(to_date(mat_exp_tms),'dd/mm/yyyy') AS "Maturity Date",
           SDSECM01.FT_W_ISSU.TICKER_ID AS "TickerID",
           SDSECM01.FT_W_ISSU.CUSIP_ID AS "CUSIPID",
           SDSECM01.FT_W_ISSU.SEDOL_ID AS "SEDOLID",
           SDSECM01.FT_W_ISSU.ISIN_ID AS "ISINID",
           PREF_ISS_ID,
          SDSECM01.RPT_FNGETBESTID(SDSECM01.FT_W_ISSU.ISS_SOK) AS BESTID,
          PREF_ID_CTXT_TYP

FROM      
            SDSECM01.FT_W_POSN,
            SDSECM01.FT_W_ACCT,
            SDSECM01.FT_W_ISSU,
            SDSECM01.FT_W_ISCL,
            SDSECM01.FT_W_INCL,
            sdsecm01.ft_w_incs,
            SDSECM01.FT_W_WPAC,
            --SDSECM01.FT_W_WPAR,
            --SDSECM01.FT_W_WPTY,
            SDSECM01.FT_W_WAGR,
            SDSECM01.FT_W_WAGP
           
         

WHERE      
          SDSECM01.FT_W_POSN.ACCT_SOK = SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_POSN.ISS_SOK = SDSECM01.FT_W_ISSU.ISS_SOK   AND
          SDSECM01.FT_W_WPAC.POSN_SOK  =SDSECM01.FT_W_POSN.POSN_SOK  AND
          SDSECM01.FT_W_ISCL.ISS_SOK  = SDSECM01.FT_W_POSN.ISS_SOK  AND
          SDSECM01.FT_W_INCL.INDUS_CLSF_SOK = SDSECM01.FT_W_ISCL.INDUS_CLSF_SOK AND
          SDSECM01.FT_W_INCS.INCS_SOK = SDSECM01.FT_W_INCL.INCS_SOK AND
          --SDSECM01.FT_W_WPAR.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          --SDSECM01.FT_W_WPAR.WPTY_SOK=SDSECM01.FT_W_WPTY.WPTY_SOK AND
          SDSECM01.FT_W_WAGP.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WAGR.WAGR_SOK=SDSECM01.FT_W_WAGP.PRNT_WAGR_SOK
          AND FT_W_WAGR.ACCT_GRP_NME<>'PAPER Group' AND ASSETS_UNDER_MGT_IND='Y'
          AND FT_W_INCS.CLSF_SET_MNEM='GICS'
         AND        
           
         SDSECM01.FT_W_POSN.AS_OF_TMS IN (DATE1,DATE2)  AND   ACCT_PRT_PURP_TYP<>'MASACCT' AND
         upper(symbol) in (lower(sdsecm01.ft_w_issu.cusip_id),upper(sdsecm01.ft_w_issu.cusip_id),initcap(cusip_id),lower(sdsecm01.ft_w_issu.ticker_id),upper(sdsecm01.ft_w_issu.ticker_id),initcap(ticker_id),lower(sdsecm01.ft_w_issu.sedol_id),upper(sdsecm01.ft_w_issu.sedol_id),initcap(sedol_id),lower(sdsecm01.ft_w_issu.isin_id),upper(sdsecm01.ft_w_issu.isin_id),initcap(isin_id))
         --AND FT_W_WPAR.PRTY_RL_TYP = 'PM'  
         --AND FT_W_WPAR.DW_STATUS_NUM = 1
         AND FT_W_ACCT.BK_ID IN ('GIMB','PACE');
ELSE
OPEN REFCURSOR FOR
SELECT     SDSECM01.FT_W_POSN.AS_OF_TMS AS "Last Held Date",
DATE2 AS "TEST",
           SDSECM01.FT_W_POSN.AS_OF_DTDF_SOK AS "Last Held Date1",
           SDSECM01.FT_W_WPTY.PRTY_NME AS "Manager Name",
           sdsecm01.rpt_fngetmandatenames(sdsecm01.ft_w_acct.acct_sok)AS MANDATE,
           Trim(SDSECM01.FT_W_ACCT.ACCT_ID) || ' ' || trim(SDSECM01.FT_W_ACCT.ACCT_NME) AS PORTFOLIO,
           sdsecm01.ft_w_posn.qty_cqty AS quantity,
           sdsecm01.rpt_fngetpctsharesout(sdsecm01.ft_w_issu.iss_sok,sdsecm01.ft_w_posn.as_of_tms) AS "Pct Shares Out",
           SDSECM01.FT_W_POSN.LOCAL_CURR_MKT_CAMT AS "Market Value",
           '' as "Tot Mkt Val",
           round((100/sdsecm01.rpt_fngettotalmarketvalue(sdsecm01.ft_w_acct.acct_sok,sdsecm01.ft_w_posn.as_of_tms))*sdsecm01.ft_w_posn.local_curr_mkt_camt,2)  AS "Market Weight",
           SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT AS "Total Cost",
          case  FT_W_POSN.QTY_CQTY when 0 then 0 else Round(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT / FT_W_POSN.QTY_CQTY,2) end  AS "Unit Cost",  
           sdsecm01.ft_w_posn.local_curr_unit_cprc as "Unit Price",
           ROUND(((SDSECM01.FT_W_POSN.Local_Curr_Dirty_Mkt_Camt)-(SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))/((SDSECM01.FT_W_POSN.LOCAL_ORIG_COST_CAMT))*100,2)  AS "Pct Unrealized Gain Loss",
           SDSECM01.FT_W_INCL.CLASS_LVL0 AS "Sector",
           SDSECM01.FT_W_INCL.CLASS_LVL1 AS "Industry",
           SDSECM01.FT_W_INCL.CLASS_LVL2 AS "Industry Group",
           SDSECM01.FT_W_INCL.CLASS_LVL3 AS "Sub Industry" ,
           FT_W_POSN.LOCAL_CURR_UNIT_CPRC,
           SDSECM01.FT_W_ACCT.ACCT_LVL_TYP,
           PREF_ISS_NME,
           PREF_ISS_DESC,
           to_char(to_date(mat_exp_tms),'dd/mm/yyyy') AS "Maturity Date",
           SDSECM01.FT_W_ISSU.TICKER_ID AS "TickerID",
           SDSECM01.FT_W_ISSU.CUSIP_ID AS "CUSIPID",
           SDSECM01.FT_W_ISSU.SEDOL_ID AS "SEDOLID",
           SDSECM01.FT_W_ISSU.ISIN_ID AS "ISINID",
           PREF_ISS_ID,
          SDSECM01.RPT_FNGETBESTID(SDSECM01.FT_W_ISSU.ISS_SOK) AS BESTID,
          PREF_ID_CTXT_TYP

FROM      
            SDSECM01.FT_W_POSN,
            SDSECM01.FT_W_ACCT,
            SDSECM01.FT_W_ISSU,
            SDSECM01.FT_W_ISCL,
            SDSECM01.FT_W_INCL,
            SDSECM01.FT_W_INCS,
            SDSECM01.FT_W_WPAC,
            SDSECM01.FT_W_WPAR,
            SDSECM01.FT_W_WPTY,
            SDSECM01.FT_W_WAGP,
            SDSECM01.FT_W_WAGR
           
         

WHERE      
          SDSECM01.FT_W_POSN.ACCT_SOK = SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_POSN.ISS_SOK = SDSECM01.FT_W_ISSU.ISS_SOK   AND
          SDSECM01.FT_W_WPAC.POSN_SOK  =SDSECM01.FT_W_POSN.POSN_SOK  AND
          SDSECM01.FT_W_ISCL.ISS_SOK  = SDSECM01.FT_W_POSN.ISS_SOK  AND
          SDSECM01.FT_W_INCL.INDUS_CLSF_SOK = SDSECM01.FT_W_ISCL.INDUS_CLSF_SOK AND
          SDSECM01.FT_W_INCS.INCS_SOK = SDSECM01.FT_W_INCL.INCS_SOK AND
          SDSECM01.FT_W_WPAR.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WPAR.WPTY_SOK=SDSECM01.FT_W_WPTY.WPTY_SOK  AND
          SDSECM01.FT_W_WAGP.ACCT_SOK=SDSECM01.FT_W_ACCT.ACCT_SOK AND
          SDSECM01.FT_W_WAGR.WAGR_SOK=SDSECM01.FT_W_WAGP.PRNT_WAGR_SOK
           AND FT_W_WAGR.ACCT_GRP_NME<>'PAPER Group' AND ASSETS_UNDER_MGT_IND='Y' AND
         
         FT_W_INCS.CLSF_SET_MNEM='GICS'
          AND          
         SDSECM01.FT_W_POSN.AS_OF_TMS IN (DATE1,DATE2)  AND   ACCT_PRT_PURP_TYP<>'MASACCT' AND
         upper(symbol) in (lower(sdsecm01.ft_w_issu.cusip_id),upper(sdsecm01.ft_w_issu.cusip_id),initcap(cusip_id),lower(sdsecm01.ft_w_issu.ticker_id),upper(sdsecm01.ft_w_issu.ticker_id),initcap(ticker_id),lower(sdsecm01.ft_w_issu.sedol_id),upper(sdsecm01.ft_w_issu.sedol_id),initcap(sedol_id),lower(sdsecm01.ft_w_issu.isin_id),upper(sdsecm01.ft_w_issu.isin_id),initcap(isin_id))
         AND SDSECM01.FT_W_WPTY.PRTY_NME=MANAGER AND FT_W_ACCT.BK_ID IN ('GIMB','PACE');
         end if;
         END IF;*/
0
Comment
Question by:Kanigi
8 Comments
 
LVL 17

Expert Comment

by:sweetfa2
Comment Utility
What do your server logs say.

Are you exhausting a resource?
0
 

Author Comment

by:Kanigi
Comment Utility
Sorry i dont have any access to check the server logs.since i only use for querying the database
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Do you have an Oracle error? "Io exception: Connection reset"  looks like an error from within some 3rd party tool.

Oracle error would be of the from  "ORA-XXXXX: Some error message here"
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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
Google'ing that exact error message points to a Java program.  Is that correct?

Check the database alert log to see if any errors are being generated.

http://forums.oracle.com/forums/thread.jspa?threadID=366113

I haven't done much with Java and Oracle but I'm wondering if something in the network might be timing out.  How long does the query take?

On a side note:
I haven't looked at all the select statements in detail but it looks like you are just changing the where clause based on the input parameters.  I would really look into going with dynamic SQL or some smoke and mirrors with something like a case statement to get it down to a single select statement.

I can see maintaining this as-is will be a headache down the road.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
Comment Utility
I agree that this doesn't look like an Oracle error.  This looks like an error from some other part of the network/application stack.

I also agree with slightwv, that this combination of multiple, but slightly different "select..." statements in this procedure that vary based on different input parameter values does *NOT* look like good programming!  Ideally, you would use just one "select..." statement with different "where" clause values, or possibly with some differences in the columns being selected, or the data being selected from various columns that use "case" or "decode" to help you get the job done with just one, flexible query.

I also see some possible performance problems in the queries:
1. "select distinct max...".  The "distinct" is totally unnecessary here, since "max" already returns just a single, distinct value.  Why add an extra query operator that adds no value?

2. "where ... upper(symbol) in ...". Try to avoid using operators like "upper", "lower", "to_char", "to_date", etc. on database columns since they prevent the use of indexes on those columns *AND* they force Oracle to read EVERY record and do the data conversion to see if the record should be returned by the query, or not.  You can use those operators on the literal values, or in the "select" columns with very little performance penalty, but on database columns in the "where" clauses, those operators can be a huge problem.

3. "where ... ACCT_PRT_PURP_TYP<>'MASACCT' ".  Try to avoid the "<>" (does not equal) operator, since that also prevents the use of indexes on that column.
0
 

Author Closing Comment

by:Kanigi
Comment Utility
I changed my code anyhow its running fine.but the inputs given are very much helpful
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.

Join & Write a Comment

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now