Introduction to tracing

This can be applied to connection/auithentication/encryption issues
(in that case ignore inside the pipe symbols ||)
- be sure to check out sqlplus and oracle client 
(it is a different 'C' language based code path) rather than the default thin/Java
 code path shipped by default with sqldeveloper and sqlcl, before going any further,

If there is an syntax error in a vendor program when using sql pl/sql or indeed any ora- error you can trace it.

Introduction to tracing - internet search for more:

Warning - this can produce large files
1/alter session set sql_trace=true;
2/or via SQLDeveloper Reports->Data Dictionary Reports ->Database Administration->Sessions->Sessions
pick a database then pick a session then right click 'Trace Session'
show parameter USER_DUMP_DEST
--for the output directory for 1/ or 2/
3/or Ethernet sniffer (wireshark) (This is what I usually use if I need to trace:)
works for me to capture then save in default format and then look for your "ora-" error in a text editor.
(note "ORAxxxx no data found" is used to end a result set so might not be the error you are looking for)
4/ alter your Oracle sqlnet.ora and put in
For thick client including JDBC thick:
trace_level_client=16
For server side (i.e. background Oracle processes) whether you are using JDBC thick or JDBC thin: (for sqlnet.ora) (might need to stop and start the database)
trace_level_server=16
There are ways to extract the data out its presented in
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  |...............................|
Most likely you want what is inside the ||. As a bonus you could track whether it is sent or received
For Thin client: There is a different type of logging for thin(I have not used it myself): http://docs.oracle.com/cd/B28359_01/java.111/b31224/diagnose.htm#CIAIDFIC
5/ On Linux
strace -f -o /tmp/trout -s 1000 the_command_with_args
or
strace -f -o /tmp/trout -s 1000-p the_process_id

You might be looking for the last error - or the first serious error (which may be simple and cascade into more serious errors that reach the screen).

Remember to switch your tracing off so you do not fill the hard disk!

Regards,
Turloch

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
or
>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;

gives:
Error starting at line 1 in command:
connect username2/password2@my_tnsnames_entry
Error report:
Connection Failed
Commit
(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
or
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.

Regards,
Turloch

SQLDeveloper and wallet authentication - use thick and check OS_AUTHENTICATION

To use the wallet feature with SQLDeveloper -

1/get wallet working for sqlplus,
2/use jdbc oci thick,
3/no username or password,
4/check OS_AUTHENTICATION (which means username is not required and you are passed back to the jdbc oci thick).
5/use TNS->connection identifier.

Tested with 11.2.0.2 server software providing OCI.

SQLDeveloper 3.0 production is supplied with the thin driver 11.2.0.2
it needs 11.2.0.2 version to use thick which has a few additional features over thin.

To avoid confusion with other Oracle software you can set the ORACLE_HOME in the session you start sqldeveloper in as described below:

The version mismatch is often because SQLDeveloper is trying to use the 'thick' driver and 11.2.0.2 thick is not available,
try
thin by:
1/(uncheck Tools->preferences->Datababae->advanced->use OCI/thick driver, and
2/If necessary, set ORACLE_HOME for your cmd.exe session where sqldev is started from to where sqldeveloper is where %ORACLE_HOME%\jdbc\lib exists, (so SQLDeveloper does not try your existing ORACLE_HOME) and
3/use Connection Type TNS -> Network Alias.
4/You may need to set Tools->preferences->Datababase->advanced->Tnsname directory to the directory containing tnsnames.ora

-Turloch

Edit 25 Sep 2011
P.S. PATH (and LD_LIBRARY_PATH ??) settings may be needed to find the new .dll (Windows) and .so (Linux) files.

Substitution and bind variables

Substitution and bind variables in execute script f5.

(execute statement f9 handles substitution variables '&' the same , but prompts for bind variables ':' in select update delete... (but not anonymous blocks))

Input

undefine a_value
undefine fred
--Substitution variables ie &...
--prompt if not known and set
prompt &&a_value
--set a value explicitly
define new_value = fred
prompt &fred

--Bind variables ie :...
variable my_bind varchar2
begin
:my_bind := 'value';
end;
/
begin
null;
--(use :my_bind within an anonymous block);
end;
/
--or
print :my_bind

Output

> undefine a_value

> undefine fred

> --Substitution variables ie &...

> --prompt if not known and set

> prompt &&a_value

old:prompt &&a_value
new:prompt set_a_value
set_a_value
> --set a value explicitly

> define new_value = fred

> prompt &fred

old:prompt &fred
new:prompt set_fred
set_fred
> --Bind variables ie :...

> variable my_bind varchar2

> begin
:my_bind := 'value';
end;

anonymous block completed
> begin
null;
--(use :my_bind within an anonymous block);
end;

anonymous block completed
> --or

> print :my_bind

MY_BIND
-----
value

Clearing substitution variables

ScriptRunnerContext substitution variables are cleared

1/if you connect the worksheet to another connection and back via the top right connection chooser.
2/You start a new work sheet
3/undefine variable_name

Using Oracle Client with SQLDeveloper

SQLDeveloper by default has limited connection configuration possibilities (thin jdbc driver) so 'edge cases' may need the Oracle (instant) Client install and full *.ora configuration

- sqldeveloper
- Tools preferences -> database -> advanced -> use oci/thick driver
- oracle client
- connection type TNS->connect identifier
- click kerberos to allow null username/password (or OS authentication but I think that has more consequences)

(and effectively kick the config back to the oracle thick client *.ora files and 'sqlplus' C code (where kerberos may not be configured))