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,802 Comments
Hi, Can I do reverse engineering with existing Microsoft SQL Database in Data Modeler tool?, I have existing database in MS SQL and I want to add new table in that, but there are references to existing tables, So I would like to bring all tables in Data modeler tool and add new table in document.
Yes, i talk/show that here
https://www.slideshare.net/slideshow/oracle-sql-developer-for-sql-server/43598010
Hi Jeff!
Any updates on the rumoured OpenID AUTH support in ORDS ?
Thanks for a great webpage BTW.
The JWT feature we added in 23.3 is that rumored support.
Hi Jeff,
Do you know if there is a way to increase the “Recent files” list in SQL developer ‘Files’ navigator? I use more than 10 files and would be handy if I can increase to let’s say 25.
Thanks
Wim
Not that I know of. VS Code also has this hard-coded to 10 for what it’s worth.
In a SQL Developer Classic worksheet, I am able to select all rows in the query result tab and copy to the clipboard and paste elsewhere. I haven’t found a way to do that in SQL Developer for VS Code. Is that feature there?
Grab today’s update (24.1.1) – you can can now select all the rows and copy/paste out…
I just grabbed this from the results of querying dbms_xplan…
'SQL_ID g6hgm74gu05t7, child number 0' '-------------------------------------' 'select x.FIRST_NAME, x.LAST_NAME, x.SALARY from employees X where ' 'x.COMMISSION_PCT is not null' 'Plan hash value: 1445457117' '| Id | Operation | Name | E-Rows |' '------------------------------------------------' '------------------------------------------------' '| 0 | SELECT STATEMENT | | |' '|* 1 | TABLE ACCESS FULL| EMPLOYEES | 35 |' '------------------------------------------------' 'Predicate Information (identified by operation id):' '---------------------------------------------------' ' 1 - filter("X"."COMMISSION_PCT" IS NOT NULL)' 'Note' '-----' ' - Warning: basic plan statistics not available. These are only collected when:' ' * hint 'gather_plan_statistics' is used for the statement or' ' * parameter 'statistics_level' is set to 'ALL', at session or system level'I am able to copy and paste all rows from the Script Output tab, but I’m not getting it to work from the Query Result grid tab. The export to clipboard function does work from that tab, though is a little cumbersome compared to just hitting CTRL-A > CTRL-C > CTRL-V for certain types of results.
I ctrl+clicked the rows I wanted and it worked…I didn’t try Ctrl+a+c
If you want all safer to use wizard as you might not have all the rows fetched yet
Ctrl+a should work though if not will file a bug
The context is I’m selecting the contents of a file from a directory as a table. I then try to copy the contents and paste into another text editor. I tried it again. If I click on the line numbers on the left of the query result tab, I can select multiple lines and paste those into an editor. But hitting ctrl-a after clicking any of those lines does not fetch all rows and then select all of them. Instead it highlights the text above the results table that says “Fetched 200 rows in x.xxx seconds”. In SQL Developer classic, if I click into the results table and hit ctrl-a, it automatically fetches the full result set and selects it. I can then copy and paste into another window or app.
I can certainly use the export wizard for now, but I’d love to have the convenience of ctrl-a,c,v that the older tool provides. Thank you for considering it.
Hi Jeff,
I am using SQLDeveloper both on Windows and on Linux (mostly) and noticed that, when the connection to the server drops for whatever reason, often, on Linux only, it happens that it is difficult to reconnect or to close the connection. Sometimes it is needed to kill the app in order to connect again. I am having this problem since I started using sqldeveloper on Linux, years ago, and, although it is less dramatic with the recent releases, it is still quite annoying. Is there a solution?
Thank you
Try this instead.
When you come back to SQLDev, don’t do anything that will attempt ‘work’ on the connection. Immediately start by doing a connection right-click Disconnect or Reconnect.
See if that helps.
And if your version of Linux supports VS Code, try our new SQL Developer extension, I think you’ll like it!
Hi Jeff,
Can we display the SYS_REFCURSOR results in a data grid within Visual Studio Code using the Oracle SQL developer extension, not printed?
this when run procedure has out parameter of SYS_REFCURSOR.
Not YET.
Hi Jeff.
We’ve installed SQL Developer Web (v. 21.3) and we want to know if it is possible to configure the NLS parameters somewhere.
Another question is to know if it’s possible to define that the outputs of the result of the sql’s execution do not show the dot separator of thousands.
Thank you so much,
Diego
The browser/computer’s regional settings influence that.
Hey Jeff,
is there an extension to format sql files? My team is struggling to keep a standard formatting for these code. We would like a prettier for SQL
All of our tools offer a SQL/plsql formatter, SQL Developer, SQLcl, sqldev web, and our vs code extension.
Hey Jeff,
I know it’s not your forte but would you happen to know a POC (and how to contact him/her) for MySQL Shell for VS Code?
Looks like it’s in PREVIEW release and I’m very curious about when it will be production ready
https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code
Did you leave a question on thr extension page Q&A? They should be monitoring that.
Hi Jeff,
I have a problem with generating a swagger document in oracle Apex. We create a GET handler, which consists of code where the data is parsed:
SELECT
JSON_OBJECT(
‘name’ VALUE JSON_OBJECT( ‘ID’ VALUE ID,
‘FIRST_NAME’ VALUE FIRST_NAME,
‘LAST_NAME’ VALUE LAST_NAME
),
‘data’ VALUE JSON_OBJECT(
‘CITY’ VALUE CITY,
‘BIRTH_DATE’ VALUE BIRTH_DATE,
‘COUNTRY’ VALUE COUNTRY,
‘POSITION’ VALUE POSITION,
‘COMPANY’ VALUE COMPANY
)
) AS json_data
FROM test_data_generator
After generating the swagger, we get an incomplete document that contains only :
{
“items”: [
{
“json_data”: “string”
}
]
}
Can we add something before ours code in hendler to swagger correctly process our data?
Please help, we have a lot of code and rewriting it is not an option.
Thank you in advance!
For ORDS to do that, it would have to inspect the nature of your SQL statement.
For the moment, you wouldn’t need to rewrite your rest api, but amend/update the swagger we generate from the api.
Maybe we can do more however, please drop me a line at [email protected].
Hey Jeff,
I get a “Error: Authorization failed or requested resource not found” with the source “Oracle SQL Developer Extension for VSCode” when I attempt to edit my Connection Name or delete it in the SQL Developer VS Code extension.
I’m guessing it’s a bug??
Sounds like it. Those connection directory/files are here, for Windows
C:\Users\YOU\AppData\Roaming\DBTools\connections\
I’ve recently started using the MongoDB API via ORDS, and can use Mongo Shell from a client tool (Compass, in this case) to create what it thinks is a MongoDB collection, but instead creates an Oracle table (a “JSON Data Store”). This collection/table is then visible from the tool. For the solution that we’re looking to use this for, I’d ideally be “pre-creating” such JSON Data Stores in the database, for the application that is being migrated away from MongoDB to be able to resume working against in Oracle. In SQL Developer, I generated a SQL script for the JSON Data Store table, changed its name, and created a second table. However, this doesn’t show up through the MongoDB API. Is there something else that is configured in the Oracle database back-end by the MongoDB API when a “db.createCollection” type of statement is run? I’d like to just take that same basic table form, as generated by SQL Developer for that first “properly-created” table, and put together a script to pre-build my empty JSON Data Store database. Does that make sense, or is it something that *can* be done?
“JSON Data Store” is not a term I’m familiar with but from the context I think you are referring to JSON collections and you would like to create them from SQL in a way that they are accessible from the Mongo API.
For Oracle Database 23.4 or later, from SQL you can execute:
create json collection table mycol;
For Oracle Database 19c->23.3, you can execute the following:
declare
col SODA_Collection_T;
begin
col := dbms_soda.CREATE_COLLECTION(‘mycol’,
‘
{
“contentColumn” : {
“name” : “DATA”,
“sqlType” : “BLOB”,
“jsonFormat” : “OSON”
},
“keyColumn” : {
“name” : “ID”,
“assignmentMethod” : “EMBEDDED_OID”,
“path” : “_id”
},
“versionColumn” : {
“name” : “VERSION”,
“method” : “UUID”
},
“lastModifiedColumn” : {
“name” : “LAST_MODIFIED”
},
“creationTimeColumn” : {
“name” : “CREATED_ON”
}
}’);
end;
/
Hello Jeff.
I am trying to invoke a UDSA exposed rest API from OIC (Rest Adapter to be specific).
https://mpr.datamart.ams.usda.gov/ws/report/v1/cattle/LM_CT100?filter={“filters”:[{“fieldName
“:”Report%20date”,”operatorType”:”EQUAL”,”values”:[“01/10/2018”]}]}
The API itself has multiple filters (derived from JSON) and work fine over the browser or on an API client like Postman.
However, I am unable to model the query in my OIC rest adapter trigger for the same.
I was trying to pass the filter condition it as a POST request body, but that is not permitted.
Any pointers?
I’m not familiar with that API and also I’m not an OIC with expert, sorry.
Hello Jeff, I am hoping that you can help me reolve a startup issue with ORDS. I just installed ORDS 24.1 on a new RHEL8 server which is running tomcat and when I try to access the ORDS url I get a 404. The tomcat localhost log has these messages
“GET /ords/ HTTP/1.1” 302 5
“GET /ords/_/landing HTTP/1.1” 404
The ORDS install log output looked all great, no errors so at a loss for where to go next.
Thanks for any help,
David Barth
Are your apex apps or rest apis working or is everything broken?
I am unable generate DDL if object definied in DB with lowercase letters. Please see:
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 24.1.0.0 build: 24.1.0.087.0929
SQL> select count(1) from dba_tables where table_name = ‘urls_config’;
COUNT(1)
___________
1
SQL> ddl urls_config
Object urls_config not found
SQL> ddl “urls_config”
Object urls_config not found
SQL> ddl OWNER.”urls_config”
Object OWNER.urls_config not found
Do know maybe any workaround for this annoying thing? Thanks & Regards!
Thanks, i’ll file a bug.
Workaround – don’t use case-sensitive object names?
> Thanks, i’ll file a bug.
Thank you Jeff! This will help in the future.
> Workaround – don’t use case-sensitive object names?
Yes, this could help. Or you can consider to surround object name with quotation mark like in typical “OWNER'(dot)”object_name”. Becase `describe` works fine in SQLcl:
SQL> describe OWNER.”urls_config”
…. here is output with table description …
Regards, Damian
Like I said, i’ll file a bug.
Hello Jeff,
I am a college student, for our final assignment of PL/SQL course, we need to create procedures and packages of a DB.
We are a team of 3 members and I would like the 3 of us to work on the same autonomous DB which I have already created. The 3 of us are working on desktop SQL developer.
So far I have managed to create their users on my DB and grant them PDB_DBA.
When we go to SQL developer, they can create new schemas and connections but we have been unable to “download” the existing schemas from the autonomous DB to the desktop SQL developer.
Is there a procedure or protocol that you could point out so the 2 of us can work on the same DB?
Thanks very much,
David
When you connect to your database using SQL developer, you should see the 3 schemas and their objects/data.
Your DAVID account should be visible to your two partners if you’re connected to the same database.
Send me an email if you’d like more assistance than this mechanism can provide –
[email protected]
Hey Jeff,
When will Oracle SQL Developer Data Modeler will support 19c models?
I connected to my 19c database, but I only see 12cR2 as the target.
Thanks in advance.
as far as the modeler is concerned those are functionality equivalent, where 19c is basically the terminal update for the 12.1/12.2 release family of the database.
Hi Jeff, I hope you can answer a question about editions (EBR). Which is the max number of editions in 19c ?? I’ve followed Doc ID 1507188.1 thechnique and I could create 15.000 editions without problem. In 11g max number was 2.000.
I’ve search into Oracle documents and could’t find that limit
Thank you very much
There’s not always an answer, but I will endeavor to find one for you.
That being said, how many do you actually need?
Thanks Jeff for the interest.
I really don’t know what could be the number.
The situation arise´d when we’ve exposed the edition’s main features and benefits to a client, and exposed the 11g limits, then they were cautios of using EBR.
They periodically (weekly) upgrade their applications…
15.000 editions is near editions for 95 years, asuming 3 editions a week and 53 weeks a year. I thinks there are enough editions, but to know the limit is a inquisitiveness
The 2k limit has been lifted to apparently, 32k editions.
Thank you very much Jefff
Hey Jeff,
Is the SQLDeveloper plugin for webstorm in your roadmap?
Thanks in advance
No, not currently. If it were free and being used by tens of millions of developers, it would definitely be something to think about though.
Hi Jess,
Do you have a post about create a structured type using DataModeler? I see we have the option on the physical model but option to create is not there. We have in ther XMLTYPE & SDO_GEOMETRY. I can’t create a new one.
Any helpful link that describes how to do that?
Regards,
I don’t think I do, but just go look at the Browser, scroll down to ‘Data Types Model’ – use that to create a new Structured Type.
Hi Jeff,
Unfortunately, I don’t have the option to create a new one in there either. When I right click on Structured Types, I don’t have anything happening. On distinct type, if I right click, I have the possibility to create one. Same for Collection types. But, not on structured types. or predefined structured type.
Thank you anyway.
Either ask on the data modeler forums or create a service request with My Oracle Support.
Thanks Jeff.
Can you please explain or give an example for “you need the employ url rewrites somewhere in front of ORDS/apex.”
Regards.
Like use a loadbalancer – these aren’t really ORDS questions.
Some examples
Hi,
How can I ask a question?
I trie several time but my question is not displayed.
Thanks and regards.
Here is my question please:
Hi,
EBS 12.2.9, DB 19c on AIX
our E-business is protected by a web application firewall. Then a reverse proxy (I think).
From our E-Business we can conect ton an APEX interface.
We configured both EBS and web application firewall on HTTPS and it works fine.
We have a VIP URL to access EBS application
https://virtualserver.domain/
and from there by VIP URL to APEX
https://virtualserver.domain/ords
But
When we access APEX page we see backend (physical) server name in URL zone of brawser:
https://physicalserver.domain/ords
Is there any way to hide physical server name and display virtual server name for APEX?
Thanks and regards.
I think you need the employ url rewrites somewhere in front of ORDS/apex.
My spam filter flags anything with more than 1 link in it, so I had to manually approve the post.
Hi Jeff.
Trying to install ORDS on windows. I am going through the installation guides of Apex 23.2 and Ords 23.4, and the java -jar ords.war install command, which would work on windows, is not supported/recommended anymore.
But… I cannot find a windows installation process instead.
I am referring to chapters 2.1.2 and 2.1.3 in
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.4/ordig/installing-and-configuring-oracle-rest-data-services.html
(No mention of config folder preparation for windows…)
Thanks in advance
Yeah we made a real cli, now you run
ords install
Did you read the installation and configuration guide?
I have blog posts showing how to install ords here (look for the 22.1 one), and I do also everything on Windows.
Hi Jeff,
Need a help, We have migrated ORDS 19 from HTTP to HTTPS with certificate from CA few month back by following below doc which was shared by Oracle SR.
https://blogs.oracle.com/fusionmiddlewaresupport/post/simplified-ssl-configuration-for-ords-a-howto-guide
Post this configuration few application were able to connect but few are not connecting they are failing in cert chain and they are saying root and intermediate certificates need to be added along with server certificate.
I have concatenated root, intermediate and server .crt files into single file servercert-chain.crt and passed this to ssl.cert=servercert-chain.crt parameter in standalone properties and bounced the ORDS but no luck when we try to connect via browser getting connection is not private. please help
You really want to avoid having to deal with multiple/chained certs for your webserver. ORSD Standalone via Jetty can work with these certs, but it would be loads easier to setup a loadbalancer in front of ords, and then terminal HTTPS/SSL at the LB and do http between the lb and ords – then you manage your certs all on the lb.
Hey Jeff,
Hope that all is well!
In the SQL Developer application, you can download query results into .xls and .xlsx format but looks like you can’t do it in the VS Code Oracle SQL Developer extension.
Is this on the radar to be in a future release??
Thank you so much in advance for your time and consideration!
Absolutely it’s on the list!