Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Question Unanswered: Getting back ##### as output Value from a StoreProcedure

    Hi All,

    I have a stored procedure which gives 2 output values. They are of type NUMBER and another is of type VARCHAR2.

    For the value of the variable with VARCHAR2 as data type, I ##### as the results. This SP used to work. My suspicion is that I am getting a NEGATIVE value and that is what is causing trouble.

    Could anybody help me??

    Here is my SP:

    PROCEDURE SP_NEW_USERS (currentMonthUserCount OUT NUMBER,
    percentageChange OUT VARCHAR2) IS

    currentBeginDate varchar2(20);
    currentEndDate varchar2(20);
    previousBeginDate varchar2(20);
    previousEndDate varchar2(20);

    --currentMonthUserCount NUMBER;
    previousMonthUserCount NUMBER;
    --percentageChange varchar2(10);

    BEGIN

    SP_GETDATES(currentBeginDate, currentEndDate, previousBeginDate, previousEndDate);

    SELECT count(user_id) into currentMonthUserCount
    FROM oradba.up_user
    WHERE (date_created BETWEEN currentBeginDate AND currentEndDate) AND
    customertype IN ('GTM', 'FSM') AND
    TNCFLAG = 'Y';

    SELECT count(user_id) into previousMonthUserCount
    FROM oradba.up_user
    WHERE (date_created BETWEEN previousBeginDate AND previousEndDate) AND
    customertype IN ('GTM', 'FSM') AND
    TNCFLAG = 'Y';

    DBMS_OUTPUT.PUT_LINE(currentMonthUserCount);
    -- DBMS_OUTPUT.PUT_LINE(previousMonthUserCount);

    percentageChange := TO_CHAR(((currentMonthUserCount - previousMonthUserCount)/currentMonthUserCount) * 100, 99.99);


    DBMS_OUTPUT.PUT_LINE(percentageChange);

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    Null;
    WHEN OTHERS THEN
    Null;
    END SP_NEW_USERS;


    - Thanks, Srikanth

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    percentageChange := TO_CHAR(((currentMonthUserCount - previousMonthUserCount)/currentMonthUserCount) * 100, S999.99);
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2004
    Posts
    35
    When I changed from 99.99 to S999.99, I get the following error -

    PLS-00103: Encountered the symbol ".99" when expecting one of the following:

    . ( ) , * @ % & | = - + < / > at in is mod not range rem =>
    .. <an exponent (**)> <> or != or ~= >= <= <> and or lik

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I tried to write procedures like you did (using good old Scott's tables):

    PHP Code:
    CREATE OR REPLACE PROCEDURE brisime
      cur OUT NUMBER
    pct OUT VARCHAR2)
    IS
      prev NUMBER
    ;
    BEGIN
      SELECT COUNT
    (*) INTO cur FROM EMP;
      
    SELECT COUNT(*) INTO prev FROM DEPT;
      
    pct := TO_CHAR((cur prev) / cur 10099.99);
    END;

    DECLARE
      
    cur1 NUMBER;
      
    pct1 VARCHAR2(255);
    BEGIN
      brisime
    (cur1pct1);
      
    dbms_output.put_line(cur1);
      
    dbms_output.put_line(pct1);
    END;

    14
    64.29 
    I'd say that result of your calculation (percentageChange) is a number > 100. Oracle represents numbers it can't display using #'s. Look:

    PHP Code:
    SQLset numformat 99.99
    SQL
    select 100 from dual;

       
    100
    ------
    ###### 
    I'd suggest you to completely remove formatting:

    percentageChange := (currentMonthUserCount - previousMonthUserCount) / currentMonthUserCount * 100;

    and see the result. Ensure you don't use any kind of "set numformat" in your environment.
    Last edited by Littlefoot; 06-02-04 at 17:22.

  5. #5
    Join Date
    Feb 2004
    Posts
    35
    I see what you are saying..

    When I manually executed the query and checked the result my result is
    -xxxx.xx.

    So, I did a little test..

    select TO_CHAR(((1550 - 22776)/1550) * 100, 9999.99) from dual;

    I get a big negative number..

    So, I am confused.....

    - Thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Big? -1369,4193548387096774193548387097 %, you mean?

    Well, if that's what your queryes return as a value, it must be true. Perhaps there's something wrong with the logic itself.

    If you invert it:

    percentageChange := (previousMonthUserCount - currentMonthUserCount) / previousMonthUserCount * 100;

    you'll get 93.19 % (which is more likely to be a correct result).

  7. #7
    Join Date
    Feb 2004
    Posts
    35
    I executed the SQL just now and I get -1369.42..

    In the sample SP that you have sent, how is it handled if you get a result like
    -xxxx.xx

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Like this:
    PHP Code:
    CREATE OR REPLACE PROCEDURE brisime
      
    (cur OUT NUMBERpct OUT VARCHAR2)
    IS
      prev NUMBER
    ;
    BEGIN
      cur 
    := 1550;
      
    prev := 22776;
      
    pct := (cur prev) / cur 100;
    END;

    DECLARE
      
    cur1 NUMBER;
      
    pct1 VARCHAR2(255);
    BEGIN
      brisime
    (cur1pct1);
      
    dbms_output.put_line(cur1);
      
    dbms_output.put_line(pct1);
    END;
     
    1550
    -1369.419354838709677419354838709677419355 

  9. #9
    Join Date
    Feb 2004
    Posts
    35
    Got it..Thanks. You know what.. I get the result -xxxx.xx

    PROCEDURE brisime
    IS
    prev NUMBER;
    cur NUMBER;
    pct VARCHAR2(255);
    BEGIN
    cur := 1550;
    prev := 22776;
    pct := TO_CHAR((cur - prev) / cur * 100, 9999.99);

    DBMS_OUTPUT.PUT_LINE(pct);
    END brisime;

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK. Once again (before going to bed): did you invert the formula?

    Try
    percentage = (PREVIOUS - CURRENT) / PREVIOUS * 100 = 93.19

    instead of
    percentage = (CURRENT - PREVIOUS) / CURRENT * 100 = -1369.42

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    I'd say
    percentage = (CURRENT - PREVIOUS) / PREVIOUS * 100 = -93.19

    If the previous value was 22776 and current value is 1550, the value has changed -93.19 percent.

Posting Permissions

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