4.5+ million 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!
6,302 Comments
Jeff,
Does SQL Developer cache connections even after we close the tool? If yes, how do we clear the cache?
We have weird issue where some users are having TNS connection problem after we migrated the database from one cluster to another last weekend. SQL Developer uses shared tnsnames.ora for all users. In the TNS entry, we use hostname= so that, after the migration, we will just have to update our DNS so that the IP points to the new cluster. After migration, some users have started to report that they are having issue connecting to the database with error “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”. We have validated that database is fine because they can connect to the database through SQLPLUS using the same tnsnames.ora that the SQL Developer uses.
We are using SQL Developer 18.3; Our database is in 19c.
Thank you.
I think my post was not clear because the actual value in hostname= was removed. It could be because I have enclosed them with less-than and greater-than symbols.
I have corrected the sentence here:
.. In the TNS entry, we use hostname=cluster_dns_alias so that, after the migration, we will just have to update our DNS so that cluster_dns_alias IP points to the new cluster.
I’m sorry for the confusion.
No, we just reference the tns name/entry.
18.3 is quite old…
Jeff,
I wanted to know what grants are needed to view the SQL of the views in SQL Developer.
According to my DBA, we in development do not have access to the “select_catalog_role” role.
Would there be another way to gain access?
[Sorry for the English of Google Translate]
Thanks.
I’ve not been able to get a list from R&D. I have an approved ER for the database such that a user having SELECT/READ/EXECUTE privs on an object should be able to get the DDL for that object.
In the meantime, if your DBA won’t give you basic privs in your development environment, you’ll need to ask them to provide these DDL scripts for you.
This is one of many reasons I recommend each developer have their own oracle db running on your machine where they can do anything they want.
Good Morning,
I am looking to create two URLS using one DB. The setup is, an off site Oracle server already exists. One Admin server to run WebLogic and manage other servers. It is up and running. I have a managed server connecting to the ORDS DB by having run ords.war, and configured it to connect to the off site Oracle DB, and it all runs fine. This server is setup for public users to access and enter data.
The goal now is to have a second server that connects to the same DB on the same Oracle server, but will be used solely for internal use.
My plan is to simply add the second server to the Admin server, then run the ords.war file. However I am unsure exactly what to do in regard’s to running ords.war. Can I use the same DB accounts, can I use ords.war or do I need to call is maybe ords2.war, I rally am not sure how to proceed on that part?
Feel free to ask me questions for clarification and I will do my best. Thanks
Right, so you can have two ords running on the same machine even, connected to the same database. They would need to be listening on separate ports…
You can have two ords on different machines, in even different networks, connected to the same database.
You just need them to share a repo – so use the same version in both places, and you’ll need to know the password for ORDS_PUBLIC_USER so they can be the same for both installs. You can create a separate user for the pool if necessary…but that’s not necessary based on what you’ve described above.
Reading your email again…you have a single WLS install, and you want to deploy 2 separate ords.war files for it? Or you have 2 different WLS deployments?
If this is a duplicate post my apologies, but I dont see my response posted, so I am not sure if it is posting.
I have an Adminserver
Then I have a managed server wls_server1 that has the ords.war file and i.war file deployed
url example: http://server1:7004/ords (this server is up and running fine)
I am building a new WLS managed server, so say we will call it:
wls_server2, deploy the ords.war and i.war file but name them ords2 in the Deployment of weblogic
a url of say: http://server2:7005/ords
This should work?
I recently installed wls_server1 so I have all the accounts passwords etc for the existing accounts.
We have several envoroments wherby we have an AdminServer and four or five managed servers, so i am familiar with deploying etc.
Ex:
AdminServer
wls_server1 ords.war amd i.war
wls_server2 ords2.war and i2.war
wlse_server3 ords3 ….
However in those cases each ords install and server connects to a different DB.
This instance I am trying to two separate urls and connecting to the same DB, one the port:7004 is a public facing app, and the port 7005 will be internal.
Again I apologize is this is a duplicate post, and thank you
SQLDeveloper 19.2.1.247 in an enclave network (no Internet access) with Windows 10 VDI clients where I have zero control (at least, not without a lot of red tape) on the environment. In trying to add a new connection, I click on the down arrow next to the big green plus and choose “New Database Connection” . . . nothing happens. Hm. So I exported my connections, used that JSON to gen up my new connection, and imported it in. Now, I need to set the password, so I right-click on the connection and choose “Properties” . . . nothing happens. I tried it on one of my existing connections that works (both “Connect” and “Open SQL Worksheet”) . . . nothing happens. I also noticed the “Reset Password” menu option is gone. Not grayed-out, just not there at all. On a whim I thought about thin vs. thick client, and played around with that (Preferences/Database/Advanced), but it made no difference. I have dumped out of the VDI and back in a couple times. Still no change. Is it trying to hit the Internet and can’t get there? Is it something Java (or Path) related? If I could use ProcMon, I might be able to at least guess at what’s going on in the background, but my hands are tied.
Got it. I had disabled a bunch of extensions to make loading faster. Guess something behind the connections dialog requires the oracle.sqldeveloper.ssh extension. Once I took that out of the exclude list, everything is back to normal.
Great!
Yes, I have one WLS Admin server, that currently one ords.war file deployed to one server.
AdminServer
managed servers:
wls_server_1 where I have ords.war, and i.war file deployed
url might be https://server1:7004/ords
The additional managed server would be:
wls_server_2: deployed files: ords2.war i2.war I have to rename the war files as they all sit on the same Admin server
url might be https://server2:7005/ords
The problem, or question is, to re-use the same DB and accounts as you mentioned.
As it is a second server, I could also stick with /ords and there is only one install on the server
My initial thought was that I could use one managed server say, wls_server_1 and keep the primary ords install, but then add a second ords2 to the same server. But that would not allow for separate url’s or ports I believe.
Not sure my last comment made it through
I have:
Adminserver
a Managed server:
wls-server_1 with ords.war and i.war file deployed
url something like: http://server1:7004/ords
Adding a second server
wls_server_2 deploying ords2.war amd i2.war as the Admin server needs to seperate the war files with a name
url might be: http://server2:7005/ords
I have build server with multiple managed servers, but all those connect to different DB’s. The trouble I am wrapping my head around here and that both managed servers will use the same DB.
Thank you
Jeff,
We can go straight to 20.2? Our Apex is at 5.1.4, database is at 18c. The main concern is the compatibility amongst all these components. Now ORDS is upgraded, we are planning to upgrade Apex to 19.1, the database to 19c. Are we safe to go to 20.2 for both Apex and ORDS in 19c database? I got mixed messages in researching for the best version to go to (together with input from colleagues), I’d prefer to go to the latest versions on everything, but don’t want to get burnt if there are issues not discovered yet.
Thanks a lot!
Latest of everything is fine.
Hi Jeff,
So I only keep the version ORDS 18.2 running, apex at 5.1.4 in the database, when we try to access apex applications, we got 403 Forbidden error. Could it be a compatibility issue? Will upgrade ORDS to a newer version resolve the issue? Thanks a lot!
Update: I was able to install the new version ORDS 18.2 and deployed on our tomcat server. We have two instances running 2.0.10 and 18.2. Will clean out the old one when the developers are ready. Thanks all the same.
Hi Jeff,
Can I configure two ORDS instances in one database? We are planning to upgrade our current ORDS from 2.x to 18.2, our developers use Apex heavily for applications development, so when I do the install I must choose the option of use Application Express for additional configurations? I can have two urls one pointing to apex (it is working well now), the other to ords (the 18.2 version). Is that achievable? Our apex version is 5.1.4. Planning to upgrade to 19.1 in a few weeks.
Thanks a lot!
Why move to 18.2? That’s already 3+ years old…
Also, I’m not seeing why you would need 2 different ORDS?
Hello Jeff
I have been having trouble setting up debugger in SQL developers.
I keep getting this error:
Connecting to the database CUDEV_PPRD.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘134.117.xxx.xx’, ‘4000’ )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.DBMS_DEBUG_JDWP”, line 68
So I ran this
DECLARE
id Varchar2(30);
BEGIN
id := DBMS_DEBUG.initialize(‘134.117.107.98.10607030547406’,0);
DBMS_DEBUG.debug_on(TRUE);
END;
This causes my SQL developer to freeze.
Please advise on how to set up.
HDean
ORA-06512: at line 1
Process exited.
Disconnecting from the database CUDEV_PPRD.
Did you read my debugger posts? If you have version 20.2 or 20.4, set debugger to dbms_debug in preferences.
Hi Jeff,
Please I want to create a batch file out of this code that I can schedule with Task Scheduler
sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt.
Does your connection in SQLDev UI have the password included?
Yes, the connection in SQLdev have the password included
Then it shouldn’t be prompting for one…
This will be much easier, faster, simpler if you just use SQLcl and spool to a CSV.
I need to schedule it to run monthly or at specified times. Thats why I am using SDcli cart
Its prompting for DB password when I run sdcli cart export -cart on cmd. Under a windows user. The SQLDev UI connection is a DB user
is your windows user the same windows user you are running sqldev (GUI) with?
How can I create this script –
sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt.
on a batch file. Maybe I can specify a username and password in the batch file and schedule it with a task scheduler
The windows user is the same windows user I am running sqldev (GUI) with? but the db connection users is different. I am using a DB user to connect to the database.
yeah that should just work a la
Please How do I place the code on a batch file. What code can I use. START does not work
C:\Apps\sqldeveloper-20.4.1.407.0006-x64\sqldeveloper\sqldeveloper\bin>sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.csv
I get the error when I just place the code on a batch file
‘C:\Apps\sqldeveloper-20.4.1.407.0006-x64\sqldeveloper\sqldeveloper\bin’ is not
recognized as an internal or external command,
operable program or batch file.
The sdcli cart code exports the data to a CSV file successfully when run from CMD. though with db password prompt. But I need to schedule it to run monthly using a bat file
So again, if using csv as your export format, don’t use sdcli, use SQLcl.
Hi Jeff,
Thanks for the support. I was able to eliminate the password prompt from CMD by clicking “save password” in the db connection. I used the code below in a batch file to schedule it on windows scheduler
cd C:\Apps\sqldeveloper-20.4.1.407.0006-x64\sqldeveloper\sqldeveloper\bin
sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.csv
The exported data comes with the dbname.”Viewname” as the worksheet name. Is there a way to change this default worksheet name in the cart configuration to a friendly name.
Create another view with the name you like better, and export that?
Is there a way of directing output from a query in SQLDeveloper to a dynamically named file on disk?
I am required monthly to produce these CSV files, each named after their month; and I have not been able to programmatically set the name of the file for the SPOOL command.
On the same topic:
Is there a way of saving the output of a script to disk? Currently I am using dbms_output.put() and then manually saving the output to a file, but it is messy and error-prone.
There’s a save button on the script output panel, use that.
For the monthly stuff, I HIGHLY recommend you use SQLcl to do that. And then you can use this.
Hi Jeff – Your BLob article tells about loading and looking at imagery with blobs in SQLDeveloper but How do I remove a blob from a row. I don’t want to remove the entire column, just the one blob from a row.
the row contains things like UUID, filename, description, image data as a blob, and a few other things. for my testing, I put in an image and now I want to remove just that one image from my table. I’ve googled it but have not found the answer. Seems like this should be easy and straight forward but it is stumping me.
Thanks!
I show how to do that, or one way how to do that, here.
Good or bad, I have way more than 1 ORDS+BLOB posts…
Hi again – I did an update on that row and set the blob cell to null. It seemed to work. I guess I didn’t need to write you but I didn’t find that way to do it until after I had written you my comment. If I need to do something else to, say, reclaim space, please do let me know. — Thanks.
No worries, Marie! Just happy to hear you got everything working 🙂