Test Scripts for PL/SQL Stored Procs

Question

With TSQL I'm used to putting some repeatable tests in for my stored procs. Typically this may include putting the db in a particular state, runnings the sproc, validating the state and rolling back. And contrived example might something like this"

BEGIN TRAN
--input for test case
DECLARE @TestName VARCHAR(10) = 'bob'
--insert test row
INSERT INTO tbl (data) values (@TestName)
--display initial state of target row
SELECT * FROM tbl WHERE data = @TestName
--do some useful test
EXEC MyProc
--display the final state of the target row
SELECT * FROM tbl WHERE data = @TestName
--put the db back where it started
ROLLBACK TRAN

Now I'm working with Oracle and PL/SQL and I'm trying to use a some similar pattern to test my work and not finding it obvious to me quite how to do that. I believe there are a few different ways I might accomplish it but haven't gotten anything to actually work. Ideally I would have a single script in which I could run multiple test cases and inspect the result.

I am trying to work in PL/SQL Developer at this point and understand that might have some differences from how it might work in Oracle SQL Developer or elsewhere.

Answer 1

In Oracle, using tools like SQL*Plus and GUI tools like SQL Developer, you have many options :

1. To execute the statements and procedures in a single session in an order, i.e. using procedural method of PL/SQL, write an anonymous plsql block and execute it as a script.
2. Most of the GUI based tools have an option like Execute as script or Test Window to execute your scripts individually or embedded in an anonymous block.
3. Using DBMS_SCHEDULER also you could achieve the same task.

As you are interested in PL/SQL Developer tool product of Allround Automations, you could simply use the test window to test individual objects.

I have documented few useful features of the PL/SQL Developer tool in my blog, please read http://lalitkumarb.wordpress.com/2014/08/14/plsql-developer-settings/