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
I am new to oracle VM, installed succesfully and also opened the terminal
oracle@localhost: and clicked sql developer and connected but i got ORA12505 error. how to resolve this and connect to sql developer
please suggest
Hi
I am unable to login into oracle sql developer. ORA 12505.
I an new to oracle VM , installed successfully any information can you provide so that i would login in to Sql developer.
how should i connect succesfully
~Mithlesh
you asked to connect to a database that the listener doesn’t know about. what did you put for service in the connection properties?
Hi
I am unable to connect:
there is one default connection system@//127.0.0.1
and when i click it automatically filled details on connctionname,username,password,servicename as orcl and port 1521but on pushing test button it shows ORA 12514 error. TNS listner error
what to do sir please help
Where did you get the VM from? The db running on it says it doesn’t know about database ‘orcl’
VM form the oracle website , i asked some in the forum
to practice plsql if you are facing problems iwth 11g installation then go for this
http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html
please help sir, i followed all the steps mentioned in the link
but still it shows an error
Upload a screenshot where I can see it showing your connection properties please.
try localhost instead of 127.0.0.1
i am able to write only comments here.
please see this one i am posting in my own blog
please suggest
https://infoallsite.wordpress.com/
hi sir
this time i got another IO failure how to deal with this
i am also checking in google, Please suggest
https://infoallsite.wordpress.com/2016/01/21/oracle-vm-error-unable-to-connect/
Hi
I am unable to login into oracle sql developer. ORA 12505.
I an new to oracle VM , installed successfully any information can you provide so that i would login in to Sql developer.
how should i connect succesfully
[email protected]
~Mithlesh
We have VM (VDI) desktops, and have Oracle 11g client with Java 6 installed on our desktops. Recently our SQL Developer 4.0.3 stared crashing consistently running SQL Scripts, or just hovering over connections, or icons, or sometimes just doing nothing. Our team reinstalled 4.1.3 version as well, but still the same issue.
Any thoughts?
Thank you.
crashing how? please share any error message/core dumps you get
413 won’t run with Java 6, so I’m assuming Java 7 is on there too
how much memory does each desktop get?
It doesnt leave any error messages on screen. My admin says SQLDeveloper comes with its own JDK (supported version) within the package, so no need of JDK8 installed on System explicitly.
My desktop got 8 GB.
This only happened with bunch of users at the same time a week ago. We are all developers working on some framework that supports only Java 6. FYI.
so the softwares are incompatible. sqldev 4.1 will only run using java 8 and higher.
Thank you, I will inform this to Admin group and try. Thank you for your time.
Hi Jeff
I am not sure can you help me but i want to ask you did you experience similar problem. I am using Centos7, SQLDeveloper Version 4.1.3.20, Java7.
When i have stable internet connection everything works fine, but when i work from home where “something” interrupting my internet, sqldeveloper connection drops and then i am in problem, only way to restart SQLDeveloper is xkill command 🙁 . Trying to reconnect or refresh tree will not help me. If i leave it for like 10 or 15 min it will disconnect but is to long for wait.
Sorry if my Eng is not best one. Thank you for your time, all best.
How to run a script with SQLCL and exit on completion?
SQLCL allows to execute an sql script from the command prompt, avoiding the interactive procedure of login, execute script and exit.
After the script execution has completed, SQLCL doesn’t exit automatically.
In SQLPlus there are two way’s to do this:
1) put an exit (or quit) statement at the end of the sql script
or call SQLPlus this way
2) exit | sqlplus -L -S admin/secret@xdm @clean_up.sql
– or for windows: echo exit | sqlplus -L -S admin/secret@xdm @clean_up.sql
The second option doesn’t work for SQLCL. Is there a way to achieve the same in SQLCL, thus without exit statement within the script?
It’s a known bug, stay tuned
Thanks for so far.
Can you please add the url to this bug?
Hi.
Do you know of any workaround?
Hi Jeff,
I have two questions on the same topic. I would like to compare two packages in the same schema, side by side.
Also, I would like to compare two packages in different shema’s with different names.
Schema: Package:
HR@DB1 MyPkg
HR@DB1 ProdPkg
HR@DB1 MyPkg
HR@DB2 ProdPkg
Is this supported?
Thanks,
Tim.
Yes.
Hi Jeff
When you export DDL scripts into folders in SQL Developer 4.1.3, packages and package bodies are exported with a “.sql” suffix. When you open the file to edit in SQL Developer, it displays it in the SQL editor, rather than the PL/SQL editor.
Is it possible to configure exports to specify suffixes based on object type?
e.g. package => “.pks”, package body => “.pkb”
Also, directories and object names are always exported in upper case, e.g.
@/Users/mark/TABLES/FLEXFIELDS.sql
@/Users/mark/PACKAGES/ADMIN_PORTAL.sql
@/Users/mark/PACKAGES/ALARM_CENTRAL.sql
@/Users/mark/PACKAGE_BODIES/ADMIN_PORTAL.sql
@/Users/mark/PACKAGE_BODIES/ALARM_CENTRAL.sql
Is it possible to include a configuration option for lower-case file names?
And finally, is it possible to use relative path names in the control script, so for the example above the output would be:
@./tables/flexfields.sql
@./packages/admin_portal.sql
@./packages/alarm_central.sql
@./package_bodies/admin_portal.sql
@./package_bodies/alarm_central.sql
Regards
Mark
Hello,
We have oracle 11G installed and in our recent projects we had issues with some of the DB objects getting invalid especially views. The queries inside the views are referencing tables/views between the schemas on the same oracle instace.
Using SQL developer when we right click on the object and compile, it said it was success but querying the SQL inside the views showed invalid identifier issue. These views originated in 8i->9i and now in 11G
We spent a lot of time trying to figure out what is going on without much success. Finally, we were able to compile successfully by formatting the entire SQL for the view and replacing it.
But doing this way resulted in extra space getting introduced on the column names and it took a lot of time to nail that down.
I have a two part question,
1. Why and when does the DB views goes invalid?
2. Are there any issues using SQL formatter in SQL developer (4.0.2.15)?
Any insights on this would be really helpful.
Thanks,
Jagan
Without seeing your code, I could only guess.
But whitespace shouldn’t matter.
Are they any issues with the formatter? Sure. That would cause your issue? Not that I know of.
Hi Jeff, I’m facing an issue with SQL Developer (versions 4.1.2 and 4.1.3), using JDK 1.8.0_65. I unzipped the program, searched for the JDK directory and then opened it. I tried to create a new DB connection, but when hitting the OK button there (clicking in the new icon -> Database tier -> Database connections) nothing happens and the DB connection creation screen doesn’t appear. I also tried copying the connections.xml file from another machine into the ..\AppData\Roaming\… directory, but the connections are not being displayed either. Any ideas on how to solve this or what else can I do to solve it? Thanks in advance for your reply.
close sqldev
go to your appData\Roaming\4.1.3 folder – nuke it
restart sqldev
if that doesn’t fix it, then delete the sqldev folder itself and ‘install’ it again
Thanks for your reply Jeff. I did as you told me but had no luck. I even downloaded a previous version (4.1.1), installed it and tried with it but got the same error. My solution was to download SQL Dev with JDK included, then, after unzipping and running it, I was able to see the connections icons in the DB connection panel and could import my previous connections. I suspect then that this is, somehow, related to the JDK version I have or the env configuration. Thanks again. Greetings.
Just glad you got it working!
Lazy coder question here.. Is it possible to configure SQLDeveloper to auto-close opening quotes, parathensis, brackets etc? A google search has not turned up anything of substance yet.
This would be done similar to what something like Notepad++ does when you are writing HTML tags etc.
not that I know of…
No worries, thanks for replying!
I tried to post a new report to the SQL Developer Exchange site today. I got an error “ORA-01400: cannot insert NULL into (“DBTOOLS”.”XML_REPORT”.”CREATED_BY”)
Unable to process row of table XML_REPORT.” Is there an official means of reporting this?
Hello Jeff,
i’m using sql developer 4.1.3.20.78, the output of the select query is clumsy as sql command prompt
i would like to see in rows and columns order what should i do..?
the output is the default, formatting it is pretty straightforward though
The Download page says => Version 4.1.3.901, Updated December 21, 2015
The About Page says =>
Oracle SQL Developer Data Modeler 4.1.2.895
Version 4.1.2.895
Sorry — My mistake!
Another sqlcl bug: “describe user_objects;” gives
Object Not Found: ribe user_objects
Usage: DESCRIBE [schema.]object[@db_link]
“desc user_objects;” works
When I try to build a User Defined Report with SQL Developer version 4.1.3.20.78 the application freezes. The only way out is to kill it in the Windows 8 Task Manager. Things worked fine with with version 3.2.20.10.
I have tried a few cleanup and reinstall operations to resolve the issue, nothing works. I have tested 4.1.3.20.78 with and without the UserReports.xml file left over from 3.2.20.10. In either case once I try to add a new report or modify an existing one, I freeze up when I click the Apply button in the Create Report pane.
so 4.1, user define reports is null – you try to add one, and it freezes?
If there is currently no UserReports.xml and I try to create a reports, when I go to save it, I get the freeze and the UserReports.xml is created with a 0 (zero) length file.
If I have a UserReports.xml that was created with a v3 SQL Developer and has actual reports defined, then when I go to add or change a report with v4 SQL Developer I get a freeze when I go to save it and the UserReports.xml file again has a 0 (zero) length.
I’ve looked for a method to debug this before my first posting, and the method that I found where you started it from the command line and from the bin directory showed no extra debugging related to what happened and/or lead up to the freeze.
I’m totally hosed here, and have to use v3 to create reports, then can run them in the v4 as long as I don’t make changes.
Hi Jeff:
Thank you for the work you do to maintain this blog.
I have a couple questions for you… I really like version 4 of SQL Developer but have experienced 2 frustrating things.
1: I have Completion Insight set to change code to “Upper Keywords / Lower Identifiers”. However, it periodically will reset itself to “Lower Case”. I don’t know if this is bug or if there is something I can to to force it to retain my setting.
2: I would really, really, really like to be able to tell SQL Developer what I consider a keyword to be. There are certain statements that it doesn’t recognize as being a keyword and will convert to lower case. For example , I want “TO_CHAR” to be upper case. Is there a way to edit the dictionary?
Thanks in advance!
Brian Rollins
Anal Retentive
Type A, OCD Certified
Oracle Database Developer
Exactly what version of 4 are you running? The first thing would be a bug.
to_char is a public synonym to the SYS function to_char()…so that gets resolved as an IDENTIFIER. If you want it upper cased, you can do that.
Now, to answer your question, can you edit the list? No. But in version 4.Next, the list is being updated to pretty much pull every single word we can find out of the Oracle Database Docs, so it should be pretty complete.
Hi Jeff,
Thank you for the quick reply.
To answer your question, I am using version 4.1.1.19
It also lists a Build MAIN-19.59
I hope this helps.
I haven’t been able to determine what makes it reset, but it does so within the same session usually about once per day.
I’ll look forward to the expanded “dictionary” in your future release.
Thanks again and keep up the great work!
Brian Rollins
weird…if you have an My Oracle Support account, I’d open a SR
Hi Jeff,
my previous comment was really too vague to be considered, but now I can be more precise. The issue is related with tables created from views using UNION. You can replicate the issue in this way (e.g. on the scott schema):
create view v_emp_10_20 as
select *
from emp
where deptno = 10
union
select *
from emp
where deptno = 20;
create table emp_10_20 as
select *
from v_emp_10_20;
Now if you try to update the table emp_10_20 in the data view of SQL Developer you will not be able to do it.
According to the Oracle Support this is related to a fairly old (but still unsolved) bug in the DBMS (Bug 7338622). In practice the data in the ALL_UPDATABLE_COLUMNS view are incorrect, when the table is created in that manner.
Nevertheless, maybe Developer could avoid checking the “updatability” of columns, which seems to be a useless info in the case of tables.
Thanks for all useful infos provided in this blog!
Jeff,
Is there something up with your style sheet?
In the Search box on your page, text entered is white on a white background.
Regards,
JT
yeah, i had this fixed…and then something happened. I’ll have to fix it, again.
Hi Jeff,
First off – I hear great things about this site – very experience IT profs that I work with says this is a very helpful site.
I have a question about SQL developer – I have a need to connect to DB2 for data extract or just to browse DB2 tables. Is this possible from to do this from SQL Developer? If so, how can this be setup? If not what are my options or alternatives to access DB2 from SQL Developer?
Any and all assistance will be highly appreciated.
Thanks
Okonita
you need to get the jdbc driver from IBM – they require you have a license/account with them – and then you can follow the directions here
I have version 4.1.1.19 and I and wanting to display the numbers in my results when I export the data to text file. How do I do this? I know in plsql/dev I would right click for and excel file and the row numbers would be automatically included in the results.
There’s no way to include row numbers, unless you include row numbers (SELECT rownum, …) in your query.
Looked; could not find answer.
I am looking for a replacement to scripts I have the use listagg against the DBA views to build pipe separated value (PSV) exports with headers.
Dos the CLI suport formatting ‘hints’ /*CSV*/ ?
What about PSV?
Headers?
Thanks! I enjoy your site.
Rich
does it support the formatter comments, such as /*csv*/ – YES
does it do pipe delimited output, – YES
I live in Cary – if there’s a few of you over there at the hospital, more than happy to come over and do a free training if you’d like.
Very cool! I’ll be in touch
I opened a binary file as script once (an xls sheet), and the SQL Developer crashed.
Now, every time I try to open a script file, its crashes again.
Where do I can see some sort of cache I believe it uses to open files, to clean it and then use again this feature ? It is installed in Mac OS X Yosemite, and works fine.
Thanks !
you try to open a .SQL file and it crashes?
Exactly, when I try to open a SQL script it crashes. I’m using version 4.0.2 in OS X, and worked fine until once day I opened a binary file by mistake…
weird
remove the app
go get 4.1.3
when it asks if you want to import your settings from 4.0.2, say ‘no’
You should still have the same sql history. you can export your connections from 402 and import them to 413 if you’d like
I installed version 4.1.3 and it worked as you said, I wonder if was my mistake or not.
Thanks !
no, i don’t think so
there’s a file in the settings you probably could have deleted to reset whatever we did when you opened that file, but the benefit of new/features/bug fixes over finding that 1 file in the haystack in the old version will always win out in my book
Question regarding sqlcl: I get errors for
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
that are accepted by sqlplus (and seem to be correct according to the documentation). Any idea why sqlcl barfs for these? Many other `WHENEVER` formats are accepted by sqlcl.
nice catch, that’s a bug
Happy to give something back. I always thought it was high time for Oracle to improve on the stone-age sqlplus, so very happy to see this.
Is there a way to subscribe to new releases?
no, but do you do Twitter? Follow me or @krisrice for sqlcl updates.
otherwise we push them every 6-10 weeks, at least here recently
HI jeff smith
actually I am trying to run a .sql file (which have an insert command) in batch file which should add a record in oracle sql developer which is in a remote server.I have tried by creating path environment variable(D:\sqldeveloper.zip\sqldeveloper\sqldeveloper\bin), but it just opens the application but it is not running the script.please help me on this..
thank you.
you can’t feed connections and scripts to SQL Developer via command-line
you can however feed it to SQLcl, which you’ll find in the \bin directory of version 4.1.2 and version 4.1.3 of SQL Developer. SQLcl is a new command-line interface, in the spirit of SQL*Plus but with many of the popular features in SQL Developer