Search code examples
sqloracle11goracle-sqldeveloper

Why does CONNECT in SQL Developer not work the way it is expected to work

Using GUI I connect as OLDUSER to a database.

When I type in the following:

CONNECT newuser/newpassword;
SELECT USER from DUAL;

I get:

Connected.
Connection created by CONNECT script command disconnected

And following that I get OLDUSER.

Why is it so? Why does CONNECT disconnect immediately before reaching the next statement when you execute code as highlighted code in SQL Developer?

UPDATE

I noticed two different behaviors in SQL Developer:

  • When I highlight the CONNECT newuser/newpassword; SELECT USER from DUAL; statement and CTRL + ENTER them - I will get the OLDUSER instead of new user. This is the behavior I described earlier.

  • When I run the entire script using F5 - I will get the NEWUSER.

To answer myself:

  • For highlighted and CTRL+ENTER executed code: It seems like a 'feature' of SQL Developer that causes CONNECT to disconnect as before the next statement is reached within highlighted text. I find it a bit counter-intuitive as you can use the same highlight + CTRL+ENTER to execute a transaction consisting of multiple statements. They will execute without breaking session.
Solution

  • In a script, we are running your code, and at the end your transaction is finished.

    When you do a CONNECT, we'll do the connect for you. And your script is executed.

    When it's over, so is your 'transaction' - so we disconnect, and return your session to where it should be - the connection defined by your connection properties in the Connection Panel.

    You see a difference between Ctrl+ENTER and F5 because only F5 invokes our script engine and that is what gives you access to SQL*Plus commands like 'CONNECT'

    When you do try to run multiple things via Ctrl+Enter, if we see a SQL*Plus command we'll send that through the script engine, but the other queries get executed on the 'main' connection.

    What you're trying:

    enter image description here

    So the connect works, then we see sql statements and you are doing ctrl+enter so you want results in a data grid so we switch code paths back to single statement execution, and back to main connection (which is HR for me)

    And what happens if you use F5:

    enter image description here

    We connect, and run queries, and it all happens in a 'local' connection to 'limited_privs' schema, and when this is done running, the 'local' connection is closed and we're back to 'HR' again