4.5+ million Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
5,924 Comments
ldap.ora and SQL Developer (version 20.2.0.175)
SQL Developer does NOT honour the ldap.ora file when setting the Tnsnames Directory in SQL Developer preferences (Database -> Advanced).
SQL Developer does honour the ldap.ora file when the TNS_ADMIN windows environment variable is set.
I’m assuming this is a bug?
No, you would need to setup your oracle client and enable Use OCI in preferences.
Then your sqlnet.ora and related config files would be honored.
Hi,
While exporting from sql developer my delimeter option is disabled. How can I enable it so that I can add comma as a delimeter.?
Which format are you selecting?
BTW, CSV is hard-coded such at a comma (,) is your delimiter.
If you want to choose your own delimiter, say a European comma (;) – choose DELIMITED format.
Hello,
I tried to execute a statement and it runs if I put in a certain number in the Where clause, but when I change the number to make sure it will provide results for new number it came back empty, but I know it should of return results.
Check your query…again.
Without an example, I can’t help much.
OK that was a double fail. 1) I didn’t submit my ‘snippets’ question in a new comment, but as a reply to an existing thread (sorry John Varian) and 2) i see now that the Snippets feature is still present in SQL Developer 20.
MY BAAAAAAAAAAD
I double forgive/absolve you – carry on!
Hey.
When exporting connections from sqldeveloper. It seems to have trouble with folders within folders. On import it becomes a bit of a mess. Any chance of a fix on that one?
Before I test…is it one layer of subfolders, or many many levels?
Folder within a folder with some connections in each.
Why did they remove Snippets from SQL Developer in version 20? That was one of my favorite features 🙁
When exporting query results from the grid as
| delimited
Left enclosure and right enclosure NONE
Line terminator |
my results are coming up as a single line.
All delimiters are in the right spots but there is only a single line
consisting of all of the data.
Any suggestjions?
Thanks
Hello,
I am new to sql developer and my question may be actually a simple question. Please guide.
I am getting ORA-00942 when I am trying to access “Other Users” section after connecting to a database using sql developer. I have given read access on the tables to the connecting user and I can access them in my queries. But the only issue is while trying to access them using “Other Users” in sql developer.
In regards to the comment above “The developer says one of the libraries we use in the EXE (to make starting this easier in Windows) is going into the Registry to look for Java.”
Due you have a bug number?
Looks like the same problem still exists in Version 20.2.0.174.1557
Thanks
Hi Jeff,
I have a local SQLDeveloper instance that uses 3rd party jar files to connect to both DB2 and SQL Server successfully.
However when attempting to make the same DB2/SQLS connections via SQLCL it fails.
How can SQLCL be configured to connect using the 3rd party drivers for DB2/SQLS?
It seems to be hardcoded to use jdbc:oracle:thin: as a prefix for all connections as show below:
SQLcl: Release 17.2.0 Production on Fri Sep 04 12:41:13 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
USER = sa
URL = jdbc:oracle:thin:@localhost:1433:sqlsdb
Error Message = IO Error: Got minus one from a read call, connect lapse 2 ms., Authentication lapse 0 ms.
USER = sa
URL = jdbc:oracle:thin:@localhost:1433:sqlsdb:1521/localhost:1433:sqlsdb
Error Message = IO Error: Invalid connection string format, a valid format is: “host:port:sid”
Username? (RETRYING) (‘sa/*********@localhost:1433:sqlsdb’?)
Thank you in advance.
David
Those sqldeveloper connections are provided for migrating those DB2 and SQL Server instances to Oracle.
That’s the only reason we support it, and since there’s no migration feature in SQLcl…
Hi David,
You don’t say what you are trying to do so this may or may not be helpful.
Apparently there is another command line for SQLDeveloper in bin folder called SDcli. I only came across it 10 mins ago and have not used it but presumably it uses SQLDeveloper connections?????
See section 1.24 in https://docs.oracle.com/en/database/oracle/sql-developer/19.4/rptug/sql-developer-concepts-usage.html#GUID-6F4050CA-8ECC-4FC1-B5D9-CA8684391CB7
if you need to query data from 3rd party DB(s) have you tried heterogenous gateway (dblink to non-oracle)? That would move the connection to the DB and therefore you would connect to Oracle using SQLcl and access 3rd party over dblink.
Hope that helps.
Cheers
Nigel
Hi Jeff/Nigel,
Thank you both for your replies.
I have also tried sdcli and that also failes to connect to SQL Server or DB2 instances as well with the error below.
“Error:java.sql.SQLException: Driver class not found.Verify the Driver location”
I am just a little puzzled that SQLS/DB2 connections that work using the GUI are fine but using the same connections on the command line fail.
Hopefully one day the DB2/SQLS jar files can be somehow referenced on the command line so that the drivers can be found.
No, it’s not supported
I’m wondering why versions 19.4+ no longer support making a worksheet with an ASM connection. It is very useful to connect directly to an ASM instance to manage with SQL Developer.
I don’t remember ever officially supporting that, but I’m also not aware of any changes.
What happens when you try to connect?
IO Eror: Invalid number format for port number. I find it hard to believe it wasn’t supported when there is a connection property for the role SYSASM?
IO Error: Invalid number format for port number. I find it hard to believe it wasn’t supported when there is a connection property for the role SYSASM?
Good point…I’ll ask QA to take a look.
Thanks, I love the product!
Why doesn’t ords work with weblogic? I have followed all of the instructions exactly. Regardless of configuration, credentials, authorization, implementation, or any combination thereof the product simply doesn’t work. It is a great product for displaying 404 messages but not great at serving any data. Are we expected to manually add ui mappings for resources created in sql developer? The alleged discovery of bound uris is just that, alleged, non-existent, fake.
You mean, why isn’t it working for your weblogic?
Good question.
What resource are you trying to access?
Hello Jeff,
i have some problems with SQLCL 20.2 when we want to import rest modules.
this is how we are trying to automate the export and import of rest modules, between environments, using SQLCL
–EXPORT —-
i am export rest modules using sqlcl 20.2, using the following commands :
sql> spool module1.sql
sql> rest export module1
sql> spool off
sql> exit
i then get a sql file,
———–Import of REST Modules—–
this is what i do to re-recreate rest modules and this is what i get.
sql> @module1.sql
2
3
^C
exit
———————–
once i try to read the script i can see a 2, if i hit enter 3, enter again and it is 4.
than i go Contol C and close the connection.
the rest module, never gets created.
using the same sql from SQLdeveloper 20.2 it works, fine, and module gets created.
Unfortunately we need sqlcl to be able to do a ci/cd automated flow ! as we are not allowed to deploy things manually with SQL Developer to our production environments.
i created a case with MOS , but it is not progressing fast enough.
Could you please help ?
What’s your SR# ?
Email me at [email protected]
Hi,
Only recently upgraded to the latest 20.2.0.175 SqlDeveloper to access some “old” Oracle Databases ( 10g)
and to my dismay all the “dbms_output.put_line” entries are simply ignored or non-visible ….
Noticed your answer in the “StackOverflow” site but upgrading the DB engine is not possible …
Any idea how to get those simple details to work ?
Find a copy of sql developer that’s as old as your database, we ended support for 10g about a decade ago.
Or upgrade your database. It is in fact possible, customers do it every day. They just have to actually want to.
On Windows 7 SP 1
I am trying to use SQLDev 20.2 32bit no jre with JDK 8-251 and 8-261. In both cases is get an error Java Virtual machine could not be started because of mscvr100.dll
Is there a solution to this.
Windows 7 is no longer supported.
We’re working on a fix.
In the meantime, find a 32bit mscvr100.dll on your machine, and copy it to your jdk home BIN folder. SQL Dev will start then.
Actually 8-251 works. I did not try the copy into 261
I am a beginner Oracle developer and am familiar with the functionality of Oracle SQL Developer. For example you have a great video explaining Toggle Ansi/Oraсle Joins. And I would like to understand how Inline works using the example of the Ansi /Oracle Joins video. The documentation at the link https://docs.oracle.com/en/database/oracle/sql-developer/20.2/rptug/sql-developer-concepts-usage.html#GUID-FB7B5B33-3B34-497D-B12A-C30779DE2322 is just one mention of Inline. I cannot find more specific information anywhere. I would be very grateful for your help.
I have Oracle11.2 client running on my windows64 operating system. I had Sql Developer v.18 installed, and have been able to connect to Oracle 11g databases just fine, with no issue. However, they have moved from Oracle11g to Oracle12c database. I have made several attempts to establish a new connection to the Oracle12c database, but have not had any success. I used basic connection, with correct user name/ password and all the correct information (that incidentally DOES work when used in successful TNSping), but I was getting failed connection test with the following message:
“Status : Failure – Test failed: null”
Needless to say, not very helpful. I have asked my work associates, and the only difference seemed to be that they were using SqlDeveloper v.4, which I have not been able to install. I went ahead and installed SqlDeveloper v.19 to see if that made any difference, but it did not. They have suggested that I need to have Oracle12 client installed on my machine, but since TNSping is successful, I don’t think that’s the case. Any suggestions on how to get a successful connection ??
exactly which version of oracle 11g database?
My question is about ORDS with query filtering.
When using query filtering, eg
{
“ENAME”: {“$eq”:”SMITH”}
}
this ends up encoded in the URL as
&q=%7B%22ENAME%22:%7B%22$eq%22:%22SMITH%22%7D%7
We have an application firewall in front of ORDS/Weblogic, and this particular construct triggers an SQL Injection detection rule (a false positive).
Do you have any advice/best practice on how to avoid these false positives caused by query filtering (apart from disabling the rule, which is not really an option)?
Surely other customers use this or similar setup and run into similar errors.
Best Regards,
Paul
In fact I’ve not heard of this, and maybe you SHOULD create an exception on this rule for these URI patterns.
Hi Jeff,
I’m trying to automatize my spool script running from Sql Plus.
When I do the same export from Sql Developer into .dsv file it is just perfect! With correct linesize and everything. Is it possible to get a Sql Developer debug script hidden behind this operation so I can reuse all necessary format settings?
Thank you a lot!
Kateryna
easy fix, don’t use sqlplus
instead, use SQLcl
same script engine as sql developer, so no discrepancies
Thank you for indicating me this fantastic tool SQLcl. It works!
The only problem now is to delete blank lines at the beginning and between queries. Can you help me with this please?
These are my extra settings:
SET SQLFORMAT DELIMITED ; ” ”
set FEEDBACK OFF
SET termout off
set heading off
SET echo off
Thank you!
i have a bug logged on those unnecessary blank lines
Please explain how works Refactoring -> Inline in popup menu SQL Worksheet
please explain what exactly you need help with
I am a beginner Oracle developer and am familiar with the functionality of Oracle SQL Developer. For example you have a great video explaining Toggle Ansi/Oraсle Joins. And I would like to understand how Inline works using the example of the Ansi /Oracle Joins video. The documentation at the link https://docs.oracle.com/en/database/oracle/sql-developer/20.2/rptug/sql-developer-concepts-usage.html#GUID-FB7B5B33-3B34-497D-B12A-C30779DE2322 is just one mention of Inline. I cannot find more specific information anywhere. I would be very grateful for your help.
Hi Jeff,
My sqldeveloper 20.2.0.175.1842-x64 hangs randomly when executing any sql (both when pressing the Execute statement button or Ctrl+Enter). the OS is Windows 7 Enterprise edition Service Pack 1 and Oracle version is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production.
There is no other way other than to kill the sql developer session using Windows Task Manager and start a new session. It was affecting me so badly that I have reverted to sqldeveloper-19.2.1.247.2212-x64 for now. is this is a known issue?
Regards
Rajesh Shastri
No, not a known issue. Use jstack to grab a stack dump from the sqldeveloper OS process and share that with me here.
Hi Jeff
The last days I struggled to connect the SQL Developer 20 on my Windows 10 notebook to an ADB instance by using the zip wallet. The connection was not created, it hung.
This issue happens on a Windows platform when:
a) the TNS_ADMIN variable is set as OS variable or in SQL Dev settings
b) a file called ojdbc.properties is located in the TNS_ADMIN directory
In my case this file was from an old ADB connection test with the Instant Client when I extracted the ADB wallet. As soon the file was removed, the ADB connection works. But why is SQL Developer depending on this directory even when using the zipped ADB wallet? Why not just ignore it? All required information are in the zipped wallet file.
Regards from Switzerland
Martin
It’s not clear what’s happening in your scenario. Can you open a SR with MOS?
for ORDS which db_connection_type is best (tns, basic or custom)
Why I am asking this question:
A query takes 20 seconds to run via toad
same query take 40 seconds from Apex/ORDS or sql workshop
Connection type is likely not a factor here.
How long does a basic connection take to establish in SQLcl?
Thanks Jeff,
Anything outside of ords/apex is fast. Actually its 2 seconds in Toad vs 40 seconds from sql workshop
how many records, we page queries with an analytic function, so the sql isn’t’ exactly the same – there’s a bit of overhead there is what i’m saying, but not THAT much
500 Records
Is ORDS 20.2 supported
Certification Matrix for Apex 20.1 –ORDS 19.4 is supported
yes, 20.2 is supported
so you have both the data being paged and converted to json, that adds a bit of overhead
have you looked at the network response times to see what sort of latency is involved as well?
I am running APEX 20.1 with ORDS 20.2 on Database 19.3
Thank You Jeff
Hi Jeff,
I am trying to import data using sql developer, and followed your step by step instructions from here https://www.thatjeffsmith.com/archive/2012/08/using-oracle-sql-developer-to-setup-sqlloader-runs/.
Everything was ok except for the BLOB type. it has some error “Data is not compatible with column definition or is not available for a not nullable column”.
loading the .ldr file is ok, but if I import data using the import wizard. The blob size is always just 6bytes
What’s your ctl file look like?
export ctl:
load data
infile ‘export.csv’ “str ‘\r\n'”
append
into table ABCD.EFGHI
fields terminated by ‘,’
OPTIONALLY ENCLOSED BY ‘”‘ AND ‘”‘
trailing nullcols
( WSQ_ID,
APPLICANT_ID CHAR(30),
RIGHT_THUMB_STATUS CHAR(1),
RIGHT_THUMB CHAR(4000),
RIGHT_INDEX_STATUS CHAR(1),
RIGHT_INDEX CHAR(4000),
RIGHT_MIDDLE_STATUS CHAR(1),
RIGHT_MIDDLE CHAR(4000),
RIGHT_RING_STATUS CHAR(1),
RIGHT_RING CHAR(4000),
RIGHT_LITTLE_STATUS CHAR(1),
RIGHT_LITTLE CHAR(4000),
LEFT_THUMB_STATUS CHAR(1),
LEFT_THUMB CHAR(4000),
LEFT_INDEX_STATUS CHAR(1),
LEFT_INDEX CHAR(4000),
LEFT_MIDDLE_STATUS CHAR(1),
LEFT_MIDDLE CHAR(4000),
LEFT_RING_STATUS CHAR(1),
LEFT_RING CHAR(4000),
LEFT_LITTLE_STATUS CHAR(1),
LEFT_LITTLE CHAR(4000),
IS_ACTIVE,
IS_DELETED,
CREATED_DATE DATE,
CREATED_BY CHAR(50),
MODIFIED_DATE DATE,
MODIFIED_BY CHAR(50)
)
Thanks Jeff!
each rows’s blob should have a file reference
export a table with blobs in sqldev using the LOADER format, you’ll see what I mean then
sqlci ver 20 can not connect to 12.1.0.2 db as /
[[email protected]] $ ./sql / as sysdba
SQLcl: Release 20.2 Production on Sun Aug 16 09:03:37 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
USER =
URL = jdbc:oracle:oci8:@
Error Message = no ocijdbc12 in java.library.path
USER =
URL = jdbc:oracle:thin:@127.0.0.1:1521:l64900091
Error Message = IO Error: The Network Adapter could not establish the connection
USER =
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connection
USER =
URL = jdbc:oracle:thin:@localhost:1521/xe
Error Message = IO Error: The Network Adapter could not establish the connection
Username? (RETRYING) (‘/ as sysdba’?)
USER =
URL = jdbc:oracle:oci8:@
Error Message = no ocijdbc12 in java.library.path
USER =
URL = jdbc:oracle:thin:@127.0.0.1:1521:l64900091
Error Message = IO Error: The Network Adapter could not establish the connection
USER =
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connection
USER =
URL = jdbc:oracle:thin:@localhost:1521/xe
Error Message = IO Error: The Network Adapter could not establish the connection
Username? (RETRYING) (‘/ as sysdba’?)
[[email protected]] $ echo $LD_LIBRARY_PATH
/opt/oracle/product/12.1.0/racdb12.1.0.2.200414/lib:/lib:/usr/lib:/usr/local/lib
[[email protected]] $ echo $ORACLE_HOME $ORACLE_SID
/opt/oracle/product/12.1.0/racdb12.1.0.2.200414 l64900091
tell me about your database? when did you install it? what did you name it?
does this work?
sql user/[email protected]:port/SERVICE
Hey Jeff ^_^,
I created a 20c (preview version) on VM server of OCI (Oracle Cloud Infrastructure) and use a SSH client tool (SecureCRT 7.3) to be able to connect it (providing username “opc” and a private SSH key file and then sudo su – oracle), but if using SQL Developer I always never connect to 20c when using the basic connect type: Hostname (a public ip address), Port Number, Service Name, SYS user and Password, SYSDBA role). At the same time I have also find the WALLET info on file SQLNET.ora and TNSNAMES.ora but I don’t download those WALLET file to my computer on SecureCRT.
Another problem is running SQL Developer 20.2 on my Windows 10, after popping up the loading process bar with graph then less than one minute it disappears, SQL Developer doesn’t open.
Could you help me separately troubleshoot them?
Best Regards
Quanwen Zhao