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

Thread: Help! What is wrong?

  1. #1
    Join Date
    Mar 2001
    Posts
    77
    I have this function and I need following result, can anyone see what's wrong with this function? Also, how do I use DBMS_OUTPUT.PUT_LINE to debugg? Thanks!!

    CREATE OR REPLACE FUNCTION annual_comp2
    (V_SAL IN EMP.SAL%TYPE,
    V_COMM IN EMP.COMM%TYPE)
    RETURN NUMBER

    IS

    V_ANNSAL EMP.SAL%TYPE;

    BEGIN
    SELECT NVL(SAL,0) * 12 + NVL(COMM,0)
    INTO V_ANNsal
    FROM EMP
    WHERE SAL = V_SAL
    AND COMM = V_COMM;
    dbms_output.put_line ('give me ' || v_sal || 'and ' || v_comm); -- How do I generate any feedback with this?

    RETURN V_ANNSAL;

    END;
    /

    REQUIRE RESULT
    -------------------

    Annual Salary
    -------------
    60000
    34200
    29400
    35700
    16400
    19500
    18000
    11400
    15500
    36000
    9600
    36000
    13200
    15600

  2. #2
    Join Date
    Sep 2000
    Posts
    47
    Hi,

    Use "SET SERVEROUTPUT ON " to set the output ON. By default the value is OFF - disabled. Set this ON and you can see the output displayed.
    This is a function. So its better if you catch the returning value into some variable and display the returned value. Execute the function in following way from your SQL.

    DECLARE
    V_ANNSAL EMP.SAL%TYPE ;
    BEGIN
    V_ANNSAL := annual_comp2(999, 999) ;
    DBMS_OUTPUT.PUT_LINE('Returned Value Is : '|| v_annsal) ;
    END ;
    /

    Hope this helps.

    Regards,

    Pinakin.


  3. #3
    Join Date
    Mar 2001
    Posts
    77
    Thanks for the reply. However, I don't quite understand why I need to call the function in itself, eg:

    V_ANNSAL := annual_comp2(999, 999) ;

    Thanks.

  4. #4
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    511
    What pinakin talking about is an anonymous PL/SQL block you have to type in SQL*PLUS to test your function.


    Other problems with your function:

    If you want to display the annual salary the way you want, define a cursor. Use a cursor for loop to fetch into v_annsal and use

    dbms_output('give_me'||v_annsal);

    inside the loop to see your output.

  5. #5
    Join Date
    Mar 2001
    Posts
    77
    Thanks Pinakin and Kris109. I guess cursor for loop will help me handle the duplicate output.

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