Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Connection Reset error while executing PL/SQL script

Posted on 2010-11-23
8
Medium Priority
?
636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 17

Expert Comment

by:sweetfa2
ID: 34203399
What do your server logs say.

Are you exhausting a resource?
0
 

Author Comment

by:Kanigi
ID: 34210753
Sorry i dont have any access to check the server logs.since i only use for querying the database
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34480319
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 34480502
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 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1000 total points
ID: 34494743
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
ID: 34733210
I changed my code anyhow its running fine.but the inputs given are very much helpful
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

715 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