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,867 Comments
Ubuntu 22.04 sqldeveloper checkbox save password cannot be checked.
try SQL Developer 24.3.1, 23.1.1, 22.2.1 on OS:Ubuntu 22.04 and java version “17.0.6” 2023-01-17 LTS.
All have this problem.
For sql developer 24.3.1, in Messages – Log window, sometimes will show Unexpected runtime exception while delivering HashStructureHookEvent’, sometimes no this error message, but in both case the save password still cannot be checked, so I think this error is not related.
There is some warnings message in command prompt windows when start sqldeveloper 24.3.1, as follow:
xxx@xxx-virtual-machine:/opt/sqldeveloper-24.3.1$ ./sqldeveloper.sh
Oracle SQL Developer
Copyright (c) 2005, 2024, Oracle and/or its affiliates. All rights reserved.
Java HotSpot(TM) 64-Bit Server VM warning: Options -Xverify:none and -noverify were deprecated in JDK 13 and will likely be removed in a future release.
WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by org.netbeans.TopSecurityManager (file:/opt/sqldeveloper-24.3.1/netbeans/platform/lib/boot.jar)
WARNING: Please consider reporting this to the maintainers of org.netbeans.TopSecurityManager
WARNING: System::setSecurityManager will be removed in a future release
WARNING: A terminally deprecated method in java.lang.System has been called
WARNING: System::setSecurityManager has been called by oracle.ide.IdeCore (file:/opt/sqldeveloper-24.3.1/ide/extensions/oracle.ide.jar)
WARNING: Please consider reporting this to the maintainers of oracle.ide.IdeCore
WARNING: System::setSecurityManager will be removed in a future release
java version:
xxx@xxx-virtual-machine:/opt/sqldeveloper-24.3.1$ java -version
java version “17.0.6” 2023-01-17 LTS
Java(TM) SE Runtime Environment (build 17.0.6+9-LTS-190)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.6+9-LTS-190, mixed mode, sharing)
I already asked on your stackoverflow question, what messages do you see in the log panel?
Jeff,
Can we activate multi-line selection in the datagrid within VS Code? If that’s not currently an option, are there plans to implement this feature in the future?
It’s there now, you have to ctrl+cmd CLICK each row…but we’re going to add shift+mouse and ctrl+A support for selection and for doing range selection of cells and columns as well.
How about windows?
I tried Windows + Ctrl + Click, Ctrl + Alt + Click, Ctrl + Shift + Click but nothing seems working.
NVM, I got it. Control + Click at row number selects the line.
I am testing First Party Basic Authentication on ORDS. I want to
1- Add a new user (hradmin) to the ORDS standalone web server (Listener)
2- Assign a predefined role to that that user
I can add a user using this command –> ords config user add hradmin “HR Administrator”
But I can’t assign a role that I have created prev. (called emp_role) to hradmin user. I’ve been trying the following command and none of them work.
C:\ords.24.3.1\bin\ords user hradmin emp_role
or
java -jar ords.war user hradmin emp_role
in both cases ords giving me the messages asking me to use other command line options
My question: What is the command to add a user and asign a role to it in ORDS? I know there is a way to do so in Tomcat.
ords version is 24.3.1
Thanks
Houman .
If you look at that credentials file, you can see the user listed along with the roles
Hi Jeff,
I started using SQLcl and I really like it.
I have a question regarding the ” set sqlformat ansiconsole -config=hightlight.json”
Where should we create this file ?
On a windows pc and on Linux Server
Because I tried in the SQLcl directory and to other directories but I always get the error :
Invalid File : highlight.json
Take note I’ve got the info from your site
here : https://www.thatjeffsmith.com/archive/2021/10/oracle-sqlcl-all-the-pretty-colors-for-your-console/
And in Kris : https://krisrice.io/2019-04-18-SQLcl-SQLFormat/
And yes, I notice the error in the help about the “endWith” in fact we nee to use “endsWith”
Thanks
here the highligh.json
{“highlights”:[
{“type”:”startWith”,”test”:”P”,”color”:”INTENSITY_BOLD,CYAN”},
{“type”:”endsWith”,”test”:”TT”,”color”:”BLUE”},
{“type”:”contains”,”test”:”RCAL”,”color”:”YELLOW”},
{“type”:”exact”,”test”:”FORD”,”color”:”GREEN”},
{“type”:”regex”,”test”:”[0-9]{2}”,”color”:”MAGENTA”}
]
}
you can put the file anywhere you want, if the CWD isn’t where it’s at, you’ll need to either cd to the directory or provide the full file path
Here’s my example
Your JSON as shared is invalid, it has ‘smart quotes’ .. but not sure if that’s because of the way it was copied/pasted (from Word?)
Hi Jeff,
I really like the SQL Developer extension for VS Code, it has improved my workflow and reduced the context switching.
One thing I wanted to check is reg. the Query Result pane/grid.
In the old SQL Developer, we are able to select and run multiple queries and see the results in separate Query Result tabs… Is this functionality available in VS Code extension? If not, is this something your team is looking to implement in future?
Thanks.
YES!
What you can do right now is open multiple sql worksheets and run a query in each, and each will see their query results.
But having ability to see them side-by-side, is something we need AND plan to build this year – including for execution plans.
Hi Jeff,
I am new to ORDS and want to log each API calls automatically (I’m still trying to get my DBA to help setup prehook).
In the meantime, I have defined my POST handler as below
ORDS.DEFINE_HANDLER(
p_module_name => ‘rest’,
p_pattern => ‘item’,
p_method => ‘POST’,
p_source_type => ‘plsql/block’,
p_items_per_page => 0,
p_mimes_allowed => ‘application/json’,
p_comments => NULL,
p_source =>
‘DECLARE
BEGIN
insert into table1(:ITEM_ID, :ITEM_TYPE, :ITEM_QUANTITY);
ins_api_log (:current_user, :content_type, :body_text, v_response);
commit;
END;’
);
I found if ins_api_log in the block, :ITEM_ID is null where as if I comment out ins_api_log line, :ITEM_ID is the bind data from json.
The above plsql/block works without issues with GET, DELETE. Is there a limitation to POST?
Note I checked ins_api_log is working as expected and inserting into the target table with required data.
I would need to see the json payload you’re including on that POST.
Your webserver logs will have what you want, by the way, you’ll see all the incoming HTTP/HTTPS requests, including the ones for ORDS API calls. No need to use a prehook for this, unless you ALSO want to log this in the database instead of in your OS or logging/metrics stack.
I installed the VS Code extension for SQL Developer yesterday and completed importing my connections. But I’m having a weird thing happen when trying to connect to certain databases. We have 2 domains. I can connect to databases in the domain I’m currently logged in to with no issue. In order to connect to a database in the other domain, I have to open VPN to that domain. But here’s the strange thing – I don’t have to do that in SQL Developer, both domains work without VPN. Furthermore, I can open SQLcl in VS Code and connect to any database. It is only when testing a connection or trying to open SQL Worksheet that I get this error:
A connection with the database could not be established. The database may be unavailable, the host or port values may be incorrect or the private endpoint configuration may be invalid. Ensure the database is operational. Check the hostname and port are correct. Ensure the correct protocol is being used. If using a private endpoint ensure it is configured correctly.
Any ideas, I’m at a loss.
Thanks,
Steve
Not sure what you mean about domains, can you go into more details?
Also, when you’re testing SQLcl, can you confirm you’re testing the SQLcl that ships with the VS Code extension? Right-click on connection, and select ‘Open SQLcl’ – that works?
Yes, I am using SQLcl that ships with the VS Code extension and that does work.
By domains I mean network domains. We have 2 that segregate servers geographically. While in the office I can connect to any database using native SQL Developer and SQLcl that is built into the extension. No VPN connection required. But for some reason, connections to the other network domain in the extension fail, both while testing the connection in VSCode or trying to open SQL Worksheet. The only way I can connect to databases in the other domain is if I first connect to the other network domain’s VPN.
So, there must be some sort of difference in how network connections work between native SQL Developer (and SQLcl in the extension) and the VS Code extension.
Hope that makes more sense.
Thanks,
Steve
On the surface this doesn’t sound possible, yet here you are 🙂
In VS Code, if you bump the extension logging level up to TRACE (in extension settings), and try to make the connection, can you share the output panel’s contents?
I set the trace level, but where are the trace files located?
it’s VS Code extension logging, it goes directly to the ‘Output’ panel in VS Code
Unfortuanately, nothing shows up in the output window when trying to login to the database in the other network domain. Just a popup window in the bottom right-hand corner showing:
A connection with the database could not be established. The database may be unavailable, the host or port values may be incorrect or the private endpoint configuration may be invalid. Ensure the database is operational. Check the hostname and port are correct. Ensure the correct protocol is being used. If using a private endpoint ensure it is configured correctly
The settings for trace level includes a selection for enabling logging to a file. Any idea what file it may be creating?
I found the output; it is rather large. Can I email it to you?
Thanks,
Steve
This is probably the part of the trace you are interested in:
Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO handleConnectPacketResponse
INFO: Got Resend, SessionTraceId = 2DB155F5
Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO handleAcceptPacket
INFO: Connection established. Cleared conn strategy stack
Jan 17, 2025 9:58:13 AM oracle.net.ns.NSProtocolNIO sendMarker
INFO: Sending break marker, SessionTraceId = 2DB155F5
Jan 17, 2025 9:58:13 AM oracle.jdbc.driver.PhysicalConnection connect
INFO: throwing
java.sql.SQLRecoverableException: ORA-17002: I/O error: Connection reset, Authentication lapse 0 ms.
Can you email me the entire log plus a dump of your runtime env (os, java, clients, …)
[email protected]
Problem solved! I had a discussion with our network guy, and it turns out that the firewall was blocking the connection because it was not only checking the port but was also checking for an Oracle signature from the software. This was removed and now I connect.
I’ll leave it to you to decide whether this is something that can be updated in the VS Code extension.
Thanks,
Steve
Can you share how this check was being enforced? You have my email…
Hi Jeff,
I am trying to setup ORDS api logging using prehook functionality. From all the pages I have come across, it is referring to https://www.jmjcloud.com/blog/using-the-ords-prehook-function which is returning 404 (even on the homepage).
From what I gathered, prehook call a procedure with no arguments. In my case, I am trying to include the :current_user when inserting into my logging table. When I compile the procedure, I am getting
PLS-00049: bad bind variable ‘CURRENT_USER’
My sample proc is
create or replace procedure pre_hook_ins_api_log as
begin
insert into api_log
( current_user
, request_method
, ords_path, query_string, headers
, remote_host
)
values (
:current_user
, owa_util.get_cgi_env(‘REQUEST_METHOD’)
, owa_util.get_cgi_env(‘SCRIPT_NAME’) || owa_util.get_cgi_env(‘PATH_INFO’), owa_util.get_cgi_env(‘QUERY_STRING’)
, owa_util.get_cgi_env(‘X-MY-HEADER’)
, owa_util.get_cgi_env(‘REMOTE_ADDR’)
);
return true;
exception
when others then
return false;
end;
Can you provide some guidance how to setup my proc?
You won’t pass anything to prehook, it will run in the same session as your request, and you can use :current_user in your function.
Docs here
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/24.4/orddg/developing-REST-applications.html#GUID-FE6D2A44-D02D-4969-B05C-E4A5BC5D9F27
It has to be a function that returns a boolean.
Hi Jeff,
Thanks for the quick reply.
I changed it to a function but it is still getting PLS-00049: bad bind variable ‘CURRENT_USER’
create or replace function pre_hook_ins_api_log
return bool
as
begin
insert into api_log
( current_user
, request_method
, ords_path, query_string, headers
, remote_host
)
values (
:current_user
, owa_util.get_cgi_env(‘REQUEST_METHOD’)
, owa_util.get_cgi_env(‘SCRIPT_NAME’) || owa_util.get_cgi_env(‘PATH_INFO’), owa_util.get_cgi_env(‘QUERY_STRING’)
, owa_util.get_cgi_env(‘X-MY-HEADER’)
, owa_util.get_cgi_env(‘REMOTE_ADDR’)
);
return true;
exception
when others then
return false;
end;
Hi Jeff
I work for a finance company and we use SQL Developer. We are wondering if Oracle will continue to develop this tool in the future or if you will only push the VSCode plugin. Is there a binding date for the end-of-life of SQL Developer?
Thank you very much for your feedback.
Salvi
The Oracle SQL Developer tool will continue…but in the form of a VS Code extension. The delivery vehicle is changing from Java Swing to VS Code. The licensing remains the same. It just gets better.
The current desktop tool will stick around until we have sufficiently moved over all the features. There is no binding EOL date. Each release gets an official 18mos support window – that’s binding.