Bug Report/Wish List: Test Window

[ScottMattes]

I would like an option to have the Test Window created for a right click on a stored proc/func to use the template rather than whatever is currently used.

I would have to provide a comment line with a tag so that the needed vars and code line could be inserted in the correct spot.

Thank you.

 

[Marco Kalter]

Can you show an example of such a script, for which you would like to specify a template?

 

[ScottMattes]

Marco,
I'll try.

Lots and lots of the code I work with uses a table to record job output, so one of the things that I always like to have in my test script is a routine to display that (so I don't have to commit and lose my test data). I also like to rollback the results so that I can test over and over again, so I have to display the output and I don't like having to retype that code each time.

declare
-- automatic proc/func required variables go here [#!#]

my_var1 integer;
my_var2 varchar2( 32 );

procedure print_query( iQuery in varchar2 )
is
begin
execution_info_pkg.print_query( iQuery, 'n' );
end print_query;

procedure show_job_stats
is
begin
print_query( 'select * from job_stats where job_num = ' || _jobnum, 'n' );
end show_job_stats;

begin
-- inserts/updates/deletes to set up job go here

print_query( 'select to show input here' );

-- stored proc call goes here [#!!#]

print_query( 'select to show output here' );

show_job_stats;
end;

Where
[#!#] = p_commit boolean := whatever that supplied package call is;

[#!!#] = something like " ajob_pkg.run_it( p_param1 => _param1,
p_param2 => _param2,
p_commit => _commit );
"

Does that make sense?

Thank you.

 

[Marco Kalter]

Thanks, it makes. I have added this to the list of enhancement requests.

 

[ScottMattes]

I would also like, actually desparately want, the ability to run queries from the test window after a run (that way I don't have to commit, but can still get at the changes made by the run for analysis). Any chance of that?????

 

[aotte]

How about a button that allows you to open a new SQL window using the SAME session as the test window?

I can see that being useful, allowing new windows to be opened in the same session as an already open window.

Difficulty of that would be in being able to detremine which windows share sessions in a multi-session set up. Maybe a SID prefixed to the title of the window? That way you can also easily report to DBA's which session to track/kill/etc.

 

[BWendling]

I like the idea from aotte.

Enhancement:
Open SQL window from Test window or SQL window or Command window using the SAME session.

 

[amaury]

Originally posted by ScottMattes:
I would also like, actually desparately want, the ability to run queries from the test window after a run (that way I don't have to commit, but can still get at the changes made by the run for analysis).

I often add this at the end of my test scripts:

open :cur for select ....;

And I bind the "cur" variable with the "Cursor" type. After running the test, click on the "..." to see the results.

Note: Don't forget to fetch all data before you "rollback" the test window.

You can perform multiple queries this way, but they must be written before the test is run.
This even works for temporary tables!

I hope this helps,
Amaury.

 

[CTzen]

Originally posted by BWendling:
I like the idea from aotte.

Enhancement:
Open SQL window from Test window or SQL window or Command window using the SAME session.

I will second that

 

[Marco Kalter]

Note that you can already execute a select statement in the Test Window (oviously using the same session). The results will show up in a SQL Output tab page.

It would be easier to run a select statement as part of the Test Script specification of course. It would be even better if the Test Window could capture the query results, and compare them to a previous run for regression testing. This is already on the list of enhancement requests.

 

[ScottMattes]

hmmmmm, I'll have to try that out. My need is to be able to execute multiple queries though.

 

[ScottMattes]

I just got back from trying select statements from the test window.

- it seems that this isn't possible if you have a PL/SQL block in the window (which is when I would want to do it).

- if I highlight a select, right click and choose Test, the test window opened does execute the select, but it must be in a different session because it doesn't see the changes made by the test window the select came from.

How about making the SQL Output tab always visible and make it like a SQL Window, just in the same session as the test window? Or, if selects are present after the PL/SQL block you could open a new SQL tab for each (with the table name as part of the tab name)?

 

[Marco Kalter]

You can only replace the entire text of the SQL script by a select statement to get the results.

But like I said, we will have something in the future.

 

[ScottMattes]

Originally posted by amaury:
I often add this at the end of my test scripts:

open :cur for select ....;

And I bind the "cur" variable with the "Cursor" type. After running the test, click on the "..." to see the results.

Note: Don't forget to fetch all data before you "rollback" the test window.

You can perform multiple queries this way, but they must be written before the test is run.
This even works for temporary tables!

I hope this helps,
Amaury. [/QB]

This is what I was doing with my print_query call, but your way looks much better since I would have a grid to work with. I can't wait to get to try this 'for real'.

THANK YOU!