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,791 Comments
Hi Jeff
I install and done all the set-up for Oracle Database 12c Release 1 and are able to login using //localhost:5501/em with my Id and password but why when I use Oracle SQL Developer to connect Oracle Database 12c but fail with error message (Status : Failure -Test failed: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor). Am I miss anything?
probably b/c you can’t use SID – if it’s a pluggable db, you have to use the Service – unless you’re actually trying to connect to the container database
also i’m not sure if em express goes into the db via the listener, so it’s not guaranteed your listener is configured correctly, although it’d be really weird if it wasn’t
a better test would be to use the same connect string in SQL*Plus to login
So you can turn on trace from the sessions screen – how do you stop the trace?
i wrote a custom report to support this, but thinking i’ll also file a bug. seems like an oversight to me to not support that out of the box
Is it possible to connect to a SQLite DB with SQL Developer? If so, how? (On Win 10 Home, 64-bit), and latest production SQL Developer).
Sorry, no.
Hello Jeff, I just recently upgraded our environment from SQL Dev 3.2 to 4.1.3. Ever since I have had a couple of users having an issue where if they run a script to get their data (without exporting it). After they have their data they will then right click and select the Export function (to a csv). On the old version of SQL Developer it would generally take a couple minutes. On the newer version this is now taking the 20+ minutes to export 581 rows.
Any ideas?
Any faster to export it to a different format? Or if they use the /*csv*/ comment in their query and run as a script?
I am not sure off hand, I will be finding out from our user to see though
Hi Jeff,
I am using /*csv*/ in my select statements to run a script with CSV output. I find that when I do this, I then cannot comment out sections that have /*csv*/ in it. For instance the below select statement is not commented out as it would be if not for the /*csv*/. I am not using SQL plus so cannot use the set sqlformat. I want to get away from using — on each line as there is a lot to maintain in this script. Any thoughts on how it can be done?
/*
Select /*csv*/ field_name
from table_name
*/
Hi Steve,
Checking in to see if you have a response to this. If there is no good solution, no problem. Thanks for reviewing!
in SD highlight the code and tap ctrl-/
This will toggle single line comments.
–select /*csv*/
–list,of_cols
–from dual ;
TR
I am running scripts in SQL Developer and they are completing successfully, but Task Progress continues to show them as running until I kill the process. Can you point me in the right direction to solve this?
Thanks!
have you confirmed they’re still running on the server?
what are the scripts doing? anything that would require a ton of undo/temp type work?
have you tried a thick connection?
Confirmed that the scripts are no longer running on the server. Haven’t tried the thick connection yet. I don’t have the thick client installed on my pc yet. These scripts are doing large selects from one table and inserting into another. We have a few billion rows of data and are trying to purge a lot of it so we’re doing CTAS. I’m committing every few million rows to keep temp down to a few gig. My DBA has confirmed that there aren’t any timeouts on the db. We had networking to a trace between the pc and db server but they didn’t see anything.
Hi Jeff,
Great site!
I’m using SqlDeveloper to copy tables from Sybase to Oracle(Copy To Oracle).
Working fine – but are there any logs created during the copy and where can I find these?
Thanks!
/jio
no logs that i know of, what do you need exactly?
It seems like breakpoints with conditions can only be used with:
numbers e.g. l_val = 5
nulls e.g. l_val is not null
However, strings do not seem to work nor do some other conditions that I would normally use in a where statement, like:
l_cmd = ‘xyz’ – there are no error messages when this is used but it does not match
l_cmd = “xyz” – there is an error message when I use this syntax.
l_cmd in (‘xyz’,’zyx’) – also error message complaining about syntax
The documentation in SQL developer says: “Condition: A SQL condition (WHERE clause without the WHERE keyword) restricting when the breakpoint occurs.”, which I read to be, anything that you can use in a where would work.
What exactly is supported?:
1) Are strings supported?
2) Are tables supported? my_table.some_val = ‘bar’?
3) What else is/is not?
and for the previous question what syntax must be used?
I’m using this today, but this seems to be a problem since at least a year or 2 ago (I haven’t experimented with an old release)
About
—–
Oracle SQL Developer 4.1.0.19
Version 4.1.0.19
Build MAIN-19.07
IDE Version: 12.2.1.0.42.150416.1320
Product ID: oracle.sqldeveloper
Product Version: 12.2.0.19.07
Version
——-
Component Version
========= =======
Oracle IDE 4.1.0.19.07
Java(TM) Platform 1.8.0_11
Versioning Support 4.1.0.19.07
i would check the plsql debugger docs to know for sure
Thanks for the reply. I looked at the jdwp, dbms_debug etc. Could you give me a hint what to search for?
A very observant person has discovered that for string conditional breaks the variable must be UPPER case, but for integers it does not matter: L_STR = ‘xyz’ , l_num = 22 , L_NUM = 22. l_str = ‘xyz’ is not valid.
I have not been able to discover how to do conditional breaks for tables.
Is it possible to run in SQL Developer a script like in SQLPlus in which I connect in different Schemas to perform some action like:
—
connect system/blabla@test
CREATE USER testuser PROFILE DEFAULT IDENTIFIED BY testpsw
DEFAULT TABLESPACE PODATKI TEMPORARY TABLESPACE TEMP1 ACCOUNT UNLOCK;
GRANT CONNECT TO testuser;
GRANT RESOURCE TO testuser;
—
connect sys/blublu@test as sysdba
Grant execute on utl_mail to testuser;
—
connect katas/blibli@test
grant select, update on parc_del to testuser;
grant execute on pg_vks to testuser;
—
connect stavbe/bbleble@test
grant select on stavbe_gr to testuser;
grant select on stavbe.STA_PARCELE to testuser;
—
connect testuser/testpsw@test
create table pdmid_seznam (pd_mid number(12), sifko number(4), parcela varchar2(10), opis varchar2(200));
create table gu_parcele (pc_mid number(12), sifko number(4), parcela varchar2(10), gu number(3));
create table temp (vsebina varchar2(200));
yes, but when the script is finished, the connection is closed (i think)
I have the following code, and using sql developer Version 4.1.3.20,
set linesize 180
set pagesize 999
col course_fee for 999,990.90 heading “Fee”
col course_id for a35
col course_institution_name for a5 heading “Type”
col display_title for a10 heading “Lesson”
col instructor for a45
col student for a22
col title for a10
col user_student_id for a15 heading “PID”
break on instructor skip page on course_institution_name on course_id on student
compute sum of course_fee on instructor
select
instructor_lastname || ‘, ‘ || instructor_firstname || ‘(‘ || instructor_email || ‘)’ instructor
, course_id
, student_lastname || ‘, ‘ || student_firstname student
, user_student_id
, title, display_title
, attempt_date
, course_fee
, course_institution_name
But “break on instructor skip page on course_institution_name on course_id on student
compute sum of course_fee on instructor” does not work. It does not break on instructor.
How do I format break on …….
Thanks for your help in advance
Hi Jeff
I’ve started using Data Modeler with Subversion option. I think it’s really nice tool, but I have some difficulties with using Pending Changes tab. I’ve seen in your posts that changes you make to model are grouped nicely in a model/table in which it was made. In my instance I can only see XML files, so basically I have to go into each of them and have a look at all XML property level (?). Do you maybe know what I’m doing wrong?
How to add more KEYWORDS to the lexer? For example “NVL”. I think that I’ve been thru all the option and do use the “PL/SQL Syntax Colors” and other “Code Editor” options, but I cannot find where to add additional keywords.
If we’re not picking up a keyword, it’s a bug.
You can’t define your own.
NVL isn’t a keyword, it’s an IDENTIFIER/function.
Yes, you’re right. At a customer site, when using SQL Developer on their host, I could have sworn that NVL() wasn’t highlighted and so I posted here to ask. Yet right now on my host, it is highlighted. I think both installs are using the same version 4.1.3.20. Hmmm … I’ll have to double check when I’m back on their site in a couple of days. Sorry to bother you.
So I’m at the customer site today, using their install of 4.1.3.20 and NVL() isn’t highlighted as it is in my other install. Both are 64-bit installs. Nor is the functions NVL2() for that matter. Yet other functions such as MIN and MAX are highlighted — meaning, they’re blue.
Any suggestions on where I could look to identify the change in configuration?
Thank you.
no, it’s just weird, sorry
you could open a Service Request with My Oracle Support I suppose
I’ve noticed the last two versions of SQL*Developer (current version 4.1.3.20) display a Logging message (in a logging page tab) upon startup: org.openide.util.RequestProcessor$Processor, Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3. I get similar messages simply by clicking in an editor window (if connected to a database). While these don’t affect my work, is there any way to suppress these? If I can’t fix the issue that causes this and there’s no mechanism to “report the problem to Oracle”, it seems as though these shouldn’t even be displayed.
close the log panel? also assuming you’re not running in debug mode
looks like it’s coming from where we send up usage info, where you have agreed to share that so we can see which features are getting touched more than others to determine where to invest R&D in the tool
Thanks for the quick reply! Yes, I did check the “Allow automated usage reporting to Oracle” message. And while I do close the log panel (and I’m not running in debug mode), it just keeps re-appearing with new messages. If someone at Oracle is actually analyzing this info, then I’m happy to hear that.
switch to the messages panel – and then physically close the log panel…you shouldn’t see it pop up again
It’s nearly impossible to find educated people for this topic,
however, you sound like you know what you’re talking about!
Thanks
Using SQLDev 4.1.3.20 i discovered an odd behaviour
whenever i add another selection point above the existing, the new entered text is printed in reverse order.
maybe you want to have a look at this little gif movie https://dl.dropboxusercontent.com/u/25663382/sqlDevMulti.gif
Jeff,
Enjoy the blog and getting a lot of productivity out of SQL Developer. I am using the cart feature and it really helps moving code for Dev to QA to Prod. It does have one feature that I’d like to have more control over. When I export a cart a second or more times the file name adds a version number. It would be nice to have an option to overwrite the file instead of adding a version number to the name.
Could this be possible, or maybe it exists and I don’t know where to look.
Cheers
TR
On that topic, I’d like the option to also overwrite when using the database export wizard also, which appears to be the same engine that the cart exports are using. Would also be useful if a file name and path could be saved with the cart also so the next time we just hit the cart export button and forget about it. If different locations are required, a user could overwrite the cart ‘default’ or define additional carts.
I am not able to view message log window in sql developer.
Try Window > Reset Windows to Factory Settings
that will reset your desktop layout
I am an old CASE user and want to use the same rules to transform logical to physical models. i.e. pluralize entity for tables, use the short name for keys (fk,uk,pk). Is there a place to do this (or even maybe a library). The docs are very weak on this.
I’m not an old CASE user, so I apologize in advance.
For plurals, you you want to use the glossary feature, and then look at the transformation scripting.
Heli and Kent both have blog posts on how to do this I think.
Here’s a tutorial on the Glossary feature.
In SQLcl, can’t execute anonymous block. Error occurs with / after end;
yeah, that’s bogus. bug.
unless i’m smoking crack, I’m talking to the dev now to confirm one way or the other.
as a workaround you can end the anon block with a ‘.’ and then execute the buffer with a ‘/’
Thx
ok in our latest dev build, this is working as expected.
Is there any way for SQL Developer to (by default) run the SQL statement that your careat is sitting on? Rather than having to highlight the statement first?
that’s how it works by default now
put cursor in statement, hit ctrl+enter to execute
OK – gotcha. It works with semi-colons – not without.
yeah I know…but as I recall TOAD worked without needing them.
Thanks
Graeme
You’re asking toad to guess what your statement is.. We don’t do that
Is there any way to add a Warning banner/dialog box (that you need to click OK) to SQL Developer when you connect to a database?
Something like ‘This is a Federal computer system….’
Thanks
Graeme
Jeff, It doesn’t appear that Oracle Data Modeler will generate enable statements for triggers as part of an export of DDL. Am I missing something? If I export say an entire schema..tables, etc and look at the DDL file, all the create trigger statements are there, but no “alter trigger enable” statements.
If there is a way to add those, it would be a huge help.
your triggers will be enabled by default, yes?
Hi Jeff-
I am really stuck and it doesn’t seem like there is an easy way to go about doing this.
I am trying to round a timestamp field in Oracle 11g to the nearest second
31-MAY-16 01.59.10.827000000 PM would become 31-MAY-16 01.59.11PM.
I have tried multiple things and I can’t seem to get it to work.
I can’t just Trunc the timestamp, as the rounding is important for my comparison.
Is there a way to do this in Oracle?
Have you seen this?
I did, that is kind of what I meant by no easy way. I was hoping you had a less confusing way to do it. I will try to work with that though. Thank you sir…
I got it to work, the post that was the accepted answer had some issues, I think I just needed to got back to it with fresh eyes. In case anyone is wondering this is the answer:
TO_CHAR(to_timestamp(to_char([timestamp field], ‘YY-MM-DD HH24:MI:SS’), ‘YY-MM-DD HH24:MI:SS’)+
CASE WHEN TO_CHAR([timestamp field],’FF9′)>=500000000
THEN INTERVAL ‘1’ SECOND
ELSE INTERVAL ‘0’ SECOND
END,’YY-MM-DD HH24:MI:SS’)AS ROUNDED_TO_NEARES_SECOND
I need an example of adding the connection to spatial oracle from sqldevolper
spatial isn’t a type of database, just connect to your database, and you can start working with things like the SDO_GEOMETRY data type
How do you do a data compare of tables existing in different schema’s using sql developer
you’d have to use SQL statements, MINUS queries…or you could use the dbms package that allows you to do data compares, check the docs
Thank you, it’s exactly what I need, I’ll try asap.
Best Regards,
Mario.