Toad World® Forums

Outcomes ?? How to check a value of column before and after actual program being tested is excecuted ??


#1

Hi there,

Scenario :

The procedure being tested actaully modifies a column in table .This column is of DATATYPE NUMBER.

I tried this way :

INITIALISATION SECTION : v_before NUMBER :=

POST EXECUTION SECTION : v_after NUMBER :=

Then in the Edit Test >> Outcomes

I wrote a query like –

SELECT v_after -v_before FROM DUAL and compared it to 0 (ZERO).

To my dismay , i found v_after = v_before. [:^)]

Actual column in the table is getting modified.[Y]

Not sure , how foes POST EXECUTION SECTION works ?Please explain.

Any other solution to this problem statement , please suggest.

Regards,

Amol J.


#2

Hi Amol,

you are not supposed to write tests in the post execution section. The code in Post execution section is executed after the test result record is written. So the post execution code is not allowed to change the test result.

You are allowed to add an outcome with Add using Grid button. Then in the column A. Data changed by program you could select a Query or a Table type outcome. If you only want to test that a column in a table is changed, you may compare two tables in the outcomes.

Dataset compare-crop.png

Best regards,

Ferdinand


#3

Hi Fero,

SELECT <amount_column>

FROM <standard_OracleEBS_Table>

WHERE <primary_key_column> = v_prim_key;

Now this <amount_column> gets modified once the procedure being tested is executed.

The standard EBS table may be having thousands of records, and me just checking TABLE changes(as you suggested) may not be a very good approach since there may be other executions going in data base which may modify the table for some different value of primary key column.

I want my Outcome to be very specifically tested for value which I need to pass as PRIMARY KEY COLUMN VALUE.

I am looking for some kind of utility in QCTO where i can check value of AMOUNT COLUMN FOR GIVEN PRIMARY KEY, before and after the procedure being tested is executed.

Regards,

Amol


#4

Hi Amol,

to achieve a check to a database state before test execution, we should use a global variable that would be visible for our test.

So, we declare such a global variable and initialize it in the customization section:

Then we set up the procedure input arguments. Let’s say the input argument is a primary key value like this:

Please notice that input parameter variable name - we can use it in the test. We can use a single value query type (in the column A.).

The single value query returns the value from the field that is modified by the tested procedure. I.e. it is reflecting the database state after tested procedure execution. Then you can compare the query result and the global variable value to achieve your task.

Please notice, that the tested procedure is responsible for committing the data - the test does rollbacks by default.

Best regards,

Ferdinand


#5

Thanx Fero…

I will try this…