Hi,
I have below error when I called procedure which has ref cursor.
Error -> ORA-24338: statement handle not executed
below is my sp.
CREATE OR REPLACE PROCEDURE DRFSMS.WS_UID_INTERNAL_COMMENTS(vUSER_ID IN VARCHAR2, vCOMMENT_ID IN NUMBER, vINFO_SUBJECT IN VARCHAR2, vCOMMENTS IN LONG,vPARCEL_ID IN VARCHAR2,
IN NUMBER, vTYPE IN VARCHAR2, vRESULT OUT NUMBER, VR_PARCEL OUT SYS_REFCURSOR)
VARCHAR2(10);
NUMBER;
NUMBER;
VARCHAR2(50);
NUMBER;
NUMBER;
Varchar2(3);
NUMBER;
LONG;
EXCEPTION;
User_type, ws_User.User_Type_ID, Decode(Sales_Org, 2010,'DHK',2020,'ENY',2030,'DRF') Into vUser_Type, vUser_Type_ID, lsCompany_Code
From ws_User, ws_User_Profile
Where ws_User.User_ID = vUSER_ID AND ws_User.User_type_Id = ws_User_Profile.User_Type_ID;
USER_TYPE INTO vUSER_TYPE FROM WS_USER_PROFILE WHERE USER_TYPE_ID =
(SELECT USER_TYPE_ID FROM WS_USER WHERE USER_ID = vUSER_ID);
INSTR(vUSER_TYPE,'ADMIN') INTO vADMIN FROM DUAL;
--Insert COmments
IF vTYPE='I' THEN
SELECT CASE WHEN MAX(INFO_ID)+1 >1 THEN MAX(INFO_ID)+1 ELSE 1 END INTO vINFO_ID FROM WS_USER_INFO;
INSERT INTO WS_USER_INFO(USER_ID, INFO_ID,INFO_SUBJECT,INFO,SUB_INFO,INFO_TYPE_ID,
,ENTERED_DATE,ENTERED_BY,COMPANY_CODE)
VALUES (vUSER_ID, vINFO_ID,vINFO_SUBJECT,vCOMMENTS,vPARCEL_ID,vINFO_TYPE_ID,1,SYSDATE,vUSER_ID, lsCompany_Code);
:= 1;
END IF;
--Update Comments
IF vTYPE='U' THEN
IF vUSER_TYPE_ID <> 1 THEN
SELECT COUNT(*) INTO vCount FROM WS_USER_INFO
WHERE INFO_ID = vCOMMENT_ID
AND USER_ID = vUSER_ID ;
IF NVL(vCOUNT,0) = 0 THEN
RAISE Not_Authorised;
END IF;
END IF;
UPDATE WS_USER_INFO SET
= vCOMMENTS,
= vUSER_ID,
= SYSDATE
WHERE SUB_INFO = vPARCEL_ID
AND INFO_ID = vCOMMENT_ID;
:= 1;
END IF;
IF vTYPE='D' THEN
IF vUSER_TYPE_ID <> 1 THEN
SELECT COUNT(*) INTO vCount FROM WS_USER_INFO
WHERE INFO_ID = vCOMMENT_ID
AND USER_ID = vUSER_ID ;
IF NVL(vCOUNT,0) = 0 THEN
RAISE Not_Authorised;
END IF;
END IF;
UPDATE WS_USER_INFO SET
= 1,
= vUSER_ID,
= SYSDATE
WHERE SUB_INFO = vPARCEL_ID
AND INFO_ID = vCOMMENT_ID;
:= 1;
END IF;
:= 'SELECT A.COMPANY_CODE, C.User_Name, B.Info, B.User_Id, B.INFO_ID, Decode(B.Updated_Date,NULL,B.Entered_Date,B.Updated_Date) as "Date"';
:= SQLSTR||' FROM WS_PARCEL A,WS_USER_INFO B, WS_USER C ';
:= SQLSTR||'WHERE A.PARCEL_ID = B.SUB_INFO AND B.INFO_TYPE_ID = 6 ';
:= SQLSTR||'AND B.USER_ID = C.USER_ID ';
:= SQLSTR||'AND (B.Is_Deleted is null or B.Is_Deleted = ''0'')';
:= SQLSTR||' AND B.Is_Active =''1''';
:= SQLSTR||' AND PARCEL_ID ='''||vPARCEL_ID||'''';
:= SQLSTR||' Order by Decode(B.Updated_Date,NULL,B.Entered_Date,B.Updated_Date)' ;
into temp_sql values(sqlstr);
VR_PARCEL FOR SQLSTR;
WHEN Not_Authorised THEN
:= -2;
WHEN NO_DATA_FOUND THEN
:= -1;
WHEN OTHERS THEN
:= 0;
WS_UID_INTERNAL_COMMENTS;
can anybody help me to get solutions pls.
any solution highly appreciated.
Siddik