Help! What is wrong?

[oraka6]

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

[Pinakin]

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.

[oraka6]

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.

[kris109]

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.

[oraka6]

Thanks Pinakin and Kris109. I guess cursor for loop will help me handle the duplicate output.