Home » SQL & PL/SQL » SQL & PL/SQL » oracle to moss (Oracle, 10g, Windows server 2003)
oracle to moss [message #443390] Mon, 15 February 2010 08:22 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
My project has oracle 10g as backend and sharepoint as
frontend.

I have designed one package which contains one procedure
which has 3 ref cursors as output parameters, but when my
sharepoint developer calls it he gets
ORA-24338: statement handle not executed

On searching i found out that this error appears when there
ref cursor in stored procedure and it is not opened, but in my
case all my three out parameters (ref cursors) are opened.

So can u please tell me what might be the reason.

Regards,
Ritesh
Re: oracle to moss [message #443393 is a reply to message #443390] Mon, 15 February 2010 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-24338: statement handle not executed
 *Cause:  A fetch or describe was attempted before executing a
          statement handle.
 *Action: Execute a statement and then fetch or describe the data.

Regards
Michel
Re: oracle to moss [message #443399 is a reply to message #443390] Mon, 15 February 2010 08:46 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
I got these causes and Action from Oracle docs. but
was not able to arrive at the solution.

Can u please elaborate the various causes and
solutions.

Regards,
Ritesh
Re: oracle to moss [message #443402 is a reply to message #443399] Mon, 15 February 2010 08:52 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

We can not debug code we can not see.
Re: oracle to moss [message #443404 is a reply to message #443399] Mon, 15 February 2010 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You fetched a cursor that was not first executed.
The normal operations on a cursor are:
1/ open/parse
2/ execute
3/ fetch
4/ close

Regards
Michel
Re: oracle to moss [message #443405 is a reply to message #443402] Mon, 15 February 2010 09:00 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
Following is my Packaged procedure named proc_unity

Procedure proc_unity(    p_fig_all_cur			OUT	ref_cur_fig, 
				p_fig_c1_cur			OUT	ref_cur_fig, 
				--p_fig_not_c1_cur		OUT	ref_cur_fig,
				p_chk				IN	Number,
				p_pageno			IN	Number		Default 1,
				p_no_of_pages			OUT	Number,
				p_no_of_records			OUT     Number			
			      ) Is

v_rec_cnt  Number := 0;

Begin
   If p_chk = 1 Then
              Select Count(*) Into v_rec_cnt 
	      From ( SELECT C.OID,C.CUSTOMER_ID AS  CAS,
			       N.NAME        AS  CUSTOMER_NAME,
			       U.CLIENT_UCN  AS  UCN,
			       G.OBG         AS  OG,
			       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
			       R.CCF_CD      AS  CREDIT_FLAG,
			       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
									  FROM   REFDBO.V_INT_GRADES
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN C.CUSTOMER_ID  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT C.CUSTOMER_ID
									  FROM   REFDBO.V_CLIENT
									  WHERE  OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_CUSTOMER_ID,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN N.NAME  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NAME
									  FROM   REFDBO.V_CIT_CLIENT_NAME
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT)  END)   FAMILY_CUSTOMER_NAME,                                                                                                                                                
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN U.CLIENT_UCN  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT CLIENT_UCN
									   FROM   REFDBO.V_CIT_CLIENT_UCN
									   WHERE  CLIENT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_UCN,  
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.OBG  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT OBG
									   FROM   REFDBO.V_INT_GRADES
									   WHERE  CLT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_OG,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN R.CCF_CD  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT CCF_CD
									   FROM   REFDBO.V_CREDIT_FLAG
									   WHERE  CLT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_CREDIT_FLAG
		       FROM    REFDBO.V_CIT_CLIENT_UCN     U,
			       REFDBO.V_INT_GRADES         G,                   
			       REFDBO.V_CREDIT_FLAG        R,
			       REFDBO.V_UNIQUE_ULT_PAR_ALL P,
			       REFDBO.V_CLIENT             C,
			       REFDBO.V_CIT_CLIENT_NAME    N,
			       REFDBO.V_TEAM_PATH_V3       T
			WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
			AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
			AND    C.OID = N.CLT_OID
			AND    N.CLT_OID = U.CLIENT_OID
			AND    U.CLIENT_OID = G.CLT_OID
			AND    G.CLT_OID = P.CHILD_CLT_OID
			AND    P.CHILD_CLT_OID = R.CLT_OID
			AND    R.CLT_OID = T.CLT_OID
			AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
			AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
			AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
			AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
			AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
			AND    C.OID = '1256120'
			GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD  
	           );
   
    Open p_fig_all_cur For Select CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CREDIT_FLAG,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,row_number
                            From (Select CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CREDIT_FLAG,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,Rownum row_number 
                                  From (SELECT C.OID,C.CUSTOMER_ID AS  CAS,
					       N.NAME        AS  CUSTOMER_NAME,
					       U.CLIENT_UCN  AS  UCN,
					       G.OBG         AS  OG,
					       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
					       R.CCF_CD      AS  CREDIT_FLAG,
					       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
					       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
					       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
					       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
							WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
							WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
											  FROM   REFDBO.V_INT_GRADES
											  WHERE  CLT_OID = P.ULT_PAR_OID
											  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT
				       FROM    REFDBO.V_CIT_CLIENT_UCN     U,
					       REFDBO.V_INT_GRADES         G,                   
					       REFDBO.V_CREDIT_FLAG        R,
					       REFDBO.V_UNIQUE_ULT_PAR_ALL P,
					       REFDBO.V_CLIENT             C,
					       REFDBO.V_CIT_CLIENT_NAME    N,
					       REFDBO.V_TEAM_PATH_V3       T
					WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
					AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
					AND    C.OID = N.CLT_OID
					AND    N.CLT_OID = U.CLIENT_OID
					AND    U.CLIENT_OID = G.CLT_OID
					AND    G.CLT_OID = P.CHILD_CLT_OID
					AND    P.CHILD_CLT_OID = R.CLT_OID
					AND    R.CLT_OID = T.CLT_OID
					AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
					AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
					AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
					AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
					AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
					AND    C.OID = '1256120'
					GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD
					)
				)
			    Where row_number Between 30*(p_pageno-1)+1 And 30*p_pageno;

       p_no_of_records := v_rec_cnt;

       p_no_of_pages := Ceil(v_rec_cnt/30);

  Elsif p_chk = 2 Then
              Select Count(*) Into v_rec_cnt 
	      From ( SELECT C.OID,C.CUSTOMER_ID AS  CAS,
			       N.NAME        AS  CUSTOMER_NAME,
			       U.CLIENT_UCN  AS  UCN,
			       G.OBG         AS  OG,
			       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
			       R.CCF_CD      AS  CREDIT_FLAG,
			       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
									  FROM   REFDBO.V_INT_GRADES
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN C.CUSTOMER_ID  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT C.CUSTOMER_ID
									  FROM   REFDBO.V_CLIENT
									  WHERE  OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_CUSTOMER_ID,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN N.NAME  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NAME
									  FROM   REFDBO.V_CIT_CLIENT_NAME
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT)  END)   FAMILY_CUSTOMER_NAME,                                                                                                                                                
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN U.CLIENT_UCN  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT CLIENT_UCN
									   FROM   REFDBO.V_CIT_CLIENT_UCN
									   WHERE  CLIENT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_UCN,  
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.OBG  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT OBG
									   FROM   REFDBO.V_INT_GRADES
									   WHERE  CLT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_OG,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN R.CCF_CD  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT CCF_CD
									   FROM   REFDBO.V_CREDIT_FLAG
									   WHERE  CLT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_CREDIT_FLAG
		       FROM    REFDBO.V_CIT_CLIENT_UCN     U,
			       REFDBO.V_INT_GRADES         G,                   
			       REFDBO.V_CREDIT_FLAG        R,
			       REFDBO.V_UNIQUE_ULT_PAR_ALL P,
			       REFDBO.V_CLIENT             C,
			       REFDBO.V_CIT_CLIENT_NAME    N,
			       REFDBO.V_TEAM_PATH_V3       T
			WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
			AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
			AND    C.OID = N.CLT_OID
			AND    N.CLT_OID = U.CLIENT_OID
			AND    U.CLIENT_OID = G.CLT_OID
			AND    G.CLT_OID = P.CHILD_CLT_OID
			AND    P.CHILD_CLT_OID = R.CLT_OID
			AND    R.CLT_OID = T.CLT_OID
			AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
			AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
			AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
			AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
			AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
			AND    R.CCF_CD = 'C1'
			AND    C.OID = '1256120'
			GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD  
	           );
   
    Open p_fig_c1_cur For Select CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CREDIT_FLAG,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,row_number
                            From (Select CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CREDIT_FLAG,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,Rownum row_number 
                                  From (SELECT C.OID,C.CUSTOMER_ID AS  CAS,
					       N.NAME        AS  CUSTOMER_NAME,
					       U.CLIENT_UCN  AS  UCN,
					       G.OBG         AS  OG,
					       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
					       R.CCF_CD      AS  CREDIT_FLAG,
					       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
					       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
					       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
					       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
							WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
							WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
											  FROM   REFDBO.V_INT_GRADES
											  WHERE  CLT_OID = P.ULT_PAR_OID
											  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT
				       FROM    REFDBO.V_CIT_CLIENT_UCN     U,
					       REFDBO.V_INT_GRADES         G,                   
					       REFDBO.V_CREDIT_FLAG        R,
					       REFDBO.V_UNIQUE_ULT_PAR_ALL P,
					       REFDBO.V_CLIENT             C,
					       REFDBO.V_CIT_CLIENT_NAME    N,
					       REFDBO.V_TEAM_PATH_V3       T
					WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
					AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
					AND    C.OID = N.CLT_OID
					AND    N.CLT_OID = U.CLIENT_OID
					AND    U.CLIENT_OID = G.CLT_OID
					AND    G.CLT_OID = P.CHILD_CLT_OID
					AND    P.CHILD_CLT_OID = R.CLT_OID
					AND    R.CLT_OID = T.CLT_OID
					AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
					AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
					AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
					AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
					AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
					AND    R.CCF_CD = 'C1'
					AND    C.OID = '1256120'
					GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD
					)
				)
			    Where row_number Between 30*(p_pageno-1)+1 And 30*p_pageno;
       p_no_of_records := v_rec_cnt;

       p_no_of_pages := Ceil(v_rec_cnt/30);

  /*Elsif p_chk = 3 Then
              Select Count(*) Into v_rec_cnt 
	      From ( SELECT C.OID,C.CUSTOMER_ID AS  CAS,
			       N.NAME        AS  CUSTOMER_NAME,
			       U.CLIENT_UCN  AS  UCN,
			       G.OBG         AS  OG,
			       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
			       R.CCF_CD      AS  CREDIT_FLAG,
			       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
			       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
									  FROM   REFDBO.V_INT_GRADES
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN C.CUSTOMER_ID  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT C.CUSTOMER_ID
									  FROM   REFDBO.V_CLIENT
									  WHERE  OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_CUSTOMER_ID,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN N.NAME  
					WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NAME
									  FROM   REFDBO.V_CIT_CLIENT_NAME
									  WHERE  CLT_OID = P.ULT_PAR_OID
									  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT)  END)   FAMILY_CUSTOMER_NAME,                                                                                                                                                
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN U.CLIENT_UCN  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT CLIENT_UCN
									   FROM   REFDBO.V_CIT_CLIENT_UCN
									   WHERE  CLIENT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_UCN,  
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN G.OBG  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT OBG
									   FROM   REFDBO.V_INT_GRADES
									   WHERE  CLT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_OG,
			       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
					WHEN C.OID = P.ULT_PAR_OID THEN R.CCF_CD  
					WHEN C.OID <> P.ULT_PAR_OID THEN ( SELECT CCF_CD
									   FROM   REFDBO.V_CREDIT_FLAG
									   WHERE  CLT_OID = P.ULT_PAR_OID
									   AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_CREDIT_FLAG
		       FROM    REFDBO.V_CIT_CLIENT_UCN     U,
			       REFDBO.V_INT_GRADES         G,                   
			       REFDBO.V_CREDIT_FLAG        R,
			       REFDBO.V_UNIQUE_ULT_PAR_ALL P,
			       REFDBO.V_CLIENT             C,
			       REFDBO.V_CIT_CLIENT_NAME    N,
			       REFDBO.V_TEAM_PATH_V3       T
			WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
			AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
			AND    C.OID = N.CLT_OID
			AND    N.CLT_OID = U.CLIENT_OID
			AND    U.CLIENT_OID = G.CLT_OID
			AND    G.CLT_OID = P.CHILD_CLT_OID
			AND    P.CHILD_CLT_OID = R.CLT_OID
			AND    R.CLT_OID = T.CLT_OID
			AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
			AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
			AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
			AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
			AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
			AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
			AND    R.CCF_CD != 'C1'
			AND    C.OID = '1256120'
			GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD  
	           );
   
    Open p_fig_not_c1_cur For Select CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CREDIT_FLAG,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,row_number
                            From (Select CAS,CUSTOMER_NAME,UCN,OG,CLIENT_NXT_RVW_DT,CREDIT_FLAG,PRIMARY_CRED_EXEC,SUPERVISORY_CRED_EXEC,CORP_BANKER,Rownum row_number 
                                  From (SELECT C.OID,C.CUSTOMER_ID AS  CAS,
					       N.NAME        AS  CUSTOMER_NAME,
					       U.CLIENT_UCN  AS  UCN,
					       G.OBG         AS  OG,
					       G.NXT_RVW_DT  AS  CLIENT_NXT_RVW_DT,
					       R.CCF_CD      AS  CREDIT_FLAG,
					       Max(Decode(T.CODE_VALUE,'C1',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) PRIMARY_CRED_EXEC,
					       Max(Decode(T.CODE_VALUE,'C3',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) SUPERVISORY_CRED_EXEC,
					       Max(Decode(T.CODE_VALUE,'CORP BKER',Substr(ID_EMAIL_INTERNAL,1,Instr(ID_EMAIL_INTERNAL,'/')-1))) AS CORP_BANKER,
					       Max(CASE WHEN P.ULT_PAR_OID IS NULL THEN NULL 
							WHEN C.OID = P.ULT_PAR_OID THEN G.NXT_RVW_DT  
							WHEN C.OID <> P.ULT_PAR_OID THEN (SELECT NXT_RVW_DT
											  FROM   REFDBO.V_INT_GRADES
											  WHERE  CLT_OID = P.ULT_PAR_OID
											  AND    SYSDATE BETWEEN CIT_EFF_ASOF_DT AND CIT_EFF_UNTIL_DT) END)   FAMILY_NXT_RVW_DT
				       FROM    REFDBO.V_CIT_CLIENT_UCN     U,
					       REFDBO.V_INT_GRADES         G,                   
					       REFDBO.V_CREDIT_FLAG        R,
					       REFDBO.V_UNIQUE_ULT_PAR_ALL P,
					       REFDBO.V_CLIENT             C,
					       REFDBO.V_CIT_CLIENT_NAME    N,
					       REFDBO.V_TEAM_PATH_V3       T
					WHERE  SUBSTR(R.CCF_CD , 1 , 1 ) = 'C'
					AND    CODE_VALUE IN ( 'C1','C3','CORP BKER','CRD ADM' )
					AND    C.OID = N.CLT_OID
					AND    N.CLT_OID = U.CLIENT_OID
					AND    U.CLIENT_OID = G.CLT_OID
					AND    G.CLT_OID = P.CHILD_CLT_OID
					AND    P.CHILD_CLT_OID = R.CLT_OID
					AND    R.CLT_OID = T.CLT_OID
					AND    SYSDATE BETWEEN C.CIT_EFF_ASOF_DT AND C.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN N.CIT_EFF_ASOF_DT AND N.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN U.CIT_EFF_ASOF_DT AND U.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN G.CIT_EFF_ASOF_DT AND G.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN P.CIT_EFF_ASOF_DT AND P.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN R.CIT_EFF_ASOF_DT AND R.CIT_EFF_UNTIL_DT
					AND    SYSDATE BETWEEN CT_ASOF_DT AND CT_UNTIL_DT
					AND    SYSDATE BETWEEN CV_ASOF_DT AND CV_UNTIL_DT
					AND    SYSDATE BETWEEN HR_ASOF_DT AND HR_UNTIL_DT
					AND    SYSDATE BETWEEN TM_ASOF_DT AND TM_UNTIL_DT
					AND    SYSDATE BETWEEN TMB_ASOF_DT AND TMB_UNTIL_DT
					AND    R.CCF_CD != 'C1'
					AND    C.OID = '1256120'
					GROUP BY C.OID,C.CUSTOMER_ID,N.NAME,U.CLIENT_UCN,G.OBG,G.NXT_RVW_DT,R.CCF_CD
					)
				)
			    Where row_number Between 30*(p_pageno-1)+1 And 30*p_pageno;
       p_no_of_records := v_rec_cnt;

       p_no_of_pages := Ceil(v_rec_cnt/30);*/

  End If;

End proc_unity;


Regards,
Ritesh




CM: removed unnecessary format tags. You really don't need to format code with anything other than code tags and skyblue is hard on the eyes.

[Updated on: Mon, 15 February 2010 09:14] by Moderator

Report message to a moderator

Re: oracle to moss [message #443408 is a reply to message #443405] Mon, 15 February 2010 09:17 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

It's quite easy to reproduce your problem.
SQL> CREATE OR REPLACE PROCEDURE refcur_test (p_in     IN     NUMBER,
  2                                           p_ref1      OUT SYS_REFCURSOR,
  3                                           p_ref2      OUT SYS_REFCURSOR)
  4  IS
  5  BEGIN
  6     IF p_in = 1
  7     THEN
  8        OPEN p_ref1 FOR SELECT 'x' FROM DUAL;
  9     ELSE
 10        OPEN p_ref2 FOR SELECT 'y' FROM DUAL;
 11     END IF;
 12  END;
 13  /

Procedure created.

SQL> var p_ref1 refcursor
SQL> var p_ref2 refcursor
SQL> exec refcur(1,:p_ref1,:p_ref2);

PL/SQL procedure successfully completed.


SQL> print :p_ref1

'
-
x

SQL> print :p_ref2
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "p_ref2"
SQL>
Re: oracle to moss [message #443409 is a reply to message #443390] Mon, 15 February 2010 09:19 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you've missed the point of ref cursors.
They allow you to use the same cursor handle (out parameter in this case) for different select statements.
You should have one ref_cursor out parameter. You should then open that one ref_cursor for one of the three selects based on the value of p_chk.
Re: oracle to moss [message #443417 is a reply to message #443408] Mon, 15 February 2010 10:41 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi tahpush,
It will be great if u explain me when and why thie error comes when using ref cursor.

Regards,
Ritesh
Re: oracle to moss [message #443418 is a reply to message #443417] Mon, 15 February 2010 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does tahpush's example not explain it?

regards
Michel

Re: oracle to moss [message #443419 is a reply to message #443418] Mon, 15 February 2010 10:59 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
I'm not able to understand the exact reason.

Re: oracle to moss [message #443420 is a reply to message #443390] Mon, 15 February 2010 11:00 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I would have thought that was obvious from tahpush's example.
His procedure returns two ref cursors but only ever opens one of them, depending on the value of p_in. When you try to read the ref cursor that hasn't been opened you get an error.
You've got similar logic in your procedure.

cookiemonster wrote on Mon, 15 February 2010 15:19
I think you've missed the point of ref cursors.
They allow you to use the same cursor handle (out parameter in this case) for different select statements.
You should have one ref_cursor out parameter. You should then open that one ref_cursor for one of the three selects based on the value of p_chk.


So following that tahpush's code would become:
SQL> CREATE OR REPLACE PROCEDURE refcur_test (p_in     IN     NUMBER,
  2                                           p_ref OUT SYS_REFCURSOR)
  3  IS
  4  BEGIN
  5    IF p_in = 1 THEN
  6      OPEN p_ref FOR SELECT 'x' FROM DUAL;
  7    ELSE
  8      OPEN p_ref FOR SELECT 'y' FROM DUAL;
  9    END IF;
 10  END;
 11  /

Procedure created.

SQL> var p_ref refcursor
SQL> exec refcur_test(1, :p_ref);

PL/SQL procedure successfully completed.

SQL> print p_ref;

'
-
x

SQL> exec refcur_test(2, :p_ref);

PL/SQL procedure successfully completed.

SQL>  print p_ref;

'
-
y

SQL> 
Re: oracle to moss [message #443451 is a reply to message #443418] Mon, 15 February 2010 21:00 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
I understood the cause of ora-24338 from tahpush's example.
But dont you if my sharepoint developer can keep a check from which cursor to fetch data then everyrhing will work smoothly.

Regards,
Ritesh
Re: oracle to moss [message #443452 is a reply to message #443451] Mon, 15 February 2010 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>But dont you if my sharepoint developer can keep a check from which cursor to fetch data then everyrhing will work smoothly.

What do your module or integration testing reveal?
Don't ask us what might work.
Run your own tests to see for yourself the results.

We don't have your tables or data so we can't run posted code.
Re: oracle to moss [message #443453 is a reply to message #443452] Mon, 15 February 2010 21:22 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,

Sorry i cant publish code, since my senior is not allowing it.

My scenario is same like the one in tahpush example.
I'm sending two ref cursors as out parameter namely
p_fig_all_cur and p_fig_c1_cur.
I'm opening each cursor depending on IF condition on p_chk.
My doubt is that using p_chk identifier which my
packaged procedure is gonna get as input parameter from
my sharepoint developer, can he check from which cursor to
fetch data
.



Regards,
Ritesh
Re: oracle to moss [message #443454 is a reply to message #443453] Mon, 15 February 2010 21:51 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Blackswan,
Sir dont misunderstand me please
My statement was


Quote:
But dont you think if my sharepoint developer can keep a check from which cursor to fetch data then everyrhing will work smoothly.



Regards,
Ritesh
Re: oracle to moss [message #443455 is a reply to message #443454] Mon, 15 February 2010 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member

cookiemonster wrote on Mon, 15 February 2010 07:19
I think you've missed the point of ref cursors.
They allow you to use the same cursor handle (out parameter in this case) for different select statements.
You should have one ref_cursor out parameter. You should then open that one ref_cursor for one of the three selects based on the value of p_chk.



Do NOT have more than 1 OUTPUT REFCURSOR!
Re: oracle to moss [message #443459 is a reply to message #443455] Mon, 15 February 2010 22:54 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Blackswan,
I agree that it will better if i have only one
one out parameter as ref cursor.
But my doubt is:

Is it wrong if i dont change my stored procedure and let
my sharepoint developer do the necessary changes



Regards,
Ritesh
Re: oracle to moss [message #443460 is a reply to message #443408] Mon, 15 February 2010 22:58 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi tahpush Something is doubtful with procedure names,

...PROCEDURE refcur_test (p_in     ...

...exec refcur(1,:p_ref1,...


regards,
Delna
Re: oracle to moss [message #443461 is a reply to message #443459] Mon, 15 February 2010 23:00 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
If you like tap dancing in a mine field, then don't change post code.

Returning an "invalid" REFCURSOR on every invocation is a design flaw in my opinion & a timebomb that could detonate at any time.
Re: oracle to moss [message #443463 is a reply to message #443459] Mon, 15 February 2010 23:06 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
getritesh
I dont know Sharepoint, but there may be some problem in the sequence of statements while working with REF CURSORs.

in calling environment, it will be
...
proc_unity(p_fig_all_cur OUT ref_cur_fig,...
loop
  fetch...
  exit when...
end loop;
close p_fig_all_cur;
...


regards,
Delna

[Updated on: Mon, 15 February 2010 23:07]

Report message to a moderator

Re: oracle to moss [message #443476 is a reply to message #443451] Tue, 16 February 2010 00:41 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
getritesh wrote on Tue, 16 February 2010 04:00
Hi Michel,
I understood the cause of ora-24338 from tahpush's example.
But dont you if my sharepoint developer can keep a check from which cursor to fetch data then everyrhing will work smoothly.

Regards,
Ritesh

Using tahpush's example:
SQL> CREATE OR REPLACE PROCEDURE refcur_test (p_in     IN     NUMBER,
  2                                           p_ref1      OUT SYS_REFCURSOR,
  3                                           p_ref2      OUT SYS_REFCURSOR)
  4  IS
  5   BEGIN
  6      IF p_in = 1
  7      THEN
  8         OPEN p_ref1 FOR SELECT 'x' FROM DUAL;
  9      ELSE
 10         OPEN p_ref2 FOR SELECT 'y' FROM DUAL;
 11      END IF;
 12   END;
 13  /

Procedure created.

SQL> var p_ref1 refcursor
SQL> var p_ref2 refcursor
SQL> exec refcur_test(1,:p_ref1,:p_ref2);

PL/SQL procedure successfully completed.

SQL> begin
  2    if :p_ref1%isopen then 
  3      dbms_output.put_line('cursor 1 is open, can fetch');
  4    else
  5      dbms_output.put_line('cursor 1 is not open, skip');
  6    end if;
  7    if :p_ref2%isopen then 
  8      dbms_output.put_line('cursor 2 is open, can fetch');
  9    else
 10      dbms_output.put_line('cursor 2 is not open, skip');
 11    end if;
 12  end;
 13  /
cursor 1 is open, can fetch
cursor 2 is not open, skip

PL/SQL procedure successfully completed.

Regards
Michel

Previous Topic: Dynamic Merge
Next Topic: Elimiate duplicate name/values from VARRAY from pl/sql oracle 9i
Goto Forum:
  


Current Time: Mon Sep 26 07:50:37 CDT 2016

Total time taken to generate the page: 0.08438 seconds