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
Hi Jeff,
In SQL Data modeller, I am trying to add RDBMS comments to columns within an editioning view definition. Within the view properties, the option exists to do just this, but this only works at the view level . I have entered the information for view columns, but when saving, the information entered is lost. It is also not generated in DDL. Is there another way to enter this information or is this a bug?
Many thanks,
Mark.
I create a view, marked it as editioned, added a comment. Saved the design. Then previewed the DDL –
CREATE OR REPLACE EDITIONING VIEW ebr_emp (column_1, column_2
) AS
SELECT *
FROM employees;
COMMENT ON TABLE ebr_emp IS
'this is a comment.';
Hi Jeff,
Thanks for the quick reply.
Can you try adding comments to each of the view columns? I can’t get this bit to work.
Many thanks,
Mark.
Hi Jeff,
I am also having an issue seeing the DDL in the SQL tab using SQL Dev 20.2. The issue only presents itself if you try to examine the DDL and you are not the object owner. If I log in as the object owner I can see the DDL.
Sounds like a privileges issue – there is a known bug about seeing DDL for a Materialized View…is that your scenario by any chance?
It is not limited to Materialized views. This includes views,
If I am logged in as the object owner I can see the DDL. If I am logged in as myself, with select any table granted via a role, I cannot see the DDL except for the GRANT. This is different behavior than in 19.4 where I could see the DDL for objects that I do not own.
Can you post your scenario to the forums and I can take a look.
Hi Jeff,
Sorry – yet another SQL formatting question.
I really want to use the tool built into SQL Developer, but I just cannot get the formatting result I want from it. I have tried the “auto detect” feature using my formatting code but that doesn’t seem to work too well for me.
I do know that the underlying formatter uses “arbori” rules and these can be modified to suit but this looks very complex. I know that a number of people have blogged on this but I still don’t feel comfortable modifying the out-of-the-box code.
I did some looking on the internet and found an on-line utility (http://dpriver.com/pp/sqlformat.htm) that, whilst not perfect, does produce output that is similar to what I want. To be clear I don’t want to use this tool as I use SQL developer every day and am more than happy with it. It would just be awesome if SQL developer provided more customisable options in the formatting department.
Any help/links/tutorials you can provide me would be greatly appreciated. I have a lot of PL/SQL legacy code that was clearly created with multiple coding standards and some that was generated by a code generator. Both would really benefit from source code formatting.
Can you help?
Mark.
Start by showing me how you want your code to look. Or suggest options you’d like added.
Hi Jeff,
How about packaging up and including the final config of this announcement.
https://www.thatjeffsmith.com/archive/2014/03/how-to-make-your-code-look-like-steven-feuersteins-in-oracle-sql-developer.
If every SQLDeveloper user formatted using the same settings – life would be easier.
This post is 6+ years old. I’m sure Steven’s formatter preferences have changed since then.
Hi Jeff,
Thanks for agreeing to help. Rather than slap the code into HTML, I decided to e-mail to you to preserve my preferred formatting. I hope that is OK and that you received it.
Kind regards,
Mark.
Getting following error :
Could not load connection type:Oracle
URI has an authority component
when trying to create new connection in the Sql Developer.
Tried looking for 18.4 folder in path
C:\Users\Manikanta\AppData
What are the other possible issues?
Make sure you’ve installed SQLDev to a ‘normal’ directory and not to a UNC path.
Assuming that, try renaming your system18.4 AppData sql developer folder and starting the program again.
Finally, 18.4 is very old, we’re up to version 20.2 now!
A Modeler question on using Search.
I search for a field name and get back several results including results under Diagrams. When I clean on a diagram under Diagrams, Modeler takes me to the entity I am looking for. However, some of my tables are very big and I would like to jump directly to the field I see in search results. Is there a way to do this, versus scrolling through the entire table?
Thanks!
Meant to write …. When I click on a diagram under Diagrams …
no, it’ll just take you to the table, but you can configure the diagram to only show the ‘important’ attributes/columns if that helps
I think it would be very handy to have a way to save/bookmark individual queries with SQL Developer. I don’t see every query I run in the history (not sure why) and there are times I’d like to keep something handy. I wish there was a way to “bookmark” individual queries within the worksheet and then bring these up similar to the way browers do with a name and folder structure. And if behind the scenes these went into a file which could be copied between computers would be great. I know I can do this myself by saving each query into an individual file but it’s slow to have to browse each time instead of just using a hotkey and selecting a query.
I guess snippets could be used for this. That doesn’t really seem to be their purpose and adding user defined snippets though. Even having a button on the worksheet toolbar that would let me select code in the worksheet and then open the User Defined Snippet dialog box with the code selected in the Snippet box. And in Snippets have a button to switch behind built in and user defined snippets to keep the user defined ones in a separate area.
I think what you’d really looking for are the Code Templates. promote the most important queries to templates, then you can invoke them by name using Code Insight (ctrl+enter)
Hi Jeff.
a while ago I posted a comment with sample code that make sql editor hang (in sql developer v 19.2.1). The latest version has not improved because today while editing a script with a lot of quote characters the editor literally trashed the while interface. I had to fix the script with an external editor and then copy the script into the Developer’s editor.
This was a double problem because I was also in a hurry.
It seems that the editor wants to have everything sintactycally OK to work. It there are missing quote char when there are a lot of them around, even a missing quote char can make it go crazy and garble everything.
Thank you
I searched your 21 comments here and see no such sample code…
It was about three months ago , you can find it here http://www.thatjeffsmith.com/ask-a-question/comment-page-84/#comment-172228
I removed the protocol to try to make the comment go through
The blog software changes the quote characters so I uploaded the plain text sample here textuploader.com/1fs63
Please try to paste it into an editor window, you’ll notice that without adding a quote somewhere before the last row, that row becomes unreachable. You can’t click there, also you can’t move the cursor there with the arrow keys.
On Friday I had somewhat the same problem but this time the editor started to show me multiple times the same rows and, by moving the cursor around , I got the interface completely garbled.
I add that I am using the Developer on Linux (with oracle java 8). I noticed that the product works better on Windows but that this bug It there nevertheless. Here is an animated gif that can illustrate the point. bb.co/bHHmmnr
the last link is ibb.co/bHHmmnr (trimmed the initial I, sorry)
ugh, got it, and yeah, that’s bad
i’ve logged a sev1 bug with the developer, will let you know if he proposes a workaround while we work on a fix in an upcoming version
thanks for pestering me, and apologies for missing this!
No problem and thanks to you Jeff..
I can pester you even more if you want, I have a long backlog of requests :-))
Hi Jeff,
Appreciate for your support. I have a requirement where want to return specific node values if a particular node exist. Example – for the below XML, my output will be 2 records because (Jobsdata/jobList/jobData/job/document) node exist 2 times and returning nodes are (job/document/fileID and job/directoryid ). O/p –> 100,D100 200,D200
JOBNAME10
100
D100
IMMEDIATE
host
node100.localdomain
host
node200.localdomain
host
JOBNAME20
200
D200
REPEAT_BY_DAYS
ACTIVE
host
node300.localdomain
host
JOBNAME300
D300
3
5
WEEKLY
node400.localdomain
host
For SQL based questions, please see Tom.
Hi Jeff,
I am using Oracle SQL Developer Version 19.2.0.206 and could you please help me with following questions?
Question 1: It happened to me many times that when I close the SQL Developer the system asked me to choose from commit/roll back my statement but all I did was running some select sql statements, nothing should change the database table/view. It is really annoying that I am afraid I accidentally run any insert/update/merge statements, but I tested a few times to make sure I only did select statements. Could you please share some insights?
Question 2: For certain reason in my work, we try to avoid writing “commit” in the sql statement, rather, we click the “commit” button in the sql developer. Is there anyway we can see the acknowledgement that the “commit” button has been clicked and the changes have been committed? That would be very helpful when running complicated statements.
Thanks a lot,
Jason
1. Are your queries going over db_links?
Hi Jeff,
When using Data Modeler, is there any way to mark an object as EDITIONABLE and NONEDITIONABLE within the physical model area. The target database for the physical model is “Oracle Database 12cR2” and I cannot find the option. I am asking because I have a collection type that is a table attribute and this must be marked as NONEDITIONABLE for it to work. I also couldn’t find a way to get a user generator with the ALTER USER ENABLE EDITIONS DDL.
I am confident that editions are fully supported by DM but options must be tucked away somewhere. 🙂
Many thanks,
Mark.
Hi Jeff,
Sorry to ask but do you have an update for me?
Many thanks,
Mark
Sorry, currently you can only set that property for views in your model.
Jeff,
Thanks for the reply. Is this something you would consider adding for the next release?. EBR is a really cool feature unique to Oracle database.
Many thanks,
Mark.
It can definitely go onto the to-do list!
SQLDev v20.2
When I startup SQLDev I get a task IndexPreferencesTask which runs for a few seconds — sometimes 10 seconds. What is it doing?
Also the logging Page shows
Level => SEVERE
Sequence => 139
Elapsed => 0
Source => oracle.ide.extension.HashStructureHook
Message => Unexpected runtime exception while delivering HashStructureHookEvent
indexing all the text/labels for preferences so when you do a search for something there, you can find them
it runs on a background task and shouldn’t interfere with anything else
Hi Mr. Jeff Smith
What is the reason for that we can (or must) not connect with sqldeveloper to oracle Fusion Cloud database (SAAS) ,Oracle Cloud ERP, Oracle HCM Cloud, but other tools can !!! Can you explain that please?
AS EBS developers, we use tools like Toad, SQL Developer, etc. but are unable now for SQL queries and analysis with Oracle Cloud.
For us tools like Oracle BI are not efficient ,user friendly and time consuming and we must go through multiple hoops to be able to execute a query etc. Can you explain ?
Regards
Mohmood
Hi Jeff
We are using ORDS for Oracle DB centric APIs. We have specific authentication use-case to validate bearer token (JWT) from third-party (Okta in my case). What would be your suggestion? Is their a provision within ORDS or should I frontend internal gateway like Spring Cloud on Apache Tomcat/WebLogic?
Appreciate your passion and intent to support the Oracle Community.
Hi Jeff,
There is a situation at one of our Production database, there was this below query, SQL Developer auto generated, which has ran for almost 30 minutes. Want to understand what user action might have triggered this query such that we shall be cautious at least to do it Production.
Version being used: 4.1.3.20 (Yeah.. Old version though)
SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE object_type =’TABLE’ and object_name not like ‘BIN$%’ and rownum <=50 union all SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE object_type ='VIEW' and object_name not like 'BIN$%' and rownum <=50 union all SELECT object_type type, owner owner, object_name, null column_name, null column_id, null data_type FROM all_objects WHERE rownum <=50 and object_name not like 'BIN$%' and object_type IN ('PROCEDURE','FUNCTION','PACKAGE')
Looks like one of the queries we run in the object navigator… Imagine going to browse the TYPES using the connection tree.
Hi
How to adjust that ‘smart data’ and ‘data’ windows are shown during debugging? I have only ‘watches’ window there.
Thank you in advance.
try resetting your desktop or try View > Debugger > Smart Data and Data
Generally those open by default, but it’s possible you previously closed them?
I’ve created an autonomous database (ATP) environment and logged on with admin. I want to create schemas to separate things.
CREATE USER HI IDENTIFIED BY somevalidpassword;
yields
Substitution cancelled
in the script output.
HI is the user/schema.
Why is it doing this and how can I create schemas in my ATP environment?
I was using & in the password.
Second problem. After I eliminated that, I got an ORA-00922: missing or invalid option.
That’s because I was starting the password with a number.
So surrounding the password with double quotes fixed that.
Well, using the syntax @http://svn.something.com/script.sql to execute a script in SVN, the script must be loaded into SQL Developer. Is this functionality not expected? Everything works well, except the constant login prompts.
yes, that’s expected…navigating authentication requests wasn’t something I’d tried before.
Hi Jeff;
I often execute SQL scripts connecting to SVN using syntax @http://svn.something.com/script.sql. Is there a way to save the “CollabNet Subversion Repository” user name and password in SQL Developer?
Thanks for your help.
Not outside of using the svn feature…but you’d use that to bring the files down locally.
Jeff;
Let me clarify. I can execute SVN scripts in SQL worksheet without download the files locally, either F5 or F9. SQL Developer will initially prompt for username/password and that stays in effect until I quite SQL Developer. That’s very convenient. However, in 19.4 (maybe lower version too), upon next restart, SQL Developer starts to prompt for SVN login. If I do not want to login SVN at that time, I have to click “cancel” many many times. So I thought maybe SVN login can be saved, similar to the “version” feature. If not, is there a way to stop the prompting?
Thanks in advance for your help.
I didn’t even know you could do what you’re doing 🙂
Hi Jeff,
Recently started using sql developer 20.2…now I am frequently getting “Your connection has been reset. Any pending transaction…” dialog.
What version were you using previously?
19.4.0.354.1759-x64 (Build 354.1759)
The only change of note is we went from one 19c jdbc driver to a more recent version of the same, 19c jdbc driver.
You could try an Instant Client/Oracle Home (OCI) Thick connection to see if that improves things.
Actually I am using oracle instant client connection (both on old and new version of sql developer)
I’ve got oracle instant client v12.1.0.2.0 installed will this cause any issue
12.1 is old, best to get the 19c client if you’re using a 19 jdbc driver, which we are.
Thanks Jeff..I will check that
Hi Jeff. I have a question for what I hope is a simple yes or no answer regarding default SQL Developer functionality (I’m using version 18.1.0.095 and Build 095.1630 currently). Is it possible to change the ordering of the Connection drop-down list in the upper right-hand corner of SQL Developer or even just completely prevent it from reordering connections? The reason I ask is because the default functionality of where it reorders the Connection list by open connections doesn’t seem to be for ALL open connections, only some, which means you have to look at the top for some open connections and then scroll down and search for others. I would rather it just left the order as-is rather than change it for some open connections and not others, which ends up scrambling them up.
Thanks in advance!
Bob
Upgrade
It should be open connections first, then the rest alphabetically
If you see differently in 20.2, please let me know
Hi,
20.2 materialized views sql tab does not display view ddl just show grants.
Numan
Hi Jeff,
I tried to use sqlcl Version 20.2 with Liquibase and reusing our current Liquibase Changelogs and changeset, but unfortunatley I saw the following exception:
SQL> lb update db.changelog-3.37.0.xml
Exception in thread “main” java.lang.NoSuchMethodError: liquibase.ext.ora.create oraclejob.CreateJobChange.setObjectType(Ljava/lang/String;)V
at liquibase.ext.ora.createoraclejob.CreateJobChange.(CreateJobCha nge.java:18)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstruct orAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingC onstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at java.lang.Class.newInstance(Class.java:442)
at liquibase.change.ChangeFactory.register(ChangeFactory.java:71)
at liquibase.change.ChangeFactory.init(ChangeFactory.java:42)
at liquibase.change.ChangeFactory.getInstance(ChangeFactory.java:52)
at liquibase.parser.core.xml.XMLChangeLogSAXHandler.(XMLChangeLogS AXHandler.java:52)
at liquibase.parser.core.xml.XMLChangeLogSAXParser.parseToNode(XMLChange LogSAXParser.java:110)
at liquibase.parser.core.xml.AbstractChangeLogParser.parse(AbstractChang eLogParser.java:15)
at liquibase.Liquibase.getDatabaseChangeLog(Liquibase.java:217)
at liquibase.Liquibase.update(Liquibase.java:190)
at liquibase.Liquibase.update(Liquibase.java:179)
at oracle.dbtools.raptor.scriptrunner.commands.liquibase.liquibaseComman d.handleEvent(liquibaseCommand.java:470)
at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(C ommandRegistry.java:346)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.j ava:226)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecut or.java:344)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecut or.java:227)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java :410)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli. java:421)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli .java:1292)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:50 2)
$
So I am not sure if we are using liquibase in a a way which does not support some liquibase features:
The changelog I was using in this test:
You have to use the -changelog flag, yes? Check the cmd help to be sure (I’m at the beach), and if that’s good, then I might need your changeLog too.
Hi,
I was trying to extract the data through sqlcl and load it using sqlldr. One issue we came across is field like comments and remarks are having line separator (\n). While extracting the data using SQL format as loader or CSV we are seeing those records are getting into new line in sqlcl and when we import it its causing an issue using sqlloader like second enclosure string not present. Any thoughts to fix it ?
Just a sample record.. You can see one record is gone to multiple line..
125 “SI3″|||”61″||”0″||”SO”|”MUDLO1″|12551|”1″|”1″||||||”2020-07-29 10:45:34″||”2020-06-20 17:56:56″|”28/06 ripini per il 12/07 mata . n otia al clt. spgar
126 12/7 sac 1266 fallito per cle assente lta!”||
Mutli-line strings basically kill CSV…for sqlldr there’s this.
If that’s not going to work, using the INSERT method or building an external table might be the way to go.
Hi Jeff,
removing deep (very deep) directory structure in windows was quite challenging. But I managed it.
The problem is reproducible.
The Migration environment from the previous Version 19.4 fails; the loading screen shows “IDE Platform-Core”. At this point, the process runs forever. The same directory structure is generated.
Hi,
The challenge is to output using ORDS “total no of rows” and recordset (sys_refcursor) so that:
(1) sql query is optimal (performed ONCE).
(2) totals is not repeated for every row returned (network efficient – totals data is returned just ONCE)
I managed to make it optimal (1), but “totals” is present in every row – (2) failed.
I could compute totals in one SQL query/cursor, store the value in an OUT parameter and perform a 2nd query with the rows returned in a sys_refcursor OUT parameter, but that would fulfill (2) and fail (1).
Any ideas ?
Thanks,
It’d be easier to have your app count the entries in the json payload … And cheaper
Hi Jeff,
i was nosey and already installed sqldev 20.2 on my windows 10 system.
During the first start the progress bar hung, the process sqldev was consuming >900MB ram and growing. so i killed that process.
after that i looked into %APPDATA% directoy and found an almost infinite number of directories which i have trouble to delete:
\\?\C:\Users\\AppData\Roaming\SQL Developer\system20.2.0.175.1842\o.ide.12.2.1.5.42.200515.1304\o.ide.13.0.0.1.42.190403.1502\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201\o.ide.13.0.0.1.42.170225.201
Did i run into a bug?
Have a nice weekend
Peter
delete all that and try again…that sounds…weird
Maybe I found the reason:
The same directory looks weird in the previous versions’ profiles as well. I guess during migration the directory structure is copied into the new version. Including this strange structure.
What du you think? Is it safe to remove the structure of my profile data? Or do I loose required informations?
Regards Peter