Nearly 7,000,000 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!
7,557 Comments
Jeff,
UNC paths just don’t seem to work in SQL Developer 18.3’s file browser.
This is a problem for anyone forced to use the world’s most popular desktop OS at sites where new drive mappings are no longer allowed – for good security reasons.
I just realised that drag and drop from a Windows Explorer panel seems to work just fine…
Can you file a bug? We can take a look at it.
Hi Jeff,
Do you know if there are any plans in ORDS to extend the parameter types allowed in ORDS.DEFINE_PARAMETER to include TYPEs (e.g. record/table object types)?
It’s kind of already there when you auto-rest-enable a procedure with object type parameters, but it would be great if they could be used in your own custom ORDS modules/handlers.
Is it possible to start SQL Developer with a series of command line options specifying the username, password, and most importantly, a connect string that specifies a Oracle Dataguard environment (i.e. Failover=on, 2 hosts specified).
We looked at the mkconn option in SDcli, but this doesn’t seem to support the required connect string format.
Use case: We are trying to use CyberArk to control/inject the credentials for SQL Dev sessions, and run them inside the privileged manager session.
No, but you do have SQLcl…
Hi Jeff,
Thanks for coming back to me so quickly……
How would we achieve this with SQLcL? – The available documentation doesn’t help…..I’m a CyberArk/Security guy and I’m having trouble finding *anyone* who knows how to achieve this…..to coin a phrase – ‘you are my only hope’…..
Regards,
Chris.
SQLcl is a command line interface for Oracle DB.
> sql hr/oracle@localhost:1521/orcl
that will launch a session using the EZ Connect syntax
I’m not a CyberArk/Security guy – we do support 2-factor authentication in SQL Developer. Is that your end-goal?
Hi Jeff,
CyberArk lets us run SQL Developer in a ‘sandbox’ and inject the username/password for the connection without the end user ever knowing it/it ever being typed in on thier desktop. This approach lets us record the session, and place additional controls around it (yes – 2FA is one of them, but not the only one)
For simple connections we have this working by using AutoIT to push the connection details in via the SQL Developer GUI, but this approach is proving unreliable for more complex connection strings unsing TNS. We were hoping that the connection string/username/password could be provided as command line options, but it seems that this is not the case…..back to the drawing board!!!
Cheers,
Chris.
You can get PRETTY complicated with the connection strings…if you need help translating those, I can go bug our devs for help 🙂
Did this ever reach a successful or otherwise conclusion?
When double clicking on a table name in the left pane a tab opens on the right with multiple sub tab underneath it. If you click on another table the original tab closes and another tab opens with the data. Is there a way to have both tabs open?
Yes, pin the table.
Or go into preferences and enable auto-pin for opening objects.
See here.
Excellent. Many Thanks
Hi
I am running SQL Developer version 1.5.5 with Java 1.7.0_75. It works fine, and I am able to connected to the TNS connections I want using Connect Identifier instead of Network Alias.
I tried installing the new SQL Developer (18.3) and running with Java 1.8.0_191 but get the error
“no ocijdbc18 in java.library.path” whenever I try to connect. I am using the same Connect Identifier and also imported all preferences from the old version.
Any idea what’s wrong?
Thanks
set your connection type from tns connect identifier to network alias…if you insist on using connect identifier, you’ll need to properly setup an oracle client on your machine so we can use a THICK (oci) connection
It says invalid username/password when I try using Network Alias on the old version. It does the same on the new version.
How can I properly set up an Oracle Client so that I can use a THICK Connection?
It’s in the preferences, on the advanced page.
I set my Use Oracle Client address and Tnsnames Directory address to the same ones my coworker has, but after I restart my client (like it tells me to) and then click on the New Connection button, the SQL Developer just freezes, and I have to use Task Manager to exit out.
I also downloaded the 18c (18.3) database from here:
https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
It gives me SQL Developer version 17.3.2… I put in the same Oracle Client and Tnsnames Directory as a different coworker, and I also copied the tsnames ORA file and tsnames.ORA.bak file that is inside the network folder of my working 1.5 version of SQL Developer, which is inside a client_1 folder of 11.2, and I copied those two files into the network folder of the new database that I downloaded.
This time it gives me the same error “no ocijdbc18 in java.library.path” and does not freeze, but I cannot connect.
you have to configure sqldev 17.3 to use the thick client – preferences, database, advanced – pick ‘Oracle Home’ from the dropdown (not instant client), then point to the 18.3 home you downloaded/installed. Then restart sqldev and you should be fine.
Yes, that’s what I did. It gives me the same error “no ocijdbc18 in java.library.path” after restarting.
I have it set as:
Use Oracle Client [Check]
Oracle Home : file: /C:/Oracle/product/downloadedDatabase/
Use OCI/Thick Driver [Check]
Tnsnames Directory: C:\Oracle\product\downloadedDatabse\network\admin
that home doesn’t look…right…can you search for the ocijdbc18.jar file and make sure it’s in your path?
I only have ocijdbc.dll and ocijdbc.sym. One pair is in the bin folder and the other pair in the .patch_storage folder. Also, that path is the same one my coworker is using, which works for them.
You grabbed a DB install, not a client…it should be there, but would definitely be there in a Client distribution
https://docs.oracle.com/en/database/oracle/oracle-database/18/lnoci/instant-client.html#GUID-7392A747-E097-4326-A194-62CE07093136
I downloaded the instant client 18.3. What should I do now? Do I move the instantclient_18_3 folder to the database folder?
NO
extract to a new directory
add to the OS PATH – as the installation directions say
It is now on a folder on my desktop. I could not really understand the section you linked me. I am new to SQL Developer and to Oracle itself. How can I add the directory to my OS Path?
Also, the instant client I downloaded has the same ocijdbc files… none of them being .jar files. One is .dll and the other .sym.
Sorry for being difficult. I can contact support if you think that would be better.
If you are new to Oracle, I would suggest opening a ticket with your desktop support folks.
90+% of users do NOT need an oracle client – connections via jdbc/tnsnames should work just fine out-of-the-box.
Apparently I need to purchase support to submit a ticket?
Yes, official Oracle Support isn’t free.
I meant you should support a ticket at Vanderbilt – or wherever you work.
Apparently support is not very helpful here in regards to SQL Developer. Would you mind answering my last questions?
1. How can I add the directory to my OS Path?
2. Also, the instant client I downloaded has the same ocijdbc files… none of them being .jar files. One is .dll and the other .sym.
If you are unable to help, that is fine. I do have a working version of SQL Developer (1.5). I just thought I might try updating to a newer one, but it’s been hard.
1 – that’s an OS question…google ‘edit path’ for Windows or OS X or whatever
2 – it’s a dll, not a JAR, so my bad
if going from 1.5 to 18.3 – i would suggest
extract 18.3 to a NEW directory
create your connection from scratch, and use the Network Alias from TNS – not the identifier.
My Windows 7 OS path had:
“C:\Oracle\product\11.2.0\client_1\bin”
in it twice. I changed one of these instances to:
“C:\Oracle\product\other client\bin” About -> Properties and scroll down to java.library.path, “C:\Oracle\product\11.2.0\client_1\bin” is listed in that path. My idea was to add “C:\Oracle\product\other client\bin” to the java.library.path, but I don’t know how. I searched for how to do it but was unable to follow.
Do you think that is what I need to do? And if so, do you know how to append the java.library.path?
Thanks
See my latest comment – skip this client stuff. Or find someone local to help you.
I’m not sure what happened with the format and content of my last reply, but I must have hit backspace and had things highlighted. It should have read…
My Windows 7 OS path had:
“C:\Oracle\product\11.2.0\client_1\bin”
in it twice. I changed one of these instances to:
“C:\Oracle\product\other client\bin”
Now when I try to connect, I get the message: “Internal Error: Fetch error message failed!”
I noticed when I went to Help -> About -> Properties and scrolled down to java.library.path, “C:\Oracle\product\11.2.0\client_1\bin” is listed in that path. My idea was to add “C:\Oracle\product\other client\bin” to the java.library.path, but I don’t know how. I searched for how to do it but was unable to follow.
Do you think that is what I need to do? And if so, do you know how to append the java.library.path?
Thanks
After trying for many hours over several days, I think I solved my issue…
1. I downloaded a new SQL Developer version 18.3.
2. Under Tools -> Preferences -> Database -> Advanced, I checked Use Oracle Client and made it “Instant Client: file:/C:/instantclient_18_3/”.
3. I replaced the “other client” from my OS Path and replaced it with the path for the Instant Client.
4. I unchecked Use OCI/Thick driver
5. My Tsnames Directory is set to “C:\Oracle\product\11.2.0\client_1\network\admin”, which is working, so I’m not going to touch it. I believe this is where it gets the information for the Connect Identifiers, which I am using since I get invalid username/password errors when I try to use Network Alias.
Thanks for the help
Hi Jeff –
I’m getting the same “no ocijdbc18 in java.library.path” as was previously reported, but can’t see a resolution?
I have found that the workaround is to set TNS_ADMIN, but I’m puzzled.
On my machine, I have OracleXE 11g, Oracle 11g and Oracle12c running (and sometimes 18cXE in a VM :)), using the Oracle12c listener.
Everything works from sqlplus.
[How do I format the code below?]
Trying to connect to the 11g XE instance (SID=XE):
>tnsping XE
TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 03-NOV-2018 15:37:03
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
D:\app\Oracle12c\product\12.1.0\dbhome_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (0 msec)
D:\...\bin>sql /nolog
SQL>show TNS
TNS Lookup locations
--------------------
1. USER Home dir
C:\Users\Andrew
Location used:
-------------
D:\app\Oracle11g\oracle\product\11.2.0\server\network\admin
Available TNS Entries
---------------------
SQL> conn cda@xe
Password? (**********?) ***
USER = cda
URL = jdbc:oracle:oci8:@xe
Error Message = no ocijdbc18 in java.library.path
USER = cda
URL = jdbc:oracle:thin:@xe
Error Message = IO Error: Unknown host specified
USER = cda
URL = jdbc:oracle:thin:@xe:1521/xe
Error Message = IO Error: Unknown host specified
SQL>exit
>set TNS_ADMIN=D:\app\Oracle12c\product\12.1.0\dbhome_1\network\admin
SQL> show tns
TNS Lookup locations
--------------------
1. USER Home dir
C:\Users\Andrew
2. TNS_ADMIN
D:\app\Oracle12c\product\12.1.0\dbhome_1\network\admin
Location used:
-------------
D:\app\Oracle12c\product\12.1.0\dbhome_1\network\admin
Available TNS Entries
---------------------
DEV11G
DEV11G
LISTENER_ORA12CSE
LISTENER_ORA12CSE
LISTENER_ORA12DEV
LISTENER_ORA12DEV
ORA12CSE
ORA12CSE
ORA12DEV
ORA12DEV
ORACLR_CONNECTION_DATA
ORACLR_CONNECTION_DATA
PDBDEV01
PDBDEV01
PDBORA12CSE
PDBORA12CSE
XE
XE
XE18C
SQL>conn cda@XE
Password? (**********?) ***
Connected.
SQL>
and before you ask 🙂
>sql cda@localhost:1521/XE
SQLcl: Release 18.3 Production on Sat Nov 03 16:03:21 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Password? (**********?) ***
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> exit
I’m assuming since you have 11g xe installed, your path has sqldev going there looking for the thick driver to do a OCI thick connection (that’s the OCI bits) on the first connection attempt.
sql cds@localhost:1521/xe does a thin connection, which is why that works. I would just do that.
Or, find the ocijdbc18 jar on your machine and manually add it to your path, or copy and paste it into a directory SQLcl will see.
I don’t understand why SQLcl is using the 11g tnsnames, when tnsping is using 12c? Shouldn’t they use the same one? How does SQLcl find an oracle_home when one is not specified?
Both 11g and 12c ORACLE_HOME\bin’s are in the path, but 12c is first.
But as you say I can just use ezconnect…
We are seeing an issue in SQL Developer 17.2 through 18.3 where for one schema on a 12.2 database, when you try to open any PL/SQL you will only see “create or replace” and the rest of the code does not show up. I have checked an old version of SQL Developer 4.0.3 and this issue does not occur. Any ideas?
check the code we’re running, it’s in the View – Log – Statements panel. Sounds like a privilege issue. Biggest change also from 4.0 and 17.2 is that the jdbc driver has changed. Also, grab version 18.3 it’s a year newer than 17.2.
Since Oracle is changing their license agreement for JDK 8+ and SQL Developer requires JDK 8 or 9…do they have plans to make SQL Developer more compatible with OpenJDK?
At the moment, i’m working to get it in writing that folks can use Oracle JDK for free with SQL Developer.
Since they require SERVER SIDE access, and this individual most unquestionably didn’t approach the server.
In any case, better believe it, in the event that you take a gander at the SQL Developer information import wizard, EXTERNAL TABLE is additionally an alternative.
An ORDS question – we have an existing application that can only send / receive Content-Type: application/x-www-form-urlencoded POST/PUT requests / results. It needs to send requests to a PL/SQL package and pass parameters.
Is ORDS compatible with Content-Type: application/x-www-form-urlencoded or can it be made compatible through some configuration?
Mmmm, I don’t think so. Form data and REST don’t mix that well… how are you doing this today?
We’re trying to migrate from using iAS to using ORDS. The client app that has been using the iAS service since around 2009 is using application/x-www-form-urlencoded. It is programmed in C and the programmer would rather not change that core code
Have you heard of mod_plsql? ORDS can satisfy those requests, but it’s not REST.
Hi
Thanks for your willingness to handle questions about SQL Developer.
Today I installed 18.3 (Oct. 29, 2018), and tried to connect to Oracle database but it showing an error msg “Test failed: ID error: The Network Adapter could not establish the connection”. Please help!!!
Thanks,
Basavaraj
See if you can ping the server the database is running on. Then make sure a firewall isn’t preventing you from getting there or to the port (which defaults to 1521). But, your error message basically says, hey, I can’t get to the place you’re telling me to go to connect to your database.
HI,
Thanks for your willingness to handle questions about SQL Developer.
Today I installed 18.3 (Oct. 29, 2018), and the first feature I tried to use appears to have hit an issue (different functionality than all prior versions).
To ‘reverse engineer’ a table’s DDL: When I am connected to a Schema and click on a table name, then ‘right click’, choose ‘QUICK DDL’ and then pick ‘Save To Worksheet’, it does NOT generate the full DDL statements anymore.
It now displays multiple “Build Before …” lines, as shown below:
**Tool Start**: Export Tool at 10.29.2018-15.46.46
Build Before Data Load Create
Any idea what the above details mean, and why did Oracle change this feature?
Thanks.
HI again,
I just now found out that the ‘tab window’ that the cursor ends-up located inside (after doing a ‘QUICK DDL’) is actually now the ‘export LOG’ tab.
Not sure why this is happening, but if I click one TAB back, then I can see the generated DDL text statements.
Sorry to waste your time – this situation was not intuitive (not sure why Oracle changed the tool’s behavior like this).
But now I know what is happening and can proceed without an issue.
Thanks, Scott.
No, I agree. For 18.4, these export logs go where they belong on the log panel and show more interesting info
Wow thanks for your quick response Jeff. After some more digging, I found that when passing the params as part of the payload body, the param names have to be all lowercase. I got it to work this way and am very happy about that.
I also tried the lowercase param names with the other method of passing the params as part of the header/URL (Query params) but that is still throwing the 500 error with the same message as before. I am using the Advanced REST client for Chrome:
POST /ords/ordstest//?vlocationshortname_in=MSN HTTP/1.1
HOST: :8080
content-type: application/json
cache-control: no-cache
content-length: 4
{ }
I have a function within a package that I have ORDS rest-enabled (using rest v 3.0.12). It has one varchar2 IN param, and returns varchar2.
I have verified that ORDS works with another function that doesn’t have any params. (if I do a POST with an empty payload, it returns the correct data)
However when I do a POST to the function with an IN param, it errors out 500 with:
SEVERE: interface oracle.dbtools.raptor.datatypes.DataValue is not a supported primitive datatype.
I found the following post which points to an Oracle bug. They were using ORDS 3.0.9
https://www.databaseusers.com/article/5979695/AutoREST+POST+to+table+with+CLOB+column
Has this bug just not been fixed yet? Is the ability to pass parameters to a PL/SQL package via ORDS rest broken?
Should work just fine, I don’t know of any bugs in this space.
I have examples on my blog of this very scenario.
If you could share a specific scenario, I can take a look.
Is there any way to block someone on the Oracle SQL Developer forum as I truly don’t want to every hear from “rp0428” again if I can help it?
Much appreciated
Ed Girard
on the post, Actions – Report Abuse
I’m not going to disagree with you, but i’ve tried myself before
Is there any support for executing single SQL statements from the command line through SQLcl or SQL*Plus?
Coming from postgres, psql -U user -d database -c “select * from info;”
Some examples here
Hi, need an advice about transforming error 500 into ORA- ERROR while using method POST. I have very simple insert and it is working, but I need to receive an ORA error trying, for example, insert null into not nullable field using curl command. Here is my handler
ORDS.DEFINE_HANDLER(
p_module_name => ‘mod_demo_tm’,
p_pattern => ‘t_demo_tm/’,
p_method => ‘POST’,
p_source_type => ‘plsql/block’,
p_items_per_page => 0,
p_mimes_allowed => ‘application/json’,
p_comments => NULL,
p_source =>
‘DECLARE
id DQ_PACKAGE_QUEUE.DQ_PACKAGE_QUEUE_ID%type;
BEGIN
INSERT INTO DQ_PACKAGE_QUEUE (WORKFLOW_RUN_ID_CREATER, PACKAGE_CD, PRIORITY)
VALUES (:WORKFLOW_RUN_ID_CREATER, :PACKAGE_CD, :PRIORITY)
RETURNING DQ_PACKAGE_QUEUE_ID INTO id;
:location := id;
:status := 201;
END;’ );
maybe you can advice where to read about it? Simple adding EXCEPTION doesn’t work
Good idea. See today’s post!
Excuse me, so should I add the new param “response” and use it, and the main question – should I add exception handling into the GET handler, not POST?
hoping so much you answer, Tanya
Your GET had a 500 response because your plsql is not working. Add exceptions to catch and properly handle those errors. It’s up to you if you want your handier to have a proper response with a message or just a status code.
For an insert, I would at least return a link to the new record on success though.
Hi Jeff,
I love the SQL Developer for my work. One thing however is really missing:
In the connections tab I can only use 2 levels (root and one folder). It would be great I it were possible to have several levels (I have dbs in several countries, several plants and several dbs and schemas in each plant). It´s very hard to have a logical structure at the moment).
Would also be great if it were possible to select the intended folder directly when I create a connection.
Thanks a lot in advance.
Upgrade to 18.3, you can have as many levels as you want now 🙂
On the instance viewer, somehow I’ve managed to make the Top Sql and Memory tabs disappear. How to I get those to come back?
Hi Jeff
I am have been working on custom design rules implementation with JavaScript (Oracle Nashorn Engine) in SQL Data Modeler 18.3.0.
For complex code a debug tool will be super handy to spot bugs in the code faster and I am missing this tool a lot there.
I noticed that the is a debug tool available in Custom Transformation Scripts, which is awesome. I was wondering if is it possible to implement a Debugger in the custom design rules interface?
Thank you in advance for your attention.
Regards,
Anderson Bestteti
Hi
I am using Oracle SQL Developer 18.3 and Oracle 12.1 database.
When I am trying to import a data dump using the “Data Pump – Import Jobs” wizard, I am getting the “Exception: ORA-31626: job does not exist” error.
Please let me know how to fix this error.
Thanks in advance
Vadi, Bengaluru, India.
You haven’t shared enough information to help you, Vadi.
Try again, this time take the Code/PLSQL we have generated in the wizard.
Copy and paste that to a SQL Worksheet.
Execute with F5 – share the code and the results here.
SET sqlformat csv
spool D:\Users\MyUser\Documents\objects_data.csv
SELECT col_1 , col_2 from My_Table;
spool off
When I am pasting the above commands in sql developer they are working as expected and I am getting complete output in objects_data.csv file.
But when I am running these commands using @myexport.sql file in sql developer then I am getting error java.io.IOException: Pipe closed.
How can i get export of data automatically at the end of executing a set of .sql files?
Hello!
Recently I’ve changed my version from 4.2 to 17.4. I always use the option “Change case as you type: Upper Keywords, Lower Identifiers”. And now my code editor makes a singular letter and the word “id” upper case, but it wasn’t the same behavior in previous versions. For example:
SELECT id FROM dual d; -> SELECT ID FROM dual D;
Should I change the version to the last one or I can change preferences somewhere to avoid uppercase at my small aliases and “id”?
Change your formatter options – that should cover the CASE used for the insight feature.
Why upgrade to 17.4, when 18.3 is available?
I am really used to the function “Change case as you type”. So when I didn’t find that in 18.3, although Help Center still had information about that, I decided to try one of the previous version. Maybe it’s time to start to use Ctrl+F7 for formating my code…
Thank you for taking the time to reply!
Not a question, just wanted to say I enjoyed your SQL*Loader post this morning. Thanks for all the information sharing you do, Jeff!
Yes, though I did wonder why external tables weren’t mentioned.
Because they require SERVER SIDE access, and this person most definitely didn’t have access to the server.
But yeah, if you look at the SQL Developer data import wizard, EXTERNAL TABLE is also an option.
All versions including 18.3
1. Connections > “sys”
Under the folder “Other Users” pick any user > Edit.
There is no information about the user profile.
SQL also do not have the profile information.
2. DBA > Connections > “sys”
Under the folder “Security” > Users > pick any user > Edit.
There is also no information about the user profile.
SQL also do not have the profile information.
3. DBA > Connections > “sys”
Under the folder “Security” > Users > pick any user > Create like…
The new user will be created with the “Default” profile
4. DBA > Connections > “sys”
Under the folder “Security” > Profiles > pick any profile.
There is no list of users with the current profile.
I love SQL Developer. Does this mean that I love myself?
yeah, our current profile support kind of sucks – i’ll try to work that in for v19.1
if you love sqldev, we’re at least in the same club 🙂