4.5+ million 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!
5,823 Comments
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