1

I am trying to test a stored procedure which is deleting data from a table and have a setup similar to this:

Oracle Setup:

CREATE TABLE table_name ( id NUMBER );

PL/SQL Script:

DECLARE
  v_scn V$DATABASE.CURRENT_SCN%TYPE;
  v_row TABLE_NAME%ROWTYPE;
BEGIN
  INSERT INTO table_name VALUES ( 1 );
  INSERT INTO table_name VALUES ( 2 );
  INSERT INTO table_name VALUES ( 3 );

  SELECT current_scn INTO v_scn FROM V$DATABASE;

  -- call a procedure with side-effects such as:
  DELETE FROM table_name WHERE id = 1;

  SELECT *
  INTO   v_row
  FROM   (
    SELECT * FROM table_name AS OF SCN( v_scn )
  MINUS
    SELECT * FROM table_name
  );

  DBMS_OUTPUT.PUT_LINE( 'One row deleted: ' || v_row.id );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE( 'Not found' );
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE( 'Too many rows' );
END;

ROLLBACK;

I would expect that v_row would be populated with a single row and that none of the exception handlers would be invoked. However, the NO_DATA_FOUND exception is being invoked.

  • Why is it not working?
  • How can I use a flashback query to determine which row(s) have been deleted?
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    You are actually going down the right path. It looks like you have a transactional boundary problem. I was able to get your test to work by first, pulling the inserts out of the pl/sql and commiting them. Then, within the pl/sql, commit the delete statement. That is all I changed and it worked for me. – unleashed Mar 08 '17 at 14:48
  • @TheGrandDuke The issue I'm having is that the stored procedures I am testing have side-effects across multiple tables and the simplest way to clean up after each test is to issue a `ROLLBACK` so I am trying to avoid using a `COMMIT` statement. If `COMMIT`ting is the only way of resolving this then I can work round it but will need more detailed/specific teardown scripts. – MT0 Mar 08 '17 at 14:56
  • You have to do a commit – OldProgrammer Mar 08 '17 at 15:16
  • Flashback would contain previous states of the rows based on actual transactions. You have to commit. – unleashed Mar 08 '17 at 15:22

0 Answers0