Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2015
    Posts
    2

    Unanswered: ORA-24338: statement handle not executed

    hello i am a beginner.I have converted stored proc with the help of sql developer and when i am using this in my app ORA-24338: statement handle not executed error.Same SP of sql server worked fine in Asp.net web app so i guess nothing is wrong with my front end.Below is my stored procedure.
    NOTE: 1) I am using vb.net in Visual studio 2008 and Oracle 11gR1 as back end
    2) tables starting with tt_ were temporary but i converted them back to standard tables.
    Code:
    CREATE OR REPLACE PROCEDURE TEST.sp_ProviderGroupDetailsMasterAdd(
        iv_ProviderGroupNo IN NUMBER,
        v_GroupName IN VARCHAR2,
        v_HOAddress IN VARCHAR2,
        v_Country IN NUMBER,
        v_City IN NUMBER,
        v_PhoneNo IN VARCHAR2,
        v_FaxNo IN VARCHAR2,
        v_WebSite IN VARCHAR2,
        v_TRANS_DATE IN DATE
        v_USERID IN VARCHAR2
        v_Ccomm IN CLOB,
        v_sMode IN NUMBER,
        v_Trans_Type IN VARCHAR2,
        v_TransId IN NUMBER DEFAULT NULL,
        cv_1 OUT SYS_REFCURSOR,
        cv_2 OUT SYS_REFCURSOR,
        cv_3 OUT SYS_REFCURSOR)
    AS
      v_ProviderGroupNo NUMBER(10,0):=iv_ProviderGroupNo;
      v_sys_error NUMBER := 0;
       v_ContNo NUMBER(10,0);
              v_ContactPerson VARCHAR2(50);
              v_Designation VARCHAR2(100);
              v_ContactType VARCHAR2(50);
              v_ContactNo VARCHAR2(50);
      XMLTYPE SYS.XMLTYPE;
    BEGIN
      utils.incrementTrancount;
      BEGIN
        DECLARE
          v_DecNo NUMBER(10,0);
          v_Trans_No NUMBER(10,0);
          v_Status VARCHAR2(30);
          v_Trans_Id NUMBER(19,0);
          v_Group_Code NUMBER(10,0);
          v_IsPolicyActive NUMBER(1,0);
          CURSOR CCur_CD  IS SELECT ContNo,ContactPerson,Designation,ContactType, ContactNo FROM tt_tblProviderGroupContactD;
        BEGIN
          SELECT dec_status  INTO v_Status  FROM tblDecisions WHERE Dec_No=(SELECT MAX(Dec_No) FROM tblDecisions WHERE Trans_code= NVL(v_ProviderGroupNo, Trans_code) AND Trans_no=15)
          AND Trans_code = NVL(v_ProviderGroupNo, Trans_code) AND Trans_no=15;
          SELECT IsContractActive INTO v_IsPolicyActive FROM tblProviderGroupDetailsMaster WHERE Audit_ProviderGroupNo=v_ProviderGroupNo;
          IF v_Trans_Type = 'Addition' THEN
            BEGIN
              SELECT NVL(MAX(Trans_ID),150000) + 1 INTO v_Trans_Id FROM tblDecisions WHERE Trans_no = 15;
              SELECT NVL(MAX(ProviderGroupNo) , 0) + 1 INTO v_ProviderGroupNo FROM tblProviderGroupDetailsMaster_ ;
              SELECT NVL(MAX(Trans_No),0) + 1 INTO v_Trans_No   FROM tblProviderGroupDetailsMaster_ WHERE ProviderGroupNo = v_ProviderGroupNo;
              SELECT NVL(MAX(Dec_No),0) + 1 INTO v_DecNo FROM tblDecisions  WHERE Trans_code = v_ProviderGroupNo AND Trans_no     = 15;
              INSERT INTO tblProviderGroupDetailsMaster_  (ProviderGroupNo,GroupName,HOAddress,Country,City,PhoneNo,FaxNo,WebSite,UserId,TRANS_DATE,STATUS,Trans_Type,Trans_No,Trans_ID) VALUES (v_ProviderGroupNo,v_GroupName,v_HOAddress,v_Country,v_City,v_PhoneNo,v_FaxNo,v_WebSite,v_USERID,v_TRANS_DATE,'APPROVED',v_Trans_Type,v_Trans_No,v_Trans_ID);
    INSERT INTO tblDecisions  (Dec_No,Trans_no,Trans_code,Dec_Status,Trans_Type,Trans_ID,Trans_Name) VALUES (v_DecNo,15,v_ProviderGroupNo,'PENDING',v_Trans_Type,v_Trans_ID,v_GroupName);
     v_Group_Code := v_ProviderGroupNo ;
    INSERT INTO tblProviderGroupDetailsMaster (ProviderGroupNo,GroupName,HOAddress,Country,City,PhoneNo,FaxNo,WebSite,TRANS_DATE,USERID,Audit_ProviderGroupNo) 
    SELECT v_Group_Code ,GroupName,HOAddress,Country,City,PhoneNo,FaxNo,WebSite,v_trans_Date,v_UserId,ProviderGroupNo FROM tblProviderGroupDetailsMaster_  WHERE ProviderGroupNo = v_ProviderGroupNo);
    INSERT INTO tblProviderGroupContactDet_Au (ProviderGroupNo,ContNo,ContactPerson,Designation,ContactType,ContactNo, UserId,Trans_Date)
    (SELECT v_Group_Code,ContNo,ContactPerson,Designation,ContactType,ContactNo,v_UserId,v_trans_Date  FROM tblProviderGroupContactDet_Au  WHERE ProviderGroupNo = v_ProviderGroupNo);
      END;
     ELSE
    IF ( v_IsPolicyActive=0 OR ( v_Trans_Type = 'Updation' AND ( v_Status IS NULL OR v_Status = 'PENDING' ) ) ) THEN
      BEGIN
         v_Trans_Id:=v_TransId ;
         SELECT Trans_No  INTO v_Trans_No  FROM tblProviderGroupDetailsMaster_  WHERE ProviderGroupNo = v_ProviderGroupNo  AND Trans_ID = v_Trans_Id;
         SELECT ProviderGroupNo  INTO v_Group_Code  FROM tblProviderGroupDetailsMaster WHERE Audit_ProviderGroupNo = v_ProviderGroupNo;
         UPDATE tblProviderGroupDetailsMaster_
         SET GroupName= v_GroupName, HOAddress= v_HOAddress, Country= v_Country,City= v_City, PhoneNo=v_PhoneNo, FaxNo = v_FaxNo,WebSite=     v_WebSite,USERID= v_USERID,TRANS_DATE= v_TRANS_DATE,Trans_No= v_Trans_No
         WHERE ProviderGroupNo = v_ProviderGroupNo AND Trans_ID = v_TransId;
         UPDATE tblProviderGroupDetailsMaster SET GroupName= v_GroupName,HOAddress= v_HOAddress,Country= v_Country, City= v_City,PhoneNo= v_PhoneNo,FaxNo= v_FaxNo,WebSite= v_WebSite,USERID= v_USERID, TRANS_DATE=v_TRANS_DATE
                  WHERE ProviderGroupNo = v_Group_Code;
                UPDATE tblDecisions SET Trans_Name = v_GroupName WHERE Trans_ID = v_TransId;
                DELETE tblProviderGroupContactDet_Au
                WHERE ProviderGroupNo = v_ProviderGroupNo
                AND Trans_No          = v_Trans_No; 
                OPEN cv_1 FOR SELECT UTILS.CONVERT_TO_VARCHAR2(v_ProviderGroupNo,10) || '-' || UTILS.CONVERT_TO_VARCHAR2(v_Trans_Id,15) Trans_Id FROM DUAL ;
              END;
            ELSE
              IF ( v_IsPolicyActive = 1 OR ( v_Trans_Type = 'Updation' AND v_Status = 'APPROVED' ) ) THEN
                BEGIN
                  SELECT NVL(MAX(Trans_No) 0) + 1 INTO v_Trans_No  FROM tblProviderGroupDetailsMaster_  WHERE ProviderGroupNo = v_ProviderGroupNo; 
                   SELECT NVL(MAX(Dec_No),0) + 1 INTO v_DecNo  FROM tblDecisions  WHERE Trans_code = v_ProviderGroupNo AND Trans_no=15;
                  SELECT NVL(MAX(Trans_ID),150000) + 1 INTO v_Trans_Id  FROM tblDecisions  WHERE Trans_no=15;
                   INSERT INTO tblProviderGroupDetailsMaster_  (ProviderGroupNo,GroupName,HOAddress,Country,City,PhoneNo,FaxNo,WebSite,UserId,TRANS_DATE,Trans_Type,Trans_No,Trans_ID)
                    VALUES (v_ProviderGroupNo,v_GroupName,v_HOAddress,v_Country,v_City,v_PhoneNo,v_FaxNo,v_WebSite,v_USERID,v_TRANS_DATE,v_Trans_Type,v_Trans_No,v_Trans_ID);
                       INSERT INTO tblDecisions  (Dec_No,Trans_no,Trans_code,Dec_Status,Trans_Type,Trans_ID,Trans_Name) VALUES (v_DecNo,15,v_ProviderGroupNo, 'PENDING',v_Trans_Type,v_Trans_ID,v_GroupName);
                END;
              ELSE
                IF v_Trans_Type = 'Deletion' THEN
                  BEGIN
                    DELETE tblProviderGroupDetailsMaster_ WHERE ProviderGroupNo = v_ProviderGroupNo  AND Trans_ID=v_TransId;
                     DELETE tblProviderGroupContactDet_Au WHERE ProviderGroupNo = v_ProviderGroupNo; 
                    DELETE tblDecisions  WHERE Trans_code = v_ProviderGroupNo  AND Trans_no= 15 AND Trans_ID=v_TransId;
                  END;
                END IF;
              END IF;
            END IF;
          END IF;
          utils.incrementTrancount;
          IF v_Trans_Type <> 'Deletion' THEN
            BEGIN
             XMLTYPE := SYS.XMLTYPE.CREATEXML (v_Ccomm);
              FOR DD
             IN (SELECT D.EXTRACT ('//Ccomm/ContNo/text() ').GETSTRINGVAL ()
                        AS ContNo,
                     D.EXTRACT ('//Ccomm/ContactPerson/text() ').GETSTRINGVAL ()
                        AS ContactPerson,
                     D.EXTRACT ('//Ccomm/Designation/text() '). GETSTRINGVAL ()
                        AS Designation,
                     D.EXTRACT ('//Ccomm/ContactType/text() '). GETSTRINGVAL ()
                        AS ContactType,
                     D.EXTRACT ('//Ccomm/ContactNo/text() '). GETSTRINGVAL ()
                     AS ContactNo
                FROM TABLE (XMLSEQUENCE (XMLTYPE.EXTRACT ('//Root/Ccomm'))) D)
            LOOP
            INSERT INTO tt_tblProviderGroupContactD  (ContNo,ContactPerson,Designation,ContactType,ContactNo)  VALUES (DD.ContNo, DD.ContactPerson,DD.Designation,DD.ContactType,DD.ContactNo);
            END LOOP;
             END;
              OPEN CCur_CD;
              FETCH CCur_CD INTO v_ContNo,v_ContactPerson, v_Designation,v_ContactType,v_ContactNo;
             WHILE NOT utils.fetch_status(CCur_CD%FOUND) <> 0 
              LOOP
                BEGIN
                 INSERT INTO tblProviderGroupContactDet_Au (ProviderGroupNo,Trans_No,ContNo,ContactPerson,Designation,ContactType,ContactNo,UserId,Trans_Date)
                    VALUES  (v_ProviderGroupNo,v_Trans_No,v_ContNo,v_ContactPerson,v_Designation,v_ContactType,v_ContactNo,v_USERID,v_TRANS_DATE);
                  FETCH CCur_CD INTO v_ContNo,v_ContactPerson, v_Designation, v_ContactType,v_ContactNo;
                END;
              END LOOP;
              CLOSE CCur_CD;
          END IF;
          IF (v_Trans_Type = 'ADDITION' OR v_IsPolicyActive=0) THEN
            BEGIN
              DELETE tblProviderGroupContactDet_Au  WHERE ProviderGroupNo = v_ProviderGroupNo;
           INSERT INTO tblProviderGroupContactDet_Au (ProviderGroupNo,ContNo,ContactPerson,Designation,ContactType,ContactNo,UserId,Trans_Date) 
            (SELECT v_Group_Code,ContNo,ContactPerson,Designation,ContactType,ContactNo,v_UserId,v_Trans_Date FROM tblProviderGroupContactDet_Au WHERE Trans_No= v_Trans_No  AND ProviderGroupNo = v_ProviderGroupNo);
          END;
          END IF;
          OPEN cv_2 FOR SELECT UTILS.CONVERT_TO_VARCHAR2
          (v_ProviderGroupNo,10)
          || '-' || UTILS.CONVERT_TO_VARCHAR2
          (v_Trans_Id,15)
          Trans_Id FROM DUAL ;
          IF ( v_sys_error <> 0 ) THEN
            ROLLBACK;
            utils.resetTrancount;
          END IF;
          v_sys_error := 0;
          utils.commit_transaction;
          utils.commit_transaction;
        END;
      EXCEPTION
      WHEN OTHERS THEN
        BEGIN
          IF utils.trancount > 0 THEN
            ROLLBACK;
            utils.resetTrancount;
          END IF;
          OPEN cv_3 FOR SELECT 'Operation Aborted' FROM DUAL ;
        END;
      END;
      v_sys_error := 0;
    EXCEPTION
    WHEN OTHERS THEN
      utils.handleerror(SQLCODE,SQLERRM);
    END;
    Last edited by gvee; 06-18-15 at 08:43. Reason: [code][/code] tags added.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm going to move this to the Oracle forum because, like you've said, the problem is likely to be there and not in the ASP.
    It's likely to get a better response there!

    If you disagree and want it moving back just ask
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2015
    Posts
    2
    Quote Originally Posted by gvee View Post
    I'm going to move this to the Oracle forum because, like you've said, the problem is likely to be there and not in the ASP.
    It's likely to get a better response there!

    If you disagree and want it moving back just ask


    yeah sure !! thanks for your help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •