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
How can we suppress the additional blank lines in the spool file while running a script through SQL Developer’s worksheet?
I’d need to know what you mean by additional blank lines, so show me an example.
Hi Jeff,
How to resolve the below error.
The connection pool named: |apex|| is not correctly configured, due to the following error(s): ORA-28000: the account is locked
Thanks & Regards
Sojan V S
Very easily, you unlock the account.
Hi Jeff,
We are able to access the account from PL/SQL developer. But when trying to do GET on an ORDS service we are getting this error. Recently DBA changed the password of this schema, is there any place where we have to update this new password for ORDS to work?
Thanks & Regards
Sojan V S
Yes.
Hi Jeff,
Thanks for this great site. Just a quick question on completion insights… when I start off with typing something like
package_name_api.get_
and then use completion insight, I end up with
package_name_api.package_name_api.get_something
and I have to go and delete the extra package name every time. Is that a setting I’ve got misconfigured? I did some googling but couldn’t find anyone with a similar issue.
Thanks!
That is a familiar bug, but that’s been fixed. I know it’s working in both 18.4 (current version) and 19.1 (what we’re testing internally now for release end of quarter)
Hi Jeff,
How can I save or extract the DB DIff results so that it is usable with colors, etc. that it shows while connected to the DB? Is it possible?
Not possible today.
But we’re working on a new change management project for your schemas, and we’ll hopefully have more flexibility to do things like this.
Hello Jeff,
I have a question regarding templates and how possible is to share them between a team. I have some templates created and would like to share with my team. I know that sharing the CodeTemplates.xml is possible, but it is still a problem since people might create their own templates and thus all files wouldn’t be syncd, so I thought about the possibility of placing the file in a shared drive, and point SQL Developer to read from there. Is this possible? I can’t find any file where I can change this parameter, so I’m wondering if this is really changeable or it is something “hardcoded” into that location.
Thanks,
Hugo
Hi Jeff,
When I use the tool to increase or decrease TEMP file size, it gives error, so I have to do the workaround, wondering if you have a fix for it? We are using 18.1.0.095.1630.
When we change TEMP File size in DBA section, expend Connection in DBA > Storage > Datafiles and
Right click on the temp file OR
double click on the temp file, then from right side panel, use Action > Edit
Then we have to make change from Datafile to Tempfile and then run it manually in SQL Worksheet:
ALTER DATABASE DATAFILE ‘+DATA/filename’ RESIZE 500M; (INCORRECT)
ALTER DATABASE TEMPFILE ‘+DATA/filename’ RESIZE 500M; (CORRECT)
Thanks.
Shoaib
Doesn’t look to be fixed in 18.4 or 19.1, so I need to make sure there’s an actual bug filed first. Thanks for the heads-up here.This has been fixed in version 18.2 – so you just need to upgrade.
Hi Jeff,
Yesterday I attended your ODTUG webinair on ORDS which was very informative.
Currently I am evaluating if ORDS would be suitable alternative for replacing the sql-layer in one of our java applications. This application is an STP data integration application, so no user interactions but sometimes a high database-load demand.
From the functional side it looks very promising but from the performance side I encountered some not-the-best results.
A benchmark test with 10.000 one-by-one row inserts showed that our java application is capable of doing this in a little bit more then 20 seconds; so average around 500 single row inserts per second.
With ORDS this benchmarktest reached only 2 row inserts per second.
I must admit that the ORDS test was done with default ORDS connectionpool settings and that I found some sheets on the web indicating that the default ORDS connection pool settings are to minimalistic.
So, I expect that with tweaked ORDS connection pool settings the ORDS performnace can be increased, but I question a bit if ORDS is capable of reaching 500 inserts per second as our current java application is capable of.
I would like to know your thoughts on this.
your test is basically, 10,000 POST calls to do 10,000 inserts?
Default pool size is 10, so how long does the db take to do the insert? That’s the main limiting factor, not ORDS…plus any latency from your mid tier (wherever ORDS is running) to the database.
Hi Jeff,
thnx for your feedback;
I will check the latency and db-speed.
Hi Jeff,
Recently I was trying to use a new version of SQL Developer for daily enquiry on version 18.4.0.376 and was getting this error when trying to test the connection.
Status : Failure -Test failed: null
I was not having such issue when using version 3.2.20.09.
I am currently using oracle database version 12c r1 on AIX OS.
There are other databases on Solaris OS and not having such issue.
Any idea what could have caused this issue? Is it due to compatibility issues?
Good afternoon Jeff,
I found sufficient documentation for me to be able install ORDS and deploy to Tomcat, and REST enable schemas and objects in our Oracke databases using SQL*Developer, and was able to map-URLs and get json formatted web pages rendered on the web browser. I can get the same output from curl and wget, so I am happy with that.
Now, I have a “Now what!” moment! I am still a DBA type, and I will be meeting with Java developers and my hope is they will know how to retrieve JSON and do whatever it is they do in their Java business logic.
I am looking for an example or illustration on how to retrieve the same via Java API and do something with it. Also, how can I use this with javascript and/or node.js applications? Can you guide me with this?
Thanks so much,
Khalid Rahim
No, you’re all good. Your APIs are ready to be consumed by your Java developers. They just need the docs on the endpoints…it’s easy to HTTP POST/GET/PUT/DELETE from Java or JavaScript
You can use a tool “Postman” for free to test Rest-Calls. Very nice !
And it has an option to show you the code it uses for many languages, e.g. Java.
Hi Jeff,
I’d like to suggest two (I hope simple and quick to implement) things for the forthcoming SQLDeveloper versions:
1.) being able to sort and filter data grids for MS SQLServer connections (like we’re able to do for Oracle tables)
2.) include a preference to open a table datagrid on “data” tab instead of columns (would come really handy when “touring” and getting to know a new system’s database)
1 – well, that IS simple, but, we’re not looking to making working with SQL Server easier or better. The SQL Server support is there to help you migrate to oracle db. If we go down that hole, it’s never ending. All of our dev and maint work is around the Oracle DB experience. Your suggestion would require a HUGE change in our priorities.
2 – this is good until you click on a table that takes seconds/minutes to query and do the first fetch
Hi Jeff,
thanx for a straight answer. Those are the things I (we) miss the most while doing a migration project from MS SQLServer to Oracle. We’d just like to be able to quickly “skim” over the tables and data we need to port over form the source DB (during the project you come back to the same look-up tables and values over and over). There are other tools to work with SQLServer, it’s not that we’re trying to use SQLDeveloper as the main tool for SQLServer.
As for directly opening the tables in data grid view (with sort and filter applied) – we know about the big tables and potentially long query times (we’re pros and we know that hitting your finger with the hammer it is going to hurt, we did it more than a few times), that’s why we asked for a switch which could be off by default and (ideally) would be “per table”.
SQLDeveloper is a great tool we use extensively during our work, but it’s the little things like these that are really not making life any easier for us while also trying to bring in more work and licenses for Oracle ;). Migrations from SQLSever to Oracle are a kind of projects that got a bad reputation as not being feasible to do because of high costs of Oracle licenses (in the first place) and the time required for migration. Most of those few projects that got undertaken nevertheless I’ve seen them fail, as the amount of unplanned work skyrocketed during the migration when more and more technical differences found needed to be worked around. Clicking over and over to get the easy stuff (like lookup values) gets old quickly since there’s real work to be done (you cannot imagine what kind of horrors and abuse we find in those DBs).
Still hoping that our wishes would be heard!
Kind regards, Aleksander
Hello Jeff.
I tried to use the ‘set null’ with ‘set sqlformat ansiconsole’ in SQLcl but it seens to bug the columns layout, did a quick google but couldn’t find any open issue about it.
I’ll try it tomorrow
Hello Jeff,
the search source code report does not find lines anymore !
I recently switched to a new computer / windows 10 and had to reinstall SQL Developer18.4.0. Was there anything special to configure to make this work? In my memory this always worked “out of the box”.
I’m speaking of course of the highly useful report: All Reports / Data Dictionary Reports / PLSQL / Search Source Code
I’ll have to check, but why aren’t you using the actual Search feature? It’s much more powerful than that report.
Oh, you mean “Find Database Object”? Gosh, this one eluded me for all those years. Thanks a lot!
Hi Jeff,
It is recommended to design an REST API with a version, like
http://:/foo/v1/bar/1
What is the suggested approach in scope of ORDS? Is there a possibility?
Thanks
Reinhard
Hi Jeff,
Is it possible to use SQL Developer unit testing feature separately from SQL Developer?
I mean to export SQL scripts of created test repository and test cases to be deployed in another environment.
For example I created tests in development environment for schemas prefixed with dev_(dev_schema1, dev_schema2 etc.) but on test environment I have prefix test_ (test_schema1, test_schema2 etc.)
How to organize version control of test cases?
In general I want to integrate unit testing into CI/CD process.
Regards,
Aliaksandr
No, but you can use it without the GUI.
The sdcli program in the BIN folder let’s you run unit tests from the command line.
Hi Jeff – Do you have list that compares the Oracle RDBMS to MS SQL Server DB? Need to know the advantages Oracle has over SQL Server. Thanks
Not really. I mean, for me the conversation ends with, reads and writes will never block a read in Oracle, whereas in SQL Server you have no_wait hints to get around reads being blocked
But i wouldn’t ask Coca-Cola if/why they’re better than Pepsi
Check this out… Interestingly, this docs is part of an old SQLDeveloper documentation about migrations. Quite old, but a good start:
https://docs.oracle.com/cd/E10405_01/appdev.120/e10379.pdf
Hi Jeff
SQL developer Version 18.4.0.376.
All by a sudden when opening a new table, file or anything, the new tab is docked to a different “sub” pane where it didn’t use to dock.
Earlier it docked to the “Main-pane” but someting chanded.
Is there a way to control where the new tab should dock?
Regards Gunnar
Window menu, Reset Windows to Factory Settings
Hi Jeff,
I currently have the complete APEX API defined as Code Templates on SQL Developer and I’m thinking to publishing it on GitHub for others to use them; however, I noticed that the Completion Insight doesn’t work when the template key contains a dot (.)
As an example, I have the following template:
apex_util.url_encode | apex_util.url_encode(p_url => ‘[]’);
On a new worksheet, if I type “apex_util” [Ctrl + Space], I can see all my code templates that start with “apex_util” but, as soon as I add a dot (.) [Ctrl + Space], I can no longer access them.
Could you help me with this?
Thank you,
Erick
you mean like, file a bug?
Something like that! 🙂
I just put it on the SQL Developer forum.
Thank you,
Erick
is the answer not as simple as not using a ‘.’ in the names of your templates?
Well, the idea is to use it as an auto-complete feature similar to other IDEs like IntelliSense on VSC. For now, I’m using an underscore (_) instead of a dot (.) but it doesn’t come natural to write apex_util_url [Ctrl + Space], as oppose to, apex_util.url [Ctrl + Space].
Thank you,
Erick
Why even build this template at all? our insight feature already supports package.proc completion with the parameter call outs – you shouldn’t need to build these out manually as code templates
if you were, they should be shorthand then, say auu – that’s 3 keystrokes to save you from writing apex_util.url
Maybe I’m mistaken but the insight feature only seems to help you with the package and procedure/function name. What my code templates are doing is auto completing the functions/procedures together with all the parameters and their default values and allowing me to navigate through each of the parameters using the [Tab] key.
If this is something currently available on SQL Developer, could you please let me know how to use it to auto-complete the functions/procedures, as well as, their parameters?
Thank you for your help and time,
Erick
no, we give you the full parameter list too
I’m currently using the latest SQL Developer, Version 18.4.0.376, freshly installed with all the defaults. I really cannot make SQL Developer to auto-complete beyond the function or procedure name.
I don’t mean to waste your time so I’ll try to look on the web to see if there is something else I’m missing.
Thanks again Jeff!
share a code sample, i’ll be happy to test your use case
Thank you Jeff, I really appreciate it.
This is just a small piece of my CodeTemplate.xml on C:\Users\\AppData\Roaming\SQL Developer:
apex_util.create_user
apex_util.create_user (
p_user_id => [null],
p_user_name => ‘[]’,
p_first_name => [null],
p_last_name => [null],
p_description => [null],
p_email_address => [null],
p_web_password => ‘[]’,
p_web_password_format => ‘[CLEAR_TEXT]’,
p_group_ids => [null],
p_developer_privs => [null],
p_default_schema => [null],
p_allow_access_to_schemas => [null],
p_account_expiry => [TRUNC(SYSDATE)],
p_account_locked => ‘[N]’,
p_failed_access_attempts => [0],
p_change_password_on_first_use => ‘[Y]’,
p_first_password_use_occurred => ‘[N]’,
p_attribute_01 => [null],
p_attribute_02 => [null],
p_attribute_03 => [null],
p_attribute_04 => [null],
p_attribute_05 => [null],
p_attribute_06 => [null],
p_attribute_07 => [null],
p_attribute_08 => [null],
p_attribute_09 => [null],
p_attribute_10 => [null],
p_allow_app_building_yn => [null],
p_allow_sql_workshop_yn => [null],
p_allow_websheet_dev_yn => [null],
p_allow_team_development_yn => [null]
);
apex_util.prepare_url
apex_util.prepare_url (
p_url => ‘[]’,
p_url_charset => [null],
p_checksum_type => [null],
p_triggering_element => ‘[this]’,
p_plain_url => [false],
);
I got it to work, but I don’t think you’re going to like the answer.
I had to use
APEX_180200.htmldb_util.create_user(
There is not apex_util package – there is a synonym called APEX_UTIL, but it points to HTMLDB_UTIL – if you call that, then the insight works. I can file a bug though.
Hi Jeff,
The XML format was removed, you can access the complete CodeTemplate.xml here:
https://gist.github.com/OraCoolAPEX/4027bdcc91666296ae41e46a0d27feb0
Thank you,
Erick
Hi Jeff,
Using “APEX_180200.htmldb_util.create_user(” works. As you mentioned, it would be nice if it also works with the synonym.
I just read your new blog post and I still think it would be nice if the Code Template allows the use of dots. I think there is an advantage of using Code Template vs the default Completion Insight, i.e. when auto-completed, the Code Template allows to navigate through each of the parameters using [Tab] while the default Code Insight automatically populates the data types next to each parameter (e.g. /*varchar2*/) which needs to be manually removed.
Once again, thank you for your help and time.
Best,
Erick
Yeah, I filed a bug.
Insight is looking to fire the data dictionary object look-ups when it sees a ‘.’ – so not sure how likely that’s going to happen. Why do you need to remove the data types? They’re comments.
Thank you Jeff for answering all my concerns, I won’t be taking more of your time! 🙂
Regarding removing the data types, I know they are comments, I just meant for cleaner code.
Have a great day,
Erick
Hi Jeff,
Is it possible for you guys to add set command in SQLCL to set end of line character explicitly for the output.
I tried your customized formatter JS (Wierd2) on windows and still I get end of line as CR+LF. In the formatter I just mentioned \n as output. But is not working.
Thanks and regards,
Milind.
end of day, what are you trying to do? spool output on Windows and move to Linux?
Hi Jeff,
I can’t imagine that this question has not been asked before, but here goes. I am trying to get the user who consumes a service.
So I have created an REST data service (POST) which, in turn, runs a procedure. To consume this data service you will need to get a authorization token. So far, so good. Now I am looking for a particular way to know which client is consuming the data service. In APEX I can use something like v(‘APP_USER’). Is there some equivalent in ORDS? Or is the concept I am trying to achieve wrong?
I really looked a long time in your posts for a solution, but if I missed it, my apologies.
With regards,
Frank
Auto-bind variable, :current_user will show that, see Colm’s post for details/example and the official Docs take on the subject
Hi Jeff,
Copy and Paste to SQL worksheet is not working properly. When i select a line of code ; it goes hidden. Once i type anywhere else; i can see the line back. This is Version 18.4.0.376. Any idea how to fix this. This is happening only when i copy from a notepad to SQL worksheet.
I wonder what you’re copying and pasting…exactly.
Hello Jeff,
I run few SQL Query and look at the data in query results window. Some times I get confused because if I run multiple query the Query Result window names it like Query Result, Query Result 1, Query Result 2, Query Result 3… and So on.
I know I can manually click on each window and rename it appropriately, But my question is. Just like the way set worksheetname ‘TEST’ do we have something to set Query Result Window ?
Easier to just look at the SQL behind the grid – then you know what the data belongs to.
Hello Jeff
Can I expect that Oracle SQL Developer Data Modeler will soon supports Analytic Views?
Regards
Bernd
At some point, I tried to export query results from SQL Developer and accidentally had a “:” in the file path. That seemed to cause significant issues and I have not been able to export any results since. I can’t seem to find a way to fix this. It seems like there should be a way to re-set the export wizard or somewhere to change the file path it is pointing to, but so far I cannot find an answer to resolve this online.
Never mind – I was finally able to find the right preferences file to edit. For anyone else that stumbles upon this with a similar issue…
C:\Users\\AppData\Roaming\SQL Developer\system.\o.sqldeveloper\product-preferences.xml
Note that portions in should be updated for your username and particular system version.
I opened this file with Notepad, but assume any text editor should do, then did a search for “path” and was able to find the section of the code with the export path that was causing a problem. I deleted that particular line and saved the .xml file. After re-starting, I was able to successfully export query results again.
Hi Jeff,
when I am using any scripts that recompile invalid objects or use the dropdown function in my SQLDeveloper (18.3.0.277), the packages are always compiled with debug info. I found already that in a older version there was a option under Preferences -> Database -> PL/SQL Compiler that was named “Generate PL/SQL Debug Information” . But I don´t find it in my options.
Do you know how to change this setting? I guess this is still somewhere in my sqldeveloper config files.
Compiler preferences, what is optimization level set to?
DEBUG
Has the same effect as PLSQL_OPTIMIZE_LEVEL=1—instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Oracle recommends using PLSQL_OPTIMIZE_LEVEL=1 instead of DEBUG.
Optimize Level was set to 0… I changed it to 2 now it does compile without the debug info.
Thanks a lot!
Hi Jeff,
I have to cleanup settings of older sql developer versions before 18.4 because of limited space in my windows profile. Up to now after switching to a new version I usually removed directories in AppData/Roaming/sql developer/system18.3……
But with 18.4 this seems to be changed. Am I right? Is system18.2… reused by 18.4?
Can you please provide some hints which directories can be removed after switching to 18.4? Where are those data stored now? (Maybe add a tool like “remove old installation data” to one of the next sql developer versions).
Thx,
Dirk
Hello Jeff
Interestingly, SQL Developer doesn’t show any updates for the product while access through the “Help->Check for update” menu. I am currently using 18.3 & found 18.4 available to download by visiting the download site. I am starting the SQL Developer as “Administrator” & I believe it does check for the updates.
Will you please let me know why the Developer is not showing updates, or I am doing something wrong?
Check for Updates is a JDeveloper feature included in the framework we use for SQL Developer. It shows you if you have any Extension updates, not SQL Developer base product version updates.