execute a select statement during debugging

[sschmiedleitner]

Hello,

I really enjoy using the feature "Testwindow Debugging" in PL/SQL-Developer.

Unfortunatly our code is extremly complex and during debugging I would need to execute a select query to check the data manipulation in the current session in order to check what was the result of i.e. an update statement.
Is this possible somehow?

Thanks a lot and regards

 

[Marco Kalter]

No, this is not possible. The debug session is busy executing your PL/SQL code, and cannot simultaneously perform a query.

 

[sschmiedleitner]

Wouldn't it be possible for the PL/SQL-Developer to inject the query code dynamically into the executed code (hidden) and display the results as a collection variable?

This would be a great feature (feature request ;-)

thx

 

[ScottMattes]

You can define a cursor in the test window that will allow you to see the before and after rows; something like

-- not tested, but it should get the idea across
begin
  -- save current SCN
  SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) into :scn FROM dual;

  -- Call the function
  :result := package_to_test.code_to_test;

  -- view changed records
  cursor :c_results
    select * from (
      select 'before', ctn.*
        from changed_table_name ctn as of scn :scn
       where some = criteria
      union
      select 'before', ctn.*
        from changed_table_name ctn
       where some = criteria )
    order by unique, rows, in, table; -- so that before/after rows appear next to each other
end;

 

[sschmiedleitner]

Thanks for the advise, but unfortunatly this code is not working. Can I really define a cursor between "begin" and "end". When I try to define a cursor in the "declare" section with doubledot, I get an error PLS-00103: Found the symbol "" as following was expected...

Any further advice?

Thanks

 

[ScottMattes]

My apologies, I should have given a clearer (and tested) answer.

This works:

Create the table and put data into it

create table tt (col1 integer, col2 varchar(2), col3 date);

insert into tt values( 1, 'on', sysdate);
insert into tt values( 2, 'tw', sysdate+1);
insert into tt values( 3, 'th', sysdate+2);
insert into tt values( 4, 'fo', sysdate+3);
insert into tt values( 5, 'fi', sysdate+4);

select * from tt;

commit;

Now put this into a test window

-- tested

-- first create the table using the DDL/DML supplied in this post

-- copy/paste this into a test window,
-- then in the variable window below the test window
-- right click on the Variable column heading
-- and select Scan Variables
-- then press F8 to execute the test
begin
  -- save current SCN
  SELECT TIMESTAMP_TO_SCN(SYSTIMESTAMP) into :scn FROM dual;

  -- Call the function
  update tt
     set col2 = 'xx'
   where trunc(col3) = trunc(sysdate);

  -- view changed records
  open :c_results for
    select * from (
      select 'before', ctn.*
        from tt as of scn :scn ctn
--       where some = criteria
      union
      select 'after', ctn.*
        from tt ctn
--       where some = criteria
)
    order by 2; -- so that before/after rows appear next to each other
end;

After executing the test, click on the '...' button to the right of the cursor c_results in the variable window - you will see a query grid with the before and after images of the rows.

 

[sschmiedleitner]

Hey Scott,

now I understand, it worked - thanks a lot!

This will help me a lot in my work pretty much!

Regards

 

[ScottMattes]

You are welcome!

Here is another that will show which rows have been modified by your code (you should probably add an order by so that the rows definitely come out in the order you suppose):

-- view just changed records
  open :c_changes for
    select * from
    (
      select *
        from tt as of scn :scn ctn
      MINUS
      select *
        from tt ctn
    );