Sunday, 21 October 2012

Connect and copy in sqldeveloper

Just a quick blog about three older features. (I mention copy to Oracle at the end)

connect - connects to oracle given a username password and tnsnames.ora entry (The script will no longer use your current connection, the connection will remain open in SQLDeveloper for you to for example rerun the script without explicitly connecting again).

>help connect
for a little more information

copy - copies tables from one connection to another - internally reuses code from the connect command.

>help copy
>copy syntaxerror

for a lot more information

The problem is the error message:
Connection Failed
is not very descriptive.

My advice is:
If copy fails with Connection Failed - try connect (this will also fail but is a bit simpler to work with)

something like
connect username2/password2@my_tnsnames_entry
select USER from dual;

Error starting at line 1 in command:
connect username2/password2@my_tnsnames_entry
Error report:
Connection Failed
(the user check (on a separate line) is so you can confirm you have logged in as a different user on success)

This is most likely because your configuration is wrong.
It is not a copy bug.

Things that can be wrong:
username wrong
password wrong
my_tnsnames_entry reference not being picked up.
(One way is does:
tools->preferences->Database->advanced->Tnsnames Directory
point to a directory containing tnsnames.ora which has a my_tnsnames_entry tnsnames.ora entry?)
Does right click on 'Connections' in the connections panel, create new connection with username2/password2 
Connection type TNS->Network alias
work (The tnsnames.ora entries should be accessible via a pull down)?

What is the my_tnsnames_entry tnsnames.ora entry ? Connect/copy used to be oci/thick option only 4 years ago but simple entries should now work without oci/thick.

You cannot just go
connect username/password@sid
connect username/password@your_SQLDeveloper_connection_name_from_the_connection_panel
where sid is not configured in the tnsnames.ora file currently in use.

You can go
connect username2/password2
to connect to the database you are connected to in the worksheet, using a different username (username2 in this case) This is slightly different to Sqlplus which will use your current environmental variables such as ORACLE_SID rather than current connection to resolve the database.

Also: In case you are looking for copy to Oracle:

Copy to oracle is a cool feature for copying tables to Oracle from 3rd party databases, right click  on the 3rd party table, select copy to oracle - then select the Oracle destination connection and take the defaults - (there are a couple of minor options). Similar to the oracle only copy command mentioned above - it does a select on one side , creates a table based on metadata, and inserts the selected rows into the Oracle Database destination.