Unable to execute a procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Unable to execute a procedure

  1. #1
    Join Date
    Aug 2000
    Posts
    4

    Talking

    Hi Oracle fans,
    I am facing a typical problem. I am able to execute a single sql from a procedure in sql prompt with parameters hardcoded the query is executing fine, the same syntax i am unable to execute as a procedure / package, it is simply going and coming out in 1 sec and the debugging messages are displayed, but no dml statements are executing. can anybody solve this problem.
    Balaji.P

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Huh?

    And I thought I strung thoughts together :)

    Post the proc please, and explain the situation again - a little slower please :)

    - Chris

  3. #3
    Join Date
    Aug 2000
    Posts
    4
    [QUOTE][i]Originally posted by chrisrlong [/i]
    [B]Huh?

    And I thought I strung thoughts together :)

    Post the proc please, and explain the situation again - a little slower please :)

    - Chris [/B][/QUOTE]

    -------starting of the procedure
    PACKAGE BODY DELL_CA_ALLOC_VARIANCES
    IS
    PROCEDURE DELL_CA_ALLOC_VARIANCE (
    AIS_CCN IN C_CTSSK.CCN%TYPE,
    AID_PROC_DATE IN DATE
    )
    IS
    LS_CCN CCN.CCN%TYPE;
    LD_FISCAL_PROC_DATE DATE;
    LS_MESSAGE VARCHAR2(255);
    BEGIN
    SET TRANSACTION USE ROLLBACK SEGMENT R01; --FORCE THE LARGEST ROLLBACK SEGMENT.
    LS_CCN := NVL (AIS_CCN, 'BCC');
    LD_FISCAL_PROC_DATE := DELL_GLOVIA_FISCAL_YEAR.CURR_SATURDAY (NVL (AID_PROC_DATE, SYSDATE));

    SELECT 'STARTING OF THE PROCEDURE dell_ca_alloc_variances '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    DBMS_OUTPUT.PUT_LINE(LS_MESSAGE);
    --STEP 1-----
    -- STEP 1

    UPDATE C_CTSCM
    SET IPV = (SELECT C_CTVWK.IPV * C_CTSCM.SHIPPED_QTY / COMP_TOTALS.TOTAL_SHIPPED
    FROM C_CTVWK,
    (SELECT A.CCN, A.COMP_ITEM, A.COMP_REV,
    SUM( A.SHIPPED_QTY ) AS TOTAL_SHIPPED
    FROM C_CTSCM A, C_CTSSK
    WHERE A.CCN = LS_CCN
    AND A.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    AND A.CCN = C_CTSSK.CCN
    AND A.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND A.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND A.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND A.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    GROUP BY A.CCN, A.COMP_ITEM, A.COMP_REV
    ) COMP_TOTALS
    WHERE C_CTVWK.CCN = COMP_TOTALS.CCN
    AND C_CTVWK.COMP_ITEM = COMP_TOTALS.COMP_ITEM
    AND C_CTVWK.COMP_REV = COMP_TOTALS.COMP_REV
    AND C_CTVWK.CCN = C_CTSCM.CCN
    AND C_CTVWK.COMP_ITEM = C_CTSCM.COMP_ITEM
    AND C_CTVWK.COMP_REV = C_CTSCM.COMP_REV
    )
    WHERE CCN = LS_CCN
    AND C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    AND EXISTS(SELECT DISTRIBUTE_VARIANCES
    FROM C_CTSSK
    WHERE C_CTSCM.CCN = C_CTSSK.CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND C_CTSCM.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND C_CTSCM.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    )
    AND C_CTSCM.SHIPPED_QTY != 0;
    dbms_output.put_line('END OF STEP 1..'||LS_MESSAGE);
    UPDATE C_CTSCM
    SET PPV = ( SELECT C_CTVWK.PPV * C_CTSCM.SHIPPED_QTY / COMP_TOTALS.TOTAL_SHIPPED
    FROM C_CTVWK,
    (SELECT A.CCN, A.COMP_ITEM, A.COMP_REV,
    SUM( A.SHIPPED_QTY ) AS TOTAL_SHIPPED
    FROM C_CTSCM A, C_CTSSK
    WHERE A.CCN = LS_CCN
    AND A.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    AND A.CCN = C_CTSSK.CCN
    AND A.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND A.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND A.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND A.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    GROUP BY A.CCN, A.COMP_ITEM, A.COMP_REV
    ) COMP_TOTALS
    WHERE C_CTVWK.CCN = COMP_TOTALS.CCN
    AND C_CTVWK.COMP_ITEM = COMP_TOTALS.COMP_ITEM
    AND C_CTVWK.COMP_REV = COMP_TOTALS.COMP_REV
    AND C_CTVWK.CCN = C_CTSCM.CCN
    AND C_CTVWK.COMP_ITEM = C_CTSCM.COMP_ITEM
    AND C_CTVWK.COMP_REV = C_CTSCM.COMP_REV
    )
    WHERE CCN = LS_CCN
    AND C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    AND EXISTS(SELECT DISTRIBUTE_VARIANCES
    FROM C_CTSSK
    WHERE C_CTSCM.CCN = C_CTSSK.CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND C_CTSCM.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND C_CTSCM.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    )
    AND C_CTSCM.SHIPPED_QTY != 0;
    UPDATE C_CTSCM
    SET STD_REVAL = (SELECT C_CTVWK.STD_REVAL * C_CTSCM.SHIPPED_QTY / COMP_TOTALS.TOTAL_SHIPPED
    FROM C_CTVWK,
    (SELECT A.CCN, A.COMP_ITEM, A.COMP_REV,
    SUM( A.SHIPPED_QTY ) AS TOTAL_SHIPPED
    FROM C_CTSCM A, C_CTSSK
    WHERE A.CCN = LS_CCN
    AND A.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    AND A.CCN = C_CTSSK.CCN
    AND A.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND A.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND A.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND A.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    GROUP BY A.CCN, A.COMP_ITEM, A.COMP_REV
    ) COMP_TOTALS
    WHERE C_CTVWK.CCN = COMP_TOTALS.CCN
    AND C_CTVWK.COMP_ITEM = COMP_TOTALS.COMP_ITEM
    AND C_CTVWK.COMP_REV = COMP_TOTALS.COMP_REV
    AND C_CTVWK.CCN = C_CTSCM.CCN
    AND C_CTVWK.COMP_ITEM = C_CTSCM.COMP_ITEM
    AND C_CTVWK.COMP_REV = C_CTSCM.COMP_REV
    )
    WHERE CCN = LS_CCN
    AND C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    AND EXISTS( SELECT DISTRIBUTE_VARIANCES --ONLY UPDATE THE COMPONENTS TO DISTRIBUTE VARIANCES TO.
    FROM C_CTSSK
    WHERE C_CTSCM.CCN = C_CTSSK.CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND C_CTSCM.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND C_CTSCM.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    )
    AND C_CTSCM.SHIPPED_QTY != 0;
    SELECT 'STEP 3 OF 1 COMPLETED' ||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    -- STEP -4
    UPDATE C_CTVWK SET
    IPV_FLAG = 'I'
    WHERE EXISTS(SELECT C_CTSSK.C_SKU_NUM FROM C_CTSSK, C_CTSCM
    WHERE C_CTSSK.CCN = C_CTSCM.CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = C_CTSCM.C_FISCAL_PROC_DATE
    AND C_CTSSK.C_SALES_ORDER_NUM = C_CTSCM.C_SALES_ORDER_NUM
    AND C_CTSSK.C_TIE_NUM = C_CTSCM.C_TIE_NUM
    AND C_CTSSK.C_SKU_NUM = C_CTSCM.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    AND C_CTSCM.CCN = C_CTVWK.CCN
    AND C_CTSCM.COMP_ITEM = C_CTVWK.COMP_ITEM
    AND C_CTSCM.COMP_REV = C_CTVWK.COMP_REV
    AND C_CTSSK.CCN = LS_CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE)
    AND IPV_FLAG = 'T'
    AND CCN = LS_CCN
    AND FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE ;

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    UPDATE C_CTVWK SET
    PPV_FLAG = 'P'
    WHERE EXISTS(SELECT C_CTSSK.C_SKU_NUM FROM C_CTSSK, C_CTSCM
    WHERE C_CTSSK.CCN = C_CTSCM.CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = C_CTSCM.C_FISCAL_PROC_DATE
    AND C_CTSSK.C_SALES_ORDER_NUM = C_CTSCM.C_SALES_ORDER_NUM
    AND C_CTSSK.C_TIE_NUM = C_CTSCM.C_TIE_NUM
    AND C_CTSSK.C_SKU_NUM = C_CTSCM.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    AND C_CTSCM.CCN = C_CTVWK.CCN
    AND C_CTSCM.COMP_ITEM = C_CTVWK.COMP_ITEM
    AND C_CTSCM.COMP_REV = C_CTVWK.COMP_REV
    AND C_CTSSK.CCN = LS_CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE)
    AND IPV_FLAG = 'T'
    AND CCN = LS_CCN
    AND FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE ;

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    UPDATE C_CTVWK SET
    STD_REVAL_FLAG = 'P'
    WHERE EXISTS(SELECT C_CTSSK.C_SKU_NUM FROM C_CTSSK, C_CTSCM
    WHERE C_CTSSK.CCN = C_CTSCM.CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = C_CTSCM.C_FISCAL_PROC_DATE
    AND C_CTSSK.C_SALES_ORDER_NUM = C_CTSCM.C_SALES_ORDER_NUM
    AND C_CTSSK.C_TIE_NUM = C_CTSCM.C_TIE_NUM
    AND C_CTSSK.C_SKU_NUM = C_CTSCM.C_SKU_NUM
    AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
    AND C_CTSCM.CCN = C_CTVWK.CCN
    AND C_CTSCM.COMP_ITEM = C_CTVWK.COMP_ITEM
    AND C_CTSCM.COMP_REV = C_CTVWK.COMP_REV
    AND C_CTSSK.CCN = LS_CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE)
    AND IPV_FLAG = 'T'
    AND CCN = LS_CCN
    AND FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE ;

    SELECT 'STEP 4 OF 1 COMPLETED '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    COMMIT;
    -- STEP 2-------
    /* TOTAL ALL SHIPPED MOD(C_CTSMD) */
    UPDATE C_CTSMD SET
    IPV = (SELECT SUM(C_CTSCM.IPV) FROM C_CTSCM
    WHERE C_CTSCM.CCN = C_CTSMD.CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSMD.C_FISCAL_PROC_DATE
    AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSMD.C_SALES_ORDER_NUM
    AND C_CTSCM.C_TIE_NUM = C_CTSMD.C_TIE_NUM
    AND C_CTSCM.C_SKU_NUM = C_CTSMD.C_SKU_NUM
    AND C_CTSCM.C_MOD = C_CTSMD.C_MOD)
    WHERE C_CTSMD.CCN = LS_CCN
    AND C_CTSMD.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE;
    UPDATE C_CTSMD SET
    PPV = (SELECT SUM(C_CTSCM.IPV) FROM C_CTSCM
    WHERE C_CTSCM.CCN = C_CTSMD.CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSMD.C_FISCAL_PROC_DATE
    AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSMD.C_SALES_ORDER_NUM
    AND C_CTSCM.C_TIE_NUM = C_CTSMD.C_TIE_NUM
    AND C_CTSCM.C_SKU_NUM = C_CTSMD.C_SKU_NUM
    AND C_CTSCM.C_MOD = C_CTSMD.C_MOD)
    WHERE C_CTSMD.CCN = LS_CCN
    AND C_CTSMD.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE;
    UPDATE C_CTSMD SET
    STD_REVAL = (SELECT SUM(C_CTSCM.IPV) FROM C_CTSCM
    WHERE C_CTSCM.CCN = C_CTSMD.CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSMD.C_FISCAL_PROC_DATE
    AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSMD.C_SALES_ORDER_NUM
    AND C_CTSCM.C_TIE_NUM = C_CTSMD.C_TIE_NUM
    AND C_CTSCM.C_SKU_NUM = C_CTSMD.C_SKU_NUM
    AND C_CTSCM.C_MOD = C_CTSMD.C_MOD)
    WHERE C_CTSMD.CCN = LS_CCN
    AND C_CTSMD.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE;
    SELECT 'STEP TWO COMPLETED '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    -- STEP 3----------------
    /* TOTAL ALL SHIPPED SKU VARIANCE FIELDS(IPV, PPV, STD_REVAL)
    BY SUMMING THE RESPECTIVE VARIANCE FIELDS IN THE MOD TABLES */
    SELECT 'BEGINING OF THE STEP THREE '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    UPDATE C_CTSSK SET
    IPV = (SELECT SUM(C_CTSMD.IPV) FROM C_CTSMD
    WHERE C_CTSMD.CCN = C_CTSSK.CCN
    AND C_CTSMD.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND C_CTSMD.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND C_CTSMD.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND C_CTSMD.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.CCN = LS_CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE);
    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    UPDATE C_CTSSK SET
    PPV = (SELECT SUM(C_CTSMD.IPV) FROM C_CTSMD
    WHERE C_CTSMD.CCN = C_CTSSK.CCN
    AND C_CTSMD.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND C_CTSMD.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND C_CTSMD.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND C_CTSMD.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.CCN = LS_CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE);
    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    UPDATE C_CTSSK SET
    STD_REVAL = (SELECT SUM(C_CTSMD.IPV) FROM C_CTSMD
    WHERE C_CTSMD.CCN = C_CTSSK.CCN
    AND C_CTSMD.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
    AND C_CTSMD.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
    AND C_CTSMD.C_TIE_NUM = C_CTSSK.C_TIE_NUM
    AND C_CTSMD.C_SKU_NUM = C_CTSSK.C_SKU_NUM
    AND C_CTSSK.CCN = LS_CCN
    AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE);
    SELECT 'END OF STEP THREE '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    /* END OF STEP --3 ----------*/
    /* STEP --4 ---------
    UPDATE VARIANCE AUDIT TABLE BY ADDING THE RECORDS */
    SELECT 'STARTING OF STEPFOUR'|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    INSERT INTO C_CTVAU(CCN,
    C_VARIANCE_TYPE,
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    VARIANCE_ALLOCATED,
    FISCAL_PROC_DATE,
    CHANGE_OPER,
    LAST_CHANGE_DATE,
    LAST_CHANGE_TIME
    )
    SELECT CCN,
    'I',
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    SUM(C_CTSCM.IPV),
    C_FISCAL_PROC_DATE,
    'SYS',
    SYSDATE,
    TO_CHAR (SYSDATE, 'HH24MISS') FROM C_CTSCM
    WHERE C_CTSCM.IPV NOT IN(NULL, 0)
    AND C_CTSCM.CCN = LS_CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    GROUP BY CCN,
    'I',
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    C_FISCAL_PROC_DATE,
    'SYS',
    SYSDATE,
    TO_CHAR (SYSDATE, 'HH24MISS');

    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line('INSERT INTO C_CTVAU FOR PPV'||LS_MESSAGE);
    -- FOR PPV
    INSERT INTO C_CTVAU(CCN,
    C_VARIANCE_TYPE,
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    VARIANCE_ALLOCATED,
    FISCAL_PROC_DATE,
    CHANGE_OPER,
    LAST_CHANGE_DATE,
    LAST_CHANGE_TIME
    )
    SELECT CCN,
    'P',
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    SUM(C_CTSCM.PPV),
    C_FISCAL_PROC_DATE,
    'SYS',
    SYSDATE,
    TO_CHAR (SYSDATE, 'HH24MISS') FROM C_CTSCM
    WHERE C_CTSCM.PPV NOT IN(NULL, 0)
    AND C_CTSCM.CCN = LS_CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    GROUP BY CCN,
    'P',
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    C_FISCAL_PROC_DATE,
    'SYS',
    SYSDATE,
    TO_CHAR (SYSDATE, 'HH24MISS');
    SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line('INSERT INTO C_CTVAU FOR STD_REVAL '||LS_MESSAGE);
    -- FOR STD_REVAL
    INSERT INTO C_CTVAU(CCN,
    C_VARIANCE_TYPE,
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    VARIANCE_ALLOCATED,
    FISCAL_PROC_DATE,
    CHANGE_OPER,
    LAST_CHANGE_DATE,
    LAST_CHANGE_TIME
    )
    SELECT CCN,
    'S',
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    SUM(C_CTSCM.STD_REVAL),
    C_FISCAL_PROC_DATE,
    'SYS',
    SYSDATE,
    TO_CHAR (SYSDATE, 'HH24MISS') FROM C_CTSCM
    WHERE C_CTSCM.STD_REVAL NOT IN(NULL, 0)
    AND C_CTSCM.CCN = LS_CCN
    AND C_CTSCM.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
    GROUP BY CCN,
    'S',
    COMP_ITEM,
    COMP_REV,
    C_SKU_NUM,
    C_MOD,
    C_FISCAL_PROC_DATE,
    'SYS',
    SYSDATE,
    TO_CHAR (SYSDATE, 'HH24MISS');
    COMMIT;
    SELECT 'END POINT OF PROCEDURE'|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
    dbms_output.put_line(LS_MESSAGE);
    EXCEPTION
    WHEN OTHERS THEN
    LS_MESSAGE :=SUBSTR(SQLERRM,1,9);
    DBMS_OUTPUT.PUT_LINE(LS_MESSAGE);
    END; -- PROCEDURE STEPFOUR
    END; -- PACKAGE BODY DELL_CA_ALLOC_VARIANCES
    ----------------end of the procedure

    i am able to execute the sql queries and it is doing well in the sql prompt, when i hardcode the parameters in the procedure it is doing well, while i am passing the parameters to the procedure and it is not executing, simply displaying all the messages coded in the procedure.
    Balaji.P

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I think what chrisrlong meant is "Post an example, not the entire procedure and explain again what you are trying to do."
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yoiks!

    Guess that falls under the 'be careful what you ask for' category, eh?:)

    This may take a bit to sort through. I don't see anything obviously wrong with the proc, however.

    I might suggest adding LS_CCN and LD_FISCAL_PROC_DATE to your DBMS_OUTPUTs to verify that they actually have the values you expect.

    Oh, wait. What version of Oracle are you using?? There was a bug in 7.x. Actually, it was in the PL/SQL engine that 7.x used, although I don't know how they version that engine. Anyway, the problem was using variables in SQL statements. I never found the actual bug 'according to Oracle', but it definitely existed. The problem is hard to define, but here are the variables involved:

    - Non-dynamic SQL statement within PL/SQL
    - Variable used in SQL statement
    - SQL statement had 3 or more levels of 'nesting'
    -- You have UPDATE...SET X=(SELECT...FROM (SELECT...
    -- That is 3 levels of nesting (by my definition)

    The engine would 'lose its mind' with this kind of statement and return no results and no error, simply blow through the statement rather quickly, much as you are describing.

    My usual workarounds were:
    - put the 'variable' into a table and access it that way
    - re-write the statement to reduce the nesting levels
    - Turn it into dynamic SQL

    The advantage of doing dynamic SQL is that you don't have to re-think anything. Just remember to de-reference the variable when you build the string.

    SQL := "SELECT * FROM FRED WHERE COL1 = " || x

    Actually, I never tried it with binds, "SELECT * FROM FRED WHERE COL1 = :x" and then binding x. That *might* work as well. That would be more beneficial from the SGA's point of view. However, I would theorize that using a bind is what the PL/SQL engine is already to do with the statement.

    In other words, if you have SELECT * FROM FRED WHERE COL1 = local_variable, the way PL/SQL processes it is to turn it into SELECT * FROM FRED WHERE COL1 = :x, followed by a bind of x. So, it depends wherein the error lies as to whether using a bind would work or not. Unfortunately, I no longer have access to any 7.x databases to test this out.

    My, but that was quite the ramble. Try the dynamic SQL thing and let me know what happens.

    Hope this helps,

    - Chris


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