Unable to execute a procedure

[balaji_p]

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.

[chrisrlong]

Huh?

And I thought I strung thoughts together :)

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

- Chris

[balaji_p]

[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.

[marist89]

I think what chrisrlong meant is "Post an example, not the entire procedure and explain again what you are trying to do."

[chrisrlong]

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