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,937 Comments
I would like to open the all models (logical, relational and physical) at the same time as I open a design. At this point, I have to manually right-click open on the physical model database site. Unfortunately, I checked “Don’t show this dialog again” on “Select Relational Models” window.
Thx!
Never mind! I found it. Under Tools -> Preferences -> Data Modeler.
I have to check “Show Select Relational Models Dialog”.
we default to ‘off’ because those things can get huge, esp if you have tens of thousands of partitions – just be careful 🙂
Hi Jeff, I am just getting started on SQL Dev, switching from TOAD. The latter has a nice feature whereby you can export data as a HTML table into your clipboard, ready to be pasted in such applications as Outlook or Word that can understand HTML tags. In SQL Developer (4.1) it appears that you cannot export HTML to the clipboard at all, only to a file. Further, this export.htm file will then contain a complete HTML page with styles etc, and the data that I am interested in as a . Is there an option to export like TOAD does, i.e. just the part, output to the clipboard, so that it can be pasted into an e-mail as a table. Thanks…
“and the data that I am interested in as a <table>”
Not sure why we don’t support HTML -> Clipboard…I’ll see about fixing that.
Workarounds…
SET SQLFORMAT html
run query as script
copy/paste
or
SELECT /*html*/ * from…
run as script, again copy to clipboard.
Since we’re not a native Windows application, I’m not sure what our options are for what you’re getting at, but I can look into it.
Hi
I am using Oracle on AWS as a RDS machine. Amazon Web Services lock down a bunch of views such that a DBA can access the views, but cannot regrant the views.
So, when using autotrace, it fails for all developers as there is no access to v$mystat, and there is no method for getting access to this vew.
Is there a way around this?
Thanks
Paul
Sure, you can move off their Cloud onto ours 🙂
Or you can beg the Amazon team to grant you access or to tune your queries for you.
If it were me, I’d probably build a copy of the system in-house, reproduce the performance problem there, and then upload the fix to your AWS instance.
Looks like begging is the only option, all dev MUST be done on the cloud….
It is really frustrating working on the AWS RDS machines with the restrictions forced on the DB.
Jeff,
Is it possible to use a jdbc connection in SQL Developer to submit Logical SQL to a BI Server for test?
Thanks!
I don’t know what Logical SQL is.
Is your BI server running an Oracle Database on it? If so, then ‘Yes.’ If not, then ‘maybe.’
Yes, it is running an Oracle Database. I may not be connecting to the correct URL because I get an “identifier is too long” error when I send Logical SQL.
Best
sounds like your object name > 30 chars, which isn’t kosher in Oracle
The Oracle BI Server transforms the Logical SQL into physical SQL. This is OBIEE Analytics Logical SQL I’m trying to send. But I guess I’m sending it to the wrong service.
Thanks!
Correct – no can do this
“OBIEE Analytics Logical SQL” in Oracle Database
Hi johnb,
I haven’t tried to do that, but this *may* help. It’s an example of using JMeter using JDBC to send Logical SQL to the BI Server.
https://gist.github.com/rmoff/5162390
Based on that, you *might* be able to accomplish your goal. That’s where I’d start anyway.
chet
Don’t know about SQL Developer -> OBIEE, but you can definitely use JDBC generally. Here’s another example: https://gist.github.com/rmoff/5a424bd8039669375c76
@JohnB — what’s your use case here? What are you trying to do? Any reason you’re not using nqcmd to do it via ODBC?
Dear Jeff,
I’m trying out the new Sqlcl and it looks great, but I keep geting the following error randomly when I press key up.
I’m on windows 8.1 and java 8 also.
may 07, 2015 10:02:12 AM oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli log
RAVE: L a o p e r a c i ¾ n s e c o m p l e t ¾ c o r r e c t a m e n t e .
ava.io.IOException: L a o p e r a c i ¾ n s e c o m p l e t ¾ c o r r e c t a m e n t e .
at org.fusesource.jansi.WindowsAnsiOutputStream.applyCursorPosition(WindowsAnsiOutputStream.java:134)
at org.fusesource.jansi.WindowsAnsiOutputStream.processCursorDown(WindowsAnsiOutputStream.java:204)
at org.fusesource.jansi.AnsiOutputStream.processEscapeCommand(AnsiOutputStream.java:228)
at org.fusesource.jansi.AnsiOutputStream.write(AnsiOutputStream.java:125)
at java.io.FilterOutputStream.write(Unknown Source)
at sun.nio.cs.StreamEncoder.writeBytes(Unknown Source)
at sun.nio.cs.StreamEncoder.implFlushBuffer(Unknown Source)
at sun.nio.cs.StreamEncoder.implFlush(Unknown Source)
at sun.nio.cs.StreamEncoder.flush(Unknown Source)
at java.io.OutputStreamWriter.flush(Unknown Source)
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.resetCurrentCursorLine(DbtoolsConsoleReader.java:4377)
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.redrawBuffer(DbtoolsConsoleReader.java:4333)
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.moveHistory(DbtoolsConsoleReader.java:3687)
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2967)
at oracle.dbtools.raptor.console.clone.DbtoolsConsoleReader.readLine(DbtoolsConsoleReader.java:2508)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:592)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:297)
a o p e r a c i ¾ n s e c o m p l e t ¾ c o r r e c t a m e n t e .
I found this post that points to the same error.
https://community.oracle.com/thread/3717472
I was executing sql.exe instead of sql.bat, my bad.
Thanks
Dear, Jeff.
in Oracle SQL Developer 4.1 while executing query like select * / select count(*) in Task Progress window I see the following:
Executing: /*+ NO_PARALLEL */ SELECT * FROM ….
What is the reason of adding that hint?
Best Regards.
Piotr
Ugh, I see that. BUG
We’re adding those hints when browsing tables, but shouldn’t be happening in the worksheet.
Dear, Jeff.
Additionally, we will get a some kind of nice feature when we try to execute something like SELECT /* + PARALLEL */ FROM …
we will get something like
Executing: /*+ NO_PARALLEL */ SELECT /*+ PARALLEL */ FROM…
What is the proper way of submitting bug report for such issues?
Best Regards.
Piotr
Yes, it’s happening to me too.
hi,
i know how to create user with privilege of admin…but also i want to create users with different and limited privilege.can you help me and or a good website about this…
thanks.
Try http://lmgtfy.com/?q=oracle+manage+user+rights
😉 sorry, could not hold that one *LOL*
It’s remarkable in favor of me to have a website,
which is beneficial in favor of my know-how. thanks admin
I’ve got version 4.1.0 up and running on my PC and getting set up it just the way I like. Unfortunately I have done something to the script output (F5). It is wrapping the text at 80 characters. I cannot seem to find where to change that setting. Can you point me to the preference setting that I’m overlooking? Thanks.
SET LINESIZE 180 – will set it to 180 instead of 80
Thanks, that works.
Is there a way to make this permanent so I do not have to set the line size each time I start up SQL developer?
yes, add it to a script, and tell sql developer in the preferences to run that script each time you make a connection
Script does not get applied when opening files, only when starting a blank connection. Is there a global fix (other than using SQLD v.3)?
script is applied when a connection is made, no connection is made when you open a file, so…
open a .sql file and run it, and the login script is not applied
so you have NO connections
you open a .sql file
you assign a connection to it – which makes that connection go active – your login.sql isn’t being applied?
can you confirm this with the View > Log > Statements panel? This is new in v4.1, which I assume you’re using.
correct — login script not applied and nothing in the log statements to suggest that it is. SQLD for mac 4.1.1.19.
that would be a bug then
BUT
I cannot not reproduce this behavior.
On connect in a worksheet for a script, i see my query running.
I’ve highlighted it so you can see where it is in the sequence of what runs as a connection is established.
What exactly do you have in your script?
There is a line “select USER from dual” if that’s what you mean.
the file contains:
set linesize 32767
set pagesize 32767
Clearly is not used when opening a file and running it, but is used when creating a blank window/new connection, typing a command, and running it. Clearly meaning that the values are changed to something reasonable (not 80/14).
Dear Jeff,
I’ve a question regarding the ‘Log-Statements’ View/Window.
I wonder if there is a way to close/disable this View. Don’t get me wrong, it’s sometimes nice to know what’s going on under the hood.
But – if I’ve seen this correctly – it seems that the Message and Statement Window/View always appears together.
And sometimes the Statements-View ‘steals’ the window-focus. And if you close it it reappears after the next statement or pl/sql compile.
Maybe I’ve missed an option in the preferences? Or maybe there is a ‘hidden’ parameter for sqldeveloper/product.conf?
But it would be nice to enable/disable this feature on demand.
Thank you in advance for any suggestions.
kind regards,
Thomas
No way to disable it, just close the Log panel. Are you using it to view something like serveroutput and the statements window is stealing focus?
Jeff,
I’m a long time plsql developer user thinking of switching to Oracle sqldeveloper. One of the major items I haven’t quite made it past is the differences between the sql beautifiers of both products. There seems to be enough differences in functionality such that I cant get them to match. Getting the beautifier to match how all my source code is currently formatted would greatly reduce the effort of transition. Any suggestions? Are there more robust external tools dedicated to just plsql formatting that could be plugged into the current version of sqldeveloper?
They’re different parsers and formatters, so they’ll probably never match.
You could keep a copy of PL/SQL Developer around JUST to format your code as it goes into source control or the database, or try to develop a new formatting standard using the formatter available in SQL Developer.
If you find huge deficiencies, or need help with certain formatting effects, we’re happy to take enhancement requests.
Jeff,
I have some specific examples. What’s the best method to get you a side by side compare of code to demonstrate? Specifically I’m focused on Global Package Vars in the package body.
Jeff –
Is SQLDeveloper 4.1 going to have a download with a local (not installed) jdk 1.8? Other applications and corporate requirements make it not possible to install JDK 1.8 at this time.
Going to, Yes. We just don’t have it available as of today.
Thanks – will keep an eye out for it. Did not realize that today was GA for 4.1 – I was just looking to update my copy this morning.
Hi Jeff,
I nstalled sqldeveloper-4.0.3.16.84-macosx.app.zip this version ni my mac book pro last night. Installations went smooth.But my issue is I am not able go through the new connection tab. I tried with
Connection_Name: HR_ORCL
Username: hr
Password: hr
port: 1521/1522( I tried both and verified the ports are free or not)
sid : orcl/orcle.xe
Can you please help me on my issue.
Thanks
Girish
SQL Developer is a client, not a database. Do you have a database?
Partha,
I was able to solve the problem it was due to a wrong SID, I discovered it watching a video on youtube.
Thanks for your help though.
Hello Mr Smith,
I’m new with Oracle. I’m using SQL Developer, when I try to create a database connection with the hr/hr user I get this 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
Please, I need help, I repeat, I’m new with Oracle. Thanks
By the way I ran the commands lsnrctl status and lsnrctl service
C:\Users\Vince>lsnrctl service
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 – Production on 01-MAY-2015 00:10
:38
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary…
Service “CLRExtProc” has 1 instance(s).
Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “orcl.168.2.2” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:259 refused:0 state:ready
LOCAL SERVER
Service “orclXDB.168.2.2” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000” established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=Vince-PC)(PORT=49199))
The command completed successfully
C:\Users\Vince>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 – Production on 01-MAY-2015 00:12
:19
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 – Produ
ction
Start Date 30-APR-2015 20:49:19
Uptime 0 days 3 hr. 23 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\Vince\product\11.2.0\dbhome_1\network\admin\lis
tener.ora
Listener Log File c:\app\vince\diag\tnslsnr\Vince-PC\listener\alert\log.
xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary…
Service “CLRExtProc” has 1 instance(s).
Instance “CLRExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl.168.2.2” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
Service “orclXDB.168.2.2” has 1 instance(s).
Instance “orcl”, status READY, has 1 handler(s) for this service…
The command completed successfully
C:\Users\Vince>
Are you using the default hr schema that comes along with Oracle DB installation? If that is the case, I believe by default the hr user is disabled.You need to unlock his account from the sys user.
Login as sys user and run the below command.
alter user hr identified by hr account unlock;
Hope this works.
Regards
Partha
Hi Partha,
you are right I am using the HR account, but I don’t know how and where to Login as sys user. I am new to ORACLE.
thank
When you installed the oracle DB, you would have supplied some password. try username/password as system/system.
Okay this is what I did
C:\Users\Vince>set ORACLE_SID = orcl
C:\Users\Vince>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 1 01:14:14 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter user hr identified by hr account unlock;
User altered.
SQL>
But still I am having the same problem when using the account HR password hr
Hello Mr Smith,
I’m new with Oracle. I’m using SQL Developer, when I try to create a database connection with the hr/hr user I get this 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
Please, I need help, I repeat, I’m new with Oracle. Thanks
Jeff,
Thanks for all the help you provide. I have a question related to a BLOB data element. When I try and view the object, by using the “pencil”, and then the external editor I receive a message “MIME type not supported”.
If I try and view the elemnt as an image I receive a “Image could not be decded from the binanry stream” message.
I can download the data but have not been able to view it.
Can you help?
Thanks
Hi Jeff,
I exported the DB schema using Tools–>Database Export.It generated a .sql file with all objects like tables,procedures etc. Now I am trying to run that SQL file into a different database schema. When I run the complete .sql file, I am getting below error when the create table gets called.
SQL Error: ORA-00439: feature not enabled: Deferred Segment Creation
00439. 00000 – “feature not enabled: %s”
*Cause: The specified feature is not enabled.
*Action: Do not attempt to use this feature.
Please provide your input. Or is there any other way to import?
Thanks
Partha
hello,
i wanted to install oracle database and in the end showed me that the amount of RAM is not enough.i allocated 2G ram…what to do?
you can do two things:
or cheat and get our virtualbox appliance and copy it’s settings – the entire VM only gets 2GB of RAM
Why unit_owner and unit_name values are null in UT_TEST_COVERAGE_STATS table. As those values are null, my code coverage report is null. It is not returning anything.
I’m using SQL Developer 4.1.0.18 (EA2)
and the View –> Snippets option isn’t showing on my menu and isn’t showing as part of my pallet, either.
I have checked the Tools –> Features –> Features –> Database –> Oracle SQL Developer – Snippet box is checked.
Ugh. of course right after I ask, I find where you answered the question here: https://community.oracle.com/thread/3522102
hello…i want to work with oracle…
i want to set my computer as a server and work locally with it…i don’t know what version of oracle (client or database ) i must to install.. i also want to use SQL Navigatoer to connect to oracle…
after that how to create user and pass to connect to oracle…
please help me…
I’m not the right person to help you with SQL Navigator. If you paid $$$$ for it, you should work with the people that sold it to you.
You will need an Oracle Database to work with it. Installing Oracle Server also gives you the client.
A client only gives you the ability to work WITH an existing database.
For more help with that, try reading this.
Hi,
I’m using SQL Developer 4.0.3.16. I’ve been using it for a while.
Today morning however, when I started the application, my connections view is missing. Even if I go to View > Connections, it just won’t appear. Why?
check your connections.xml file for any bad XML or file corruptions
Hi Jeff,
I am using SQL Developer 4.0.
I have a package of 3400 lines.
I am unable to debug any procedures or functions in this package. Debugguer just starts and does nothing…
Is there any limitation in SQL developer tool with respect to number of lines in a package.
I am able to debug all other packages which are around 2000 to 2500 lines.
Please help me on how to debug this package….
Thanks,
Kumar
There’s no limit. I talk to customers that routinely work with 20,000+ lines of PL/SQL code.
What’s the debugger output show?
Below message shown in the log. I have done compile for debug for both body and specification after keeping the break points in the package like any other debug i will do usually…
It just try to start the debugger and exits after 4 seconds.
Connecting to the database
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( IP, Port )
Debugger accepted connection from database on port 63107.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Process exited.
Disconnecting from the database
Debugger disconnected from database.
So it’s ‘working’ – do you have any breakpoints defined?
yes…defined at 1st line and after the begin. total 2..
even i am unable to step in to this package through debugger from another package..but i am able to step into debugger for all other packages.
Time to open a SR with MOS then I guess
can you elaborate this. SR and MOS..
Thanks much for quick response.
Got it.. i will contact the support.
Thanks again for your response.
I’m using 4.1.0.17, also confirmed same situation on 4.0.2.15. when using the DB Diff utility in SQL Developer, is there a way to ignore the “supplemental log group” name on tables? We use GoldenGate and when we compare two schemas, every table gets flagged as having a difference, even if the only difference is the supplemental log group name, which is system assigned and therefore always different. Could this be added as a future enhancement.