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,803 Comments
I’m interested in using a some tool to generate and maintain documentation for PL/SQL code. I’m aware of PLDoc. Is this the best opensource approach to doing documentation for PL/SQL – database documentation?
Thanks,
Dennis
Sounds like a great question for the pl/sql developer community. ‘Best’ is always a fun word to interpret.
Hi Jeff
I’ve been watching your videos on installing ORDS which are great.
I think ORDS will be a great product for us to provide REST APIs for our Oracle Databases (for both web services and interfaces for our front end PHP websites) as well as a replacement for our MOD_PLSQL database administration tools. Using Web SQL Developer looks interesting too.
Do you have any videos or instructions specifically on installing ORDS on a WebLogic Server. We have this already for our Oracle Forms deployment so it seems sensible to use it for ORDS.
Kind Regards
Neil
We have Oracle Docs which covers the WebLogic-specific process, you can find those here
Hi Jeff!
I’m a noob at SQL but I find it very addictive ones you got started. Now to the question. I want the GROUP BY function to group all the three Select queries. How do you combine GROUP BY and UNION ALL so the result of the queries groups together?
Select messagename, messagetransactiontype, COUNT(*) AS Quantity
from dual
where messagename = ‘Z04’
and messagetransactiontype = ‘L’
GROUP BY messagename, messagetransactiontype
UNION ALL
Select messagename, messagetransactiontype, COUNT(*) AS Quantity
from dual
where messagename = ‘Z04’
and messagetransactiontype = ‘LK’
and netarea = ‘XXX’
GROUP BY messagename, messagetransactiontype
UNION ALL
Select messagename, messagetransactiontype, COUNT(*) AS Quantity
from dual
where messagename = ‘Z04%’
aand messagetransactiontype = ‘LK’
and netarea != ‘XXX’
GROUP BY messagename, messagetransactiontype
ORDER by 1
Best regards
Anders Eriksson
Hi Jeff,
I think I’ve found a small bug in SQLcl:
SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 21.4.1.0 build: 21.4.1.17.1458
SQL> select dummy, ‘/*csv*/’ from dual;
“X”,”/*csv*/”
The literal ‘/*csv*/’ is wrongly interpreted as a hint and the total output is thus formatted as csv.
Hi Jeff,
We’re running into an issue when creating tables with liquibase in SQLcl.
It seems the tablespace clause is not processed correctly from the changelog.
Here is the error:
######## ERROR ##################
ssr_features_table.xml::542e881d3591950c7cfb427d5b4029ce5a346e4e::(SSR)-Generated
Error starting at line : 1 in command –
CREATE TABLE “SSR_FEATURES”
( “FEATURE” VARCHAR2(3 CHAR) NOT NULL ENABLE,
“DESCRIPTION” VARCHAR2(80 CHAR) NOT NULL ENABLE,
“PRODUCT_GROUP_GRANTABLE” VARCHAR2(3 CHAR) DEFAULT ON NULL ‘Yes’ NOT NULL ENABLE,
“APEX_PAGES” VARCHAR2(20 CHAR),
CONSTRAINT “FEA_PK” PRIMARY KEY (“FEATURE”)
USING INDEX
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 NOCOMPRESS LOGGING
STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE
Error report –
ORA-02216: Naam van tabelruimte verwacht.
02216. 00000 – “tablespace name expected”
*Cause: A tablespace name was not present where required by the
syntax for the failing statement.
*Action: Specify a tablespace name where required by the syntax.
######## ERROR END ##################
As you can see the tablespace keyword is present (both in the PK index and heap table segment) but not its value, although it is in the XML changelog – here is the part for the table:
10
40
1
255
65536
1048576
1
2147483645
0
1
1
DEFAULT
DEFAULT
DEFAULT
SSR
Y
N
This tablespace exists and user has quota on it.
Versions:
RDBMS: 19.11.0.0.0
SQLcl: 21.4.1.0 build: 21.4.1.17.1458
Any idea if we’re hitting a bug or some other issue?
Thanks in advance for your reply and help!
use the SET DDL commands to have the storage clauses omitted when generating your changelogs
Hi,
How do I change the windows background color in SQL developer ver 21?
Thanks,
Ravi
I have various posts showing how to do this, somewhat.
We have been encountering high memory usage with Oracle SQL developer software. There has been an instance where the memory usage of the instance is anywhere from approx 700 MB to occasions where the memory usage is close to 2 GB also. This is causing slowness on the machines in which our developers work.
This is not specific to a particular version of the operating system but in various OS i.e 2012, 2016, 2019, etc. Also, this high memory usage was observed in Old releases of SQL developers as well not just 20.2. We have tried changing the look and feel of SQL developer but it did not help in reducing the memory footprint.
Is there a way to limit or reduce the memory footprint of Oracle SQL developer software?
That’s relatively normal, and wouldn’t be a cause for concern or for the application to become slow or unresponsive.
Something else is probably causing the problem…unless your machine itself is resource starved.
Hi Jeff,
We have installed SQL Developer Version 21.4.2.018 Build 018.1706 after Log4J issue. The SQL Developer is running very slow. We are on “Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production”. Please suggest in this regards.
Thank you.
Use your work’s My Oracle Support contract and open a SEV-2 case, you can say that the SQLDev PM ‘sent you’ – once you have the SR#, let me know, i’ll jump in and see what’s happening.
By any chance do you have a TNSNames.ora file with LOTS of entries?
Hello Jeff
Sometimes your posts are related with the project I am developing. It has been fantastic.
At this moment I trying to consume a private API that use Auth 2.0.
First, I tried with postman in which I could get the token without problem. But using PL/SQL under APEX environment I have the following error.
“ORA-20001: Server responded with unsupported OAuth token type”
I do not know why. But that token request is using POST method instead GET method.
In Postman I make the request as follow:
• Method POST
• Authorization Only selected type OAuth 2.0
• Headers Key “Content-Type” ==> application/json
• Body Raw/JSON
{
“APIKey”:”xxxxxxxxxxxxxxxxxxxxxxxxx”,
“APIPassword”:”yyyyyyyyyyyyyyyyyyyyyyy”
}
In PL/SQL – APEX environment I am using this script
begin
apex_web_service.g_request_headers(1).name := ‘Content-Type’;
apex_web_service.g_request_headers(1).value := ‘application/json’;
apex_web_service.oauth_authenticate(
p_token_url => ‘https://api.marketman.com/v3/buyers/auth/GetToken’,
p_client_id => ‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’,
p_client_secret => ‘yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy’);
end;
I am thinking that apex authenticate function use GET method, but I cannot find how to change it to POST.
You’ll need to go to the APEX team for help with the APEX_ packages. If Postman or cURL are working, it indicates ORDS is working. Good luck with your app!
Hi Jeff,
I think I’ve found a bug with Oracle SQL Developer and echo. I’m on Version 21.4.2.018
I have a startup script that runs when SQL Developer is opened and one of the items in the script is “SET ECHO ON”. There are a couple commands after that and I can tell from the Message Log that “SET ECHO ON” is turned on because those commands are listed in the Message Log.
If I open a file with PL/SQL and run it by pressing F5, the file contents are not echoed the first time I run the file. If I run it a second time, the contents are echoed. I normally don’t want to run a file twice so at the beginning of every file I would have to add “SET ECHO ON” which defeats the purpose of having a start up script. If I click the Run Script button on the worksheet toolbar, the same thing happens.
Interestingly, if I press Ctrl + Enter or click the Run Statement button, the echo works when the file is run for the first time.
Hi
I have installed APEX (21.2.3) and ORDS (21.4) on a Linux 8 box. I chose to use HTTPS (port 8443). Running APEX from the Linux box works fine. When running from a Windows 10 Desktop I get
This page isn’t workingb560m redirected you too many times.
Try clearing your cookies.
ERR_TOO_MANY_REDIRECTS
How to I configure APEX and ORDS to get the access from Windows Desktop?
Regards
Sorry not enough info here to help, but sounds like a network issue, not ORDS.
Hello,
I have installed Oracle SQL Developer with the XEPDB1 database.
I didn’t have any luck with the VM machine version as my Windows 10 Home machine is objecting to it. 🙂
I know how to create my own tables and views, etc and load data.
But in order to build “juicy” queries, I need lots of data.
Are there any scripts available that can build a moderate-large amount of data that can be loaded at once?
Any advice or direction you can provide would be appreciated.
Thank you.
Mark
APEX has some data generation capabilities. Connor McDonald has some nice scripts handy (check his blog and AskTom). Dom Gile’s Swingbench has some data generation capabilities. I always look for interesting csv/json data and load that to new tables using SQLcl or SQL Developer.
We have sample schemas on GitHub as well…
Thank you.
Hi Jeff –
I was wondering if you or your team ever tried compiling sqlcl to a native image with GraalVM? I was hoping to reduce the startup time and was wondering if you all have the steps /command needed to compile it as I’m a db guy not a java guy. I have been fumbling my way thorough it, but am not quite there yet.
Yes, we’re working on it..and will ship it when ready.
Great to hear, thanks. Don’t suppose there’s a ballpark on release time frame to share? Figured it didn’t hurt to at least ask.
Thanks again.
Hello Jeff,
My colleague is trying to open a table from connections->tables. The table column contains column with name UID and is giving him error that “uid” is invalid. it is working fine for me. When looked at the query sql developer made to extract table has UID in small letters for him but for me it is in caps. We have checked preferences too and they seems simillar to me. searched forums too but no luck. Can you please help?
we are using Sql Developer Version 18.1.0.095
Is upgrading an option? Because 18.1 is 4 years old and we’ve made many improvements and bug fixes since then.
Hi Jeff,
While attempting to create a database in SQL Developer 21.4.2.018 (Basic Connection), using the Service Name option on a local Linux machine (RHEL 8.x), along with the ojdbc8 driver, I encountered the following error message:
Status: Failure -Test failed: IO Error: NL Exception was generated TNS-04603: Invalid syntax error: Unexpected char “#” while parsing KEY (CONNECTION_ID=UJBzS7………)
I set up the tnsnames, listener, and sqlnet.ora files and the listener runs. ORACLE_HOME, ORACLE_BASE, ORACLE_SID, and TNS_ADMIN are set.
Any help would be appreciated.
Thanks!
Does straight jdbc work?
CLASSPATH and LD_LIBRARY_PATH are also set, and java and javac point to 17.0.2.
The SQL Developer Help > About > Properties tab shows the ojdbc8.jar is attached to jdbc.library. The Help > About > Version tab shows Java 17.0.2 is attached. The Help > About > Extensions tab shows jdbcproxy is attached to JDBC Proxy (fully loaded).
Additional info. on the Help > About > Properties tab (defaults):
oracle.jdbc.autoCommitSpecCompliant = false
oracle.jdbc.mapDateToTimestamp = false
oracle.jdbc.Trace = true
oracle.jdbc.useFetchSizeWithLongColumn = true
As far as a direct jdbc test, I don’t currently have a functioning database on this machine that I could test in, say, a Java environment, where I could run: Connection conn…”jdbc:oracle:thin:username/password@//localhost:1521/pdb” to test a handshake.
I also attempted to create a database using the DBCA tool, and I was met with the following error message:
[DBT-05509] Failed to connect to the specified database.
Cause – OS Authentication might be disabled for this database.
Action – Specify a valid sysdba user name and password to connect to the database.
The sqlnet.ora setting: SQLNET.AUTHENTICATION_SERVICES=(ALL), which I thought would satisfy the ‘OS Authentication’ requirement.
You’re going to an awful lot of effort to get OCI8 going on your mac, and I’m asking why when just the jdbc driver gives most people what they want with ZERO configuration required.
Also if you don’t have a database to work with in the first place, aren’t you getting ahead of yourself?
I’m trying to create an Oracle database in a Linux environment. The first attempt was with SQL Developer. I ran into the TNS-04603 error while attempting to create the database.
I also tried to create a database using the DBCA tool via the GUI interface, initiated at the command line, and that’s when I ran into the DBT-05509 error message I posted.
The error messages halted the database creation process in both SQL Developer and with the DBCA tool.
There’s no way to create an oracle database from scratch using SQL Developer. You’ll need to use the Creation Assistant (DBCA).
Or skip all that.
To be more specific in regards to SQL Developer, I ran into the TNS-04603 error message while attempting to create a new connection.
As mentioned, I ran into the DBT-05509 error message with the DBCA tool.
Why bother? Just Docker, VirtualBox, or Cloud.
Hi Jeff,
thanks for your amazing blog!
I have a question about SQLcl.
Currently we are calling “lb update -changelog _install_packages.xml” and inside we have more than 300 with Each is one separated database package in separated file.
Example snippet:
During the installation, the content of each package is printed twice in the “ScriptRunner Executing:” and “Liquibase Executing:” sections.
Is there any way to modify the output generated by the “lb update” command to not be so verbose on the output? There is a terrible flood on the terminal output of the content of all database packages and it is impossible to check visually anything inside.
Thanks in advance,
Chose
Hi Jeff, I am connecting to oracle as a normal user (non DBA) who does not have alter system kill session privilege, and neither do I see the details on Monitor session screen, from where I could kill a hung session. If I disconnect VPN then normally it will disconnect the session of SQL Developer but this time, even after disconnecting VPN and internet it still remained hung. I did not want to kill windows process as I did not want to loose the multiple select statements that I had prepared while running the proc in one window, and checking data in other editor window. Basically it got hung while a debug procedure process was on (with break points though). After a break point halt, I was checking data in a table that was just inserted before the breakpoint code. Is there any way to simply disconnect the sql developer IDE from that thread of the session running on the DB so that at least I do not have to loose those sql queries I kept on writing while the testing was on? It is ok if the db session runs on and the objects are locked. I can connect with DBA later or after a time lapse my session will go inactive or will get closed – but it is painful at times when we keep working logically and keep writing queries in the editor without copying in notepad or somewhere. In this case, if I kill the sql developer process from Windows Task manager, then pressing F8 does not give me all those sqls back. Please suggest something. Sorry for big post. I am using SQL developer — Oracle IDE 21.2.1.204.1703. Thanks.
Abhivyakti Mirajkar
Hi Jeff,
Installed 21.4.2 on MacBook Pro M1 Monterey 12.1
The font size in the object browser seems to be fixed on a specific size. When I have it on 4K monitor it still show like size 16/18. The worksheet and editor scale properly (size 12 and shows like size 12 should display on a 4K monitor (3840×2160) . Running Java JDK 8
Of course Windows 10 behaves normal on 4k screen.
Log4j.jar is a vulnerability. Does deleting this java folder break sql dev?
I answer your question here, but basically, ‘no’ – I would suggest you test this first however.
Great product and customer support Jeff!
When viewing the error logging page in SQL Developer, how do I determine where to find the source code that is generating an error? For example, I get the same error repeatedly under the source: “o.d.db.DBUtil” When I copy and paste out of the grid, that value is expanded to “oracle.dbtools.db.DBUtil” This error statement is generated
I’ve diagnosed the problem: a statement issued by SQL Developer is looking for the “type_object_type” column in the data dictionary view ALL_ARGUMENTS — but that column was introduced in 18c and I work on 12c. I just can’t figure out how to find the source code and comment out that column in the SQL statement. I think it’s related to the code editor’s completion insight feature because errors are logged when I am typing into the SQL worksheet, even prior to executing a command.
Cheers and thanks for all the hard work your team does to provide an excellent product at zero cost to Oracle users.
Can you grab 21.4.2 and try again?
I’m connected to a 12.2. instance and I’m not seeing any queries to all_arguments or errors around type_object_type. What are you doing when you see this query pop up?
Hi Jeff,
Update regarding the below case:
We have figured out the issue,some tables have policies defined on it , this causes our problem , we exempted the user X (which is REST enabled schema ) from the policy and the issue was solved,but this is not an applicable solution because these polices are important and required in our business , please note that this is only happening the ORDS roles/privileges are defined.
So, in conclusion, there is a conflict between ORDS roles and privileges and the policies defined on the table.
Below the case:
We are having a strange issue with ORDS we used to run ORDS 19, and then decided to upgrade to ORDS 21.4, the installation went fine and our stored pl/sql packages ran as the suppose to , but after we build our previous role and privileges for our authentication OAUTH 2 some of our REST services are returning null objects although the HTTP response code is 200 (ok), this is only happening when i built the role/privilege in the new ORDS
– My rest service PL/SQL :
DECLARE
OUT_OBJECT CUSTOMER_TYPE;
REF_CUR SYS_REFCURSOR;
RETURN_STATUS NUMBER;
BEGIN
OUT_OBJECT := CUSTOMER_TYPE ();
PW04CUSA0.W04CUSA0 (INP_TELL_BRA_CODE => :INP_TELL_BRA_CODE,
INP_TELL_ID => :INP_TELL_ID,
OUT_CUSTOMER => OUT_OBJECT,
RETURN_STATUS => :RETURN_STATUS);
OPEN REF_CUR FOR SELECT * FROM TABLE (OUT_OBJECT);
:CUSTOMERS := REF_CUR;
END;
when :CUSTOMERS is an out string of type RESULTSTRING
-and the response i get from POST MAN: {
“CUSTOMERS”: []
}
– My role / privilege:
BEGIN
ORDS.create_role(
p_role_name => ‘360_role’
);
COMMIT;
END;
/
DECLARE
l_roles_arr OWA.vc_arr;
l_patterns_arr OWA.vc_arr;
BEGIN
l_roles_arr(1) := ‘360_role’;
l_patterns_arr(1) := ‘/JET_360/*’;
ORDS.define_privilege (
p_privilege_name => ‘360_priv’,
p_roles => l_roles_arr,
p_patterns => l_patterns_arr,
p_label => ‘360 Data’,
p_description => ‘Allow access to the 360 data.’
);
COMMIT;
END;
/
Note:The reason we decided to upgrade to ORDS 21.4, is that we noticed the performnace is very bad when we went live with our production environment.
Note:Our database version is 11G and our application server is TOMCAT 8.5.58 and java JDK 1.8.0_261
Also, is there any prerequisite for ORDS 21.4 that might cause such an issue.
Your help is appreciated
Muhammed Emad
I need more info on this, like an example of your table and policy.
Dear Jiff,
We have opened a SR with Oracle and they responded that “There is a existing bug for similar issue and it is not yet fixed BUG – ORDS OAUTH TOKEN EMPTY RESPONSE IN ADB(S) “.
Best Regards.
Hi Jeff
Your blogs are brilliant and extemely helpful. I want to show my students how they can do minimal formatting with oracle sql developer. The example I am using is as follows: select ‘A’ || chr(10) || chr(13) || ‘B’ from dual; what I am getting is all on one line as follows: “AB” instead of
A
B
Any help you can offer would be outstanding
thanks in advance
The query results try to show you as much as possible, on a single, line, in the Excel style data grid. If you pop open the single record view or double click on the cell to get the text editor, you’ll see the new lines. Or, if you just run your query as a script, you’ll get exactly what you’re looking to show your students.
Do you mind if I ask where you’re teaching?
I am currently at Northern Virginia Community College (NVCC).
Just as an fyi, I have taught oracle and pl/sql at Drexel University for approx 18 years.
Also, teach at LaSalle University
Of course NVCC is the only out of state location.
I have one more question if you don’t mind.
I am using sql developer (21.4.1 I think because I am not on my teaching computer) and when I go to create an erd, per your outstanding blog, I don’t get relationship lines. I checked and have PK, FK, etc all correctly defined and I think I have tried ever option available…again any assistance you can give would be greatly appreciated. Many thanks in advance.
Ping me when you get back to your teaching computer, if the foreign key constraints are there, when you import your objects from the database, those lines should be drawn.
Do you have access to a sample schema like HR, SH, or OE? Testing those would be a good way to see if there’s a problem with your environment, our software, or something else.
Jeff
I will log on to it around 5 pm east coast time today.
we can screen share if you like
somehow, since I am using xe (because it has a smaller footprint and easier for the students to install by themselves) none of the standard example (play) databases got loaded down.
Those should be on the latest XE by default…and sorry, I don’t have the ability/bandwidth to to 1:1 support/zoom calls…
Jeff, I agree it always used to be part of, I think, all versions of oracle. Kind of dismayed that the sample databases did not download with xe. I used all the defaults so I have no idea what went wrong.
When I click on a function in SQL Dev, I only see “create or replace” and nothing further. In Toad, I do not have this issue. What could be the problem? Thanks.
Hi!
We are using SQL*Developer 21.1.4
Since we are using proxy-users, our private user only has create session privilege.
The trouble is when trying to change the password using the Reset password function in SQL*Developer.
Logged in as my own user (not with proxy user) I only got “ORA-01031: insufficient privileges” hven changing the password. I’m quite sure it worket in an earlier version.
Changing the password through sqlplus and the password function works fine.
—
Regards
Audun
SQL*Developer is the correct version v. 21.4.1.349
I’ve already logged a bug with proxy user connections and password resets.
Hi Jeff
I’ve followed the hints in your note Oracle SQL Developer: Make it Run Faster! but comparing the performance of even the most simple SQL statement between Version 20.2.0.175 and Version 21.4.1.349 , V21 is much much slower .
Thinks taking sub second in V20 are taking multiple second in V21 to return.
It does not look like it’s DB time, but rather the time in the UI
Are their are other tips to for making SQL Developer faster?
You’re running into a bug with 21.4 and 21.4.1
Go into preferences, and the Insight page. Disable the last check box, Semantic…, Click OK, restart SQLDev.
We’ll have a 21.4.2 out next week to remedy this.