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
Hi Jeff,
I’ve encountered an issue with SSH Hosts in SQL Developer 21.4.1.349 on Windows 10 that I’m hoping you can advise on. After creating a new SSH Host, when testing the connection the following message appears:
An error occurred while opening the host connection.
No more authentication methods available.
Using the same Windows 10 computer, I can connect to the remote host using BitVise SSH Client without any issue. In both cases I’m using the exact same key for authentication.
I have also tested the same SSH connection using SQL Developer 21.2.1.204 on Mac OS Catalina without any issues. I’ve used the same key for authentication.
Is there something more I need to do on Windows to establish an SSH connection with SQL Developer?
Thanks,
Jason
That error message is pretty generic, it basically means the client tried every which way it knows to authenticate, and they all failed. Sounds like a bug if the exact same thing works on your Mac vs Windows though. It might be easier to just create the connections/putty locally on the machine and connect through them in SQLDev instead of using SQLDev to create/manage the SSH connection.
If you need help, I suggest opening a Service Request with My Oracle Support.
One last thing, you could enable debug mode for SQLDev to get more verbose logging to see if that reveals anything going amiss in the connection test.
Hi Jeff, is ORDS 19.2 certified with database 19c? No APEX or REST involved, we are using the ORDS 19.2 jarfile (with tomcat app server) as a plsql gateway for a plsql web application.
ORDS 19.2 documentation just says requirement is 11.1 db or higher. But certification tab in MOS confusingly says ORDS 19.2 is only certified with DB 11.2 to 12.2.
Thanks
Ords 19.2 is 2+ years old, you shouldn’t be using it at all.
Go grab 21.4 and use that.
Yeah, background – we implemented ORDS 19.2 a couple of years ago and now want to upgrade our 12,2 Db to 19c and thought to avoid having to upgrade ORDS .. Would that work or should we pgrade ORDS to 21.4 at the same time?
There’s really no dependency between APEX and ORDS.
We’re not using APEX, it’s purely a plsql,app. Does ORDS needed to be certified with the DB version (as long as it meets minimum requirements of 11.1)?
11gR1 isn’t supported, hasn’t been tested in years. ORDS will work on any supported version of Oracle Database – that is, versions of the database Oracle still supports.
Thanks. Is there an end of life support date for ORDS 19.2?
There are no patches for ORDS so should we be periodically upgrading ORDS to the latest release? Or not necessary if we are not encountering any bugs.
you should be upgrading at LEAST once a year
A user has identified that the following SQL is being executed by SQL Developer at some point in time:
select distinct aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.argument_name,
aa.position, aa.data_type, aa.data_length, aa.in_out, aa.type_name, aa.type_subname, aa.type_link,
aa.type_object_type, aa.pls_type, aa.char_length, aa.char_used, aa.defaulted from all_procedures ap, all_arguments aa where aa.object_id = ap.object_id and ap.subprogram_id = aa.subprogram_id and aa.package_name = ap.object_name and aa.owner = :OWNER and aa.package_name = :PACKAGE order by aa.owner, aa.package_name, aa.subprogram_id, aa.object_name, aa.overload, aa.position
The statement is not being run by the actual user.
This then is resulting in the following in the alert.log:
WARNING: too many parse errors, count=100 SQL hash=0x9f893bf2 PARSE ERROR: ospid=7941, error=904 for statement:
2022-01-06T15:15:54.039523-05:00
for that statement.
Question: Is something missing in the query, like:
and aa.owner = ap.owner
?. A package could exist with various owners and so whatever is running this would need to only get the arguments for the version owned by the user requested. And user A could have different parameters for their version of the pakcage than the version owned by user B.
So the ‘distinct’ is probably in there due to the same parameter existing for the same package with various owners.
Perhaps add the above join and get rid of the distinct as it is probably returning too much data.
Thoughts?
The statements log panel will show this query and anything else – what are they doing on screen when they see it?
Sorry, ‘statement logs panel’? The DBA is reporting the error to the user when found in the alert log. DBA indicates that the SQL came from SQL Developer. Would that query be run when expanding packages/procedures in the Connections panel?
I’m saying the user can observe the queries being executed on their behalf in the Log panel.
So if you go expand the plsql tree items in the connection panel, you can see those queries.
That query indeed is a problem, it’s tied to a bug in 21.4. They can disable the last option on the Completion Insight page, restart SQL Dev, and that should fix things up.
We’re working on a 21.4.2 update to address this and a few other bugs.
jeff
Hello Jeff,
My co-worker Jeff asked you a week ago regarding to Apache Log4j vulnerability remediation for sqldeveloper in Oracle homes.
You’ve advised that we can safely remove the folder from the Oracle Home. I’m wondering if this also applicable for EBS Oracle Home as well or not. If so, can you please send me any document or reference?
Thanks,
Naeem
I only handle my products, and that’s more than enough work. EBS is a completely different part of the company – you should refer to the published documents in My Oracle Support re: log4j, or open a SR and ask the EBS team.
Dear Jeff, yes this is my problem, am I getting 200/empty set when that returned data type is resultset when applying the Oauth2.
Best regares
Hi, Jeff. Thank for your reply!
I don’t have DDL, because I get this screenshot from one website. I interesting in what does mean this relationships? https://ibb.co/BPhH4jh
There’s a relationship between those two tables. An ‘Accounts’ entry will be tied to a ‘Clients’ entry…looks to be a 1:1 relationship, I think.
Hi, Jeff. You replied on my previous comment. You asked: “Why do you have tables defined in SYS?”. That diagram is not my. My diagram has same relationship and I don’t understand how to get it. My original diagram is here: https://ibb.co/CWKpW6t
If you open the relationship properties, what do you see?
If you preview the ddl for those objects, what do you see?
Dear Jeff,
Any update please?
Regards.
So, you need help reading the pictures?
Hi Jeff,
I’m often confronted with the task to compare the data of two tables between different databases and users.
Regarding the structure of object we have this wonderful feature of database diff.
Would it be possible to implement a data diff as well? Icing on the cake would be to let the user decide which columns should be compared in this diff.
Best wishes for 2022
Sören
Hi Jeff,
Row comparison.
It can be very useful to quickly find difference between 2 rows in a visual manner, especially when they are almost the same (e.g. ‘a’ ‘a ‘). Double clicking and writing a query against such tables isn’t quite convenient.
Can you introduce an one-click feature like this?
Illustration: https://www.allroundautomations.com/wp/inhoud/uploads/pls140-sw-compareresults.png
This example is from PL/SQL Developer and I really miss it in SQL Developer!
Thank you,
Adam
I understand what you’re asking for.
Hi Jeff,
My best wishes! I have a question regarding Table DDL transformation scripts. After updating to Version 21.4.1.349 Build 349.1605 my Table DDL Transformation scripts aren’t executed anymore. The scripts are visible in the specific window (), I can select a set but I can’t debug anymore (button gone) and running the test functionality doesn’t take into account this script
Are you using Oracle JDK 8? If so, can you try 11 to see if that fixes it?
Hi Jeff, installing JDK jdk-11.0.13 partially worked: the ‘Test’ functionality works and scripts are added to DDL Preview. The Debug Button though is still missing. I can now continue using the scripts I prepared
Thanks for the feedback!
Hello, Jeff. How to create the relationship on the screenshot (I marked it red) in Oracle Data Modeler?
https://i.stack.imgur.com/nO2G9.jpg
Before we go any further, why why why do you have tables defined in SYS? That’s a big, no-no. SYS is reserved for the database itself. Don’t use that account, for pretty much anything.
Dear Jeff,
Any update?
Best Regards.
Hi Jeff.
How can I paste NULL in the worksheet of SQL Developer?
Regards.
NULL is the absence of data, so what would we be pasting exactly?
When adding some records to a table, all columns are already null, which is fine. What I’m asking is when I want to update lots of records that are not null with null. Pasting new data over columns works well, but I see no way to paste “null” .
NULL is nothing, so there’s nothing to paste. Just empty the cell.
Working through Apache Log4j vulnerability remediation and have a question. We have reviewed, and understand the process to update “standalone” SQLDEV “unzipped” installs to update to the 21.4 version for remediation, but should we be concerned with the SQLDEV installs that come as part of database RDBMS software installs similar to the example below?
We run versions 11gR2 to latest 19c across Windows, Linux and AIX and see this SQL Developer directory structure in all homes. Should we be concerned?
oracle CRMSPURG /u01/app/oracle/product/11.2.0.4.200414/sqldeveloper: ls
BC4J icon.png j2ee jdev lib raptor_image.jpg relnotes.html sqlcli.bat sqldeveloper.exe
dvt ide jdbc jlib otn_new.css rdbms sqlcli sqldeveloper sqldeveloper.sh
Is there a way to delete or update this SQLDEV installation? I see no option in OUI to pick or single out just this product.
Any feedback is appreciated.
Jeff
i.e.:
You can safely just remove the sqldeveloper directory from those older oracle home environments.
Thank you for the confirmation and quick reply. Cheers and Happy Holidays!
Hi Jeff,
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
Are you getting a 200/empty response when you submit an oauth2 secret/ID?
If so, try dropping and re-creating the ROLE. There’s a bug in the OAuth2 feature set, but it’s surfaced when your client is granted a role outside the current schema like ‘SQL Developer’ or ‘SODA Developer’ – so not sure this is your problem.
Hi Jef,
With lots of with admiration and enthusiasm, I have just downloaded the latest version of SQL-DEV (JDK included).
SQLcl works perfectly I just can’t get the java scripting to work
script
write.ct(‘hi’)
/
js language engine not found
Please add js language engine to the classpath
What am I doing wrong?
Kind regards
Dbitcha
Bug. We’ll have it fixed in the New Year. If you can’t wait, grab and use a JDK11, and it should work A-OK.
Hi Jeff,
Is this issue fixed in the new 21.4.2 release ?
Kind regards
Dbitcha
Yes.
Hi Jeff,
Thanks for your support on this and other forums!
I’m trying to set up liquibase from sqlcl and am running into some issues.
Versions:
SQLcl 21.4 build 21.4.0.348.1716
RDBMS 19.11
APEX 19.1.0.00.15
1)
Following command works:
lb genobject -type apex -applicationid 103
(even though the location of the file is unexpected, if my working directory is /home/oracle the file will end up as /home/oracle/home/oracle/f103.xml)
Same command with additional parameters fails:
1a)
SQL> lb genobject -type apex -applicationid 103 -split -dir /tmp
Processing has failed for your request.
ERROR: Unknown parameter -dir
1b)
SQL> lb genobject -type apex -applicationid 103 -split
Exporting application 103
Completed at Thu Dec 23 11:38:08 CET 2021
Processing has failed for your request.
Unable to retrieve object metadata are you sure it exists?
2) Also this command fails (output shortened and obfuscated)
SQL> lb genschema
Export Flags Used:
Export Grants false
Export Synonyms false
[Method loadCaptureTable]:
Executing
[…]
End
[Method loadCaptureTable]: 42217 ms
[Method processCaptureTable]: 34004 ms
[Method sortCaptureTable]: Processing has failed for your request.
ORA-00001: unique constraint (XYZ.DATABASECHANGELOG_EXPORT_PK) violated
ORA-06512: at “XYZ.SQLCL_LB_CAPTURE”, line 205
Are these bugs or am I doing something wrong?
APEX and split is an APEX bug.
The PK constraint violation i’ve seen reported by one other customer, but I’m trying to find a test case I can use to reproduce.
How big is your schema? Any chance you could export the ddl for it and share it with me privately ([email protected]) ?
Or…try it again but with the -debug flag on, more output may expose what’s breaking
Hi Jeff,
Is it possible to access databases through SQL Developer Web using a Windows OS authenticated (OPS$….) user?
If not, is this planned for the future?
Many thanks,
Tim
Adding additional authentication methods to SQL Developer Web is definitely on the roadmap.
But, how we would get OS Auth working across a mid-tier/HTTPS is not clear to me.
Hi Jeff,
I’ve been running SQL Developer 18.1.0.x for several years now, but I’d really like to upgrade and stay current with the new releases. I’ve been holding out because I can’t seem to get the current code formatter to match the format I’ve grown accustomed to in v18.x. I use this feature constantly, so it’s been a major roadblock to adopting the current release.
I’ve tried matching my settings 1-to-1 between versions, and I’ve even experimented with the Arbori custom format; all to no avail. The main setting I can’t seem to adjust is line breaks within functions. Even a simple TO_CHAR() in a SELECT goes from this (v18.1.0.095.1630):
TO_CHAR(TRANS_DATE, ‘MM/DD/YYYY’) AS “TRANS_DATE”,
to this (v21.4.1.349.1822):
TO_CHAR(
TRANS_DATE, ‘MM/DD/YYYY’
)AS “TRANS_DATE”,
This makes the code nearly impossible to read when there are more complex functions in a query. For example (disclaimer: not my code), this snippet goes from:
DECODE(NORMAL_BAL, ‘C’, DECODE(DR_CR_IND, ‘+’, TRANS_AMT, 0), ‘D’, DECODE(DR_CR_IND, ‘-‘, TRANS_AMT, 0) ) AS “CREDITS”,
to this in v21.x:
DECODE(
NORMAL_BAL, ‘C’, DECODE(
DR_CR_IND, ‘+’, TRANS_AMT, 0
), ‘D’, DECODE(
DR_CR_IND, ‘-‘, TRANS_AMT, 0
)
)AS “CREDITS”,
Any insight or suggestions?
Much appreciated!
Please post your question to the forums so our formatting experts/community can help you out.
Hi Jeff,
I am still facing the same issue in SQLcl 21.4 as described in https://stackoverflow.com/questions/68457702/sqlcl-cant-perform-liquibase-update-for-a-simple-changelog .
Do you have any advice what try to do next or if I should request the support to fix this bug?
Thank you!
Josef
Hi Jeff,
Thanks for your blog 🙂
I’m attempting to install SQL Developer Data Modeller on a MacBook Air M1 chip. I’ve installed JRE 8 (x64) and can see it on the Mac but whenever I launch the SQL Data Modeller installer, it can’t find the JRE. Not sure if this is due to it being an M1 Mac.
Thanks
Jerry
You’re probably going to need the Java 17 distribution for the M1 (macOS Arm 64)
Hi Jeff,
I make good use of the Snippets panel in SQL Developer. One thing I’ve not been able to do is sort my snippets list. Is there a way to do this?
Thanks!
Sorting and Searching of Snippets is HIGH on my list for Enhancements for SQL Dev in early 2022.
Hi Jeff, hope you are well.
Loving the ORDS stuff for creating endpoints for various things.
I have started having to produce my own JSON response using apex_json, as we need to do some security checks when doing anything with our data first.
Is there any way I can add a custom response body to the swagger document that’s produced?
Thank you and Happy Holidays.
>>Is there any way I can add a custom response body to the swagger document that’s produced
No, there’s just the comment/note field you can populate at the moment.
Hi,
I’m trying to setup SQL Developer Web in Docker using the fuzziebrain scripts. I’ve got XE21c and APEX and ORDS running but when I open http://localhost/ords/sql-developer I do see the username input screen but in the logs there’s a stacktrace saying “oracle.dbtools.injector.DependencyNotAvailableException: The provider oracle.dbtools.rest.resource.generator.ResourceGeneratorConnections is not available”
I have to add that the scripts didn’t work out of the box with regards to Java setup. I first decided to go with INSTALL_FILE_JAVA=openjdk11 but the yum command didn’t run because it wanted to be run as root. Then I provided server-jre-8u311-linux-x64.tar.gz but this also wasn’t installed. I then installed OpenJDK11 manually as root and also had to add the install dir to the path.
Do you have any idea what might be causing this? Thanks!
Here is as search for “java” and the ENV output:
bash-4.2# find / -name java
/etc/alternatives/java
/etc/pki/java
/etc/pki/ca-trust/extracted/java
/etc/java
/usr/share/java
/usr/lib/java
/usr/lib/jvm/java-11-openjdk-11.0.13.0.8-1.0.1.el7_9.x86_64/bin/java
/usr/bin/java
/var/lib/alternatives/java
/opt/oracle/product/21c/dbhomeXE/xdk/doc/java
/opt/oracle/product/21c/dbhomeXE/jdk/jre/bin/java
/opt/oracle/product/21c/dbhomeXE/jdk/bin/java
/opt/oracle/product/java
/opt/oracle/product/apex/images/libraries/monaco-editor/0.22.3/min/vs/basic-languages/java
bash-4.2# env
INSTALL_FILE_APEX=apex_21.2.zip
HOSTNAME=26d93f397130
ORACLE_BASE_HOME=/opt/oracle/product/21c/dbhomeXE
SHELL=/bin/bash
TERM=xterm
ORACLE_DOCKER_INSTALL=true
INSTALL_DIR=/install
CHECKPOINT_FILE_EXTN=.created
DOCKER_NETWORK_NAME=oracle_network
ORACLE_SID=xe
SETUP_LINUX_FILE=setupLinuxEnv.sh
DATABASEAPI=Y
[email protected]
CHECK_DB_FILE=checkDBStatus.sh
INSTALL_FILE_1=https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-21c-1.0-1.ol7.x86_64.rpm
ORACLE_BASE=/opt/oracle
APEX_PUBLIC_USER_PWD=oracle
CONF_FILE=oracle-xe-21c.conf
CHECK_SPACE_FILE=checkSpace.sh
ORACLE_PDB=xepdb1
ORDS_PUBLIC_USER_PWD=oracle
INSTALL_FILE_JAVA=server-jre-8u311-linux-x64.tar.gz
PATH=/opt/oracle/product/21c/dbhomeXE/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
PWD=/home/oracle
APEX_LISTENER_PWD=oracle
REST_ENABLED_SQL=Y
DOCKER_EM_PORT=5050
PWD_FILE=setPassword.sh
RUN_FILE=runOracle.sh
CREATE_DB_FILE=createDB.sh
DB_VERSION=21.3.0
SHLVL=3
HOME=/root
USER_SCRIPTS_FILE=runUserScripts.sh
DB_EDITION=xe
APEX_REST_PUBLIC_USER_PWD=oracle
DOCKER_DB_PORT=1523
OML4R_SUPPORT=N
DOCKER_ORDS_PORT=8080
APEX_ADMIN_PWD=oracle
RTU_ENABLED=N
ALLOW_DB_PATCHING=N
SQLDEVWEB=Y
ORACLE_PWD=oracle
ORACLE_HOME=/opt/oracle/product/21c/dbhomeXE
INSTALL_FILE_ORDS=ords-21.3.1.301.2050.zip
_=/usr/bin/env
If you’re using FuzzieBrain’s scripts..sorry, not familiar with those, and you’re having issues, why aren’t you asking him for help?
Also, we don’t support OpenJDK.
Hi Jeff,
We are planning a small APEX application (max 20 users concurrent), to publish databases information(grow of tablespaces, Oracle database uptime etc).
We think ORDS standalone is in our case a good choose. We need to install a JDK on our Linux host. APEX 21.2 is certified with ORDS 21.3. ORDS 21.3 is certified with Oracle JDK 1.8.0_311.
We don’t need an APEX or ORDS license. Need we a JDK license or is it not necessary in our case?
Thanks in advance
You don’t need a JDK at all.
Just grab a JRE.
However, any Oracle product requiring a JDK to run, does not require a license for the JDK. So even if you had a JDK, it wouldn’t’ require to be licensed as you’re using it for ORDS, which requires Java.
Hi Jeff,
Thank you very much for your answer. A JRE is a good solution in our situation.
Regards
Henk