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,793 Comments
Our development team has hundreds of packages/procedures that have been developed over the years and formatted with the PL/SQL beautifier embedded in the competing product PL/SQL Developer from Allround. The formatting was 100% based on GUI options, check boxes for this, radio button for that, etc… for different types of structures. We’d like to move to Oracle SQL Developer, but one of the sticking points is the code formatter. Looking for someone or someplace to go for helping tune the SQL Developer Custom Format to adhere to our long standing code format standards. Currently, the simple options dont allow me to match up, and the complete custom option is a bit much to digest without guidance. Anyone willing to take a stab on your team Jeff?
Our gui checkbox items are meant to cover the 95% of users…and the custom stuff for what’s ‘missing’
We’re not going to copy another product’s Formatter, so take your best stab at ours, and then let is know what else you need.
We’ll either enhance the formatter or give you a custom rule.
Running Multiple Queries in Oracle SQL Developer, but to differents connections
Hi Jeff, Can I run a simple query to different connections?. The schemas and Bds have the same names, just change IP server in the connections setting
I’m using SQL Developer v19.1.0.94
Thanks a lot!!
We don’t have a multi-connection execution feature, so you’d have to have a connection to both and run the query in each.
******
thatjeffsmith POST AUTHOR
We don’t have a multi-connection execution feature, so you’d have to have a connection to both and run the query in each.
******
Thanks Master !
Hi Jeff,
I created a Web Source Module in Oracle Apex 18.2 in a Development database.
The Base_URL is defined based on the Dev database.
When exporting the application and importing this into a Test environment the Base_URL is still pointing to the Development Database.
Is this a bug?
Or do i have to define this as a variable?
Any help or hint will be much appreciated.
Regards,
Anibal
ORDS Service URIs aren’t tied to a database…they’re available in the database they’re defined in.
So since I don’t understand what might be happening here, I’m going to guess this is an APEX issue.
below is a snip from a sqlplus session. It showr a lot on invalid password 1017 and account locked. The only process on the machine was sql developer. and the login attempts stopped once I exited sqldev .
any idea what caused this? Been seen before?
1 select USERHOST, TERMINAL, ACTION#, RETURNCODE,ntimestamp# from sys.aud$
2 where NTIMESTAMP# > sysdate -1 and USERID=’MY_PROD’
3* order by ntimestamp#
TUS114035904 unknown 100 1017 12-JUN-19 03.45.59.541197 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.46.04.508358 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.47.01.011931 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.47.06.719161 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.48.04.539455 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.48.11.041766 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.49.10.146022 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.49.17.639592 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.50.17.645569 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.50.18.812180 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.51.18.532852 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.51.19.734862 PM
BHS\TUS114035904 TUS114035904 101 0 12-JUN-19 03.51.49.388711 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.52.25.173911 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.52.26.363945 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.53.26.401239 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.53.27.597728 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.54.36.329373 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.54.37.154791 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.55.36.922181 PM
TUS114035904 unknown 100 28000 12-JUN-19 03.55.37.551698 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.56.38.817370 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.56.39.386218 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.57.39.337458 PM
TUS114035904 unknown 100 1017 12-JUN-19 03.57.40.823877 PM
BHS\TUS114035904 TUS114035904 102 0 12-JUN-19 03.58.33.519271 PM
Not much to go on there, what’s your question though – what caused SQL Developer to try to login to your database?
Jeff,
Yes , why might sqldeveloper be connecting and WHY with a bad password.
bad password part is easy – your connections have saved passwords, and one has changed
why it’s trying to connect – i would ask the person using the machine/program what they’re doing
Hello,
I’d like to know how to model attributes and columns that are of the GEOMETRY data type in SQL Developer Data Modeler (for both Oracle and SQL Server if possible).
It seems that there is no logical or physical datatype available for this out-of-the-box?
If there are blogs with a how-to, could you please point me to them.
Thank you so much. The content in this site is greatly appreciated!
Beatriz
switch your data type list to ‘Structured’
In SQL*Developer 19.1 I can access Edit User and see information about the user, Granted Roles, System Privileges, Quotas and associated SQL. Under Granted Roles I can see which role they’ve been granted, whether it is a default role or not and if it was given with ‘ADMIN OPTION’.
So where would I see table grants for the user and which privileges (i.e.: SELECT, UPDATE, etc.) they have? It doesn’t have to have every table in the system… as roles appears to have. Just the actual tables the user has been given direct access to.
Dan
Click on the SQL page, at the bottom of the script, you’ll see a list of the object grants.
The SQL has four sections: User SQL, Quotas, Roles and System Privileges. Each section has a bit of SQL… except for System Privileges which has nothing under it.
You asked about object grants not system privs. Which one is it?
HiJeff,
Is there a way in SQLDeveloper Data Modeler to generate DDL directly into a GIT branch and commit so that the scripts can be used as part of an auto-build CI process ?
If not, are there any plans for it ?
It would help massively with Agile database/DW development.
We’re building a CLI for the modeler now, so you could generate DDL scripts for this purpose.
Right now it’s interactive, push button.
Jeff;
If you’ve already provided information on this, please point me to the article.
We store a lot of XML data in CLOB’s in various tables. I need to be able to run a select for a particular tag within a CLOB. In the past I’ve used something like this:
where XMLPARSE(CONTENT bo_data_area WELLFORMED).EXTRACT(‘/utilityDeviceIdentifierNumber/text()’).getStringVal() = ‘F76481594’
If you have other suggestions, please let me know.
Not my field of expertise…I’d post your question to our forums, StackOverflow, or AskTOM
I want to run the SQL Developer database diff feature on schemas different than the schema associated with the SQL Developer connection (my userid). I log in with my userid but want to use one of the production schema for the comparison. The DBA’s control the credentials for the production schemas; my userid has privileges to see the production schema objects. we have multiple production databases and I want to check for differences in the objects’ DDL across the various production schemas.
I tried sofar: alter session set current_schema = prod_schema; This did not work and the diff report is based on objects under my userid ( I have no objects under my userid in production).
I use the GUI for the “diff wizard” in SQL Developer. No code.
Desired results would list the differences for the production schemas. Actual results are 0 results since my userid in the production database and does not have any objects
I answered your question on StackOverflow and directed you here for more details.
Hello Jeff,
First, thanks for this wonderful blog !
Right now. I am experimenting with the unit test feature of SQL Developer.
I like it but…
What annoys me right now is that there is no possibility of duplicating a test implementation.
By right clicking on a test implementation, It’s only possible to :
-Open
-Delete implementation
-Rename implementation
-Debug implementation
Can you tell me why ?
Is there a hidden way to do it ?
Would you have a script to do it automatically ?
For information, I know it’s possible to reuse startup/teardown procedures using the unit test library.
But I’m tired to rewrite time and time again the same function parameters, which change only slightly on each of my implementations.
Thanks !
Alexandre
Hello,
Since I did not get an answer, let me answer it myself 😀
I discovered my approach was wrong. I needed to duplicate tests instead of implementations.
And then create a suite for the tests to have a global startup and teardown procedure.
Also, I noticed the lookup functionality might be the reason why there is no duplicate feature on implementations.
Cheers,
Alexandre
Hi Jeff,
My objective is to Export the results of Multiple queries to a single excel file. I couldnt find solution for this to be precise. So tried if I can write a spool statement with each query and specify the Worksheet path against each one.
When I tried for the very first query, I am getting both query and results in my CSV file. I just want the results in csv file, can that be done?
spool S:\myresults.csv
select /*csv*/ * from EMPLOYEES;
spool off
Works for me
Hello Jeff – Firstly thanks for this miraculous ORDS product. I really love working on it!!
Is there any way we can export ords_metadata and later on import same ords_metadata without issues? We are on 12c DB and received a request to clone database from prod. In order to preserve the metadata we took export of ords_metadata, cloned database from Prod and then reimported ords_metadata but after import found that none of the objects imported. Found metalink note 1912162.1 that schemas with ‘ORACLE_MAINTAINED’ bit set cannot be exported and ords_metadata is one of them!
Is there any way to preserve metadata post clone? If not then it’s a good amount of work that we have to do to reinstall ords, create all security, services from scratch after every clone which is truly a laborious job!
Thanks
Absolutely there’s a way, you would use the PL/SQL API for example to get your MODULES out to a set of SQL scripts you’d run on your target system to copy things over. I talk all about this here.
I’ve downloaded SQL Developer and SSMS however I’m an error message appears saying the Network Adaptor can not establish a connection. Several sites have suggested I enable the Oracle services however no Oracle Services are installed. Where can I get the correct Oracle services which will solve this problem?
Thanking you in advance…
Bob
I think this if for you.
Hi
How can I save bind variable values between sessions?
I have a query with 20 bind variables and I have to enter each manually.
When I close sqldeveloper and re-open the bind variable values are gone!
Is there a way to save and re-use?
Thanks
Yeah, make your query a report.
Then set default values for all of the binds.
Hello Mr. Jeff,
I found it – a question exactly like mine – just to show you I did look:
https://www.thatjeffsmith.com/archive/2016/09/toggling-between-sql-worksheet-result-panels/#comment-118803
Question asks “How we can minimize and maximize result window like we can do in Toad using F2.” I would give my left arm for this functionality. Is it still a possible enhancement? Soon maybe?
Thank you for all you do
(SQL Developer 19.1)
I have logged a bug. If it were an easy fix/implementation, it would have happened already. I’ll see if I can light a fire and get it done for 19.3.
Thanks but I need this in order to EXPLAIN PLAN
and when I run the sql as a Report I don’t have the explain plan icon
you can have your cake and eat it too.
pull up the query in a worksheet, use the DBMS_XPLAN plan feature
pull up the report, run it
power-up your sqldev experience by creating a new tab group for the report so you can see both, side-by-side
Hi Jeff,
When calling an autoRest api through my app I get blocked by CORS policy saying the response to preflight request doesn’t pass access control check. The API works fine through postman and I don’t get any errors calling ords calls to a PL/SQL api.
Any thoughts?
Thanks,
Wayne
“pull up the query in a worksheet, use the DBMS_XPLAN plan feature” –
But that is the problem! when I run the query in a worksheet I have to re-enter all the (20) bind values !! It does not get them from the values I already entered in the report
“pull up the report, run it” – yes but no explain icon there
Your query had a sqlID… pull up the plan for it in the worksheet, run it via the report
SQL Dev v19.1
Tools/Monitor Sessions
Looking for ACTIVE_SQL
The Log shows the query had an elapsed time of 382657 — a long time
The code that was running
select
replace(q.sql_fulltext,chr(0)) sql_text
from gv$session s, gv$sql q
where s.sql_address = q.address
and s.sql_hash_value = q.hash_value
and s.sql_child_number = q.child_number
and s.sid = :SID
and s.inst_id = :INST_ID;
should there not be an equate of s.INST_ID = Q.INST_ID
and maybe s.serial# = :SERIAL (that is displayed column_name)
running the modified query with the s.inst_id = q.inst_id gave a much better response
Hello Jeff,
Great stuff on SQL Developer 19.1 New Connections screen! However, with the constantly growing number of connections (thanks to PDBS 🙂 ), we are missing the ability to search and filter connections on the left side list of the New Connection modal. I have about 60 or so of them and need a way to deal. Even an option to organize connections by folder, just like you have in a stand alone connection list would be helpful.
Thank you.
I suggest this instead…find the connection you want to base your new one on. Edit it – give it a new name and click Save. You’ve just cloned your connection 🙂
hi,
i want connect sqldeveloper 19.1 to database 19c but this error on connection :
Status : Failure -Test failed: IO Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond, Authentication lapse 0 ms.
why?
thanks
Hello Jeff !
How are you my friend ??
Jeff,
How I can use the Option Oracle Advanced Network Compression in SQL Developer when using MACOS with JDBC ?
Remember…I can use this now in Oracle 18c XE !!!
Thanks and congratulations for your BLOG !!!
In our production environment, SQL Developer cannot see packages – in the Connection Navigator, when trying to expand the Packages node, the packages list remains empty. I can see Tables and Views etc. I have access to DBA_SOURCE so that’s not the issue.
We can’t execute procedures in Prod so if SQL Developer is using dbms_metadata to extract the source code to display then maybe that’s the issue.
Do you know if SQL Developer is using dbms_metadata to extract the source code to display?
Thanks.
Just getting started. Would appreciate some tips on what to do next. I installed oracle 12c release 2 and can get to the SQL> prompt but it shows database idle. What is the easiest way to get sqldeveloper connected to say the sample databases? Also SQL> help is not available for some reason.
[oracle@yoda ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 16:16:49 2019
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> HELP INDEX
SP2-0171: HELP system not available.
I’m trying to run an export in sdcli like you do above but keep getting the error: CART parameter value is missing: cart. Do you know how to resolve this error?
can you show me what you’re running and what you get back?
Check your connection. Check your cart. Check your export operation settings.
Sure, I’m running this code
sdcli cart export -cart C:\Users\tlimer\CART FOR EXPORTDSRIP_POP_G1.xml -config C:\Users\tlimer\CART FOR EXPORTDSRIP_POP_G1_CONFIG.XML -target C:\Users\tlimer\CART FOR EXPORTDSRIP_POP_G1_REPORT.xlsx
getting back this message.
CART parameter value is missing: cart
you have spaces in your file names, so you’ll need to quote them
Ok, removed the spaces and it’s saying Command completed. I watch the folder where the report should land and it shows up for a couple of seconds and then disappears.
Ok, SQLDev would not be creating and then DELETING anything…so if you see it..something is stepping into between SQLDev and the output when you see it disappear
Thanks for your help.
Hi Jeff,
I noticed that in later releases of SQL Developer (certainly 19.1) the tree within the connections pane does not have “Editioning View” node. Is there an option to get this important EBR category back?
Many thanks,
Mark.
Editioned based views are really just…views. There’s no object of type ‘EDITIONING VIEW’ in the database.
We originally put them in as separate objects to advertise the feature. But they’ve been out for awhile, and most of our users are not using them (based on usage data).
If you want a way to key in on just those views, apply a filter, you have Exclude Editioning and Show Only Editioning Views options available.
Hi Jeff,
I am confused by the migration tool when moving mysql to oracle. source and destination is clear, however when you perform the migration it creates a schema with the name of the mysql database you are migrating.
So if my target would be schema ABC, and the database in MySQL is called FRED, the migration creates a schema in Oracle named FRED. Schema ABC just remains empty and is effectively only a facilitator.
Is there a way to get it actually into schema ABC.
The intent is to create an Oracle schema based on the database or user you are migrating over.
The oracle account you specify in the wizard is used to connect to said database and create all those objects. Easiest way to get what you want may be to rename the schema after it’s been brought over.