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,793 Comments
Hi Jeff,
SQLDeveloper is a fantastic tool and I use it very often. Its a one stop shop of lot of things. And that is the issue now –
As you know people have and I have 25 or could be 50 DB connections **with** passwords to prod and critical environments.
Given all the (bad) publicity in the media on passwords – Can you please think of following suggestion ?
**Can we have a MASTER PASSWORD ability to get-in SQLDeveloper?
If you set it — you must enter than to expose all the profiles. Else not — this will make me feel so much better
Is this already asked before? I for one will feel saving passwords saves time but presents uneasiness ….
Please provide feedback – eagerly awaiting ….
Thanks much
Sudhir
I think your concerns are valid but I’d offer an alternative, two factor authentication. The database and SQLDev both support it.
If someone gets onto a machine, many things are already compromised..so if two factor isn’t an option, spend more time securing the machines.
You could also disable the ability to save passwords to begin with.
Jeff,
thanks for your feedback. However, tongue-in-cheek here but I may not be kidding if I say you guys have introduced 100s and 100s of features that are way way more complex.
What I am pointing out is just a simple change. A blank screen and user enters master password. Obviously you are already storing encrypted passwords.
Is this too much to ask? Especially your motto has been to *improve* productivity. I do want to save passwords as it leads me to have quicker response time and avoid repeat action injury 🙁
However, you don’t seem to think that is useful?
Additionally, enterprise backups of desktop will not allow anyone to restore and access DB information. I can’t see any downside to getting a peace of mind with this investment (may be very little efforts considering complexity of this is very simple for you guys)
Thanks again for all you guys do!
What I’m saying is there are already features that handle your requirements…which means you can have what you want today,without us writing any code.
Desktop security, lock machines down automatically and require passwords..this will protect everything, not just SQLDev and your orcl passwords.
And even better 2 factor authentication, even if someone gets ahold of your password, they can’t get in without the physical key.
At my new job they store sql files as .txt files for reasons I won’t get into. I want to keep using SQL Developer, but if I can’t figure this out I may have to switch to the sql editor they use. When I open .txt files in SQL developer, I lose all SQL color-coding as well as the ability to actually execute the script. I can’t go changing file extensions everywhere to .sql because others need them as .txt. Is there any way to get sql developer to treat .txt files as .sql files?
I’m on vacation without my computer until Tuesday. I’ll look then, but I’m sure there’s a way.
Not a question, but a comment. I had SQL*Developer in Windows 7 open with a lot of unsaved work. The UI stopped responding. I could move and resize the window. I could drag other windows across it. Each time, SQL*Developer updated its window without a flaw. However, I couldn’t click anything. Unresponsive. I right-clicked on the title bar and selected “close”. I was amazed to see SQL*Developer ask me if I wanted to save each item of unsaved work. Wow! When I reopened SQL*Developer, everything opened with no issues. Nice job!
Good to hear, I think 🙂
Hi Jeff,
I was trying to send some data from inside my form to Excel and I tried what you said in the community forum:
SET SQLFORMAT CSV
SPOOL c:\users\user\data.csv
SELECT * FROM EMPLOYEES;
SPOOL OFF
Problem is the command Set sqlformat csv gave me an error. I tried it in SQLPLus to see if it works there and it didn’t either. Would you please help me with this? Thank you.
csv lowercase…
It is only available in SQLDev and SQLcl
Oh OK, thank you very much.
Hello Jeff
Can we use SQL Developer 17.4 to connect to Sybase IQ and migrate the Sybase to DB to Oracle DB, by connecting to Sybase through , Jconnect plugins.
Otherwise please provide the right plugin which could be used for above migration purpose, we are ok to do Offline migration for data also.
No we support ASE only…
v18.1.0
How do I get from Sessions SQL_ID to details of the Real Time SQL Monitor?
The SQL_ID from Session does not show in the Real Time Window
By the way, is there any way to increase the Font Size in the various dialog boxes? I am using the Oracle Look and Feel. Tried Windows and that is just ugly
I’m on vacation and away from my PC..is it not listed in the grid on the page of available reports?
Hi Jeff,
In the leftmost pane you can place a filter on a schema’s tables by right clicking on “Tables”.
But in the filter expression, how do you protect wildcards?
e.g., I want to search for the literal ‘_RR_’. How do I protect ‘_’ from being interpreted as a wild card?
AFAIK, you can’t. I’ve tried and failed. I’m out on vacation for a week, so you might get a better answer if you post to our official forums.
Workaround is to use the Schema Browser and the client-side filter, that should work just fine.
If you open the View > Log > Statements panel, you’ll see how we’re constructing the SQL to filter the tree objects.
v18.1.0
I am trying to find dependencies on a particular package A
On the Package Body — the dependency tab shows Package B
The Find Database Objects does not show anything to do with Package B
How do I find the dependency of A on B
Code in A uses Views which call Functions in B — but should this not show someplace in Find Objects?
I think this feature might be borked, consulting with DEV to make sure we’re not both doing something ‘wrong.’
Hi Jeff,
When using SQL Developer 18c, I am performing a “create like” on a user. Everything seems to work perfectly with the exception that the “profile” of the user being copied is not put in the “create like” sql. Is there some type of environment setting that I don’t have enabled?
Thanks,
Dave
I noticed very strange behavior
When I change the ojdbc8.jar stored in the jdbc\lib folder, it does not pickup the modified or patched version, unless I edit the sqldeveloper.conf file. This makes me thinking is that it is somewhere caching this file and does not lookup the newer version
SQL Developer version : 17.4.0
OS : Windows 10 1607
Java 8
Is there a way to clear the cache or force SQL Developer to use updated jar files. I could not find any documentation.
ojdbc8.jar has various bugs and I download the patches from Oracle and manually patch the ojdbc8.jar file. I want to make sure that SQL Developer picks up the new file.
I use the below batch file to start my SQL Developer
@echo off
set IDE_USER_DIR=”C:\abc\%USERNAME%”
start /d C:\abc\local\sqldeveloper\sqldeveloper\bin\ C:\abc\local\sqldeveloper\sqldeveloper.exe
>>When I change the ojdbc8.jar stored in the jdbc\lib folder,
You’re not supposed to do that. It’s unsupported.
In that case SQL Developer will fail to connect to Oracle 11.2.0.4 database that uses the oid entry with TRANSPORT_CONNECT_TIMEOUT=3. As in 12c Oracle supports millisecond for this parameter, there is a known bug in the delivered ojdbc8.jar which reads this parameter as millisecond and fails to connect and gives the error :
An error was encountered performing the requested operation:
Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of server requested in connect descriptor
Oracle support has provided a patch
Patch 25977056: AC: UNITS FOR TRANSPORT_CONNECT_TIMEOUT CHANGED WITH NO QUALIFIER
To fix this issue and below document specifies how to patch the standalone ojdbc8.jar
How to Manually Apply a One-Off Patch to the JDBC Thin Driver or to the Universal Connection Pool When Oracle Client or Database Home Is Not Available To Apply the Patch via OPatch (Doc ID 431463.1)
My Question is if we are not suppose to update the ojdbc8.jar (I thought we can use SQL Developer with appropriate user provided jdbc driver), How are we going to get fix for SQL Developer fails to connect to Database that uses oid entry with TRANSPORT_CONNECT_TIMEOUT=3 parameter.
Please respond. I can open a support case with Oracle if needed for SQL Developer. I am surprised that we are not allowed to update the ojdbc8.jar with the oracle provided patches for a oracle supported product. Please clarify as jdbc patches are posted all the time.
Those jar’s are provided for folks and their java applications.
But SQL Developer is different. It’s our app, if you change it, you’re on your own, and you’re no longer supported.
If you want us to pick up a new jar, you need to open a SR.
We generally pick up the latest drivers/jars as each release comes about. It’s possible that’s already in there for 18.1 – available now on OTN. I’ll check with the devs and if not, will see about getting it included for 18.2.
Thanks for the update. I understand it is not supported to update the ojdbc8.jar. However sometimes the delivered ojdbc8.jar breaks and Oracle support has fixes that we can manually apply. If you can explain how the caching of jar files work, it will be easier for us to use it till we get the official fix in newer versions.
As I have said earlier, only workaround I found is to update the sqldeveloper.conf file and restarting the SQL Developer and it picks up the updated jar file. However not sure if this is the best way.
I fully understand that using in this manner, it will be out of support from Oracle.
I will test 18.1 and see if the fix is there or not. If not will wait for 18.2
that patch fix was delivered for the 12.2.0.2 driver, which hasn’t officially been released yet. We’re running 12.2.0.1. When that comes out, or if it comes out as an 18 jar, we’ll pick it up.
What you’re doing isn’t supported by us, but it would work.
Hi Jeff,
Could you please explain the difference between code generated when using:
1. Synchronize with Data Dictionary
2. DDL File Editor ?
I understand that the first approach creates diff statements, but if we are creating new table shouldn’t these two generate the same output?
Automatic indices and Table DDL transformation are missing although they have been set up.
Probably. I think when you’re generating DDL there’s a place to enable the DDL transformations, so be sure you’ve done that.
Another quick question regarding custum transformations.
I know that it is possible to apply the transformation to the whole relational model by right clicking on the model, but is it possible to apply the transformation in similar manner to a subview or an object?
I don’t believe so…not without coding that into the transformation bits itself.
You might get a better answer if you ask on the Data Modeler forums though.
Thank you!
When using SQL Developer to do a Database Export, a snippet of the resulting DDL is:
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOPARALLELCREATE TABLE MRDP01_OWNER.ADDRESS_R
(
ADDRESS_ID NUMBER(6, 0) NOT NULL
, REV_DT DATE DEFAULT SYSDATE NOT NULL
, REV_BY VARCHAR2(8 BYTE) NOT NULL
, ADDR_LINE1 VARCHAR2(100 BYTE) NOT NULL
“NOPARALLELCREATE TABLE” is not right, it should be “NOPARALLEL CREATE TABLE” and fails when I try to import the exported ddl.
What am I doing wrong?
Thanks,
Dan
Looks like we’re doing something wrong, not you. Can you share the ddl for the 2 tables so I can try reproduce locally? Also, what version of SQL Developer are you using?
Jeff,
Thanks for your quick reply. I am using SQL Developer 18.1.0.095.
The DDL for 3 tables which have the bad syntax is 266 lines long. Might not be so good to paste here. Can I get it to you another way?
Thanks,
Dan
[email protected]
Hi jeff,
Greetings of the day!
I want genarate excel files(.xlsx) from sql developer ,Here is the scenario.
I had 10 sql queries .each produce one table.I want to put each qurey result in one work sheet and all work sheet’s in one excel file .
Above 10 sql queries run multiple times and produce excel file same as above mentioned .
Example: query1 –>worksheet1
query2 –>worksheet2
………………………………………….
………………………………………….
query10 –>worksheet10
________________
excel file1
like this way i want produce more excel files
We can generate 10 spreadsheets for 10 queries. Or, you can create a VIEW for each query, and we can export the 10 VIEWs (which would be your query), to separate sheets in one Excel file.
Hi Jeff,
I’m able to use SQL Developer 4.1.5 (yes, we’re trying to get to 18.1 soon) to schedule a nightly RMAN backup in DBMS_SCHEDULER on a 12c database very quickly and easily. Is there a way to do the same thing on an 11g database using SQL Developer? Thanks,
Mark
How are you doing it for 12c? Did you try and fail for 11g?
I created a “Script” job type with a “Backup” script type with a “connect target /” followed immediately by an RMAN run block in each of my 12c databases. It runs like a champ every night straight out of DBMS_SCHEDULER. However, the Script job type doesn’t exist in 11g so, I’ve tried numerous times to create an “Executable” job type and associate a valid credential with it. But I always run into “ORA-27369: job of type EXECUTABLE failed with exit code: Operation not permitted”.
My executable (bash shell script on Linux with permissions wide open) exports the ORACLE_SID and ORACLE_HOME and then does:
$ORACLE_HOME/bin/rman target / nocatalog << EOF
run {
backup database;
}
exit;
EOF
Is there a better way to do this against an 11g database? Thanks,
Mark
See the docs and Example 29-5 Creating a Detached Job That Performs a Cold Backup
Yep, tried that very thing yesterday numerous times and it always looks like it’s going to work. However, the job goes into “RUNNING” status but never actually does anything, throws an error, or finishes.
I know just enough to be dangerous with rman an data pump – i suggest you share your code with the AskTom folks or post a note to the plsql or scheduler forums.
Sounds right. I was just wondering if you or any of your readers knew of any way to setup an 11g backup job through SQL Developer somewhat like a 12c db. It’s extremely slick on a 12c db using an RMAN Script in SQL Developer. So much so that we’ve moved all of our 12c nightly backups to DBMS_SCHEDULER jobs using SQL Developer. However, our 11g backups remain defined as OEMCC jobs until we can find a better method like SQL Developer has for 12c. Thanks,
Mark
Hi Jeff –
Using sqlcl distributed with 12.2 (SQLcl : Release 12.2.0.1.0 RC). Linux x86.
If I run sqlcl as the oracle user, root user, other users, everything seems to be fine.
For some reason, my personal user, as soon as I touch any key after connecting and sitting at the SQL> prompt, I get:
SEVERE: null
java.lang.NullPointerException
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2725)
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2590)
at oracle .dbtools.raptor.scriptrunner.cmdLine.SqlCli.startSQLPlus(SqlCli.java:861)
at oracle .dbtools.raptor.scriptrunner.cmdLine.SqlCli.main(SqlCli.java:399)
I can connect to the databases just fine, I get to the SQL> prompt, and get the exception as soon as I press any key.
I’m using bash and have stripped my environment down to nothing but the oracle variables and JAVA_HOME, identical to other users that work, but can’t seem to fix or find the issue.
Thanks.
v18.1.0
When looking at a table data or Sessions (Monitor Sessions)
Is there anyway to set the Font of the headers. Currently the Line Number starts getting the …. when I reach to a 10000
I can’t reproduce this for any report, the line numbers show ok for me, even to say 11,250…can you send me a screenshot? Post it to say imgur and send the link here as a comment.
The font for the headers in v17.4 is fixed Courier size 10 while in 18.1 it seems to be Arial size 14 or 16
row # isn’t a column header though, so not sure what you’re seeing.
Removed the old installation and re-installed and this time did not opy setting from v17.4. Seems to have solved the problem
Is it possible in SQL Developer to run a query against an Oracle database connection and a Teradata database connection at the same time? From what I can tell you can only run a query against one database connection.
You can run 2 queries, one on an Oracle connection and one on a Teradata connection, simultaneously, yes.
But you can’t run a single query that hits both Oracle and Teradata.
Jeff,
Just installed SQL Developer 18.1.0.095.
In the Worksheet tab if I enter the command:
set sqlformat
and execute the result in the Script Output is:
SQL Format Cleared
In the Worksheet tab if i enter the command:
set sqlformat csv
execute the command (nothing in the Script Output) and then execute a SQL query results are not in CSV format. Works in sqlcl. Isn’t it supposed to work in SQL Developer?
Sorry, I was executing command, not script.
Absolutely that should work. Try an app restart?
Hello,
I just upgraded to 18.1 and find that the auto-format to UPPER Case for Keywords is no longer working. Is this a bug that might be fixed, or is there a setting beyond Tools –> Preferences –> Code Editor –> Format: Keywords Case: UPPER ?
Thank you for your feedback!
It was removed as the formatter already covers this use case. You’ll just use it on demand via Ctrl+F7. Make sure you set the case preferences appropriately first, of course.
Out of interest, why was this removed?
The auto Uppercase-ing was useful in a SQL Worksheet where you might be working on multiple SQL statements (it makes things easier from a readability point of view), and auto-formatting isn’t a suitable option.
It was causing problems, and since it was a formatting feature,it was decided to just have users invoke the formatter.
In 18.2, you’ll be able to configure the formatter to ONLY do the keywords/identifier casing.
Hi Jeff,
Is there a setting to make the highlighted occurrences stay highlighted even though one clicks with the mouse in the SQL editing window? Or even if one starts typing? I am asking this because sometimes it is extremely useful to have a term highlighted while coding.
Thank you in advance and congratulations for a great product!
Peter
— Athens, Greece
Thanks Peter!
Not super obvious, but once you have your code highlighted, hit the tab button to get your cursor back to the editor, then you can type. Your highlights will remain. If you mouse-click, they’ll go away though.
No, thank you! I would not have found this in a million years. Hope you fix this soon though!
Thanks again for the work-arround!
Peter
— Athens, Greece
Hi, Jeff.
Is there a way in sql developer that will automatically convert to UPPERCASE the data during import of CSV files?
No…but several thoughts:
v18.1.0
I run a script which calls other scripts and the output goes to a spool file. The script stops running part of the way.
In 17.4 the prior, the script completes and I also get a complete spool file.
W/o your script, there’s not much I can do. Can you build a simple test scenario that demonstrates the issue?
In the Logs I see a line
Source => o.d.r.newscriptrunner.ScriptRunnerContext
Message => Could not write
Hi Jeff
We are using ORDS 17.4 [without APEX , without PLSQL Gateway].
is ther any way to get requested URL (or my template path or my template ID) and remote IP from my GET handler (source type : query or collection) ?
regrading to owa_util.get_cgi_env it is working only source type ‘plsql’.
Thanks
Hi,
I’m probably overlooking something obvious to do with formatting SQL. I can get SQL Developer to format it nicely using Ctrl-F7, but is there a way to format it as a single line, e.g. going from
SELECT
wm_concat(column_name)
FROM
all_tab_columns
WHERE
table_name = ‘&TABLENAME’
GROUP BY
table_name;
to
SELECT wm_concat(column_name) FROM all_tab_columns WHERE table_name = ‘&TABLENAME’ GROUP BY table_name;
I currently do it by copying it into Notepad 2 and using its Align Lines command, but was wondering if there was something in SQL Developer.
Thanks
David
That looks like the opposite of formatting…you just need a macro to remove linebreaks, right?
Yes, but that would be too easy.
Thanks for the tip and prompt reply
What are the benefits of using Oracle Instant Client with SQL*Developer? The SQL Developer 17.4 User Guide references the ‘User Oracle Client’ to configure SQL*Developer to use it, but not why one would want to.
If you need something not available, or easily available via the JDBC driver.
What is the most recent version of Sql Developer? I have 4.0.3.16, and I see references to 4.2 as of Sept 2017. But what is this new numbering system 17.4, 18.1? Has the numbering system changed, or this is a different ‘flavour’ of Sql Developer?
17.4 and soon to be released 18.1.
Yes, we changed the numbering system.