2

In Oracle SQL Developer, I need to switch the active database connection manually. Is there a command that will connect to a different database programmatically, assuming that the login credentials are already saved? I'm trying to avoid clicking on the drop-down menu at the top right of the window which selects the active connection.

Perhaps I should rather have a single SQL file per database? I could understand that argument. But this to prepare to migrate some tables from one database to another and so it's nice to have all of the context in one file.

On database1, run a query on table1 which is located in schema1.

-- manually switch to database1 (looking for a command to replace this step)

ALTER SESSION SET CURRENT_SCHEMA = schema1
SELECT * FROM table1;

On database2, run a query on table2 which is located in schema2.

-- manually switch to database2

ALTER SESSION SET CURRENT_SCHEMA = schema2
SELECT * FROM table2;
Improve this question
4
  • 2
    Use the CONNECT command, it will be valid during the execution of your script. Commented Feb 23, 2019 at 18:00
  • 1
    @thatjeffsmith that sounds promising. Can it use connections I've already saved in SQL Developer? docs.oracle.com/database/sql-developer-4.2/SQCQR/… Commented Feb 23, 2019 at 19:46
  • are we going to a database OTHER than the one we're connected to? you should be able to use an EZ connect string to go outside the local db Commented Feb 23, 2019 at 20:19
  • Why not use fully quantified indentifiers in SQL? SELECT * FROM database.table then you don't have to set a current "working" database.. Pretty sure this also works in Oracle database. Commented Feb 23, 2019 at 22:24

3 Answers 3

Reset to default
3

Looks like this is well documented here

Use this command

CONN[ECT] [{<logon>| / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You need a DDL TRIGGER to perform an event after your presql

  CREATE TRIGGER sample
    ON TABLE 
    AFTER 
     Event
    ........
 THEN
  ALTER SESSION SET 
   CURRENT_SCHEMA = schema2
    SELECT * FROM table2;
Improve this answer

3 Comments

Thanks for the answer, but I don't really understand the concept of how this should work or the details about how to set it up. Does it require write access to the database? I was looking for a solution that changes only my local session in SQL Developer.
The question talks about migrating tables to another database. This might apply if the task only required switching schemas in the same database. But even then it's probably not the correct solution.
Yes, it works in the same database but perhaps i thought as its a ddl it might work remotely via some protocols/commands connect by etc as well this answer was to automate it meaning once you perform some action the trigger will automatically alter the further changes on that associated event.
0

I don't know of a way in which to change your selected connection in SQL Developer, but there is a programmatic method for temporarily changing the connection under which the script commands are run, as @T.S. pointed out. I want to give a few examples, which might be helpful to people (as they would have been for me).

So let's say your script has part A and part B and you want to execute them one after the other but from different connections. Then you can use this:

CONNECT username1/password1@connect_identifier1;
-- Put commands A here to be executed under this connection.
DISCONNECT; -- username1

CONNECT username2/password2@connect_identifier2;
-- Put commands B here to be executed under this connection.
DISCONNECT; -- username2

The connect_identifier part identifies the database where you want to connect. For instance, if you want to connect to a pluggable database on the local machine, you may use something like this:

CONNECT username/password@localhost/pluggable_database_name;

or if you want to connect to a remote database:

CONNECT username/password@IP:port/database_name;

You can omit the password, but then you will have to input it in a prompt each time you run that section. If you want to consult the CONNECT command in more detail, this reference document may be useful.

In order to execute the commands, you would then select the code that you are interested in (including the relevant CONNECT commands) and use Run Script (F5) or just use Run Script (F5) without selecting anything which will execute the entire script file. SQL Developer will execute your commands, put the output into the Script Output tab and then stop the connection. Note that the output of SELECT commands might be unpleasant to read inside Script Output. This can be mitigated by running the following command first (just once):

SET sqlformat ansiconsole;

There is also Run Statement (Ctrl+Enter), but do note that Run Statement (Ctrl+Enter) does not seem to work well with this workflow. It will execute and display each SELECT statement into a separate Query Result tab, which is easier to read, BUT the SELECT query will always be executed from the context of the active connection in SQL Developer (the one in the top right), not the current code connection of the CONNECT statement. On the other hand, INSERT commands, for instance, DO seem to be executed in the context of the current code connection of the CONNECT statement. This (rather inconsistent) behaviour is probably not what you want, so I recommend using Run Script (F5) as described above.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.