how to test a oracle package

I created a oracle package How do I test all the functions and procedures in it
Who is Participating?
sdstuberConnect With a Mentor Commented:
select your_package.your_function('param1','param2') from dual;

v_result number; -- or whatever type makes sense
     your_package.your_procedure('param1','param2', v_result);
same as you would if the procedures and functions were standalone.

You call of them with whatever test data makes sense to validate/stress them.

If you have some un-exposed functions/procedures.  I suggest you expose them for the purposes of testing and then remove them from the package specification before releasing to GA.
A lot of it depends on what the functions and procedures are intended to do, but some things that will be helpful with testing are developer tools, like sqlplus or TOAD, and the dbms_output package.  For example, if you want to test a function, then you can set up an anonymous PL/SQL block in a little script file that returns a value into your variable, then display the variable contents to confirm that it worked.  Like this:

set serveroutput on
  v_return_value NUMBER;
  v_return := mypackage.myfunction;
  dbms_output.put_line('v_return = ' || v_return);

Procedures are a little more difficult because they're usually more complex, but basically you want to set up a test case and run the procedure to see if the expected results occur.  If not, then you can insert some debugging statements (provided you don't have a source-level debugger) that display results or write results to a logging table or file.  Be sure and test all possible scenarios - don't just run through the most common scenario and assume that all use cases will succeed.

Finally, it is important to code your functions and stored procedures to trap for unexpected SQL errors and report the results.  Do a little research on the EXCEPTION clause in PL/SQL if you're not already familiar with it.
check this out for testing oracle PL/SQL ...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.