DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: REF Cursor error

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Question

    Hi all,
    i'm using the following procedure(REF Cursor) and functions,it's compiling well(without error in oralce),but when i call that procedure in Crystal report the following error is coming,
    " STATEMENT HANDLE NOT EXECUTED",
    error number:ORA 24338,
    this is the package:
    -----------------------
    CREATE OR REPLACE package pack_corpus
    as type TEST_TYPE IS REF CURSOR;
    END PACK_corpus;
    /


    this is the procedure:
    ----------------------------
    PROCEDURE PCG_MOVEMENT
    (
    T_CURSOR OUT PACK_CORPUS.TEST_TYPE,
    P_REPORT_TYPE VARCHAR2,
    P_ASONDATE DATE,
    P_PREV_DATE DATE
    )
    AS
    BEGIN
    IF P_REPORT_TYPE = 'U' THEN
    OPEN T_CURSOR FOR

    SELECT SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60) PRIORITY_CUST_1,
    SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60) PRIORITY_CUST_2,
    A.CUST_ID,
    A.FIRST_NAME,
    A.MIDDLE_NAME,
    A.LAST_NAME,
    GETCUSTUNITS(P_ASONDATE, A.CUST_ID) UNITS
    FROM CUSTOMER_MASTER A
    WHERE (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'PLATINUM' AND (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IN
    ('GOLD', 'SILVER') OR UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IS NULL))
    OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'GOLD' AND (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) = 'SILVER'
    OR UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IS NULL))
    OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'SILVER' AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IS NULL);
    END IF;
    IF P_REPORT_TYPE = 'D' THEN
    OPEN T_CURSOR FOR
    SELECT SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60) PRIORITY_CUST_1,
    SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60) PRIORITY_CUST_2,
    A.CUST_ID,
    A.FIRST_NAME,
    A.MIDDLE_NAME,
    A.LAST_NAME,
    GETCUSTUNITS(P_ASONDATE, A.CUST_ID) UNITS
    FROM CUSTOMER_MASTER A
    WHERE (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) IN ('PLATINUM', 'GOLD', 'SILVER')
    AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) IS NULL)
    OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) = 'PLATINUM'
    AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) IN ('GOLD', 'SILVER'))
    OR (UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60)))) = 'GOLD'
    AND UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = 'SILVER');
    END IF;
    IF P_REPORT_TYPE = 'S' THEN
    OPEN T_CURSOR FOR
    SELECT SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60) PRIORITY_CUST_1,
    SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60) PRIORITY_CUST_2,
    A.CUST_ID,
    A.FIRST_NAME,
    A.MIDDLE_NAME,
    A.LAST_NAME,
    GETCUSTUNITS(P_ASONDATE, A.CUST_ID) UNITS
    FROM CUSTOMER_MASTER A
    WHERE UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_ASONDATE))), 1, 60)))) = UPPER(LTRIM(RTRIM(SUBSTR(LTRIM(RTRIM(GETPRIORITYCUST(A.CUST_ID, P_PREV_DATE))), 1, 60))));
    END IF;
    END;


    these are the functions:
    ---------------------------
    1)
    FUNCTION GETAUMACNO
    (
    P_ASONDATE DATE,
    P_ACCOUNT_NUMBER VARCHAR2,
    P_FUND_cATEGORY VARCHAR2
    ) RETURN NUMBER AS
    M_CATEG_AUM NUMBER(20,2) := 0;
    M_NAV_VALUE NUMBER(20,2) := 0;
    M_DATE DATE;
    M_PUR NUMBER(20,4) := 0;
    M_RED NUMBER(20,4) := 0;
    M_FUND_OPTION FUND_OPTION_MASTER.FUND_OPTION%TYPE := NULL;
    BEGIN
    BEGIN
    SELECT SUM(UNITS_ALLOTTED) INTO M_PUR
    FROM PURCHASE_LOG A, V_FUND_OPTION B
    WHERE B.FUND_OPTION = A.FUND_OPTION
    AND A.ACCOUNT_NUMBER= P_ACCOUNT_NUMBER
    AND B.FUND_CATEGORY LIKE NVL(P_FUND_CATEGORY, '%')
    AND A.PURCHASE_ALLOTTED_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND P_ASONDATE;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;
    BEGIN
    SELECT SUM(UNITS_REDEEMED) INTO M_RED
    FROM REDEMPTION_LOG A, V_FUND_OPTION B
    WHERE B.FUND_OPTION = A.FUND_OPTION
    AND A.ACCOUNT_NUMBER= P_ACCOUNT_NUMBER
    AND B.FUND_CATEGORY LIKE NVL(P_FUND_CATEGORY, '%')
    AND A.REDEEM_EFFECTIVE_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND P_ASONDATE;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;

    BEGIN
    SELECT FUND_OPTION INTO M_FUND_OPTION
    FROM ACCOUNT_MASTER
    WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;

    BEGIN
    SELECT MAX(EFFECTIVE_DATE), NAV_VALUE INTO M_DATE, M_NAV_VALUE FROM NAV
    WHERE FUND_OPTION = M_FUND_OPTION
    AND EFFECTIVE_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND P_ASONDATE
    GROUP BY NAV_VALUE;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;
    M_CATEG_AUM := (M_PUR - M_RED) * M_NAV_VALUE;
    RETURN NVL(M_CATEG_AUM,0);
    END;

    2)
    FUNCTION GETCUSTUNITS
    (
    P_ASONDATE DATE,
    P_CUST_ID VARCHAR2
    ) RETURN NUMBER AS

    CURSOR C1 IS
    SELECT ACCOUNT_NUMBER FROM ACC_CUS_LINK
    WHERE CUST_ID = P_CUST_ID;

    M_UNITS NUMBER(20,2) := 0;
    M_PUR NUMBER(20,4) := 0;
    M_RED NUMBER(20,4) := 0;
    M_TOT_PUR NUMBER(20,4) := 0;
    M_TOT_RED NUMBER(20,4) := 0;
    M_ASONDATE DATE := NULL;

    BEGIN
    IF P_ASONDATE IS NULL THEN
    BEGIN
    SELECT MAX(BUSINESS_DATE) INTO M_ASONDATE
    FROM DATE_MASTER;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;
    ELSE
    M_ASONDATE := P_ASONDATE;
    END IF;

    FOR C1REC IN C1 LOOP
    M_PUR := 0;
    M_RED := 0;
    BEGIN
    SELECT SUM(UNITS_ALLOTTED) INTO M_PUR
    FROM PURCHASE_LOG
    WHERE ACCOUNT_NUMBER = C1REC.ACCOUNT_NUMBER
    AND PURCHASE_ALLOTTED_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND M_ASONDATE;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;
    BEGIN
    SELECT SUM(UNITS_REDEEMED) INTO M_RED
    FROM REDEMPTION_LOG
    WHERE ACCOUNT_NUMBER = C1REC.ACCOUNT_NUMBER
    AND REDEEM_EFFECTIVE_DATE BETWEEN to_date('01-JAN-1900','DD-MON-YYYY') AND M_ASONDATE;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;
    M_TOT_PUR := M_TOT_PUR + M_PUR;
    M_TOT_RED := M_TOT_RED + M_RED;
    END LOOP;

    M_UNITS := (M_TOT_PUR - M_TOT_RED);
    RETURN NVL(M_UNITS,0);
    END;

    3)
    UNCTION GETPRIORITYCUST
    (
    P_CUST_ID VARCHAR2,
    P_ASONDATE DATE
    )
    RETURN VARCHAR2 IS

    CURSOR C1 IS
    SELECT CODE, VALUE
    FROM PARAMETER
    WHERE UPPER(CODE) IN ('EQUITY','INCOME','CASH')
    AND UPPER(GROUP_ID)='PRIORITYWT';

    CURSOR C2 IS
    SELECT CODE,
    SUBSTR(VALUE, 1, (INSTR(VALUE, '-') - 1)) FROMVAL,
    SUBSTR(VALUE, (INSTR(VALUE, '-') + 1),
    (LENGTH(LTRIM(RTRIM(VALUE))) -
    LENGTH(LTRIM(RTRIM(SUBSTR(VALUE, 1, (INSTR(VALUE, '-') - 1))))))) TOVAL
    FROM PARAMETER WHERE UPPER(GROUP_ID) = 'PRIORITYCUST';

    CURSOR C3 IS
    SELECT ACCOUNT_NUMBER
    FROM ACC_CUS_LINK
    WHERE CUST_ID = P_CUST_ID
    AND HOLDING_STATUS = 'F';

    M_EQ_WT NUMBER :=0;
    M_IN_WT NUMBER :=0;
    M_CA_WT NUMBER :=0;
    M_PL_FROMVAL NUMBER :=0;
    M_PL_TOVAL NUMBER :=0;
    M_GL_FROMVAL NUMBER :=0;
    M_GL_TOVAL NUMBER :=0;
    M_CL_FROMVAL NUMBER :=0;
    M_CL_TOVAL NUMBER :=0;
    M_WT_EQ_AUM NUMBER :=0;
    M_WT_IN_AUM NUMBER :=0;
    M_WT_CA_AUM NUMBER :=0;
    M_EQ_AUM NUMBER :=0;
    M_IN_AUM NUMBER :=0;
    M_CA_AUM NUMBER :=0;
    M_TOT_EQ_AUM NUMBER :=0;
    M_TOT_IN_AUM NUMBER :=0;
    M_TOT_CA_AUM NUMBER :=0;
    M_PRIORITY_CUST VARCHAR2(30) := NULL;
    M_CURR_DATE DATE := NULL;
    M_ASONDATE DATE := NULL;

    BEGIN
    BEGIN
    SELECT MAX(BUSINESS_DATE) INTO M_CURR_DATE
    FROM DATE_MASTER;
    EXCEPTION WHEN OTHERS THEN
    NULL;
    END;

    IF P_ASONDATE IS NULL THEN
    M_ASONDATE := M_CURR_DATE;
    ELSE
    M_ASONDATE := P_ASONDATE;
    END IF;

    FOR I IN C1 LOOP
    IF UPPER(I.CODE)='EQUITY' then
    M_EQ_WT :=TO_NUMBER(I.VALUE);
    ELSIF UPPER(I.CODE)='INCOME' then
    M_IN_WT :=TO_NUMBER(I.VALUE);
    ELSIF UPPER(I.CODE)='CASH' then
    M_CA_WT :=TO_NUMBER(I.VALUE);
    END IF;
    END LOOP;

    FOR I IN C2 LOOP
    IF UPPER(I.CODE)='PLATINUM' THEN
    M_PL_FROMVAL := I.FROMVAL;
    M_PL_TOVAL := I.TOVAL;
    ELSIF UPPER(I.CODE)='GOLD' THEN
    M_GL_FROMVAL := I.FROMVAL;
    M_GL_TOVAL := I.TOVAL;
    ELSIF UPPER(I.CODE)='SILVER' THEN
    M_CL_FROMVAL := I.FROMVAL;
    M_CL_TOVAL := I.TOVAL;
    END IF;
    END LOOP;

    FOR I IN C3 LOOP
    M_EQ_AUM := 0;
    M_IN_AUM := 0;
    M_CA_AUM := 0;

    M_EQ_AUM := GETAUMACNO(M_ASONDATE, I.ACCOUNT_NUMBER, 'EQUITY');
    M_IN_AUM := GETAUMACNO(M_ASONDATE, I.ACCOUNT_NUMBER, 'INCOME');
    M_CA_AUM := GETAUMACNO(M_ASONDATE, I.ACCOUNT_NUMBER, 'CASH');

    M_TOT_EQ_AUM := M_TOT_EQ_AUM + M_EQ_AUM;
    M_TOT_IN_AUM := M_TOT_IN_AUM + M_IN_AUM;
    M_TOT_CA_AUM := M_TOT_CA_AUM + M_CA_AUM;
    END LOOP;

    IF M_EQ_WT > 0 THEN
    M_WT_EQ_AUM := (M_TOT_EQ_AUM * M_EQ_WT) / 10;
    END IF;

    IF M_IN_WT > 0 THEN
    M_WT_IN_AUM := (M_TOT_IN_AUM * M_IN_WT) / 10;
    END IF;

    IF M_CA_WT > 0 THEN
    M_WT_CA_AUM := (M_TOT_CA_AUM * M_CA_WT) / 10;
    END IF;

    IF (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) >= M_PL_FROMVAL AND
    (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) <= M_PL_TOVAL THEN
    M_PRIORITY_CUST := 'PLATINUM';
    END IF;

    IF (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) >= M_GL_FROMVAL AND
    (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) <= M_GL_TOVAL THEN
    M_PRIORITY_CUST := 'GOLD';
    END IF;

    IF (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) >= M_CL_FROMVAL AND
    (M_WT_EQ_AUM + M_WT_IN_AUM + M_WT_CA_AUM) <= M_CL_TOVAL THEN
    M_PRIORITY_CUST := 'SILVER';
    END IF;

    RETURN M_PRIORITY_CUST;
    END;

    kindly give me the solution.
    Cheers!
    OraKid.

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    ORA-24338 statement handle not executed

    Cause: A fetch was attempted before executing a statement handle.

    Action: Execute a statement and then fetch the data.

Posting Permissions

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


Click Here to Expand Forum to Full Width