Search code examples
sqloracleoracle-sqldeveloperoracle21c

My Oracle database 21c express is duplicating my query result in the result tab

The oracle database developer is duplicating my queried data in the table, for example, in the attached screenshot I only insert two rows in each of the 3 tables i.e student table, course table and enrollment table, but it displays 8 enrollment rows instead of the only two I inserted.enter image description here

I tried re-running the code but and clearing the result space but still not working.

Solution

  • Every time you run the script (using F5 or clicking the icon with a green triangle with a page behind it), it is inserting two rows into the table and then running the SELECT statements. So when you run the final SELECT * FROM enrollments it will show you all the rows you inserted this time and EVERY previous time that you ran the script.

    So if you ran the script a total of four times then the pairs of INSERT statements will have been executed four times and the table will contain 8 rows and the SELECT statement will show all 8 rows.

    The next time you run the entire script you will see the previous 8 rows, plus 2 new rows for a total of 10 rows.

    To show the difference between the pairs of rows, if you execute:

    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

    at the top of the script then when you run the script it will show you the complete date (including the time component) and you will see that the pairs of rows INSERTed into the enrollments table will each have different timestamps that correspond to the times when you ran the script.

    Alternatively, if you put the cursor on the statement:

    SELECT * FROM enrollments

    and press CTRL+ENTER (or click the green triangle icon) then it will just run the single statement and you should see that no new rows have been inserted (just the rows that were inserted every previous time that you ran the script).