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
Good Morning,
On my Windows 10 laptop i run SWLDev 18.4.
So the OS is part of the companies Windows Domain.
Some days ago i took my nb to work at one of our customers site. I plugged it into the customers network, logged in using my company’s Domain credentials.
Whenever u powered up SQLDev I got errors, telling me that some link destinations are not accessible. This only happens when starting and working with SQLDev. And the Open Cart Dialog did not work, when clicking just nothing happened.
I could not reproduce the problems when back in the office.
Any idea what causes the problems and how to avoid them when working outside the window domain?
Wish a great Sunday
Peter
Hi Jeff,
how can I configure native encryption (Oracle advanced security) for jdbc thin driver in SQL-Developer? I’ve found the java properties that have to be set but I have no clue and couldnt find anything online WHERE to set them.
Thanks
Andreas
We don’t support jdbc thin ssl connections today, but we’re hoping to get that for v19.1
Today you need to have an Oracle Home and do a thick connection.
Hi Jeff,
I need to have multiple versions of SQL Developer installed on the same Linux system at the same time. I currently have /sqldeveloper_4.1.5 & /sqldeveloper_18.2 installed with the all their top level directories immediately below those root directories (e.g., /sqldeveloper_18.2/configuration, /sqldeveloper_4.1.5/dataminer, etc.). Do you see any problem with this installation structure? Thanks,
Mark
Nope – should be just fine
Hello Jeff,
I have SQLDeveloper on mac – today with SQLDeveloper 18.3 I was reconnecting a session. It hung, so I force quit.
Now at startup SQLDeveloper is spinning/hanging on IndexPreferences Task – have you seen this before???
Shaun
hi jeff,
I have a question in ORDS.
I have a table without a primary key constraint.I have enabled auto-REST on that table using ORDS and was able to do create,read,delete operations but how can i do update operations on it. because according to the documentation i do not have a primary key to use a PUT operation.
Pattern: PUT http://:/ords///
I tried to use filter query in place of . but it does not work. kindly suggest a method to update data of particular row.
How are you doing deletes? An update would be the same.
In the absence of a PK, items in your table collection (rows) are addressable by the ROWID. You can see this in any ORDS response where you’re doing GETs on the table or a particular row.
{ "items": [ { "x": 1521, "links": [ { "rel": "self", "href": "http://localhost:8080/ords/hr/nokeys/AAAY7%2FAAMAAABauAAA" } ] }, { "x": 1522, "links": [ { "rel": "self", "href": "http://localhost:8080/ords/hr/nokeys/AAAY7%2FAAMAAABauAAB" } ] },Hi Jeff,
when renaming columns with sql developer the pop shows only the table name, and not the old column name. I would like to copy the old name and make small changes sometimes.
Is that “feature” available in a future sql release? Where can i look for enhancement reqeusts / “bugs” / features already known and evaluated by you?
Cheers,
Michael
Right-click on table, choose Column – Rename
The popup dialog shows the column name – you just can’t select it to copy/paste…is that what you’re missing? I’m guessing you can’t copy it before you go in?
You can search My Oracle Support SRs in the MOS portal. You can also log ERs there.
I have started using SQLDeveloper for unit testing.
every thing is fine up til now.
only one problem on “teardown” phase when I select “Table or Row Restore” to restore data from previously created tmp table to the original table.
the test fails here with error “ORA-01031- insufficient privileges” although both tables are in the same schema.
thanl you.
Hi Jeff,
First of all thanks for all that you and your team do for Oracle users! I use this tool the most in my role aside from a terminal app. Is there a way in Sql Developer to format a list into a IN list with quotes and commas? I’ve seen this feature in other sql ides and curious if there is a way to do this in Sql Developer. Thanks again!
Yes, drag and drop from a grid will do that
Wow! Its the little things. I’ve always just copy/pasted from the grid. Thanks Jeff!
I have a package.procedure whose parameters are of type PL/SQL table and inserts records in to custom tables, i registered the code a s Oracle REST service and when i try to evoke the REST service passing values only NULL values are getting inserted am I missing something here?
Sample Code and payload below:
Code:
CREATE OR REPLACE PACKAGE INDUS_LCBL_REST_PKG
AS
/* $Header: $ */
/*#
* This interface imports Local Bills Supplier and Invoice
* information and accordingly creates/updates customers
* as suppliers/sites and creates the corresponding invoices
* @rep:scope public
* @rep:product Payables
* @rep:displayname Local Bills PL/SQL Table Type
* @rep:lifecycle active
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY AP_INVOICE
*/
TYPE ttab_INDUS_ap_inv_headers_tab
IS TABLE OF INDUS_AP_MRCH_INV_INB_HDR_STG%ROWTYPE
INDEX BY BINARY_INTEGER;
TYPE ttab_INDUS_ap_inv_lines_tab
IS TABLE OF INDUS_AP_MRCH_INV_INB_LIN_STG%ROWTYPE
INDEX BY BINARY_INTEGER;
/*#
* This procedure Returns Supplier Number
* @param pv_hdr_rec ttab_INDUS_ap_inv_headers_tab ttab_INDUS_ap_inv_headers_tab
* @param pv_lin_rec ttab_INDUS_ap_inv_lines_tab ttab_INDUS_ap_inv_lines_tab
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Insert Table Type
*/
PROCEDURE insert_tbl_type_stg (pv_hdr_rec IN ttab_INDUS_ap_inv_headers_tab
,pv_lin_rec IN ttab_INDUS_ap_inv_lines_tab);
END INDUS_LCBL_REST_PKG;
/
CREATE OR REPLACE PACKAGE BODY INDUS_LCBL_REST_PKG
AS
PROCEDURE insert_tbl_type_stg (pv_hdr_rec IN ttab_INDUS_ap_inv_headers_tab
,pv_lin_rec IN ttab_INDUS_ap_inv_lines_tab)
IS
lv_error_text VARCHAR2(4000);
ln_ret_code NUMBER;
BEGIN
BEGIN
IF pv_hdr_rec.COUNT > 0 THEN
FORALL i IN pv_hdr_rec.FIRST .. pv_hdr_rec.LAST
INSERT INTO INDUS_AP_MRCH_INV_INB_HDR_STG (data_source
,version_id
,load_id
)
VALUES (pv_hdr_rec(i).data_source
,pv_hdr_rec(i).version_id
,pv_hdr_rec(i).load_id
);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
lv_error_text :=’Unexpected error inserting in header staging table.’||SQLERRM;
ln_ret_code := 2;
END;
BEGIN
IF pv_lin_rec.COUNT > 0 THEN
FORALL i IN pv_lin_rec.FIRST .. pv_lin_rec.LAST
INSERT INTO INDUS_AP_MRCH_INV_INB_LIN_STG (data_source
,version_id
,load_id
)
VALUES (pv_lin_rec(i).data_source
,pv_lin_rec(i).version_id
,pv_lin_rec(i).load_id
);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
lv_error_text :=’Unexpected error inserting in line staging table.’||SQLERRM;
ln_ret_code := 2;
END;
——————————
— Call Merch INV Package —
——————————
/*INDUS_AP_MRCH_INV_INB_INTFC_PKG.INDUS_MRCH_INV_INTERFACE(xv_errbuf => lv_error_text
,xn_retcode => ln_ret_code
,pv_run_mode => ‘PROCESS’
,pn_org_id => 681
,pv_source_name => ‘LCBL’
);*/
EXCEPTION
WHEN OTHERS THEN
lv_error_text :=’Unexpected error occured in the procedure insert_tbl_type_stg.’||SQLERRM;
ln_ret_code := 2;
END insert_tbl_type_stg;
END INDUS_LCBL_REST_PKG;
/
Payload:
“InputParameters”: {“PV_HDR_REC” : [{“__db_type” : “APPS.JCPX_LCBL_REST_PX4487634X1X1”, “DATA_SOURCE” : “LCBH1”, “VERSION_ID” : 1, “LOAD_ID” : 1}
,{“__db_type” : “APPS.JCPX_LCBL_REST_PX4487634X1X1″,”DATA_SOURCE” : “LCBH2”, “VERSION_ID” : 2, “LOAD_ID” : 2}],
“PV_LIN_REC” : [{“__db_type” : “APPS.JCPX_LCBL_REST_X4487634X1X89″,”DATA_SOURCE” : “LCBL1”, “VERSION_ID” : 1, “LOAD_ID” : 1}
,{“__db_type” : “APPS.JCPX_LCBL_REST_X4487634X1X89″,”DATA_SOURCE” : “LCBL2”, “VERSION_ID” : 2, “LOAD_ID” : 2}]
}
Why bundle the JDK with SQL Developer but not with SQLcl where one needs to *install* yet another version of Java?
Why can’t we use the version you bundled with SQL Developer?
And what’s with the forced Browser launch with the Java home page? Microsoft used to pull that move until they realized it was just rude.
SQLcl is only 20MB and needs a JRE, not a JDK… bundling a JDK is overkill.
SQLcl can use the jre already on your machine.
If it can’t find one, we try to help you download it.
Does this make sense?
Version 19.1 will support Java 11.
I cannot get SQlcl on Windows to recognize/use an existing JRE or JDK for that matter. Nothing I’ve done so far works. I traced the process and it appeared to be looking in the Windows registry when led me to believe that it will only work with an ‘installed’ Java. I’ve set JAVA_HOME, JRE_HOME, added it to the PATH but it still says it needs Java and launches the browser. I really don’t think you need to ‘help’ someone who is planning to use a low level command line SQL tool to download Java. Does that make sense?
Thanks!
Graeme
The EXE on Windows should def be picking up the Java Home… developer will take a deeper look on Monday.
Thanks Jeff. I appreciate it – and I really want to use this tool.
I’m on Windows 2008 Server R2/Windows 10/Windows 7 using SQLcl 18.3 although I’ve tried and failed on 18.2 and 18.1.1
X:\Operational\Environments>CD /D Y:\Oracle\sqlcl-18.3\sqlcl\bin
Y:\Oracle\sqlcl-18.3\sqlcl\bin>SET JAVA_HOME=Y:\Oracle\Java\Java8\32-bit\jre
Y:\Oracle\sqlcl-18.3\sqlcl\bin>SET JRE_HOME=Y:\Oracle\Java\Java8\32-bit\jre
Y:\Oracle\sqlcl-18.3\sqlcl\bin>SET PATH=%JRE_HOME%\bin;%JAVA_HOME%\bin;%PATH%
Y:\Oracle\sqlcl-18.3\sqlcl\bin>java -version
java version “1.8.0_171”
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) Client VM (build 25.171-b11, mixed mode)
Y:\Oracle\sqlcl-18.3\sqlcl\bin>sql
This application requires a Java Runtime Environment 1.8.0_150
Same issue on 18.4.0.007.1818
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.
Easiest fix would be to run Oracle JRE installer on that machine.
We’ll file a bug – it should just work if a Java Home is available.
Thanks – yeah I noticed it looked in the registry. I’ll wait till this is fixed.
Appreciate the quick response.
Hi Jeff.
Bit of a silly but annoying one I have here. Only in Sql developer I’m unable to hold down the shift key and simultaneously use the spacebar key. Using a Macbook.
Any preference setting I should be aware of on sql developer?
Thanks!
what is shift+spacebar supposed to do on a Mac? what are you trying to get it to do in SQL Developer?
It’s supposed to create a space, it does not do that. While holding shift it basically cancels the spacebar. Let’s say I want to type a sentence in uppercase I would hold shift, now if I want to start a new word in that sentence I have to press spacebar, but I cannot do that unless I release my finger off the shift button
Hi – is there an equivalent in SQL Developer to Toad’s Profiler feature that gives a hierarchical report and graphical view of the time taken to execute lines of PL/SQL code?
We have an interface to the hierarchical profiler.
I have a schema with a few hundred packages. Its not easy to see which package bodies are invalid unless I expand all package specs manually.. For views, its easy enough, you can see the red cross, I can compile all invalid ones, etc. For packages its awkward. Could we have an ‘expand all’ option so I can see the red crosses on the bodies or maybe a ‘show only invalid packages’ option. Something to make this a little easier. Thx.
Hi Jeff
I am new to SQL Developer, used to native SQL*Plus, Windows explorer, TOAD and learned a lot from your posts and videos. Well done, thanks.
Is there a way to PREVIEW the content of a file in OPEN (Ctrl-O) like in Windows Explorer ?
My Worksheet and the Script Output window show both a vertical line on col. 128 of 200. What’s its meaning – and how to get rid of it ?
SQL Develper 18.4, Windows Server 2012 R2, rdp, Citrix Desktop
Regards,
Martin
File preview, no, sorry.
That vertical line is an arbitrary marker in case you need help knowing when to hit the return key 😉
You can change the position or turn it off in the Code Editor, Display preferences.
Hello Jeff,
I been working on the Sql developer migration, issue is Migration failed with ora-0942 error in generated phase for Sybase to Oracle in 18.4 version.
Below are the steps fallowed :
1. created a user for repository migration
2. grant connect,resource,dba to
3. Associate a repository for
4. From tool–>migration->migrate
Migration wizard failed with ora-0942 and surprising two customers are facing the same issue in 18.4
2) If the sybase datatype having a “datetime” , then what should be at Oracle end, do we need to change in the migration wizard?
Appreciate if you can help me on this issue.
Regards,
Kanthi Kiran.
If you work for Oracle support, why are you leaving me a comment on my blog? Just email me at work.
For ora-0942 on migrations, make sure the Repository User has GOD privs so it can create anything it wants. And in 12c and higher also make sure you have QUOTA on your tablespaces.
DATETIME is probably a DATE in Oracle – but that’s a question for the customer and whoever has their data model, not us. Do you need a TZ? How about fractional second precision? If either of those,then you need Timestamp with TZ
Hi. In SQL Developer what is the difference between the encoding UTF8 and UTF-8?
I think you pretty much ALWAYS want UTF-8
Hi Jeff:
I recently upgraded to 18.4. I have two issues;
1) Previously opened procedures still displayed
2) Memory hogger
1) Whenever I open sqldeveloper, it displays a sql file left open previously (expected behavior), but also displays a few objects – procedures & packages – along with it.
I have attempted to close it (including the sql file) and reopening SqlDeveloper several times, but no success. The tabs do not have actual procedure code, since when I click on these procedures, it would close.
However, if I have a sqldeveloper Instance open and new open at the same time, second one is clean.
2) Memory Hogger: It still takes almost one Gig. But better than 18.3, which used to go beyond 1.7 GB and slow down machine.
System configuration: Windows 64, 16GB RAM, Intel i7
Thank you for your time,
Srini
1 GB of RAM isn’t that much – go look and see how much Chrome is using on your machine.
Also, the number you are seeing is the amount of RAM allocated to the JVM – not how much memory SQL Developer is using from the JVM – that’s two very different things.
Hi Jeff:
Let me start all over, as there seems to be miscommunication.
My M/c has 16GB Ram, Intel i7. Task Manager shows SqlDeveloper using upto 1.7GB of Ram. How to prevent this or is there a workaround?
–> I have a few tables (definitions or data) open, some sqls executed in sql windows, to test Packages opened in readonly mode, as well as editing/executing one package/procedure.
Thanking you,
Srini
How often are you closing SQL Developer? You could also try disabling Features.
–> “How often are you closing SQL Developer?” –> not frequently, maybe once in 2 days . What is your recommendation?
–> “You could also try disabling Features.” — like?
Search my blog, I have a post dedicated to this topic.
Will do. Thank you.
I have another question:
SQLDeveloper does not seem to “close” old tabs. I always have a *.sql (same file), and a few tables & Procedures open. This started a few weeks ago, in 18.3. Thought it would go away, if I migrate to 18.4. But it still happening. Even though I close the sql file AND rest of the tabs (I no longer work on that Program), when I shutdown SQLDeveloper and restart, this appears again. Any solution? It is annoyance, not a show stopper.
Hi Jeff.
I’ve upgraded to SqlDeveloper 18 for MAC.
I noticed that when writing my code, the “sysdate” keyword is black colored automatically, which in previous version was blue colored.
Example of use:
INSERT INTO table VALUES (SYSDATE,value1,value2);
I didn’t find the way to format this behavior.
Thank you in advance.
Regards
what color do you have set for Default Identifier in PL/SQL Syntax Colors in Preferences (under Code Editor)
–> “1 GB of RAM isn’t that much – go look and see how much Chrome is using on your machine.” — Sorry it is 16GB RAM.
–> “amount of RAM allocated to the JVM” — Okay, presently the System slows, down and I have no option but to shutdown and restart SqlDeveloper.
–> what about #1 ( “The tabs do not have actual procedure code, since when I click on these procedures, it would close.”)? It is annoying.
Any workaround, please?
Your JVM has consumed 16GB of ram? if that’s true, it’s only because you configured the sqldeveloper.conf to allow the max size of the JVM to grow that large.
What exactly are you doing to consume so much memory – do you have a huge data model open?
Hi Jeff,
When looking at triggers defined on a table or view, the bottom part of the screen should show the code for the highlighted trigger object. Unfortunately we have a procedure and trigger called the same name within the dictionary (but as they reside in different namespaces this is allowed) and SQL developer elects to show the procedure code not the trigger code. If you right-click on the trigger and select the “Go To…” option on the menu, the trigger code is then displayed.
Would you accept this as a bug?
Kind regards,
Mark.
Definitely a bug.
Hi Jeff,
We have recently moved to GitLab for source versioning, and I did notice that SQL Developer data modeler seems to support Git via svn-git utility. I was wondering if you could demo how to setup connection in SQL Developer Data Modeler to connect to GitLab.
Thanks and Regards
Sagi
We looked at this, but I don’t think it’s a viable solution.
V 18.4
I have Windows 10 and screen resolution is set for 3840×2160 with a scaling factor of 300.
v17.4 shows properly while for 18.4 the display is just not readable — way too tiny. Is there any setting tweek to be made
Regards
I have had this issue inn the past. You may find some relief here
http://www.thatjeffsmith.com/archive/2013/12/how-to-change-the-ui-font-size-for-oracle-sql-developer/
the file can be found at:
~\AppData\Roaming\SQL Developer\system18.4.0.376.1900\o.sqldeveloper\ide.properties
You should be able to make some tweaks to make it usable. The structure of properties have changed since the post, so I could be completely off base
The creation of extensions for SQL Developer has changed a lot over the years.
I am struggling to find an up to date set of resources that accurately describe the technology and steps required to create an extension for SQL Developer 18.
Is it possible for you to summarise those links and resources (help, blog articles, resources, forums etc) which would aid new developers in building such extensions quickly and without wasting too much time?
regards
Simon
This is the best we have today
https://github.com/oracle/oracle-db-examples/tree/master/sqldeveloper
There’s a place there to ask questions
Thanks.
Thanks Jeff,
I just switched everything on again and it has worked. Now I will disable features one at a time and see which feature removes it.
Vic
My guess would be ‘OLAP’
Hi Jeff,
I’m using Sql DEV 18.4 and can’t find the snippets option in the View tab. I have enabled Snippets in the Tools –> Features option, and restarted SQL Dev a few times to no avail. Any ideas?
Thanks
Vic
What else have you turned off? First thing I’d try is turn everything back on. If that doesn’t work, maybe try extracting the 18.4 from the Zip again to a fresh directory. If THAT doesn’t work, rename the system18.4 directory in your AppData (windows) or .sqldeveloper (NIX) folder to force a ‘fresh’ set of application settings.
I want my team to share snippets, we all have individual installations of SQL Developer Version 18.3.0.277 and currently our snippet files reside in our local directories
e.g C:\Users\username\AppData\Roaming\SQL Developer\
I want to change this to use one snippet file on a shared directory.
I’ve tried adding
AddVMOption -Draptor.user.snippets=T:/Documents/UserSnippets.xml
To the Conf file C:\Users\ username \sqldeveloper\sqldeveloper\bin\ sqldeveloper.conf
But it hasn’t worked, is this the correct Conf file?
What am I missing? I’d really appreciate it if you could help me out with this.