Toad World® Forums

How to fill collections with test data?


I cannot figure out how to define a test where I need to pass a table of records to a stored procedure. When I generate and compile the test code, the named parameter in my store procedure is assigned a NULL value, instead of the collection I have defined, like so:

        q##start_time := DBMS_UTILITY.get_time;
        super_test_cp.ins (**rows_in => NULL**, handle_error_in => TRUE);
        -- Capture post-execution values.
        q##sql_rowcount := SQL%ROWCOUNT;

The variable name automatically available in Step 2 of the Test Case Editor (i_ROWS_IN in my case) does not seem to be used in the call to the stored procedure. I can add initialization code to fill the collection with data, but it is never passed to the procedure and I get a compile time error (cannot pass NULL to a NOT NULL constrained formal parameter).

It feels like I’m missing something obvious here, but I can’t figure out what it is .


Oh…you’re just missing…a bug!

Yes, we had a bug in how we let you specify that you want to use the variable that you have populated. The result is that we pass NULL instead of the argument name. But good news, there is a quick workaround:

Navigate to the collection input in Test Editor and copy the GUID from the Audit Info to your clipboard.

Paste that GUID into this block:

UPDATE qu_input SET assign_in_setup = ‘Y’
WHERE universal_id = ‘’;

Run it and then see if your argument is passed properly. This bug will be fixed in 1.5.2 (in code freeze now).


That worked great!

Thanks a lot for the quick reply. I have already decided to buy Code Tester (when it is available), but it is good to know that the feedback on questions is fast :-).


Erik - Then I have some very good news for you: you can now (right now!)
purchase Code Tester. Just click on the link below to purchase online,
or contact your Quest sales rep…

Many thanks, SF


I have a input variable in my procedure as a rowtype of a table. Normally, for testing it with anonymous block, I would get the data from the table into an rowtype variable and pass that as an input parameter. Can I do the same with code tester? Or do I have to inidividually set up each and every column of the input variable I pass? My input table has very large no. of rows.