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,561 Comments
Hi Jeff,
I am running SQL Developer 4.1.1.19. We are not able to use the JDWP version of the debugger because our servers are not local.
For prior versions of SQL Developer you had given me instructions to disable the JDWP debugger by putting the directive:
DatabaseDebuggerDisableJDWP=true
. . . in the ide.properties file in the path
C:\Users\bhmurphy\AppData\Roaming\SQL Developer\system4.1.1.19.59\o.sqldeveloper.12.2.0.19.59\ide.properties
. . . but that doesn’t work any more. I know I am not putting the directive in the right file. Can you tell me how to disable the JDWP debugger if it is still possible?
Thanks!
Barry
>>We are not able to use the JDWP version of the debugger because our servers are not local.
not sure what this means – they don’t have to be ‘local’
>> I know I am not putting the directive in the right file.
how do you mean? AFAIK the instructions haven’t changed in this area
Well, by “not local” I mean we use Oracle on-demand to provide server hosting capabilities, so that TCP_CONNECT() command fails when the JDWP package tries its initial connection.
Never mind, I’m am not sure what I did to make this fail, I just got the debugger working by modifying ide.properties in c:\users\userid\AppData\Roaming\SQL Developer\system4.1.1.19.59\o.sqldeveloper.12.2.0.19.59\ide.properties so it really does still work. My mistake and thanks for your attention!
Hi Jeff ,
In sqldeveloper data modeler I have relational model, tables, foreign key, unique key, …
I apply from design rules menu relational then index/constraint , some errors appears , when I want to double click on row to edit one error nothing happened , when I put the mouse on the row a message appear like “Unique key name is out of bounds”. How can I solve this problem
hi! i installed sql developer on mac. but i cannot create a connection because of password problem. during the download never asked me password. how can i create a connection?
Hi,
I am loading in .sql files into a database via the sql developer. When I open a .sql file and run script, it runs it one after the other. Is it possible ton run the script of multiple .sql files concurrently at the same time, thereby saving some time?
you’d have to run each one in an unshared sql worksheet
the worksheet is just single-threaded, and connections are shared
hence, the unshared worksheet
so, open worksheet, run .SQL
open an unshared worksheet, run .SQL
continue..
“hover over” object name in editor not changing to blue link 90% of time. same is the case while hovering cursor over “*”. I am using sqldeveloper64W (4.1.1.19) on Windows 7 64 bit. Java jdk1.8.0_60. I am quite unhappy on shifting from TOAD. Please help.
Sure. Give me a use case.
Show me what’s in your editor.
Then tell me what your object is – do you own it, or is it in another schema?
Sorry Jeff, may was my laziness to put semicolon at the end of each line. Earlier I tried below statements , only first object got highlighted
SELECT * FROM SHU.TAB_MDL_A
SELECT * FROM SHU.TAB_MDL_B
SELECT * FROM SHU.TAB_MHD_C
SHU.PKG_AR_NOTIF
And after adding “;” all below objects got highlighted blue on hover.
SELECT * FROM SHU.TAB_MDL_A;
SELECT * FROM SHU.TAB_MDL_B;
SELECT * FROM SHU.TAB_MHD_C;
SHU.PKG_AR_NOTIF
But I feel, you should point out this in your tips / hints page to avoid under estimation on SQL Developer usage.
w/o the statement delimiters, they’re seen as a single, invalid statement
when folks say this feature doesn’t work, the first troubleshooting step is to confirm you have valid code in your editor…
As I’m considering your blog as one of the main sources for the new SQLcl I thinkyou can answer this one. Where to add bug reports?
I think the current version does not handle empty lines in PL/SQL blocks correcty.
Editor returns to SQL> (Leaves editing mode) when an empty line is in the code.
can you provide an example? seems to work for me
you can provide feedback including bug reports to the SQL Developer OTN Space/Forum. Once we officially release it, bug reports can be submitted as Service Requests with My Oracle Support.
Hi Jeff,
I am looking for three things:
1. How can I control the convention for naming of constraints in the relational model when I generate it from the logical model?
2. How can I control the convention for naming of referenced columns?
3. How can I stop overwriting of constraint names in the relational model in case of regenerating the model based on the logical?
I would be happy to get an answer. I was checking the configuration but haven’t found anything.
Thanks & Regards,
Reinhard
naming standards are in the design properties. right click on your design in the tree, and go to properties. you can then see how the constraint and object names are generated
on 3 – not sure exactly what you’re doing, but are you evaluating the merge properties when you go to engineer the relational design from the logical? you might just need to uncheck the constraints you don’t want brought in b/c they’re already there
THANKS!
Hi,
First of all thanks for your great blog ! I really like it 🙂
Here’s my quick question for you :
In a last version of Data modeler the value’s textbox for a dynamic property was able to expand and was kinda acting like an “editor”. With the latest version (4.1.1.888) when you click to edit a dynamic property you have a simple input showing the content on only one line.
Do you have an idea why this “regression” happened ? It was much user friendly before (even if a better editor with color syntax and line number would much awesome !).
Thank you for your reply.
Clément
Hi Jeff,
I am trying to execute the DB2 Migration to Oracle using Utility Tool. But I am getting error which is specified below. Moreover I have all the previliages which is mentioned below also. Kindly help me.
CREATE USER migrations IDENTIFIED BY password
DEFAULT TABLESAPACE users TEMPORARY TABLESPACE temp;
GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO
migrations WITH ADMIN OPTION;
GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE,
ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,
CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY
SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY
ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO
migrations;
And error I am getting is
http://dba.stackexchange.com/questions/108410/sql-error-on-script-execution-try-deleting-repository-before-creating-repositor
Kindly help me.
delete all of the tables and views for that user and try again
if that fails, open a Service Request with My Oracle Support or post a note to our migrations forum here
Hi Jeff,
i’ve been looking around for info about sessiontimezone that is being defined by the sql developer client when creation a session and couldn’t quite find what i’m looking for so i hope you might have the answer
Here’s the deal:
When i run the client (Version 4.0.3.16) on my laptop running windows 7 with eastern time zone, i connect to a database (11g), run select sessiontimezone from dual and get America/New_York as a result which is neat because it takes into account daylight saving time.
When i do the same from a terminal server running windows server 2008 R2 using the same version of the client with the eastern time zone set as well, the sessiontimezone select return -05:00 instead America/New_York which is bad in the sense that every timestamp column is an hour late
I understand that an absolute -5h doesn’t care about DST so that’s why i’m seeing every times an hour late. I also understand that i can fix it quickly with an alter session set time_zone = ‘America/New_York’ but having to do that alter every single time i connect to a database is rather annoying.
From what i can see, the default value appears to be set time_zone = local.
Now what i’d like to know is how that local value is defined because on 2 different system using the same time zone, one has local = America/New_York and the other has local = -05:00 and i have no clue on how to set the sql developer client to behave the same way
thx for any input you can provide
I’m not sure HOW it works – I think the JDBC driver sucks up the local info and sets that property.
If you want to change it automatically, add it to your login script. Then you won’t have to run it manually anymore.
I’m curious if you have an Oracle client on your machine and SQL*Plus what it picks up…if you like that, you could try a thick connection in SQL Developer and I’m betting you’d get the same answer as SQL*Plus then.
Hi Jeff,
I don’t know if it’s the reason behind but a difference is that my laptop use a complete oracle client while the TS use an instant client (11.2). However specifying the instant client location in the database > advanced preference and check the use oci/thick driver doesn’t seem to help.
Am i missing something or is it something that is not available in the instant client?
Hi Jeff,
I attend your webcast on Wednesdays if I get a break from daily meetings. In one of your sessions I mentioned to you that we are having some issues with viewing the Output Variables when a stored procedure containing ref cursor is executed. We opened an SR with Oracle seeking some help. Their answer was that they can see it but we cannot. The reason was not explained quite well or it may just be our team who did not comprehend it. Regardless, during that day’s webcast, after I brought it up, you wanted me to send you the SR # and the DDL of the tables and the stored procedure. I am not sure how I can send you that information. Could you please send me an email where I can send you all of that information?
Thanks in advance.
[email protected]
I have sent the DDL of the tables and the stored proc to the email address you have provided.
Thanks for your help.
Hi, I am using SQLDeveloper 4.1.1 and would like to use the ssh tunnel feature but cannot find where to enter the listener ip.
Scenario:
connection from localhost (with installed SQL Developer), any local port,
connect via ssh to desthost:22 using username and password,
tunnel thru that connection to listenerip:7777 (a virtual ip address on the desthost).
So I need: localhost, destination host and listener ip, but there are only two possible entries!
Do I miss anything?
Regards
Roger
that goes in your database connection details
you go through your ssh tunnel, and then once there, the db connection says, take me to listenerip:7777
Sorry for reasking, but I could not follow:
When using the ssh-hosts feature of SQL Developer 4.1.1,
I can define a “new SSH-Host”, can enter host, port, username (of the destination host I assume) and I can optionally add a local port forward.
But where can I define the listener-ip and its port?
The database connection properties only let me chose the defined ssh-host entry.
Sorry i answered too quickly, once you have your SSH tunnel defined, right-click and add a local port forward. There you supply the server and port number once you get across the tunnel to find your db
Ok, I was getting a bit further: the destination host shows the /etc/issue but I can only click [OK]. The host does not ask for a password, though, and I got an “Auth fail” error.
Tunnelling via PuTTY works and I will continue using that.
Thanks for your (I know it is not) support
Roger
I have one problem can you help me.
ID PID Status
101 100 N
102 100 O
103 100 R
104 103 O
105 103 R
Output shoub be like
ID Original _ ID Status Level New_Status FLG
101 100 N 0 AMD N
102 100 O 1 AMD N
103 100 R 1 AMD Y
104 103 O 2 AMD N
105 103 R 2 AMD Y
106 106 N 0 CAN N
107 106 O 1 CAN N
if for any Original id Status is like (N ,O ,R) in sequence or (O ,R ) in sequence then update NEw Status as AMD
AND Update the Records with Status R = ‘Y’ all others will be ‘N’
for any Original ID — Status like (N,O) only in row wise sequence then New Status will be CAN
AND Update the Records with Status of N and O as ‘N’
How to Implement this logic ?? I need to compare the the Row wise for the Column status ???
Pls Help me
sorry, i don’t have time to help people with their SQL – helping them with SQL Developer is where I have to draw the line
Thanks for the reply.
I’m not asking for complete solution If you have any Idea on how to compare or build logics on comparing row by row values from same table , pls guide me for this.
Version 4.1.1.888
I have an Entity with many subtypes. I used to be able to click on the entity and bring up the details or to move it in the diagram. This has stopped.
If I want to add a file as part of the documentation for an Entity how do I do that? Best thing would be a link to show up in the Report.
What is the limit in Characters in the comment field?
1. something is wrong – can you open the properties for any entity in your design?
2. not sure you can
3. not sure there is one, why are you running into a limit?
1. Works now. Seems like some memory issue perhaps. But I was able to click down to properties for other entities both outside the Entity-SubEntity and SubEntities within the Entity. Just not the “big” one.
2. Too bad for documents; would have been nice. I think Designer had some feature like that.
3. I was trying to include all of the 1000+ examples for the kinds of things being measured. After about halfway down, I could see nothing — just blank lines.
I have 32 GB RAM and 64 bit OS (Win 7)
Hi Jeff,
I’m using SDDM 4.0.2.840. Is there a way to display the Abbreviation of a Table in a Diagram?
Thanks,
Arjen
From what I can tell, no.
But you could probably generate a data dictionary report that would show the table name and it’s abbreviation.
SQLDev v4.1.1.19.59
Trying to import data from Excel spreadsheet. After completing about 20 or so, for the next spreadsheet, I get a message “GC overhead limit exceeded”
Also the right-mouse click to invoke “paste” does not work and so have to resort ti ctrl+v
xls file or xlsx file?
20 rows and it craps out?
what if you save it as CSV?
can you send me your Excel file so I can try it?
It was after 20 or so files not rows from oNE file. Anyway after shutting down and restarting the file got processed.
xls or xlsx? better memory management available when working with xlsx files
Hi there
i just installed oracle SQL developer and i am stuck on how to make a coneccetion to an oracle Server.
i want to run my database on my localhost
thanks
Search on here for: I installed sql developer, now what?
HI Jeff,
I have been working with PLSQL Developer tool before, in which while writing queries, when you type apps. it shows all the tables and views , from which i can select. i am looking for that kind of drop down in sql developer when i am writing queries, i cant remember all the table names. like i type hr. it should show all the tables in it. Do we have any such thing in sql developer.
We have that feature. But, we don’t show the suggestions if there’s more than 10 objects.
Go into preferences and disable the filter. I have a blog post on the feature if you need help.
I do a long running query (~60 minutes). I watch it appear to finish in the remote db v$session with a final state of “SQL*Net message to client” like it is waiting to send the results back (which is only a count). Meanwhile SQL Developer sits spinning like it is wait also. Any concept of why they appear to have stopped talking? If either side times out shouldn’t SQL Developer have kicked out saying something?
The Sql Developer Text Editor is the worst in the world..
and in the latest version sqldeveloper-4.1.1.19.59-no-jre, the Script output is also behaving really weirdly..
i know ure not in support…
that’s not a question
it’s also not anything I can take to help make the product better. saying something is the ‘worst in the world’ helps no one except yourself.
give concrete examples of what is not working
Hi Jeff,
I’ve gotten a lot of great mileage out of SQLcl. Nice tool. I have one issue below I would like to see if you can help me with.
It appears that the use of “set sqlformat csv” causes date output to ignore the current NLS_DATE_FORMAT setting. The only workaround appears to be to use TO_CHAR(). Is there another way to resolve this? Is this a currently reported bug? Thanks for all your help.
here is an example session showing this issue…
SQLcl: Release 4.2.0.15.275.1225 RC on Fri Oct 09 11:44:44 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Password? (**********?) ****
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sysdate from dual;
SYSDATE
——————————-
09-OCT-15
SQL> set sqlformat csv
SQL> select sysdate from dual
“SYSDATE”
09-OCT-15 11.45.25.000000000 AM
SQL> alter session set nls_date_format=’DD-MON-YYYY’;
Session altered.
SQL> select sysdate from dual;
“SYSDATE”
09-OCT-15 11.45.43.000000000 AM
SQL>
no, that’s a bug
Hi Jeff, Thanks for the feedback. Is there a specific place to report bugs with SQLcl or have you already reported this one? Thanks again for the great blog posts!
Since it’s still an Early Adopter, the best place to report issues is to the OTN Space/Forum for SQL Developer.
I reproduced and logged this for you.
Data Modeler v4.1.1-888
1. Is there a way to create a diagram using a subset of entities? We used to be able to create these in Designer; much easier to discuss a “Subject” having a diagram that fits on a page
2. In the comments section , how can I have a formatted table so that the entities report looks “nice”. I want to be able to have an example of Species with all of the 7 layers identified for a specific set of species. Even spaces to separate the columns would be good.
you want SubViews for #1
for #2, not sure how you mean…can you provide an example
Hi!
Say I have 4 Columns with several rows of values
Headings => C1 C2 C3 C4
Row 1 => Value11 Value12 Value13 Value14
Row 2 => Value21 Value22 Value23 Value24
Cannot figure out how to paste a screen shot on here
you can’t, you have to use an image sharing service like imgur and then use the link here
I guess the same thing then for the Data Modeler Entity comments?
so you want to be able to pretty print in HTML your entity comments?
And the pretty print should maintain the prettiness even in the Entity Report! — please
it’s probably just dumping raw text to html – you should consider using the reporting repository and building your own reports – then you can do whatever you want
Hi Jeff, is there any way to launch a script using SQLcl. I’d like to schedule unattended jobs with SQLcl. Thanks for all you do for us!
just like you would with SQL*Plus…
I’m seeing some odd behavior lately:
When I’m finished working, I right-click the connection and select “Disconnect”. Then I close any open worksheets.
But if the worksheet has an open Query Result tab, closing that worksheet (or just the Query Result tab) causes the connection to spontaneously reopen (and sometimes a new worksheet as well).
Thoughts?
what version are you on Doug?
It was 4.1.0, but I just upgraded to 4.1.1, and it seems to be fixed.
So, never mind and thanks!!!
Do you have any posts or articles about using PL SQL to make http post and put request? What has been tried results in the following errors but it is not clear by DBA how to fix the access issue.
Error report:
ORA-29273: HTTP request failed
ORA-06512: at “SYS.UTL_HTTP”, line 1130
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 32
29273. 00000 – “HTTP request failed”
*Cause: The UTL_HTTP package failed to execute the HTTP request.
Thanks for any suggestion.
the error message tells you everything you need to know, specifically the ORA-24247.
Here’s where you need to go, or send your DBA.