CODEWITHSUNDEEP
sqloracle-databasesql-insertplsqldeveloper
Elyasaf755
Elyasaf755

Reputation: 3509

PL/SQL Developer Can't See Results Of Insert Statement

As the title says - when I perform an "INSERT" statement, I can't see the results unless I re-open PL/SQL Developer.

To make things a bit more clear: After I perform this statement on the empty table "worker_temp" -

insert into worker_temp
select * from worker_b

I see that 100 records have been inserted: enter image description here
But when I try to see the results using this query:

select * from worker_temp;

I still see an empty table:

enter image description here

But only after I quit PL/SQL Developer and re-open it, I can see the records that I inserted earlier:

enter image description here

Is there a way to see the changes without closing and re-opening PL/SQL Developer?

What I've tried so far:

I've tried to refresh the table using right click on it:

enter image description here

And I've also tried to refresh the whole tables folder:

enter image description here

I also tried committing -

commit;

But I'm not sure what that even is.

Upvotes: 1

Views: 2044

Answers (2)

William Robertson
William Robertson

Reputation: 15991

The default setting in PL/SQL Developer is Multi session:

PL/SQL Developer Connections preference settings screen

This means that each editor window you have open is logged into the database in a separate session. A session can't see another session's changes until it commits. This is rather like saving a shared Excel spreadsheet on a network drive. Nobody can see your changes until you have finished making them, which you'll appreciate is an important feature in a multi-user database.

In PL/SQL Developer, the Multi session default setting means that you can start a long-running query in one SQL window, and then get on with something else in another without being blocked and having to wait for it. With this setting, you'll need to commit your changes before any other editor window can see them. There are Commit and Rollback icons in the toolbar, or you can type commit; and execute it.

However, I always set mine to Dual session, meaning all windows are part of the same session, even if it means I sometimes have to wait for something. I find this simplifies things considerably, and also I can make changes across multiple windows without needing to commit, which can be helpful when working with global temporary tables or alter session commands.

Read more in this setup guide.

Upvotes: 0

Brian
Brian

Reputation: 13571

Tool agnostic way:

begin
insert into worker_temp
select * from worker_b;
commit;
end;

Judging by all the screenshots you are likely getting separate database sessions in 'each' tab you are using - which is a good thing. You have to issue the commit on the same session that performed the insert. Another way of understanding this:

begin
  insert into worker_temp select * from worker_b;
  DBMS_OUTPUT.PUT_LINE('Rows inserted but not committed ' || SQL%ROWCOUNT);
  -- 'undo' the insert by rolling back the insert instead of commit.
  rollback;
end;

Upvotes: 2

Related Questions