—%test
—%parametrized(select name, birthdate from test_clients)
procedure tst_client_proc(a_name varchar2, a_birthdate date);

We can call it providing parameter values by iterating over sql statement using dbms_sql

We can also accept a function which returns a cursor.

Parameters should be mapped by name if the column aliases in the query correspond equally or by position if not.

Does this need to support query input? The model I have in mind is;

• a data setup step
• parameterised annotation passing list of items set up in data (without SQL)

With this you have the same capability, just without exposing SQL to the annotation.

My concern is opening up avenue of issues where users want to persist test / results data to drive tests rather that keep tests isolated and UtPLSQL clean from data storage.

I agree with @mathewbutler. I guess the better option is to preset all test data (like test_clients) and then iterate over it in a single test and check the results. Given that we have:

• Knowing that ut.expect doesn't stop execution after the first fail we will iterate through all given data;
• We don't have tons of test executions in reports about every single launch of iterated test but still keep data about all fails;
• I think that inventing a new annotation should provide some sort of a utility but we just take parametrizing (params, query or whatever) out of the test body and put it in a declaration part. Instead test-writer can do it by himself feeling free to write any kind of query and handle it in a way he wants it inside of the test body.

No offense, guys, just thoughts out of my head :)

We should look at something like this:
https://junit.org/junit5/docs/current/user-guide/#writing-tests-parameterized-tests

We should look at something like this:
https://junit.org/junit5/docs/current/user-guide/#writing-tests-parameterized-tests

I like the Functionality that the @MethodSource annotation in jUnit has. It provides a Stream of (multiple) parameters that are handed to the test executions. Like a rowtype in a way...

I think there must be some kind of cursor as parameter ... so i can enter all i like - i hope you implemet this soon ....

I think we could approach that problem with a new annotation to imitate an above junit5 approach.

--%parameterizedtest
--%valuesource{"Test"}
procedure testme(a_displayname in varchar2);

result in :
Test was expected to be called Test

or

--%parameterizedtest
--%valuesource{"Test,10"}
procedure testme(a_displayname in varchar2, a_beer_count in number);

result in :
Test was expected to be called Testand had a 10 beers

or

--%parameterizedtest
--%valuesource{"Test,10":"Test2,5"}
procedure testme(a_displayname in varchar2, a_beer_count in number);

as alternative we could use a different annotation for multiple parameters so the code is more readable. e.g.

--%parameterizedtest
--%valuesource{"Test,10"}
--%valuesource{"Test2,5"}
procedure testme(a_displayname in varchar2, a_beer_count in number);

result in :
Test was expected to be called Testand had a 10 beers
Test was expected to be called Test2,5and had a 5 beers

The value source would be a colon separated inputs with list of coma separated values hat would have to be ordered in same way parameters in procedure each parameter entry as an annotation entry one below the other.
The valuesource would be iterated over list of values.
This would not solve the problem of complex types and to be fair not sure how to approach as the complexity is infinite here.
I can see maybe using a datatype information as junit5 to cast to datatype e.g.--%valuesource{"test.test_type=Test"} but there are nested types, types that are not part of tested schema ( so extra grants complexity ).

Personally if we would to implement this I would suggest stick to basic datatypes.

Actually I take it back. We could try to write a tests with anydata and let the user define a logic to handle the type inside tests.
What you think about it @jgebal and above suggestions ?

I think it would be good to do a full review of what JUnit is proposing for parametrized tests and maybe check how other testing frameworks are solving this.

In essence the key challenge is to come up with a good specification of annotations and parameters that will be relatively easy to use for average Oracle PL/SQL engineer.
Having that said, I would refrain from leveraging ANYDATA, as it's API is quite complicated and not very friendly to use.

I think we should consider using DB tables for storing and reading parameters.