We help IT Professionals succeed at work.

how to test a oracle package

vishnut28 asked
Medium Priority
Last Modified: 2013-12-19
I created a oracle package How do I test all the functions and procedures in it
Watch Question

Sean StuberDatabase Developer & Administrator
Most Valuable Expert 2011
Top Expert 2012

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.
Database Developer & Administrator
Most Valuable Expert 2011
Top Expert 2012
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);

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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 ...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.