how to test a oracle package

Posted on 2009-02-10
Medium Priority
Last Modified: 2013-12-19
I created a oracle package How do I test all the functions and procedures in it
Question by:vishnut28
  • 2
LVL 74

Expert Comment

ID: 23603297
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.
LVL 74

Accepted Solution

sdstuber earned 750 total points
ID: 23603329
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);

Expert Comment

ID: 23603404
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.
LVL 18

Expert Comment

ID: 23608039
check this out for testing oracle PL/SQL ...

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question