Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: Oracl PL/SQL Question

    Hi I am new to Oracle Pl/SQL . I wrote a small pl.sql procedure to which i am passing 5 parameters some of which are string parameters
    in the pl/sql procedure I have only a select query which uses these parameter and retrieves a sum.
    Problem is when I run this SQL at SQL prompt I get a correct value but when I run through Procedure it gives me wrong value .
    Can any tell me where I am doing wrong?
    Thanks a lot

  2. #2
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    code

    Hi

    There is no way anyone can tell what's going wrong, please provide the code and the select statement.

    Good luck

    Remi

    http://askremi.ora-0000.com
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  3. #3
    Join Date
    Jan 2003
    Posts
    4

    Re: code

    create or replace PROCEDURE PROC_REP_AIR_SUM_REV_CAL
    (
    p_GROUP_ID IN GROUP_FACT.GROUP_ID%TYPE,
    p_ALC_ID IN ALC_FACT.ALC_ID%TYPE,
    p_RANK IN NUMBER,
    p_CURR_CODE IN REP_PROC_REF.CURR_CODE%TYPE,
    p_COUNTRY IN REP_PROC_REF.COUNTRY%TYPE,
    p_BEGIN_MONTH IN NUMBER,
    p_END_MONTH IN NUMBER,
    p_YEAR IN NUMBER,
    p_CALCULATED_VALUE OUT REP_AIR_SUMMARY.TOT_BASE_TKT_VAL%TYPE
    )
    IS
    v_START_DATE TKT_AGG_CLASS.COLLECT_DATE%TYPE;
    v_END_DATE TKT_AGG_CLASS.COLLECT_DATE%TYPE;
    vBMonthYear VARCHAR2(15);
    vEMonthYear VARCHAR2(15);
    vSqlStr VARCHAR2(200);
    BEGIN
    vBMonthYear := TO_CHAR(p_BEGIN_MONTH)||'/'||TO_CHAR(p_YEAR);
    vEMonthYear := TO_CHAR(p_END_MONTH)||'/'||TO_CHAR(p_YEAR);
    p_CALCULATED_VALUE :=0;
    SELECT TRUNC(TO_DATE(vBMonthYear,'MM/YYYY'),'MONTH') ,
    LAST_DAY(TO_DATE(vEMonthYear,'MM/YYYY'))
    INTO v_START_DATE, v_END_DATE
    FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('----------------------------------START');
    DBMS_OUTPUT.PUT_LINE('v_START_DATE =' || v_START_DATE);
    DBMS_OUTPUT.PUT_LINE('v_END_DATE =' || v_END_DATE);
    DBMS_OUTPUT.PUT_LINE('p_GROUP_ID =' || p_GROUP_ID);
    DBMS_OUTPUT.PUT_LINE('p_ALC_ID =' || p_ALC_ID);
    DBMS_OUTPUT.PUT_LINE('p_CURR_CODE =' || p_CURR_CODE);
    DBMS_OUTPUT.PUT_LINE('p_COUNTRY =' || p_COUNTRY);
    DBMS_OUTPUT.PUT_LINE('p_BEGIN_MONTH =' || p_BEGIN_MONTH);
    DBMS_OUTPUT.PUT_LINE('p_COUNTRY =' || p_COUNTRY);
    DBMS_OUTPUT.PUT_LINE('p_END_MONTH =' || p_END_MONTH);
    DBMS_OUTPUT.PUT_LINE('p_YEAR =' || p_YEAR);
    DBMS_OUTPUT.PUT_LINE('----------------------------------ENDING');

    SELECT SUM(TOTVALUE) INTO p_CALCULATED_VALUE FROM
    ( SELECT ALC_ID,TOTVALUE, ROWNUM FROM (
    SELECT TKT_AGG_CLASS.ALC_ID AS ALC_ID,
    sum(TKT_AGG_CLASS.TOT_BASE_TKT_VAL) AS REVENUE,
    TKT_AGG_CLASS.OD_DPTR_DATE,
    CURRENCY_CONV.EXCHNG_RATE AS ERATE,
    (sum(TKT_AGG_CLASS.TOT_BASE_TKT_VAL) * CURRENCY_CONV.EXCHNG_RATE) AS TOTVALUE
    FROM
    TA_GROUP_LNK,
    TKT_AGG_CLASS,
    CURRENCY_CONV
    WHERE TA_GROUP_LNK.GROUP_ID = p_GROUP_ID
    AND TKT_AGG_CLASS.ALC_ID = p_ALC_ID
    AND TA_GROUP_LNK.TA_ID = TKT_AGG_CLASS.TA_ID
    AND TKT_AGG_CLASS.OD_DPTR_DATE >= v_START_DATE
    AND TKT_AGG_CLASS.OD_DPTR_DATE <= v_END_DATE
    AND TKT_AGG_CLASS.OD_DPTR_DATE = CURRENCY_CONV.COLLECT_DATE
    AND CURRENCY_CONV.CURR_CODE = p_CURR_CODE
    AND CURRENCY_CONV.COUNTRY = p_COUNTRY
    GROUP BY
    TKT_AGG_CLASS.ALC_ID, TKT_AGG_CLASS.OD_DPTR_DATE,CURRENCY_CONV.EXCHNG_RA TE
    ORDER BY REVENUE DESC
    ) WHERE ROWNUM <= p_RANK );

    DBMS_OUTPUT.PUT_LINE('TOTAL VALUE IN ' || p_CURR_CODE || ' IS '|| p_CALCULATED_VALUE );
    END PROC_REP_AIR_SUM_REV_CAL ;



    SQL is


    SELECT SUM(TOTVALUE) FROM
    ( SELECT ALC_ID,TOTVALUE, ROWNUM FROM (
    SELECT TKT_AGG_CLASS.ALC_ID AS ALC_ID,
    sum(TKT_AGG_CLASS.TOT_BASE_TKT_VAL) AS REVENUE,
    TKT_AGG_CLASS.OD_DPTR_DATE,
    CURRENCY_CONV.EXCHNG_RATE AS ERATE,
    (sum(TKT_AGG_CLASS.TOT_BASE_TKT_VAL) * CURRENCY_CONV.EXCHNG_RATE) AS TOTVALUE
    FROM
    TA_GROUP_LNK,
    TKT_AGG_CLASS,
    CURRENCY_CONV
    WHERE TA_GROUP_LNK.GROUP_ID = 359
    AND TKT_AGG_CLASS.ALC_ID = 1
    AND TA_GROUP_LNK.TA_ID = TKT_AGG_CLASS.TA_ID
    AND TKT_AGG_CLASS.OD_DPTR_DATE >= '01-Jan-02'
    AND TKT_AGG_CLASS.OD_DPTR_DATE <= '31-JAN-02'
    AND TKT_AGG_CLASS.OD_DPTR_DATE = CURRENCY_CONV.COLLECT_DATE
    AND CURRENCY_CONV.CURR_CODE = 'CAD'
    AND CURRENCY_CONV.COUNTRY = 'CANADA'
    GROUP BY
    TKT_AGG_CLASS.ALC_ID, TKT_AGG_CLASS.OD_DPTR_DATE,CURRENCY_CONV.EXCHNG_RA TE
    ORDER BY REVENUE DESC
    ) WHERE ROWNUM <= 100);

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: code

    Is TKT_AGG_CLASS.OD_DPTR_DATE a DATE or a VARCHAR2?

    If it is VARCHAR2 I would expect problems - it SHOULD be a DATE.

  5. #5
    Join Date
    Jan 2003
    Posts
    4

    Re: code

    It is a Date field ..
    Vedbharati


    Originally posted by andrewst
    Is TKT_AGG_CLASS.OD_DPTR_DATE a DATE or a VARCHAR2?

    If it is VARCHAR2 I would expect problems - it SHOULD be a DATE.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: code

    Originally posted by VEDBHARATHI
    It is a Date field ..
    Vedbharati
    Then I can't really see where the problem comes from. Some suggestions:

    1) In SQL, you should change the date checks to:

    AND TKT_AGG_CLASS.OD_DPTR_DATE >= TO_DATE('01-JAN-2002','DD-MON-YYYY')
    AND TKT_AGG_CLASS.OD_DPTR_DATE <= TO_DATE('31-JAN-2002','DD-MON-YYYY')

    to be sure you get the right DATE values.

    2) Try changing the query from a SELECT SUM(...) into SELECT pk_col(s) and then you will see which records are appearing in one query but not the other.

  7. #7
    Join Date
    Jan 2003
    Posts
    4

    Re: code

    I tried to print the cols . it is giving me the values which I am passing to the procedure like group id 359 and also rownum as 359.
    What should I do now ?
    SQL> declare
    2 vGROUP_ID number :=359;
    3 vALC_ID number:=1;
    4 vRANK number :=100;
    5 vCURR_CODE varchar2(3) :='CAD';
    6 vCOUNTRY varchar2(15) := 'CANADA';
    7 vBEGIN_MONTH NUMBER := 1;
    8 vEND_MONTH number:= 3;
    9 vYEAR NUMBER := 2002;
    10 vCALCULATED_VALUE REP_AIR_SUMMARY.TOT_BASE_TKT_VAL%TYPE ;
    11 begin
    12
    13 PROC_REP_AIR_SUM_REV_CAL(vGROUP_ID,vALC_ID,vRANK ,vCURR_CODE,vCOUNTRY ,vBEGIN_MONTH , vEND_MON
    TH , vYEAR , vCALCULATED_VALUE);
    14 DBMS_OUTPUT.PUT_LINE('TOTAL VALUE IN ' || vCURR_CODE || ' IS '|| vCALCULATED_VALUE);
    15 end;
    16 /
    ----------------------------------START
    v_START_DATE =01-JAN-02
    v_END_DATE =31-MAR-02
    p_GROUP_ID =359
    p_ALC_ID =1
    p_CURR_CODE =CAD
    p_COUNTRY =CANADA
    p_BEGIN_MONTH =1
    p_COUNTRY =CANADA
    p_END_MONTH =3
    p_YEAR =2002
    ----------------------------------ENDING
    VC1.ALC_ID IS 1
    VC1.TOTVALUE is 359
    VC1.ROWNUM is 359
    VC1.ALC_ID IS 1
    VC1.TOTVALUE is 359
    VC1.ROWNUM is 359
    TOTAL VALUE IN CAD IS 0

    PL/SQL procedure successfully completed.

    Please let me know as soon as possible
    Thanks
    vedbharati
    Originally posted by andrewst
    Then I can't really see where the problem comes from. Some suggestions:

    1) In SQL, you should change the date checks to:

    AND TKT_AGG_CLASS.OD_DPTR_DATE >= TO_DATE('01-JAN-2002','DD-MON-YYYY')
    AND TKT_AGG_CLASS.OD_DPTR_DATE <= TO_DATE('31-JAN-2002','DD-MON-YYYY')

    to be sure you get the right DATE values.

    2) Try changing the query from a SELECT SUM(...) into SELECT pk_col(s) and then you will see which records are appearing in one query but not the other.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: code

    I was suggesting not that you print the PARAMETERS to your query, but that you modify the query to return all the selected RECORDS rather than just a SUM, and then compare which records you get with each method. You may then be able to spot what it is about a record that makes it appear in one query and not the other.

  9. #9
    Join Date
    Sep 2003
    Posts
    2

    Angry Re: Oracl PL/SQL Question

    This is test!

Posting Permissions

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