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,566 Comments
I need to know if it is possible to find invalid java objects with SQL Developer and then compile them. Can do it in TOAD, but would like to know if I can do it with SQL Developer. I can browse them, but nothing else. Thanks
Hi Jeff,
We recently used ORDS to build a quick read-only API for a legacy application, with great success. However, that success has led to ongoing requests for more and broader search criteria, which highlights the difficulties in static SQL, as now we are getting performance issues. To get over this we are having to resort to PL/SQL procedures and losing a lot of the ease of use that drove the initial use.
Dynamic SQL can result in a great deal of complexity, but I remember using Oracle Reports and being able to use “lexical parameters” as well as bind variables. Lexicals injected an additional piece of code into the SQL statement, allowing quite complex search criteria that still only had the essential predicates.
I was wondering if you had considered a similar capability for ORDS.
I thought a relatively simple, and quite useful capability, would be to associate a predicate condition with a parameter, and only include that condition if the parameter is present. That would avoid the “(:param is null or column = :param) we keep compounding, but more importantly would handle the need to add an EXISTS clause.
It preserves all that useful automatic handling and declarative ease of use/
The search is built in, you can use the URI to manipulate the predicates/sorting used on a SQL based or Auto TABLE REST end point. I have examples here.
That works in terms of a set of generic filters on returned values, but also imposes a degree of complexity on the user of the API to perform common use cases. An example, we have a “contract_type” value, that fall into two major logical groups, “pricing” and “operational”, so we use those as parameters and apply the list of values, which can change, as an IN. And those are the relatively simple requirements. How do we address more complex, but in our case, common requirements. This is especially true for calculated values, or dependent columns that do no appear in the view, where we need to include an EXISTS clause. An example, “which contracts include product XYZ”, which requires an EXISTS on contract_lines.
Most of our users are spreadsheet users, not database users, so the KISS rule applies.
You’re describing something that’s easily solved with an APEX app.
Jeff, when I am trying to create a procedure that contains an error, SQLcl won’t report the error back to me automatically as I am used to from SQL*Plus. Is this by design or am I missing something?
rene@ORA19> create or replace procedure drop_me as begin no_such_function; end drop_me;
2* /
—> Here, I expected an error message, yet none is shown
The procedure DOES have an error:
rene@ORA19> select text from user_errors where name = ‘DROP_ME’;
TEXT
—————————————————————————————————————
PL/SQL: Statement ignored
PLS-00201: identifier ‘NO_SUCH_FUNCTION’ must be declared
Works for me using SYSTEM using sqlcl on Windows10 [using cygwin shell].
=====
SQLcl: Release 20.3 Production on Wed Nov 11 08:21:26 2020
[…]
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.5.0.0.0
SQL> create or replace procedure z as begin nullx; end;
create or replace procedure z as begin nullx; end;
Procedure Z compiled
LINE/COL ERROR
——— ————————————————————-
1/22 PL/SQL: Statement ignored
1/22 PLS-00201: identifier ‘NULLX’ must be declared
Errors: check compiler log
SQL> exit
exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.5.0.0.0
Version of SQLcl? On a compile, I’m pretty sure we do an automatic ‘Show Errors’
In 20.3, creating a procedure with an error
Jeffreys-Mini:bin thatjeffsmith$ ./sql hr/oracleSQLcl: Release 20.3 Production on Thu Nov 12 12:10:03 2020
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Thu Nov 12 2020 12:06:15 -05:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create or replace procedure has_errors is
2 begin
3 null
4 end has_errors;
5 /
Procedure HAS_ERRORS compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "END" when expecting one of the following: ; The symbol ";" was substituted for "END" to continue.
Errors: check compiler log
SQL>
Long time SQLDeveloper user. Currently on 20.2
1) When I start SQLDeveloper IndexPreferenceTask is executing for several minutes. Can you please give some clarity about what is going on under the hood? How can I speed this up?
2) When two SQLDevelopers are open, none of them will recognize externally modified files any more 🙁
It’s indexing the preferences dialog, so you can search all of the preferences. It shouldn’t block anything in the UI except for opening the preferences themselves.
On externally modified files, that works if you only have one copy open?
Well, technically the GUI is “operational” but my IndexPreferenceTask runs for over three minutes (just timed it) and during that time I guess you can say that everything works but there are lag times of 5-10 seconds for certain operations. It feels like using an app on a workstations with not enough RAM and every new operation needs to page memory in and out (more likely the application is running the IndexPreferenceTask in the same thread as the front end).
Regarding Externally modified files: I am not able to reproduce the issue in 20.2. I did not double check this version before I posted 🙁 However this has been a feature for many years, and yes I have always had the option to reload externally modified files enabled)
Version seems to be 19.2:
PS C:\Users\Rene\bin\sqldeveloper\sqldeveloper\bin> .\sql.exe -V
SQLcl: Release 19.2.1.0 Production
PS C:\Users\Rene\bin\sqldeveloper\sqldeveloper\bin> .\sql.exe rene/rene@ora19
…
rene@ORA19> create or replace procedure has_errors is begin null end has_errors;
2* /
rene@ORA19>
– – – –
I have downloaded the what I believe to be newest version of SQLcl, but this one won’t connect to Oracle at all.
What happens when you try to connect?
In SQL Developer.
I would like to create a custom sub connection for my db connections that store server hostname, a username, and password. The host name can be pulled from a db table, the username and password will need to be provided by the user.
I would like a connection context menu item that enables/disables a flag, that I can use later for making decisions. I would also like to know how to access that flag.
Hey Jeff,
I want to create a custom worksheet action that executes Run Statement and then some custom stuff I’ve written. How do I create a custom worksheet button and leverage existing code for Run Statement?
I don’t believe there’s a way to do that with our XML or Java plug-in framework.
What is your ‘custom stuff?’
I want to be able to run the SQL from worksheet and get results. (Normal run)
I then want to translate the SQL to Java Code and get the results of that (My custom stuff)
I currently have the code for my custom stuff working, I just want to add it to SQL Developer
There is some problem in the download for Data Modeler 20.3. Even after accepting the license terms I get a message “can’t be downloaded securely”. There was no issue with ORDS and SQLCl
I just tried and successfully downloaded the OS X zip…
Hi Jeff,
I’m having an issue with APEX_PUBLIC_USER FOR ORDS, similar to the error that is stated here, i have reset the password but it doesnt solve anythiing. can you give me some guidance mate.
I think i may have configured something wrong idk.
Here are the details: https://stackoverflow.com/questions/64599812/ords-oracle-rest-data-services-503-error
The error message is clear. Either the password is wrong, or the account is locked/expired. Make sure the account is good. Then update the pool XML file appropriately to reflect the right password.
When using SQL Developer CART to export to Excel, I am unable to remove the Owner as part of the newly created worksheet name when selecting multiple tables to a single excel file. There is only so many characters I can use in the name of the tab when all I want is the table name for the worksheet name (not owner.tablename). Do you know of a way to disable the owner as part of the worksheet name or is this a feature?
Hi Jeff,
Not sure you answer SQLCL liquibase questions, so feel free to ignore if it is not the right place.
I’d like to do liquibase diff with difftypes=data,
I see the lb command line from SQLCL doesn’t support that.
I can use SQL Dev to do an insert,
however, then it is where I need to disable PK / FK for loading that,
for which I’d have to write some custom code.
Hence I thought for DevOps Liquibase would come in handy.
Please advise on the preferred way to go.
we’re not doing anything with data at the moment, so it’s on your to manage those with your own changelogs, via INSERT scripts most likely
Hi Jeff,
is there a way to lock the connection folder structur? Sometimes I move a connection from one folder to another by accident and have to search through all the folders. One time I had to export all my connections to look up the path in JSON.
Thank you!
Oliver
Hi Jeff, I think I found a bug in SQLcl: Release 20.2
When you have the password expired and you try to change, if you repeat and old password yo have this error:
ORA-28001: the password has expired
New password: ********
Retype new password: ********
In command –
connect …
Error report –
Connection Failed
USER = orcl
URL = jdbc:oracle:thin:@ORCL
Error Message = no ocijdbc19 in java.library.path
And the password not change.
But if you do from SQLPLUS the message is:
New password:
Retype new password:
ERROR:
ORA-28007: the password cannot be reused
Help or Enhancement suggestion. SQL Dev 20.2
Either I haven’t yet found out how to export / import all the connections in the DBA pane, or you can’t. I’m a DBA so use SQL Dev a Lot in that role, depending on connections in the DBA pane. When exporting everything I can for import to another machine, I always have to add connections to the DBA pane one-by-one. It’s a pain.
If that could be an option when exporting Connections – Include DBA Connections – it would be greatly appreciated.
Thanks.
Jack
Yeah – I agree that should be better. I’ll file an issue for you.
SQQL Dev 20.2 Database 19.3
How do I get all of the non-ASCII characters to display as they do in Excel? For example, when I paste from the worksheet in SQL Developer I get
Bengali bn Indo-European বাংলা
Bihari languages bh Indo-European भोजपुरी
Bengali shows as tiny squares. I have set the encoding to UTF-32
We’re Java, so unicode by default for everything in SQL Developer. Just make sure your Editor Font supports the characters you want to work with.
Hi Jeff,
Can you tell me if a single OAUTH2 client credential be granted access to REST modules in multiple schemas, or is an OAUTH2 client credential specific only to the REST modules within a given schema?
Thanks,
Jason
SQLDev 20.2 on Linux.
I’ve had a separate file for each of 5 connections to 19.8 PDBs for a couple of weeks. I’m building these bare bones PDBs a few pieces at a time and the files serve as execution sheets and logs of what I’ve done.
All of a sudden today they briefly open Read-Write, but then spontaneously become Read-Only in the SQL Worksheet. Clicking on the History tab shows that what I’ve got is View-Only and that the source File on Disk is Editable. I can find no way to make the SQL Worksheet Editable. I’ve closed and re-opened them, restarted SQL Dev, but nothing seems to work. The file permissions are correct for the User I am.
Any ideas?
Thanks.
Jack
Hold on. I think something else may be causing this. I have an unusual setup here.
I’m using an Ubuntu 18.04 VM under VMWare that one of our SysAdmins set up for me.
In that I’ve got an X session to the database server – an X5-2 ODA running OEL 7 – where I’ve installed and am running SQL Dev 20.2.
It’s been working very well, but now I remember there was just an Ubuntu update that required a restart. I’m thinking maybe that did it, because another problem is that I lose my cursor within a Worksheet, though can still edit it. I’ll try a restart of Ubuntu to see if it helps, but I remember that SQL Dev in an Ubuntu VBox VM can lose its cursor, just can’t remember the fix for that.
Thanks.
Jack
It’s not the mouse cursor I lose, but the keyboard cursor, and only in the SQLDev running in X. Mouse cursor is fine in SQLDev and keyboard cursor works everywhere else, including SQLDev running in the Ubuntu VM, not in X window.
I’ll keep trying stuff.
Sorry to be a nuisance.
Jack
Ji Jeff,
In the new version of SQL Developer I seem to have lost the ability to preserve line breaks, using the formatter.
Is this possible please?
Thanks
Phil
You want to look at the preserve option.
Do the format…
Hi Jeff,
I started recently to implement REST Data Services with ORDS. The product is really great and it help a lot to speed up the process.
I have one question. I was requested to format the JSON keys using CamelCase format. I formatted the SQL columns in CamelCase using “”, but is seems that ORDS generates the JSON keys in lowercase.
Please help me to solve this issue.
Regards,
Claudiu Paunescu
Hi Jeff,
I started recently to implement REST Data Services with ORDS. The product is really great and it help a lot to spped up the process.
I have one question. I was requested to format the JSON keys using CamelCase format. I formatted the SQL columns in CamelCase using “”, but is seems that ORDS generates the JSON keys in lowercase.
Please help me to solve this issue.
Regards,
Claudiu Paunescu
unfortunately that’s not possible with ords today – you would have to generate the JSON response yourself, and use the Media Resource for your handler…
when doing and update with ORDS it seems to update all the columns not just the columns provided in json payload.
is there a way to change this to only update the columns provided in json?
You’re using AUTO TABLE feature?
Hi Jeff,
I’m trying to take steps towards understanding and enabling OAUTH2 and am following a Youtube tutorial by Tim Hall on Oracle Base.
According to this tutorial enabling first party authentication is a pre-requisite, but I cannot get basic first party cretentials to authenticate via CURL.
My ORDS instance is deployed on weblogic. I have published a basic ‘GET’ handler API on this URL:
http://act-xsts-12-md.xsts-be.local:7001/ords/brokasure/umr/umr
This command returned a success 200 code:
curl -isk http://act-xsts-12-md.xsts-be.local:7001/ords/brokasure/umr/umr
I created a role and a privilege both named “umr_generation” and have protected the ‘/umr’ pattern with that privilege
select * from user_ords_privilege_mappings
/
PRIVILEGE_ID NAME PATTERN CREATED_BY CREATED_O UPDATED_BY UPDATED_O
————– ———————————- —————– ————- ———– ——————– ———
10236 oracle.soda.privilege.developer /soda/* XXORDS_BSE 30-SEP-20 XXORDS_BSE 30-SEP-20
10296 umr_generation /umr/* XXORDS_BSE 30-SEP-20 XXORDS_BSE 30-SEP-20
I created a user via a call to “java -jar ords.war user umr_generation” on the mid-tier, then restarted the ORDS deployment on weblogic (having noted the new user is not recognised until the deployment is restarted).
Now when I access the API via the browser I am prompted to go to the sign in page, where I can enter the credentials created above and the API completes successfully.
However, when I run the CURL command passing the new credentials authentication fails with “Error 401–Unauthorized”:
curl -k -u “:” http://act-xsts-12-md.xsts-be.local:7001/ords/brokasure/umr/umr
Can you help me out with why authentication keeps failing via CURL but works ok via the browser?
Thank you,
Sophie.
Hello, Thank you for your very useful site. In order to know the version of the model in the physical database, I wrote an SQL “After create” request for insertion in a log table. This query inserts a trace containing the version. I would like the version to be saved in a user property. I have created a “VERSION” variable in the “User Defined Properties” of the project. But I can’t manage to use this variable in my insert request. Is it possible ?
Thank you,
Best regards,
P.LEBRUN
The database itself is happy to tell you the version it’s currently running at, what are you trying to log in addition to that in your log table?
Hi Jeff
I’m struggling to work out how to automatically pass the calling use via ORDs into the DB, do you have any hint or posts that might help me solve this.
Thanks
Roger
Hey Roger, that’s a bit vague…’calling use’ – can you provide an example? Then I’d be happy to help!
ORDS will be call from different sources including Windows based apps. I’m looking to pass the windows account information via ORDs to the database so that I’m able to track in the database then end users who are calling the Rest services.
Can ORDS read and process the HTTP Headers being set from the clients?
Absolutely – you can define a bind variable parameter that retrieves information off of the HTTP request headers or the request body.
I show the headers bit here
Hi Jeff!
The Community is in Read Only currently due to maintenance.
We are also facing the issue with the connections to database from SQL Developer 20.2. We are using OCI. I noticed if I leave the worksheet for 5-10 minutes then come back to do something the worksheet is locked not responding. I can’t hightlight and run a SQL. The green execute buttons are greyed out. One thing I noticed is that the session is waiting on event “SQL*Net break/reset to client”. This is occurring a lot. Last versions we were using is 18.4 and 19.4 without issues.
Hope this helps resolving.
Thank you,
Terry Baker
I see this happening with “Unshared Worksheets”. I don’t recall seeing this with a shared worksheet.
Since you’re using OCI/SQLNet, can you try a SQLNet client side trace of your session(s)?
Hi Jeff –
Is it possible to have a stored procedure ( returning ref cursor) as the source for a report in SQL Developer ?
I would pass the value of P1 and return the results in a SQL developer in a report?
If this is not possible , I guess , I could use table / piplelined function to send the results to the report .
Please advise.
create or replace PROCEDURE list_all( p1 int ,
cursor_all OUT SYS_REFCURSOR)
AS
BEGIN
OPEN Cursor_All FOR
Select level as lvl , ‘Testing’
from dual connect by level <= p1;
END ;
var r refcursor;
exec list_all(15 , :r);
print r;
I think you need to do what you suggested yourself…the SQL/script solution.