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,937 Comments
I am loving sqlcl and the /* csv */ feature, too! However, when I try to execute this statment – to generate a select statement – it does something kind of odd.
SELECT ‘select /*csv*/ * from (‘ FROM DUAL;
In sqcl the result is this:
SQL> SELECT ‘select /*csv*/ * from (‘ FROM DUAL;
“‘SELECT/*CSV*/*FROM(‘”
“select /*csv*/ * from (”
in sqlplus the result is this (the “good” result)
v717627dba@rasuai3> SELECT ‘select /*csv*/ * from (‘ FROM DUAL;
‘SELECT/*CSV*/*FROM(‘
————————-
select /*csv*/ * from (
Any suggestions for a workaround?
Thanks again for providing all this info on sql developer and the wonderful new sqlcl!
Kate
Jeff, I found a workaround using the set sqlformat option. Hope the other is handled soon though.
Thanks again for all the information you’ve distributed.
Kate
SQL Developer is my very first choice tool and I really appreciate it.
I develop PL/SQL Stored procedures APIs and I like test/debug using pipelined functions returning CLOB containing formatted text data.
Is there a way to change the pop-up window ‘VIEW VALUE’ to a fixed font ?
With the actual proportional font my data seems mangled…
Trying to install Data Miner and getting error
Error opeing build node editor and details shows
java.lang.ArrayIndexOutOfBoundsException
Version 4.1.0.18
Build MAIN-18.37
Database 12.1.0.2
Installing in Pluggable database
Problem solved by using v4.1.0.19.07
Is there a way to import json data into a table like we can csv data?
Not today, but it’s on our list.
Hi Jeff,
Love the blog … and the ‘that’ before the name 🙂
Apologies if this has already been asked or if I am missing an obvious answer.
When using “Database Export …” wizard in SQL Developer 4.1.0.19.07 I don’t see an option to export the DDL for the scheduled jobs. I do not recall if this option was available before. Are there plans to add this to the SQL Developer?
Thanks,
Nikolay Petrouchev
Hi Jeff,
I am using SQL Developer 4.1. on ubuntu 14.0 LTS.
Every time when SQL Developer is started, for each saved connection, I see listener log entries in the server side listener log with PROGRAM and USER attributes as ‘null’ (refer to following listener entry for more details)
(CONNECT_DATA=(SID=XYZ)(CID=(PROGRAM=null)(HOST=__ABC__)(USER=null))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*.*.*.*)(PORT=*****)) * establish * XYZ * 0
1. Why SQL Developer should send those empty connections to all respective DB server listeners for every startup?
2. If this is the default behavior, then is there any option to disable the same?
OR
Is it possible to log the real USER and PROGRAM attributes to make sure those entries are not orphan?
Awaiting your reply with interest.
Thanks,
Chaitanya
we ping each server at startup to see how far away they are, you can see the results by mouse hovering over the connection names in the tree
there is a property you can set in your .conf file if you want to disable this, let me know if you want it
First of all, thanks for reply.
Well, I would definitely ask for that property to disable and once I do that I expect there would be no deviations w.r.t. how SQLDeveloper work.
I don’t understand the ‘deviations w.r.t how SQLDeveloper work’ bit, but add this to your conf file
AddVMOption -Dsqldev.tnsping=false
Hi Jeff.
I recently installed SQL Developer v4.1.0.19 on a Windows 7 64-bit machine, using the 32-bit JDK (v1.8.0_45). It takes approximately 2 minutes to launch, hanging on either “Restoring Editors” or “Restoring Windows”.
I have tried doing each of the following (as suggested by various forum posts), but the issue is still present:
– Excluding the entire SQL Developer folder from virus scanning
– Reinstalling SQL Developer to a new folder
– Deleting various configuration-related files/folders and re-launching
I have also tried using the 64-bit JDK (v1.8.0_45) instead, which technically resolves the issue, as the startup then takes only a few seconds. However, I then cannot connect to any database, as the Oracle client on my system is 32-bit (as is required by the applications my company develops). E.g., clicking the “Test” button in Properties for any Connection returns the following: Status : Failure -Test failed: C:\Oracle\product\11.2.0\client_1\BIN\ocijdbc11.dll: Can’t load IA 32-bit .dll on a AMD 64-bit platform
SQL Developer v4.0.3.16 (which I still have installed on the same machine), with 32-bit JDK v1.7.0_71, does not have this issue. I have also tried using the 32-bit JDK v1.8.0_45 with SQL Developer v4.0.3.16. Using this JDK, the 4.0.3.16 version displays a warning on launch stating that Java versions of 1.8 and greater are not supported, but once the message is dismissed, it opens within seconds.
I suppose I could make database connections via the 64-bit JDK if I installed a 64-bit Oracle client, but I would prefer to do this only as a last resort. Would you be able to suggest anything else that might resolve this issue?
Thanks in advance for any help you may be able to provide.
easiest fix, don’t use thick connections with your 64 bit jdk setup in 4.1
although i don’t have the slightest idea why a 32 bit vs 64 bit jdk would have that affect with startup time with SQL Developer
Thanks very much, but I’ve never actually done any JDK setup apart from the initial install, so I’m not sure how to do that. Could you please let me know what setting(s) to change, and where?
Never mind, I was able to find it: In SQL Developer, Tools -> Preferences -> Database -> Advanced, uncheck “Use OCI/Thick driver”. I also had to specify the Tnsnames directory.
Now startup time is just a few seconds for 4.1, and there are no connection issues. Again, thanks very much for your help.
You have to install Java. On Windows it has an installer. Download a 32 or 64 bit JDK for Windows, version 8. Run the installer.
Then run SQL Developer.
SQL Developer is my very first choice tool and I really appreciate it.
I develop PL/SQL Stored procedures APIs and I like test/debug using pipelined functions returning CLOB containing formatted text data.
Is there a way to change the pop-up window ‘VIEW VALUE’ to a fixed font ?
With the actual proportional font my data seems mangled…
Thanks in advance and have a nice day
PS: sorry my poor english
Using SQL Develpoer v4.1.0.19, can I “Generate DB Doc” for a SQL Server database to which I’ve successfully connected via the jtds v1.3.1 (JDBC) driver? I don’t find a “Connection, R-Click”, Generate DB Doc option. 🙁
If not, any chance I can setup an Oracle 11g Express Edition instance, connect to my SQL Server DB, “Connection, R-Click”, “Migrate to Oracle” and then from the migrated Oracle DB “Generate DB Doc”?
I’ll try the later to see if it works…
The later did indeed work 🙂 Nice!
A bit of a long haul though to get generated docs. I’d obviously prefer something more “push button”. Can/should “Generate DB Doc” work for a connected SQL Server database (JDBC)?
SQL Server…? We support connections to SQL Server for one reason only – to migrate them to Oracle 🙂
LOL. Fair enough 🙂
Still. You have a nice tool and I would have appreciated using it against the odd SQL Server database from time to time.
On an aside, I was pleasantly surprised by how easy it was to spin up an Oracle instance and migrate the SQL Server DB. Nice!
Hi Jeff,
I have a really silly question. I’m on sql Developer 4.1.0.19, on a mac. I can’t remember how to switch to view from windows to mac. I know it’s under Preferences, but I can’t find it to save my life. I just want to be able to scroll horizontally!
Thank you!
Katie
mmmm, not sure exactly what you mean, do you think it’s the Look and Feel setting on the Environment page?
Ah! Yes, that was it!
Thank you 🙂
Jeff,
My packages have first lines like so:
create or replace package “LOCAL_IMPORT_P” as
but when I compile a package, then close it, then reopen it, it has added 8 additional spaces between “package” and “LOCAL_IMPORT_P”. If I compile again, not even modifying the package, close it, reopen it, there will then be 8 more spaces for a total of 16 additional spaces. This happens in all my packages, on windows machines and mac machines. I opened the package in TOAD for Oracle and it correctly displayed one space. I scoured as many preferences and settings as I could find but I was not successful at getting SQL Developer to stop doing this behavior. I googled and couldn’t find anyone else asking about the issue. The extra spaces are driving me nuts. I’m using version 4.0.2.15. Any Ideas?
anything on the create or replace line is being generated on-they-fly – so we’re not corrupting the code at least – you can see this by how the other application shows it correctly.
So we’re doing something weird there with some whitespace or control characters.
Before we get too crazy, there have been two updates since 4.0.2.
You can try either 4.0.3 or version 4.1.
Version 4.1 has more bug fixes than 4.0.3…
I installed 4.1 on my mac and didn’t transfer any preferences, just in case. 4.1 behaves the same. Possibly notable is: if I compile in 4.1, close the application, open 4.0.2 and open the package, it has displays however many extra spaces 4.1 had added when compiling. Well, what I’m attempting to say is their compiling affects each other.
I forgot to mention in my initial post I turned invisible characters on (as well as pasted it into another program that shows invisible characters) and it does appear to be the space character eight times.
Is SQL Developer adding a NO_PARALLEL to my queries? Can I change that default behavior? I looked but don’t see how.
Thanks,
DJ
kinda, not really
two bugs:
so the ‘not really’ is b/c the hints aren’t seen by the optimizer b/c of #2
when our next patch goes out, you won’t see the bad hint, or any hint at all, in the SQL Worksheet, unless you put it there yourself
Could you remove this altogether and provide a way for users to specify the hint?
I don’t see why SQL Developer should be adding no_parallel, surely it should be left to the optimiser? e.g. our dev machine is fairly slow, but a highly parallel architecture, so some parallelism is useful on large full scan queries.
Always adding “parallel(tab,2)” or “parallel(tab,3)” would seem more sensible to me, it wouldn’t have any effect if indexes were being used, but should speed up most full scan queries noticeably. You could maybe add a user option and default it to parallel,2, then users could disable or increase the parallelism if they wanted.
Sorry, I forgot to mention – my point above was in relation to the table editors. No hint at all in the worksheets seems like the right idea.
don’t see why – too many end users opening tables, never closing the editors, consuming all of the parallel slaves on the server meant for doing real work
if you know what you’re doing, and you want to get the data quickly, use the worksheet
i’m not a fan of baby-sitting users, but we made a measured exception here
Feature Suggestion –> Include a tab showing the visual representation of the data stored in objects. This could be handy for beginners using partitioned tables, checking to see the right data is in the right partitions, and for accurate sizing of table clusters.
It would also help in the understanding of how objects store their information (for example, show an actual representation of the B-tree used in indexes etc. etc.).
Hi Jeff,
I have a weird issue when i create a table from a view. I cannot edit the data in the table from sql developer, but i am able to do it via a statement.
Any idea why this happens ? When i create a table from scratch it work fine.
you’re using CTAS from the view to create the table?
Hi Jeff, that is correct. I use create table …. as select * from view
Just tried out the Database Copy Tool. Very nice. One problem though – I was copying the objects in a schema in one database (DB1) to the same schema in another (DB2).
The tables in DB1 were in the USERS tablespace. The schema in DB2 didn’t have quota in USERS. So Copy failed – because it couldn’t create objects in USERS. Shouldn’t it try the schema’s DEFAULT tablespace instead?
So I granted the schema in DB2 a quota in USERS, did the copy, moved the tables where I wanted them and revoked quota.
it’s grabbing the storage DDL – imagine if your objects have partitioning – you kind of need that stuff
Jeff,
I’m attempting to format the following example and the result is un-runnable. This appears to be a problem when the Max Line Width is not large enough to accommodate the entire line, it kinda defeats the purpose. Can you tell me if this is a bug or a setting issue? Unfortunately, this is preventing me from formatting the SQL and using SQL Developer as our production tool.
Thanks so much,
Brian
SELECT DECODE (INSTR (usr.os_user_name_f, ‘cdmimail\’), 1, SUBSTR (usr.os_user_name_f, 10, LENGTH (usr.os_user_name_f)), usr.os_user_name_f) AS trader, TO_TIMESTAMP (sysdate, ‘yyyy/mm/dd hh24:mi:ss.xff’) AS system_timestamp_f
FROM dual;
–AND TRUNC (TO_TIMESTAMP (p.system_timestamp_f, ‘yyyy/mm/dd hh24:mi:ss.xff’)) = ‘3 – feb – 14
BTW,
About
—–
Oracle SQL Developer 4.1.0.19
Version 4.1.0.19
Build MAIN-19.07
IDE Version: 12.2.1.0.42.150416.1320
Product ID: oracle.sqldeveloper
Product Version: 12.2.0.19.07
Version
——-
Component Version
========= =======
Oracle IDE 4.1.0.19.07
Java(TM) Platform 1.8.0_45
I get a syntax error on it before I format it?
Jeff,
Thanks for getting back so soon. Try running the following example and then formatting the script (ctrl-F7). You’ll see that the following comment is split out on to a second line. In my package bodies, this same thing is happening which basically causes the package to fail compilation.
— EXAMPLE code that does not format in SQL Developer
SELECT DECODE (INSTR (dummy, ‘cdmimail\’), 1, SUBSTR (dummy, 10, LENGTH (dummy)), dummy) AS trader, TO_TIMESTAMP (sysdate, ‘yyyy/mm/dd hh24:mi:ss.xff’) AS system_timestamp_f
FROM dual;
–AND TRUNC (TO_TIMESTAMP (p.system_timestamp_f, ‘yyyy/mm/dd hh24:mi:ss.xff’)) = ‘3 – feb – 14
This is what I see when I format your code with the default formatter preferences in v4.1
what do you have MAX LINE LENGTH set to in your formatter preferences, under the Line Breaks Section?
Jeff,
The default Max Line Width is 999. This basically puts your code on one line. I have it set at 120. This pushes the comment after 120 to the next line. If I make the Max Line Width 200, it keeps the comment on the same line but it changes the content/spacing so if I uncomment the line of code to use later its messed up. The comment should not change in mind.
Thanks,
Brian
I think I’d have to agree with you – I’ll log a bug for you.
In the meantime, if you use the /* */ commenting style, splitting them out over multiple lines won’t bite you.
So a quick, immediate fix, for you…
Formatter preferences, Other -> Put — comment between /* … */ – toggle that ON
Jeff,
Thanks for the response. How will I know if the issue is addressed in SQL Developer?
Greatly Appreciated,
Brian
To know for reals, you’ll need to log a service request with My Oracle Support.
Can you try the preference to see if that fixes your issue in the meantime?
hi jeff,
i have installed oracle on my windows machine.while connecting to database its was giving me listener error i did some changes in listener files now its giving me network adapter error vendor code 17002
and when am trying to start the listener its giving me following error.
LSNRCTL> start
Starting tnslsnr: please wait…
Unable to OpenSCManager: err=5
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
LSNRCTL>
>> its was giving me listener error i did some changes in listener files
changed what, and why?
You shouldn’t have had to change anything. Please consult the forums or Support re the Unable to OpenSCManager: err=5 stuff -> I’ve never seen that one before.
Hi Jeff,
I just downloaded sqlcl as I attended a great talk on it at the Scotland OUG conference yesterday.
Anyway, when I run it in Windows I cannot connect to a database.
I am prompted for a username
Username?
and I type in system
the prompt then changes to
Password? Database?
so I have not had a chance to type in the password. I put in a database name and get
Password?
repeated multiple times on my screen.
Any ideas,
Thanks,
Jim.
Hi,
I can now get connected using
C:\sqlcl\sqlcl\bin\sql.exe system/****@***:1521/orcl
but I get no output from any commands I type in e.g.
show user
select * from all_tables
all just return me back to the SQL> prompt but no output appears on the screen,
Hope you can help,
Thanks,
Jim.
you on a mac or windows?
Hi Jeff,
I’m on Windows 7
thanks,
Jim
Hi Jeff,
i’am interested in creating “custom design rules” – is it possible to get the “predefined design rules” (if they are implemented in same technic as script) ?
I would also would appreciate any links regarding documentation and examples about that topic.
Regards Günter
hi jeff
i want to export 1856963 rows.now i have created a dummy table and stored the data in that table.When i am trying to export it in xls/csv/txt, the sql developer is getting hung. So i tried SPOOLING, created a input.sql file as
SET NEWPAGE 0
SET SPACE 0
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
set verify off
SET ECHO OFF
spool D:\output.csv
select /*csv*/ * from f0911_co_0200_p_01;
spool off;
now after running, for 3 hours it ran fine, i was able to see data in console and then suddenly this error occured
exception at java.io.IOException: read dead end ……
i Have increased the buffer size in ide.config file to 1024M
SetMainClass oracle.ide.boot.Launcher
AddVMOption -Xmx1024M
AddVMOption -Xms1024M
Need your help on this, is there any way to export this much amount of data from sql developer
CSV should just work (as should XLSX) – I’ve done a million records no problem. You shouldn’t’ need to resize the jvm heap size, as the records are read in, written out, and released as it goes. The exception to this is if you use the XLS format – that library isn’t’ very good and doesn’t release the memory.
i have installed SQL developer, and only one day it worked for me and then its giving me listener problem i googled it and changed my tnsnames and listener file.Now its giving me another error saying that “Network adapter could not established the connection Vendor code 17002″.
I am importing test case from one repository to another. test case is having array as 1 of the input. if i am importing it through SQL developer graphically then it imports the value of array but if i am importing the same test case from command line interface with help ‘sdcli’ command then the array is empty.
Why this is happening ?
Sounds like a bug.
Hi Jeff,
I just noticed that when using the data tab on a describe window that a hint is added to the select statement. In this case I am displaying data in a 4,400,000 row table and doing a descending sort on a non-indexed date field. The hint displayed is /* +NO_PARALLEL*/select…
My question is, it looks like the syntax is incorrect for a hint in that the + is not next to the *. My other question is, is there a way to manipulate what hint SQLD is using when querying a table from the describe window?
Thanks for all your help!
it’s a bug, we’ll have the fix in there shortly such that hint works correctly
to answer your question – No, at least not as a part of SQL Developer
Technically, it’s not a hint when it’s added before the SELECT. However, it’s ugly and confusing.
i have exported the test case from 1 schema and imported in another repository and changed owner name ,type_owner in ut_test and UT_TEST_ARGUMENTS table which are in repository. The test case which i have imported is having array as 1 of the input. While running that imported test case i am getting an error.
Error :ADD_LIST failed: oracle.dbtools.raptor.datatypes.DataTypeIllegalArgumentException: Unable to convert to SNEHAL_1506.NUM_LIST
Dear Jeff,
Recently, I have updated my Oracle SQL Developer to the latest version (see below) and now when I try to run function or procedure from the IDE I have got an alert with error: “ORA-00904: “OBJECT_ID”: invalid identifier. Vendor code 904″ Previous version of IDE works quite fine. What is the problem?
Oracle SQL Developer 4.1.0.19
Version 4.1.0.19
Build MAIN-19.07
We’re querying ALL_PROCEDURES and DBA_ARGUMENTS – what version of Oracle Database are you connected to?
Thanks for reply, Jeff!
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
“CORE 10.2.0.1.0 Production”
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
Hi Jeff,
Thank you for providing so much knowledge on various modeling topics.
I have a question on Oracle Data Modeler.
Is it possible to create a column group and include it in one shot to all tables? I am referring to something like audit columns (added_user, modified_user, added_date and modified_date).
Thank you again.
Hi Jeff,
Ignore the question. I saw the option for column groups in the Table Properties Dialog….Thank you.
Sure thing, it’s easy