CODEWITHSUNDEEP
sqloracle-databasewindows-7oracle-sqldeveloper
Bobby
Bobby

Reputation: 1595

Change the database connection programmatically

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;

Upvotes: 2

Views: 1700

Answers (3)

Burcea Bogdan Madalin
Burcea Bogdan Madalin

Reputation: 194

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.

Upvotes: 0

T.S.
T.S.

Reputation: 19340

Looks like this is well documented here

Use this command

CONN[ECT] [{<logon>| / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]

Upvotes: 3

Himanshu
Himanshu

Reputation: 3970

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;

Upvotes: 0

Related Questions