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,803 Comments
Any plans to add an auto-save feature for Worksheets?
Thanks.
I am writing a RegEx search expression in a SQL Developer Worksheet.
Every time I pause to think, it searches for what I’ve typed so far.
When it can’t find what I’ve typed so far (beacuse the expression isn’t yet complete), it makes a beeping sound.
So for one RegEx search expression, it might make 50 beeps.
EXTREMELY annoying and stressful.
Is there any way to control this? Either 1) search only after pressing Enter or 2) turn off the beep
I can’t find anything under Preferences for either “sound”, “beep”, “search” or “find”.
Thank you!!
Ugh, that’s annoying as hell.
I’ll log a bug.
Workaround, type your regex string en toto in the worksheet/notepad, and then paste it into the search dialog.
Thank you very much!!! Yes, I agree with you about the Notepad idea. I’ll probably just use a scratchpad file I create-use here in SQL Developer. Thanks again.
Hi Jeff,
I have a use case where I would like to suppress generation of an index object when generating DDL for a specific physical model of a relational model. Is this possible?
Many thanks,
Mark.
Hi Jeff,
Do you have an update?
Many thanks,
Mark.
Hello Jeff
I was being almost fully satisfied user of SQL Developer for a long time. It is really great tool. Thank you.
Recently I changed my work and now do more PL/SQL stuff.
Some PL/SQL packages I work with are rather big. And I mentioned code completion insight completely not working at these packages.
Not even trying to do single query to database;
By some experiments figured that there is some hard coded limit based on lexical elements of script, some what 10000.
If I comment some code it start working. Even being a little lower than 10000 it work fast so it is not performance issue (not timeout etc.). I use latest 21.2.1 checked also old 17.4 and seen same limit there.
So my question is this completion insight limit based on lexical elements adjustable (may be some property for AddVMOption)?
It would be great if so. Any way I think current limit is too low.
PS. If needed I can post simple script generated package script where the limit is obvious.
Regards
Tools > Preferences > Code Editor > Parser > Parser Threshold
It’s based lexical tokens, is more intense, and is set to switch over to the powerful parsing algorithm (less features) at 15000.
>Tools > Preferences > Code Editor > Parser > Parser Threshold
Thank you for respond. Yes this parameter affects. If it is low other parser specific things like Code Outline also won’t work.
I have increased it to 150000, and Code outline is correctly formed, even on huge scripts.
But Code Complete Insight seems to be limited by other limit threshold as well. If Parser Threshold is greater than 10000 then real show stopper come from other place.
Could you please recommend a way for me to find people who I could pay to provide me 1×1 coaching for TOAD for Microsoft SQL Server? (I know the basics of programming)
Sorry, I haven’t worked with Toad or SQL Server for 10 years.
My advice would be to exhaust your free resources first. Follow the #sqlhelp hashtag on Twitter for some ideas.
Hi Jeff,
In the latest version of SQL Data Modeler (21.2 and a number of previous versions), I am unable to create or modify column “Comments in RDBMS”, “Comments” or “Notes” on any view definition in Relational Models. The input box is available for all three and not read only, but when the Apply or OK button is pressed, the comments are not saved.
Is this expected behaviour or a bug? If the latter, please could this be fixed.
Thanks in advance,
Mark.
Definitely a bug…or at least, that should work.
It’s working for me – do you see any ominous messages/warnings in the Log panel?
Hi Jeff,
Thanks for the quick reply.
Unfortunately it isn’t working for me in version 21.2.0.183.1957 and it hasn’t for the last two versions I have tested. Steps to re-create:
1) Create a new definition view in the relational model.
2) Using the query functionality, get one column into the view from either another view or a table. Click apply.
3) Open the view and navigate to columns.
4) Highlight one of the columns. Note that if the table/view column referenced by this view already had comments, this will be copied over.
5) Modify the “Comments in RDBMS” or “Comments” or “Notes”. Once changed, click the Apply button and the changes are lost.
I have two error messages in the logging page. I have no idea whether these are related to the issue:
sequence=166
Elapsed =12
Source = oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1
Message = null at com.oracle.jdeveloper.nbwindowsystem.NbEditorContainer.getURL(NbEditorContainer.java:1019)
See View > External Log for the entire log, and share that back if you can, or open a thread on the Modeler Forums.
Hi Jeff,
There was nothing in the external log.
I am happy to send screenshots of the issue to your e-mail address if that helps?
Many thanks,
Mark.
Not really. I would start troubleshooting, does this happen for any design, or just one particular one? What OS, Java, SQLDevDM versions are you using? Are other changes persisting? Are files corrupted/read only? That kind of stuff.
Hi,
SQL DM version: 21.2.0.183 Build 183.1957
OS: Win 10 Enterprise Build 19041
Java: 1.8.0_291
All attributes in the View Properties except Columns -> Comments in RDBMS seem to be editable and get persisted as expected.
I have checked all the XML files that make up the model and all are writeable. I even found the XML file for the view definition in question and modified the tag within the <viewElement tag outside of the modeling tool. After saving the file and reloading the model, the modified value from the XML file is reflected in the UI.
However, any attempt to further modify this value through the UI results in the modification being lost as per my original explanation of the issue.
I hope this helps to replicate.
Many thanks
Mark.
Oh a view..I tested a table. Will try your scenario on Monday after a long weekend.
Hi Jeff,
Did you get chance to try my scenario?
Many thanks,
Mark.
Just now, and drumroll…BUG! I see this in my log –
SEVERE 2033 0 oracle.dbtools.raptor.controls.grid.DefaultResultSetTableCellRenderer oracle.dbtools.raptor.utils.DataTypesUtil.stringValue(DataTypesUtil.java:406)
I’ll log this for you, thanks for sticking with me on this one!
Hey Mark – if you click from one column to another, and then come back to your original column where you entered your comments/notes – they’ll appear again. Developer sees the issue, and gave us the workaround 🙂
Unfortunately too late to get ready for 21.3…
Hi Jeff,
Thanks so much for the workaround.
Mark.
Where do I find documentation on CopyFormatter API? Concrete question is: I like the “card.js” script to show individual rows from a query result, but I would like to include the column names for every value. Is there a way to do this?
I tried commenting on the Youtube video here: https://www.youtube.com/watch?v=7A-anQoi6tI&lc=UgzZq17l4LLd_0RDOap4AaABAg.9REDWaByMy29REG-_C2hhq with the link to the oracle forum where I posted my question and it seems like Youtube quietly refuses to post them.
So I’ve already written up the question on the oracle forums here:
https://community.oracle.com/tech/developers/discussion/comment/16806379#Comment_16806379
Any advice is appreciated!
Can SQL Developer 20 graphically model a deep UDT hierarchy?
create type grandparent as object;
create type parent under grandparent;
create type child under parent;
What are the steps to get SQL Developer to show this and other UDTs graphically?
Thanks!
Jeff,
I still have trouble to validate my ORDS which connects to a secured Oracle DB (19c).
When you say “get the JDBC driver properly going OR you can use an OCI (thick)..”
Do you have any links I can follow to have this setup?
Once setup do I use the same command below or need extract parameter to pickup the required wallet?
java -jar ords.war validate
It’s not a simple answer – you can either get ORDS to use a thick connection, and so it goes through the same path to the database as SQL*Plus does – so you setup your wallet there, ands ORDS to tags onto it.
Or you go through the process of getting a custom JDBC URL going to pass everything over required for the secured database connection.
user@jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=something.com))(connect_data=(service_name=dbservicename))(security=(ssl_server_cert_dn=”CN=cert_info_here”))) UNKNOWN — you can look at the JDBC Oracle docs for more info on this.
In SQLCL, can you somehow reference UNIX variables like $ORACLE_HOME?
Oracle support says they do not support Tomcat, is this Tomcat error applicable to using ords of TCPS?
Database is on Linux cloud VM, App Server/ORDS/Tomcat on Windows
04-Sep-2021 23:18:22.318 INFO [main] org.apache.catalina.core.AprLifecycleListener.lifecycleEvent The Apache Tomcat Native library which allows using OpenSSL was not found on the java.library.path: [C:\Tomcat 9.0\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Program Files\Java\jre1.8.0_301\bin;C:\app\oracle\product\19.0.0\client_1\bin;C:\Program Files (x86)\Common Files\Oracle\Java\javapath;C:\Windows\system32;C:\Windows;C:ge\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\OpenSSH\;C:\Windows\ServiceProfiles\LocalService\AppData\Local\Microsoft\WindowsApps;.]
04-Sep-2021 23:18:23.430 INFO [main] org.apache.coyote.AbstractProtocol.init Initializing ProtocolHandler [“http-nio-8080”]
here is my defaults.xml changes
default
customurl
jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.0.146)(PORT = 2484))(ADDRESS = (PROTOCOL = TCPS)(HOST = 10.0.0.146)(PORT = 2484))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1.bbb.xxx.yyy.com)) (SECURITY = (MY_WALLET_DIRECTORY =c:\app\oracle\wallet)))
Hi Jeff
How do you avoid SqlDeveoper to float all over the place when using Spaces on Mac ??
regardless of what I do SqlDeveloper pops up on any Desktop that I have defined….
Any way to avoid this ?
No idea, I only have one monitor for my Mac Mini – suggest pinging @krisrice on Twitter or on his blog krisrice.io
Hi Jeff,
We were Using sqldeveloper with Kerberos authentication. How can we enable it for sqldeveloper web?
Thank you and have a nice day.
As of today, you cannot, it’s limited to database user authentication. There are plans in the works to extend availability by proxy users, Cloud Identity/AD (IDCS), and more.
Hi Jeff,
I have trouble to validate ORDS to a db requires TCPS connection. Running the ORDS is fine but when I am trying to validate it using the following methods got different errors:
METHOD 1: (seems like ORDS don’t know where the wallet is, not sure how to provide the wallet???)
C:\oracle\apexuser\ORDS>java -jar ords.war validate
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:24841
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:DCPAMPCE
Requires to login with administrator privileges to verify Oracle REST Data Services schema.
Enter the administrator username:teldbadmin
Enter the database password for teldbadmin:
Confirm password:
Aug 25, 2021 10:11:29 AM oracle.dbtools.rt.config.setup.SchemaSetup verifyDBAUserConnection
WARNING: Failed to connect to user teldbadmin jdbc:oracle:thin:@:24841:DCPAMPCE
IO Error: Got minus one from a read call
METHOD 2: ( already provide the db connection details in apex_pu.xml, apex.xml, apex_al.xml)
C:\oracle\apexuser\ORDS>java -jar ords.war validate –database apex
Requires to login with administrator privileges to verify Oracle REST Data Services schema.
Enter the administrator username:teldbadmin
Enter the database password for teldbadmin:
Confirm password:
Aug 25, 2021 11:02:45 AM oracle.dbtools.rt.config.setup.SchemaSetup verifyDBAUserConnection
WARNING: Failed to connect to user teldbadmin jdbc:oracle:thin:@null:null:null
IO Error: could not resolve the connect identifier “null:null:null”
So you’re wanting to get ORDS’ connection pools to the database to be encrypted a la secure sqlnet/SSL.
You’ll need to get the JDBC driver properly going OR you can use an OCI (thick) connection, which will tie into an existing $ORACLE_HOME, where the wallets, etc are configured. It doesn’t sound like either of those things are happening here.
Jeff,
“get the JDBC driver properly going OR you can use an OCI (thick)..”
Do you have any links I can follow to have this setup?
Once setup do I use the same command below or need extract parameter to pickup the required wallet?
java -jar ords.war validate
Hi Jeff,
Is there a way to remove the record count from a query result in SQLcl?
I know I can remove the columns header with SET HEADINGS OFF.
Also, if one want to know what each of the 72 environment variables accepted by the SET command do, where is the best place to go?
HR@orcl> set feedback offHR@orcl> select * from locations
2* fetch first 5 rows only;
LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID
______________ __________________________ ______________ ____________ ___________________ _____________
1000 1297 Via Cola di Rie 00989 Roma IT
1100 93091 Calle della Testa 10934 Venice IT
1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
1300 9450 Kamiya-cho 6823 Hiroshima JP
1400 2014 Jabberwocky Rd 26192 Southlake Texas US
HR@orcl>
There’s probably more than 72.
show all or show all+, and then some/most of those should have help text avail, like
HR@orcl> help set sqlformatSET SQLFORMAT
SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}
default : SQL*PLUS style formatting
csv : comma separated and string enclosed with "
html : html tabular format
xml : xml format of /results/rows/column/*
json : json format matching ORDS Collection Format
json-formatted : json format matching ORDS Collection Format and pretty printed
fixed : fixed width
insert : generates insert statements from sql results
Example
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
loader : pipe (|) delimited enclosed with "
Example:
7369|"SMITH"|"CLERK"|7902|"1980-12-17 00:00:00"|800||20|5555555555554444|
delimited : CSV format with optional separator , left, and right enclosure,,7902,17-DEC-80,800,,20,5555555555554444
set sqlformat delimited [separator] [left enclosure] [right enclosure]
Example:
set sqlformat delimited , < >
7369,
ansiconsole : advanced formatting based on data and terminal size : Mask following Java DecimalFormat
set sqlformat ansiconsole : base format
set sqlformat ansiconsole default : number formatting to ###,###.###
set sqlformat ansiconsole
https://docs.oracle.com/javase/8/docs/api/java/text/DecimalFormat.html
set sqlformat ansiconsole -config=highlight.json : highlight matches in results
highlight options :
Example :
{"highlights":[
{"type":"startWith","test":"W","color":"INTENSITY_BOLD,CYAN"},
{"type":"endWith","test":"MAN","color":"BLUE"},
{"type":"contains","test":"MIT","color":"YELLOW"},
{"type":"exact","test":"FORD","color":"GREEN"},
{"type":"regex","test":"[0-9]{2}","color":"MAGENTA"}
]
}
HR@orcl>
In the Real Time SQL Monitor at times the SQL comes back ‘incomplete’, with ‘…’ as the last three characters. What is the easiest way to get the complete SQL statement?
Where are you seeing the ‘…’ ?
Yes, when I hover over the line on the page and it comes up in the black window.
Just a black panel, with no query text?
No, there is query text. But the query is long and ends with ‘…’ at the bottom of the black box.
You can right-click ‘Copy’ to get the full SQL text.
I do right click and copy the statement. And then I paste it into a worksheet. And the statement ends with:
and email.goremal_status_ind = ‘A’
and …
Dan
how many characters are you getting until the Ellipsis (…) come into play?
Maybe I should log a bug, but not sure you want 1,000,000 characters put onto your clipboard when you could/should just query v#sql for the SQL_ID text.
Hi Jeff,
I’m probably going to feel foolish when you provide the answer, but…. I’ve got a simple query that runs fine in SQLD (v.21.2) but when I transfer it to a User Defined Report it fails with a ORA-00933 error. What am I missing? The query:
SELECT
‘1’ srt,
‘irdb_dw’ owner,
MAX(ir_run_date) run_date
FROM
irdb_dw.wc_ira_term_enrlmt_ytd_ps@irdbprod
UNION
SELECT
‘2’ srt,
‘fnd_irdb_dw’ owner,
MAX(ir_run_date) run_date
FROM
fnd_irdb_dw.wc_ira_term_enrlmt_ytd_ps
UNION
SELECT
‘3’ srt,
‘stg_ir’ owner,
MAX(ir_run_date) run_date
FROM
stg_ir.wc_ira_term_enrlmt_ytd_ps@tdtbl
order by
srt
;
Thanks,
Mark
Hmmm, I can do a TABLE style report with this code, with or without a semicolon (;) at the end…now, I don’t have your code or DB_LINKS to play with, so not sure if they come into play or not.
SELECT'1' srt,
user owner
FROM
dual
UNION
SELECT
'2' srt,
user owner
FROM
dual
UNION
SELECT
'3' srt,
user owner
FROM
dual
ORDER BY
srt
Hi Jeff,
That code runs fine so I’m assuming there is something about the dblinks it doesn’t like. If you think of something else I should test, or if you think of another way to do this, I’d love to know.
Thanks,
Mark
Hi Jeff,
In MySQL Workbench, there’s a feature that shows the SQL commands that will be executed every time a user change something (Update a row in the datagrid, change a column’s datatype). I want to ask if SQL developer has a similar feature. Ultimately, I just want to be careful when creating any changes.
Thank you in advance
Data grid changes, no.. Although you can see them afterwards in the Log.
Table structure changes yes, we preview that DDL for your.
Hi So I have this fairly large model comprising of 500+ tables in SQL Data Modeler – with a whole bunch of subviews thrown in. The question is – is it possible to export / save each subview as a separate model – for ease of distribution, deployment etc.?
The use-case here is that we would like to manage the data-model centrally across a suite of apps – with each subview representative of a specific app within this suite. When an app is deployed, the corresponding subview alone would need to get deployed.
Hi Jeff,
is it possible to export the ORDS_METADATA schema (with our pl/sql handlers) via expdp and import the schema in different database ?
background:
Usually we export all relevant application schemas from production database to our testing environment. We implemented pl/sql handlers for our application and we want to keep the ords-pl/sql code compatible to correspondig application schemas in testing env.
For sure.
I would normally recommend you source control your rest APIs, oauth2 clients, etc via scripts…then you could just run those as needed in various environments.
But data pump should work just fine.
Thanks, Jeff!
Hi Jeff
Another question on scaling with a 4k monitor. Followed your suggestions re jdk11 and that works great. However the Real Time SQL Monito remains tiny. Any advice on how to increase the font size of the monitor
Thanks
Keith
You could disable scaling.
Hi Jeff,
tried following:
Windows 10
Right click on sqldeveloper.exe -> properties -> compatibility -> Change high DPI settings -> Override high DPI scaling behavior. Scaling Performed by -> select “System” form drop down.
Doesn’t affect the Real Time monitor – still very small
Keith
Disable it entirely (I think, typing from the beach)
Ah – has to be done from a beach. Lying on a towel or a beach chair 🙂
Cheers – enjoy
K
Thanks for the quick response Jeff..Appreciate it.
1 – yes, simply swap out the files–
Is this supported officialy and what files should we be swapping out..swapping just “sql” will do?
2-I tried set history filter for alter it works..but create doesnot..
SQL> set history FILTER create
Unknown Command create
Unknown command in history filter
1 – altering the home, probably not officially supported, so just download sqlcl to /opt or wherever, update your path and run it from there
2 – yup, i see that, logging a bug now – thanks for the report!
Questions on sqlcl
1)Is it possible to upgrade sqlcl which is shipped with client/DB software?If Yes, how do we do it?
2)Is it possible to exclude create user/alter user from history. Currently the passwords could get exposed if sqlcl installation is shared.
1 – yes, simply swap out the files
2 – yes, see the HISTORY command setings, you would just add CREATE and ALTER
SQL> help set historySET HISTORY
-----------
set history [FAILS [LIMIT [ n | DEFAULT ] ] | NOFAILS
|FILTER [DEFAULT
|LIMIT [n|DEFAULT]]
FAILS sets history command to show failed statements.
use limit to limit number of failues
NOFAILS sets history command to not show failed statements
FILTER sets a list of sqlcl commands that can be excluded from the history
LIMIT sets a new max size for the history
NB. History will not save failed commands at end of session.
SQL> show history
HISTORY
enabled
filter: show, history, connect, clear, dbccred, dbcc
Do not show failed statements
SQL>
Load Table using sqlcl
I have been trying to use load table in sqlcl..For number data type i noticed sqlcl is rounding off big numbers…Is there anyway to avoid it?
how is the column defined and what are some example numbers?
Column is defined as number(28)..example numbers are:
592135377776198004078,
592135377776635704299,
592135377776852704089,
592135377776773404009
Yeah, I see that now, not good! Bug logged, we’ll get that fixed ASAP.
Thanks Jeff..
Hello. I’ve installed the newest SQL Developer 21.2 on MacBook Pro OSx11.5 Java 11.0.9
The problem is that in a code editing window (package or procedure) the run/debug icons don’t appear.
Is there a solution?
Thanks in advance.
Can you show me?
Hello Jeff,
Since SQl Developer Version 19. a connection to the database via VPN is no longer possible. (ERROR 17002).
When I work in the company, SQLD works fine. Only version 18.4.0.376 is still able to establish a connection via VPN. We also have a VPN in the company, and the same phenomenon can be observed there. Every employee of our company or of customers have the same problem. What is the cause and can we fix it ourselves?
regards from germany
Jens
You’ve tried this with 21.2?
It’s been happening for 2 years, have you opened a SR with MOS? Give me that SR# and I can take a look.
Your problem description doesn’t match any known issues…we use VPN here all day, every day as you can probably imagine.
How to send you a screenshot?
post it on the internet somewhere and send me the link