How can I test a function working or not in pl-sql?

Question

I learned the basics of writing a function and procedure in pl-sql but I don't know how to test if it's working , and if not How can I debug it . Any Ideas please . thanks all. Here is the function I'm trying to test it

CREATE OR REPLACE FUNCTION MIN_MAX_SAL RETURN NUMBER AS 
cursor emp_cur is select salary from employees ;
emp_sal number;
min_sal jobs.min_salary%type;
max_sal jobs.max_salary%type;
BEGIN
select min_salary , max_salary into min_sal , max_sal from jobs;
for emp_sal in emp_cur loop
if (emp_sal > max_sal or emp_sal < min_sal) then
return 0;
end loop;
RETURN 1;
END MIN_MAX_SAL;

Any help ?

score 11 · Answer 1 · answered May 22 '13 at 21:48

In general, like the question - how to run/test in SQL*Plus...

To test a function that takes scalar arguments and returns a scalar value:

SELECT function_name(parm1, parm2) FROM DUAL;

To test a procedure:

EXEC procedure_name(parm1, parm2)

If the procedure returns a REFCURSOR or SYS_REFCURSOR by way of an OUT parameter:

VAR x REFCURSOR
EXEC procedure_name(parm1, parm2, :x)
PRINT x

The colon before the x in the second line above is required. It's not allowed in the other two lines (VAR and PRINT).

If you're having trouble with testing a specific function or procedure in SQL*Plus, please post the function/proc or at the very least its declaration.

To debug, sprinkle DBMS_Output.Put_Line calls at places where you want to check values. The DBMS_Output package is documented here. Before running a proc with DBMS _Output calls, type the following at the SQL*Plus command line:

SET SERVEROUTPUT ON SIZE 50000

The SIZE is the number of characters that Oracle will allow DBMS_Output to echo. All output is sent back after the procedure is finished.

How can I test a function working or not in pl-sql?

1 Answers1