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,564 Comments
Hi Jeff, I’m trying to create a recursive relationship of ‘Person Reports to Person’ in my logical model in ODM 23.1. I’ve tried a dozen ways from Sunday to have it accept the relationship role name for the inherited FK and engineer the role name to my relational model. When engineering, it ignores my role name and defaults to the PK with an underscore with number. The user guide doesn’t discuss this requirement. How can I do accomplish my objective?
Best bet, ask on the forums, the developers monitor questions there.
I have the next problem with SQL Developer:
We run +20 queries that use different dblinks and then export the the output of these queries on excel files.
We have tried this using “Execute statement”, but it gives the error ORA-02020: too many database links in use. It is because SQL Developer holds the cursor open for every select until the Query Result box is closed. Because of this isn’t possible to close the dblinks with ALTER SESSION CLOSE DATABASE LINK mydb;
¿Do you have any alternative, cli, etc? I have looked Cart, but it doesn’t work with queries, and it would be be problematic to create views in production systems (it has to be authorized, and it can take months).
The only option I have seen is to create csv files from cli, but Excel would be the desired format.
You either have to close the query results panel OR fetch/read ALL of the rows.
Honestly, using sqlcl to spool the results to CSV files is the way to go.
Or have the db create the excel files via plsql and scheduler jobs.
Even if you fetch/read ALL the rows the cursor isn’t closed until you close the query results panel. I have tried it.
We will try the CSV route.
We were writing some Linux scripts and also executing some sql queries in SQLdeveloper.
So, by mistake, we executed the command “echo” in sqldeveloper and we got this in the script output ”
PL/SQL procedure successfully completed.
Alias sqlcl_int_runme dropped
”
We were a bit surprised seeing something is dropped. Is this a bug or what exactly happened here?
I just filed a bug for you, thanks for the report.
Hi Jeff,
Is there a way you can know if someone else has opened a package after you opened it and compiled it with SQL Developer so that you don’t compile over their changes?
Thanks,
Scot
You would check the LAST_DDL_TIME in the dictionary to see when the last time it was compiled.
But ideally…you wouldn’t have people touching the database in an ad-hoc fashion, you’d have changes automated in a ci/cd pipeline for example.
Or you’d use file based source control to make sure people aren’t clobbering each other’s work.
Hi Jeff,
I’m trying to export my pluggable databases from the root container. But when I’m using
expdp username/password@root_sid directory=dir_name dumpfile=export.dmp logfile=export.log full = y
It is only exporting the root container, not the underlying pdbs in it.
How can I take export all the container databases including all the pluggables databases in it?
(I’m using Oracle 19c database)
Thanks
That’s not how you do it.
Connect to the PDB directly to run the Data Pump job, you can’t do it from the container.
Thank you for your reply.
I’ve more than 100 pdbs. I’ve to take export of the database. I can’t change pdb everytime to export database. It will be chaotic to take bake by changing PDB every time.
is there any way around for that to take backup?
Thanks
Anurag Rawat
Data Pump isn’t primarily used for backups, if you want something for backup and recovery, look at RMAN.
Also to your question, sure you could. Don’t do it manually, write a SQL script that does it for you, recursively. Or create a database job that takes an export for you, and publish that job into each of the PDBs.
Hi Jeff
I have ORDS19 installed with HTTP. I am stuck at a point that I can’t make changes HTTP to HTTPS and the huddle is I can’t uninstall and re-install again. I am having few Clients who are using my Apex Application and don’t want to loose it. In case if you can help me on this it will save my position. PLs let me know how to change HTTP to HTTPS if any detailed steps availbale would be really helpful
Did you ask me this on YouTube as well?
No need to uninstall or reinstall.
You just need to get a Certificate for your domain, and then configure ORDS to listen on https vs http, and then add your cert to the ORDS config. This is shown/demonstrated in the ORDS Docs.
Thank you so much for the reply. Yes I have commented in YouTube as well Jeff. Would it be possible to share the link u said about ords docs please. I am googling but no luck .
Go here
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/
Pick your version
Go to the books, then search for ‘https’ and ‘cert’
Hi Jeff
Thank you so much for this link , we have successfully migrated from http to https with CA certificates for ords standalone.
Is there any way to redirect all http traffic to https ?
We have more than 200 applications configured to use http which we need to redirect to https.
Hi Jeff
Thank you so much as per below details you shared we are able to migrate http to https successfully.
Go here
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/
Pick your version
Go to the books, then search for ‘https’ and ‘cert’
Is there any way to redirect all http traffic to https in. Stand-alone ords 19.2 version.
We have more than 200 applications which are configured with http all these need to be redirected to https. Pls help
Holy Moly, 200+ apps??
Maybe could be done via a load Balancer perhaps? Are these plsql gateway/modplsql apps or just rest APIs?
Hi Jeff,
Can I install Oracle 23c (free) in a server with CentOS for learning and testing ?
Just use Oracle Linux, it’s free and we know it works.
Also, isn’t CentOS dead?
Thanks Jeff !
Well it’s still installed in some of the servers available to me.
Now I have to convince few people to install Oracle Linux.
Let me get started.
Thanks again !
Hi Jeff, In SQL Dev 4.0 a new feature came out to hover the mouse over * to expand the column list. Is that still active in 21.4.3? If so, what settings need to be in place to allow that to happen. Secondly, if it is still available, is that feature usable in a saved worksheet vs a new worksheet?
That feature never left, it’s working in 23.1, I just demonstrated it yesterday.
So, could be a bug in your version, or your code is confusing the parser.
In a completely empty editor, does
select * from table;
work? Where ‘table’ is like employees or something local in your schema.
Jeff, I’ve tried that in a brand new worksheet with only a simple statement. I get the squiggly line under the Select and * but nothing else happens when I tried to hover over either.
Weird
Um, what version of Oracle are you using? And then yeah, try 23.1
Jeff,
We are using Oracle 12c and SQL Dev Version 21.4.3.063. The Version tab also lists Java(TM) Platform as 1.8.0_311.
Jeff, sorry, Oracle 19c not 12c
Either way i’d grab a newer SQLDev to see if that helps.
Thank you for your responses.
SQL Dev Version 23.1
AddVMOption -Xms12g
AddVMOption -Xmx12g
Desktop has 64GB RAM
Invoke Real Time SQL Monitor
Waiting for Editor to initialize
Status Bar shows Loading Realtime SQL Monitor — going on for the last 15 minutes
Try to kill — x in the Tab => SQL Dev freezes
Task Manager to Kill the process which means the query I want to montor is also gone as is everything else
There must be some setting I am missing or not set properly
I replied to your original thread on LinkedIn
Hey Jeff,
I’m implementing cyberark and it doesn’t have a plugin for sqldeveloper, I’ve tried to implement that using a .bat together with connection helper but cyberark doesn’t persist the option to enable the connectionhelper at startup.
The problem is that the dbas must not know the password for the user, only cyberark knows and it rotates it every week, that works greak for sql and sqlcl but I couldn’t find a way to make it work on sqldeveloper, can you think of something?
I was hoping for the new sqldeveloper (vscode based) but I’m not sure it will fix it.
Thanks.
No, I’m not aware of any beans, magical or otherwise, that will help with SQL Developer and CyberArk.
SQL Developer’s VS Code extension will keep our passwords in local java wallets, if cyberark could update those, that might be an option…we’ll also have ability to source connections from our Vault in Oracle Cloud Infrastructure, but that’s an alternative to cyberark.
It will be possible to create a connection on the new sqldeveloper using command line?
Something like sqlplus /@tns?
SQLcl will be built-in.
Is it possible to have new window open for the report instead of reusing 1 window? Example I have 2 reports
Rep 1
Rep 2
When I click on 1 it pops up new window for results. If I click on Rep 2 it overwrites result of Rep and uses same window. Now I cannot compare few details between 2 reports.
Thank you
Yes, just make sure you pin the report first.
Hi
I am using v23.1.0.097
I generate he AWR Report on Exadata at both the CBD and Pluggable Database. Why is there nothing for the Exadata specific Report components. What do I need to add?
We don’t add or change the the AWR report plsql calls for an Exadata connection, so you’re seeing the default output. You can see what we’re doing in the statements log panel.
Hi Jeff,
I work for an Oracle Partner and I do a lot of custom developments. I use SQLDeveloper on a daily basis for interacting with the Oracle E-Business Suite database.
As you know I cannot do that when it comes to Oracle Fusion. I looked on support.oracle.com and I found the document: How To Connect SQL Developer to a SaaS/Fusion Database (Doc ID 2556632.1) which states that it is not possible to connect with SQL Developer to a SaaS / Fusion database.
I looked on Google and it seems that is possible to access the Fusion database through an OTBI web service. I even found two companies that are selling SQLDeveloper-like tools that work fine. Can you please look at https://ritesoftware.com/products/cloudminer/ and https://www.sqlconnect.com/? Is there any chance to make SQLDeveloper do the same? It will save a lot of work hours for every developer.
Thank you very much.
Regards,
Claudiu
No, I can’t comment on those 3rd party approaches, and they could very well violate the TOS.
Our advice is to push the Fusion service owners to enable SQL Developer Web for their customers, so they can have SQL access to their data. And, that’s happened for SOME, but not ALL services. I just make the tools, I can’t dictate the terms and services offered on the apps side of the house.
Hi Jeff,
Is it possible to block access to the ORDS landing page when it comes to the domain, but leave it unblocked when it comes to localhost? An example is the following:
My application is available at the public address https://siteApexApp/ords/r/fina/a1032023072912022768/login_desktop, but if someone enters just the part https://siteApexApp/ords, they get the ORDS landing page (https://siteApexApp/ords/_/landing). Can the landing page be blocked only for the domain (siteApexApp), while remaining unblocked for localhost?
Best regards.
Not with an ORDS setting. If you’re running ORDS on a public server, I would DISABLE the landing page, you don’t want folks having access to design-time pages or SQL Developer Web, right?
Set misc.defaultPage to ‘apex’ in the global settings (settings.xml)
Can ORDS, for protected REST endpoints, be configured to use an external OAuth2 authorization server to configure REST clients for OAuth2 2-legged and/or 3-legged flows, instead of creating them in the database ORDS_METADATA.OAUTH_CLIENTS?
Not today, but we’re working on getting closer to support OpenID and having JWT support using token validation (not token introspection)
We have a windows application server running 3 Tomcat9 services (one per PDB connection)
Catalina_home is c:\tomcat9
catalina_base is c:\tomcatxxx where xxx is hte PDB being connected to
under catalina_base we have an ORDS folder and ords-Config folder.
Ords 21
url for apex is :port/xxx
Moving to Ords23
I am trying to set it up in a similar way but may have misunderstood.
Tomcat installed correctly
I have tried different installation of ords config files and deployed the war file. Tomcat picks up this war file but fails to connect to the database. (the first iteration it did connect, but to the first database connection I configured)
should I have only one ORDSs folder on the server, or should I have one per tomcat service?
is using pools the correct way to define the connection?
I would really like some help here.
Not sure why you have 3 tomcats, but that’s fine
each would have it’s own war file, each would have it’s own configdir
ords has to migrate the old config files to the new format when going from 21 to 22 and higher
And then here are directions for preparing the war file(s) for Tomcat
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.2/ordig/deploying-and-monitoring-oracle-rest-data-services.html#GUID-3F2AE730-69D0-4A64-A13A-76745B7467CD
You might also find this post from our lead dev helpful as well.
https://peterobrien.blog/2022/05/13/multiple-ords-instances-in-weblogic-server-or-apache-tomcat/
Thanks for replying
Maybe I dont need 3 tomcats – I have one for each environment, which worked before, but self taught so am willing to accept I have not done it in the most efficient way.
I have a war file for each, and have specified a different config directory.
I migrated one ords and then created a new one on another PDB (I am currently working on a “sandbox” server).
I have read both those documents and tried to make them apply to our config.
maybe I should go to one tomcat and see if that helps.
Cheers
Lizzie
All sorted now
used the –db-pool command and can now get connections to multiple PDBs using one Tomcat service (bonus!).
Have a good weekend
Awesome!
Hi Jeff,
I think I’ve hit a bug in SQLcl when using it for CI/CD deployments of DDL/schema between Dev & PreProd databases (both are 21c SE2).
SQLcl 23.1 tries to enable INMEMORY on a table when it’s explicitly disabled in the XML changeset
Please see https://forums.oracle.com/ords/apexds/post/sqlcl-23-1-tries-to-enable-inmemory-on-a-table-when-it-s-ex-3863
I’ve posted it on the SQLcl forum and the Facebook page for SQL Developer – no responses yet in more than 2 weeks.
Thanks,
Wim
I’ve left you a comment to your post on the forums, basically asking for a test case. And then I’ll test it out on 23.2/3 and log a bug if required.
Hi!
I’m experiencing an issue with SQL Developer 23.1.0.097 – when I try to copy a CLOB result value from data grid, a result from a simple query like:
select dbms_metadata.get_ddl(‘TABLE’,’DUAL’,’SYS’) from dual;
It copies only 75 characters of the CLOB.
I have found no setting to increase the size of copied CLOB, I also let it use my older 21.4.1.349 settings on installation, no other settings were “manually” changed after install.
Let me say that in 21.4.1.349 this works OK – copies entire CLOB value (besides enclosing the CLOB value in pesky double quotes – there’s no preference to turn this enclosing behavior off).
Is this newly introduced CLOB result value truncation on copy a bug, feature or am I doing something wrong ?
Kind regards,
Aleksander
It’s a known bug and will be fixed in an update this Summer.
Hello Aleksander,
Maybe you can try this in SQL Developer:
SET long 32000
select dbms_metadata.get_ddl(‘TABLE’,’DUAL’,’SYS’) from dual;
This has worked for me in SQL Developer 23.1.
Hope this helps.
Hi Jeff,
I would like to know for SQL Developer Data Modeler how I can save Domains to a file. The reason I ask is because I have model with 70 domains but the domain.xml file only lists 1 of the 70. I’m not sure why only one of the 70 domains was saved to the domain.xml file.
The problem is significant because the model is version controlled via a GitHub repository and all that shows up in Git is the one domain (i.e. 69 are missing), which matches the contents of the domain.xml file. As a result of the missing domains, anyone who pulls down the model from the git repository is displayed logical and physical models with the majority of the attributes and columns with the UNKNOWN datatype. What’s puzzling is my local copy of the model has all 70 domains.
In reading the documentation there’s information on how to import a domain file. However there’s nothing in the documentation how to save to a domain file.
In advance, thanks,
Alvaro
Read the doc section on Adding System Data Types Directory under Git Control
If there’s no open design, a new domain is added to defaultdommains
If there is an open design, the file name can be provided where domain is to be saved, these are called design level domains, in a directory “domains” under the design directory
When a file with Domains is imported, domains go into the current design, again as design level domains. There is a checkbox for them to go into defaultdomains
Hi Jeff,
Thanks for the solution about Domains and sharing domains via a file when the models are stored in Git repositories. The objective at our site is to have a master Domain file that can be shared across all models by using just the system_data_types/defaultdomains.xml file . Our latest challenge in how to merge Domains from the separate models into the system_data_types/defaultdomains.xml file.
In advance thanks
Please ask your question on the data modeler forum
Hi Jeff
I’m tearing my hair out (and I don’t have much left anyway)!
Apex 21.2 was working fine using an Apache Tomcat 9 web server I had built for R&D running on ORDS 21.4.
Subsequently, I’ve installed Apex 23.1 on our development database (21.2 is still installed in it’s own tablespace).
I’ve installed ORDS 23.2 (skipped standalone) and deployed on Tomcat 9.0.78 on a new web server.
When I go to the Tomcat Web Application Manager, I can see the ORDS app but when I click on it, it goes to the new ORDS landing page but the “Oracle APEX” tile is greyed out saying “App Unavailable”. The same with “SQL Developer Web”. The only one not greyed out is “OAuth2 Administration”.
I trawled multiple times through the Oracle documentation and various online guides etc but can’t work out what I’ve done wrong. The database users like “APEX_PUBLIC_USER” and “ORDS_PUBLIC_USER” are unlocked.
Any ideas please?
Thanks in advance.
Neil
is SQL Developer Web enabled? That should be done via the ORDS CLI and/or looking at the pool config file.
For APEX, use the validate command – that will fix any proxy grants missing between ORDS_PUBLIC_USER and the APEX account(s).
i can’t find sql developer or RESTful services in group grants in user groups. i want to create a user to acces the API.
It’s not a database role.
You use the ords.enable() plsql API to enable that feature for a schema/user
ords is already enabled. i created an API and secured it with role and privilege. Now i want to create a user who can access that API. On creating user i have to select sql developer and restfull service. but that tab comes blank , @jeff
Those are web roles and privs, not database roles and privs
Same name, but completely different
What you want is under restful services
SO, How do I counter this problem. Is there any way out.
I don’t know what your problem is.
I don’t recommend you use database auth to access REST APIs – you’re sharing credentials to people who probably don’t need them for the db, and that method of auth is SLOWER.
I have created API in oracle apex, I have secured it with role and privileges . when I try to access that API from browser or from postman, it shows sign in required. In order to sign in , I have to create a user in manage user and groups in apex administration. On creating the user I have to assign a group in group assignments. Usually there are three groups by default which we can assign(SQL developer, RESTful services and OAuth2 client developer). But I am getting blank tab.
How can I overcome this problem.
How are you going to use this API I’m real world, not with postman, right?
From another apex app, something else?
The user will use that API in browser and a file will download in his local.
For that i want to create an end user in oracle apex who can access the API in browser . To login i have to give him group grant which contain RESTful services, sql developer etc. But that tab is blank
https://forums.oracle.com/ords/apexds/post/sql-developer-or-restful-services-in-oracle-apex-9261
i have also shared a link in which i have uploaded the image.
You can use apex auth token in URL to auth a rest API
I can’t use auth token system, my requirement is to access the API using username and password.
it is working in every apex(apex 21, apex 22) but I am not getting it in apex 23
Then either create
Highly recommend you stay away from the 3rd option for reasons previously mentioned.
Is this a bug in oracle apex 23 ?
Also these options are available in apex.oracle .com
I’m not an apex developer but the apex workshop should let you see rest roles and privs.
However you can’t create ords or db users there.
You don’t have many choices, oauth2 or apex auth header.
That’s not for production use either, you should setup your own rig in Oracle Cloud like Always Free or a paid tenancy.
Is it possible to generate objects from other schema than default with LB?
Server: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.18.0.0.0
SQLcl: Release 23.2
I login into ORACLE DB with my login (XXX) and try to export from APEX schema REPORTS with SQLcl liquibase:
SQL> lb geao -log true -woi 1550974938701311
and get such error
SQL> lb geao -log true -woi 1550974938701311
–Starting Liquibase at 13:11:06 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)
Exporting Workspace REPORTS – application 100:XLSX Loader
ORA-20987: APEX – Security Group ID (your workspace identity) is invalid. – Contact your application administrator.
No, the conceit is that you’re doing active dev work on a schema…
Hi there,
I’m trying to install Apex 22.2 and ORDS 22.3 in my Pdbs.
I’ve successfully installed 2 Apex in 2 different PDBS.
My goal is to run multiple Apex from one ORDS. I want to run 2 different Apex on one port with the PDB name differentiating which database it will connect.
Is this possible? If yes, Please guide me.
Thanks
Anurag Rawat
Hi Jeff,
Trying out SQL Developer in Database Actions (OCI APEX Dev service), but I just cannot get the code formatter to permanently keep all code (SQL and PL/SQL) in lower case as my preference.
I select Lowercase, then type, but object suggestions still appear in uppercase.
I have to “select all” code each time and choose Lowercase. It works, but only until I start typing again.
Is there a way to make the Lowercase selection permanent? (I don’t see it as a permanent option in my Preferences under my username[top right]).
Thanks,
Wim
Working from memory, I’m on the Med…
There are no preferences for the formatter at the moment.
Hi Jeff,
I have a bunch of queries to run, and I need to export all of them to a csv / excel. Is there a way to automatically export the result, or do I have to constantly click the result and export and set csv blah blah
Cheers,
Daniel
Spool!