Forum Stats

  • 3,838,112 Users
  • 2,262,333 Discussions
  • 7,900,511 Comments

Discussions

Categories

Auto commit broken in 21.4.0?

MagnusMG
MagnusMG Member Posts: 5 Green Ribbon
in Oracle Developer Tools for VS Code

Since a couple of weeks I have had problems with auto commit in VS Code. It seems to coincide with the release of 21.4.0.

I develop my scripts in VS Code, but run SQL Developer in parallel, for quick checks. Sometimes (I have not quite figured out what controls this) changed I make in VS Code ('Execute SQL' or ctrl-e) are not visible in SQL Developer until I issue an explicit 'commit' in VS code.

I also sometimes end up with a lot of connections from VS Code to the database, visible when I execute a 'SELECT * FROM V$SESSION WHERE username =''<my user name>'''. They typically disappear if I close restart VS Code.

I did not have these problems before. Yes, the autocommit setting is on in my VS Code preferences.

· Share on TwitterShare on Facebook

Answers

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,167 Employee
    edited Apr 1, 2022 3:57PM
    https://community.oracle.com/tech/developers/discussion/4496490/auto-commit-broken-in-21-4-0

    I'm having trouble reproducing the autocommit issue. Did you see the words "Commit complete" in the results window after you executed the SQL?

    Maybe you can help me reproduce this. Could you please provide me with some SQL that reproduces on your machine including any CREATE statements needed?

    As for the connections open issue, we can't reproduce that either. Could you please execute:

     select count(*) from v$session where username = 'yourusername';

    And then let me know how many VS Code tabs associated with our extensipon are open on your machine and how many connection nodes are in Oracle Explorer at the time you run that.

    · Share on TwitterShare on Facebook
  • MagnusMG
    MagnusMG Member Posts: 5 Green Ribbon

    I will try to collect this information.

    · Share on TwitterShare on Facebook
  • MagnusMG
    MagnusMG Member Posts: 5 Green Ribbon

    I have not had problems with failing autocommit again, so we can close this, at least for now.

    VSCode still leaves a lot of connections open. When I executed your statement I got a count of 8 connections, even though I only had one tab open, and one database connected in the Oracle Explorer pane. (This is a bit annoying sometimes, but not a big problem.)

    Thank you.

    · Share on TwitterShare on Facebook
  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,167 Employee
    https://community.oracle.com/tech/developers/discussion/comment/16833636#Comment_16833636

    Thanks! We are considering a connection sharing feature for a future release for those customers with a limited number of connections available to them. In the meantime, if you run into issues with too many connections despite tabs being closed, you should try to right click on the connection in database explorer and select "Disconnect".

    If that does not solve the problem, you could also try to close and reopen VS code itself. Let me know if any of that helps.

    · Share on TwitterShare on Facebook
  • MagnusMG
    MagnusMG Member Posts: 5 Green Ribbon

    The problem reappeared today. The message in VSC when I issue a commit is 'Commit complete.'

    I have 5 connections open according to V$SESSION, 2 tabs open in VSC and 1 database connection open in VSC Oracle Explorer. (So, 2 connections that seems unnecessary.)

    It is not easy to reproduce, and there are many SQL statements behind this. In this particular case I first create a table, fill it with data, and then add more data using three MERGE statements. When I checked the table in SQL Developer, the merge statements had had not effect. After I issued a commit, I could see all the changes in SQL Developer.

    I tried a simple repeat of this structure using the same tab in VSC:

    CREATE TABLE FOO (

    a NUMBER NULL,

    b VARCHAR2(50) NULL

    );

    Issuing this I got the response 'table created' and 'commit complete'. I checked in SQL Developer and could see thet table FOO there.

    So I inserted som data:

    INSERT INTO FOO VALUES (1, 'aaa');

    Again, I got the message data inserted and commit complete. I could see the data in SQL Developer.

    Then I tried a merge:

    MERGE INTO FOO

    USING (

    SELECT 1 AS a, 'bbb' AS b FROM dual

    ) SRC ON (FOO.a=SRC.a)

    WHEN MATCHED THEN UPDATE SET

    FOO.b=SRC.b

    ;

    I got the response '1 row merged', but no 'commit complete'. I could not see the change in SQL Developer.

    I issued a commit in VSC, got 'commit complete', and could see the change in SQL Developer.


    Is there a problem with MERGE statements?

    · Share on TwitterShare on Facebook
  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,167 Employee
    edited May 2, 2022 5:22PM

    Yes, this is a bug. I will report it. Thanks for tracking this down! Please let me know if you come across anything else that causes autocomplete to fail.

    · Share on TwitterShare on Facebook
  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,167 Employee
    edited May 2, 2022 7:39PM

    Hi, I forgot to address the connection count issue you raised:

    If the two tabs you mention are files (sql scripts) then a count of 5 in V$SESSION is expected, because we use 2 connections per file (one for the script, and one to do autocomplete/intellisense), plus the one connection in Oracle Database explorer. Please let me know if the two tabs are sql script files.

    If a tab is opened via an object in Oracle Explorer, eg, "Open Package Body", then we use two connections total, since we share a connection for all code objects to do autocomplete/intellisense. Eg, if you had 10 code objects open for the same connection in DB Explorer, we would still only use 2 connections total.

    In the next release we hope to use connection sharing for files as well so you should see your connection count cut nearly in half.

    Thanks,

    Christian

    · Share on TwitterShare on Facebook
  • MagnusMG
    MagnusMG Member Posts: 5 Green Ribbon

    Hi,

    Yes , they were SQL script files.

    Glad I could help with tracking a bug down! I should also say that since I now know that the message 'commit complete' is crucial and not always there, it is quite easy to keep an eye out for it. I doubt that counts as a work-around, but still...

    Thanks,

    MagnusMG

    · Share on TwitterShare on Facebook