-
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
-
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.
-
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.
-
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.
-
Thanks Pinakin and Kris109. I guess cursor for loop will help me handle the duplicate output.
