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,800 Comments
I want to use SQL Developer to access Hive2. Normal connection works fine but I’m facing connection issues when I have to use Kerberos. Does SQL Developer support Kerberos with Hive2 as indicated through optional connection parameters? Could not found any docs about this.
As I try to import excel data into an existing database in SQl developer, I am stuck on Step 1. I followed what is in here: http://www.thatjeffsmith.com/archive/2012/04/how-to-import-from-excel-to-oracle-with-sql-developer/ . I was successful last time but when I tried it for another excel file, it does not show me the Data Preview (which is Step 3 from the above link). When I press ” Next” nothing come up. I tried to modify the file name, file location and even restarted the PC, …still the same issue. any help is appreciated!.
What’s in your Excel file? Is it XLS or XLSX? Any formulas or pivot tables?
Hi Jeff
Are there any plans to publish sql formatter that is used in sqldeveloper as java class so all could use it directly in database PL/SQL code. Eg. I would like to format dynamically generated sqls in PL/SQL.
Kind Regards
Tomasz Lesinski
Hello Jeff,
Thank you for your posts on Oracle SQL Developer Data Modeler.
I have now downloaded the latest version and am trying to connect to SQL Server. Following your instructions, I have downloaded the JDBC drivers for SQL and included them in Tools > Preferences.
Now, when I proceed to import Data Dictionary, I see the tab for SQL Server connection. I have entered the hostname, port and other information, however, it gives me an error when I test connection:
“oracle.jdeveloper.db.ConnectionException: Test failed: Network error IOException: Invalid argument: create”.
I must admit, I am also getting an error when I test proxy in Tools > Preferences.
Kind Regards,
Avnip
well that’s weird. what jTDS driver did you download? what version of sql server are you trying to connect to?
can you ping that SS box from your machine?
you can always do an RE from a DDL script – just generate it out of SSMS and then use the import feature in SQLDev
Thank you for the quick response Jeff.
SQL Server is 2008 R2
jTDS is 1.3.1 from sourceforge dot net
I am able to connect to the SQL Server via SSMS
Thank you for the tip regarding DDL scripts. Will try that next.
you seem to be doing everything correctly, sorry I’m not sure what the problem might be
I’ve been using SQLDeveloper for about 5 years and love it! I recently had to rebuild my development machine and decided to install the latest version (4.1.1.19.59). No matter what I do, I just can’t get rid of the FUZZY FONT that plagues the entire app; it’s not just the worksheet or query result panels!
Can you please help me fix this? It’s the only app on my system that renders like this and since I use this program every day, almost all day, this is quite hard on my aging eyes!
Thanks again for your work!
Randy
try this
Hello,
We are facing a weird issue and hope you can give some pointers on that.We have enabled sql profile for a particular sql and when I run it in SQL developer 4.0.3 it is not using that sql profile on the contrary we did see the profile was used in TOAD and sqlplus on the server.
Thanks
i think profiles are case/text sensitive – are you running it exactly as you are in SQL*Plus?
Also check the NLS parameters defined in preferences and make sure they match what you have going for your session in SQL*Plus
Thanks for your reply.Actually this is what I did ,I tried to change the name of the SQL profile from DEFAULT to TEST then did a ALTER SESSION SET SQLTUNE_CATEGORY = ‘TEST’; in SQL developer session after that when I run the query the sql_id does use the sql profile now but I am wondering why we need to do a alter session as it should be using the profile without that.
How do I use Query Builder in SQL Developer 4.1 to create an Update query in 1 table? When I first started to use the app a menu would appear when dragging a table to the Query builder. That menu no longer shows.
Thanks
Dave
not sure, to the best of my knowledge, we’ve only ever supported SELECT statements in the query builder
you could use it to build a select, then go to the worksheet and remove the select bit and add in an UPDATE or DELETE, keeping the WHERE
Hi
Have access to a oracle role[ not dba] which has access to some v$ views (inc v_$session)
however these do not work in sqldeveloper (cant see v$session but can look at v$session_longops which is granted to public). Does role access work in sqldeveloper ? Im keen to look at monitor sql tool.
‘monitor sql tool’ – requires more than just access to v$session
monitor sql under tools menu is Real Time SQL Monitoring, is that what you want, or do you mean the Sessions report under Tools menu?
thks for reply.
yes tools-> mon sql and monitor session.
sry the v$session is only a example, the role has access to +100 views v$sql sqlarea lock sort_usage process + some dba_ tables
but only longsops works which is ganted to public.
Hi,
I am using Oracle SQL Developer 4.0 tool. Though the DBA has given access to a specific user to run the Explain plan statements, I am not able to see the latest plan statements in the plan_table.
I run the Explain plan for <>
I get a message that Plan for succeeded.
Then I query the plan table, I see the old values which was there before i ran new query.
Pl help if any setting exists to refresh values.
Thanks
why run explain plan for when you can just use the Explain Plan and AutoTrace buttons in the sql worksheet toolbar?
what is your query, it’s probably missing a where clause.
you can also try ‘show plan’
Hi, I have a simple question how to display details of employees who are receiving salary more than ‘ALEN’ employee using emp table in scott i think it is self join
SELECT employee_id
, first_name
, last_name
, salary
FROM hr.employees
WHERE salary >
(SELECT salary FROM hr.employees WHERE employee_id = 187
)
ORDER BY salary DESC;
Assuming that ALEN is employee_id 187…this is just one way to do it.
I hope that I get an A on your homework.
Hey there… know you are quite busy this month so no rush on this. I am not seeing the bind variable output within SQL Developer but CAN see it on SQL Plus using the same script.
Wondering if it is a preference that needs to be set.
—- SQL Developer (using F5, RUN script)
SET serveroutput ON
VARIABLE b_basic_percent NUMBER
VARIABLE b_pf_percent NUMBER
DECLARE
v_today DATE:=SYSDATE;
v_tomorrow v_today%TYPE;
BEGIN
v_tomorrow:=v_today + 1;
DBMS_OUTPUT.PUT_LINE(‘Hello World’);
DBMS_OUTPUT.PUT_LINE (‘Today is : ‘|| v_today);
DBMS_OUTPUT.PUT_LINE (‘Tomorrow is : ‘||v_tomorrow);
:b_basic_percent:=45;
:b_pf_percent:=12;
END;
/
print b_basic_percent
print b_pf_percent
PL/SQL procedure successfully completed.
Hello World
Today is : 25-SEP-15
Tomorrow is : 26-SEP-15
B_BASIC_PERCENT
——
B_PF_PERCENT
——
—-SQL Plus (same script and same machine)
Connected.
SQL> SET serveroutput ON
SQL> VARIABLE b_basic_percent NUMBER
SQL> VARIABLE b_pf_percent NUMBER
SQL> DECLARE
2 v_today DATE:=SYSDATE;
3 v_tomorrow v_today%TYPE;
4 BEGIN
5 v_tomorrow:=v_today + 1;
6 DBMS_OUTPUT.PUT_LINE(‘Hello World’);
7 DBMS_OUTPUT.PUT_LINE (‘Today is : ‘|| v_today);
8 DBMS_OUTPUT.PUT_LINE (‘Tomorrow is : ‘||v_tomorrow);
9 :b_basic_percent:=45;
10 :b_pf_percent:=12;
11 END;
12 /
Hello World
Today is : 25-SEP-15
Tomorrow is : 26-SEP-15
PL/SQL procedure successfully completed.
SQL> print b_basic_percent;
B_BASIC_PERCENT
—————
45
SQL> print b_pf_percent;
B_PF_PERCENT
————
12
SQL>
Any clues?
~Thanks, Melody
Never too busy for you!
All I have to say at the moment though is that it’s working for me. What do you see on the script output tab after running this?
I am not saavy enough to be able to insert a screenshot here. Will do on FB.
I need some of your mojo!
Jeff,
Is there a way to transfer the sql developer data modelling project files to another computer? Can i just copy the projects folder , is that enough?
thanks a bunch!
Anne
I am using sql developer 4.1.1.19 and am unable to import ORDS 2.0.9 that was created with sqldeveloper 4.0.0.12.84
Jeff, is there a way …
Frequently, rather than building a query from scratch or using query builder, I will just click on the table or view and then click on the “Data” tab. Then I proceed to filter, sort and even hide columns. I get what I want quickly and easily. I would like, however, to see and have access to the query which produced that result set. I want to copy/paste it into a worksheet to make some additional tweaks and have it placed in my SQL history. Your thoughts?
hiding columns is done visually, not via SQL…filtering, depending on how you do it, is done client side.
So quick answer: that won’t work….
I don’t have a slow answer that will give you what you want, at least not today.
I guess this is a good spot for an enhancement request. Maybe a button or action item which lives under the “Data” tab to “Show SQL”. I just don’t see it as that big of a problem for SQL Dev to see which columns I have displayed rather than “*”, see my filters and sorts and then generate a SQL statement. When I first click on the “Data” tab, obviously the statement would be “Select * from My_Table_or_View”. As I filter, sort or otherwise modify the displayed result set (even if it is on the client-side) the dynamically generated “Show SQL” statement would be reflect the modifications.
right, but we’d be lying – b/c we do select * whether we’re showing the column or not. now if you wanted a context menu item, save display as query/report, that would dynamically build the SQL to give you what you see there, then that would be cool
Yes, you’ve got idea.
That would be cool.
If you are not doing anything this weekend, could you please develop that for us?
Thanks.
I am new to SQL Dev Data Modler and have an issue that may be me or the tool. I reverse-engineered an 11G database to version 4.1 data modeler. Views are validated, yet I cannot edit them or change column order. View Properties -> General -> Query Builder shows no columns or query. If I preview DDL, I see the current DDL. Where do I edit it?
Thanks Master Jeff
you need to validate the views – it’s more expensive than a RE, so we don’t do it by default
I did (at least the tool says I did) and said so in the original post. The display no longer showed warning icons. How else do I know they have not been validated? Still nothing.
sorry, didn’t see where you had validated, and I think i misspoke, you need to parse them, not validate them. Try the ‘parse older style views.’
I read that to, but that option does not appear on 4.1.0. Do I need to upgrade?
it says ‘parse sql’ in the newer versions
Thanks. Getting closer. The Parse SQL does nothing. Testing the Query, though gives me some insight. The view accesses through a DB Link. My user can execute the view just fine, but not the query directly. I get the 02019. 00000 – “connection description for remote database not found” from the tool and a SQL session.
Are SQL Developer reports compatible with Microsoft SQL Server? Even the simplest query won’t run. It works fine with report type table, but when changing to a chart I get an error that I think indicates that SQL Server did not like the query. It is like SQL Developer is changing the query when it runs a chart versus a table.
Here is a query:
select ‘ ‘, highland.dbo.arcnamp.ycompn, 14
from highland.dbo.arcnamp
I know the query doesn’t make sense purpose wise, but I wanted it to be as simple as possible because the error says “Incorrect syntax near the keyword ‘Select’, Vendor Code 156”.
I wish every database I worked with was Oracle, but sometimes I don’t have a choice.
I am also fine if your answer is that charts only work with Oracle. I just want to know for sure, I hate giving up unless I know for sure something just can’t work.
BTW it is because of your blogs and emails that I am an Oracle convert. Between SQL Developer and APEX who could resist Oracle.
Thanks for your help now and over the past couple of years in your writings.
Not really, so don’t beat yourself up anymore trying.
I wish every database you worked with was Oracle too!
trying to use git with ssh with keys
$> tail ~/.sqldeveloper/system4.1.0.19.07/o.jdeveloper.git.12.2.1.0.42.150416.1320/vcs.log
[20:34:17.758 CDT 23.09.15] git ls-remote –heads ssh://[email protected]/nfs/infsrv_shared/git-repo/test.git
[20:34:17.984 CDT 23.09.15] ssh://[email protected]/nfs/infsrv_shared/git-repo/test.git: java.lang.ArrayIndexOutOfBoundsException
from command line
$> git ls-remote –heads ssh://johmicd@ssh-1+banqualjss.its.bethel.edu/nfs/infsrv_shared/git-repo/banner_home.git
Pseudo-terminal will not be allocated because stdin is not a terminal.
Killed by signal 1.
f428c73526fdbe1a5e378e366c2a19ddef4fefa7 refs/heads/master
Any ideas of other logs to look for more info on.?
sorry my last example was not right it should have been
from command line
$> git ls-remote –heads ssh://[email protected]/nfs/infsrv_shared/git-repo/test.git
Pseudo-terminal will not be allocated because stdin is not a terminal.
Killed by signal 1.
f428c73526fdbe1a5e378e366c2a19ddef4fefa7 refs/heads/master
I have created a few relational models. I see some objects are automatically created under logical model. I tried to delete them. But when I save, close and open the model again I see those object under Logical model. I can create a confusion with other users that I am created a logical model too. Is there anyway I can delete the logical model?
Hi Jeff,
We are getting an error ‘ Unable to launch the JVM located at *\jdk\jre\bin\server\jvm.dll’.
Can you please help
-Punit
Hi,
Many consider the context diagram (data flow diagram – level 0) as the one that contains system(s) or application(s) along with external entities that interact with them.
I can’t see anything within the DFD tool to represent the system. Is the concept within Oracle a bit different and it assumes to start from what some others call DFD – level 1?
Thank you!
Hi Jeff,
I have SQL Developer 4.1 and when I first started dragging tables into Query Builder a window popped up asking what type of query to create. I’ve since lost this window. How do I get it back or how can I choose the query type?
Thanks, Dave
hmmm, you’ve lost the Query Builder window entirely?
The Query Builder window is still there. When I drag a table into it another window would open asking what type of query to create (e.g. insert, update, etc.) That window no longer opens. So how/where do I choose the type of query?
if > 1 table, it defaults to SELECT
DELETE, INSERT, UPDATE for > 1 table, doesn’t make sense
I want to update a field in Table1 with the contents of a field in Table2 where Table 1 and Table2 are joined.
yeah, don’t think our Query Builder supports that
How do I use Query Builder to create an Update query in 1 table?
I am using SQLDeveloper v4.0.3.16, I wanted to execute below command automatically as and when I login to a particular DB. Is it possible?
ALTER SESSION SET CURRENT_SCHEMA =;
Thanks
Bala
configure your login script
have it do something like this:
declarestatement varchar2(200) := 'ALTER SESSION SET CURRENT_SCHEMA= BASIC_PRIVS';
name varchar2(200);
begin
select ora_database_name into name from dual;
if name = 'ORCL' then
EXECUTE IMMEDIATE statement;
end if;
end;
/
Use a CASE statement to make the CURRENT_SCHEMA value dynamic based on the database you’re working with…
Thank you much for your quick response.
Jeff,
I just downloaded Data Modeler 4.1.1.888 onto my MacBook Pro, opened a model and cannot CMD-Click on anything. Can’t get contextual menus, or add new items in the Design browser tree.
Any thoughts?
Can’t find anyone here with the same issue.
Going back to 4.0.3.853 and everything works as advertised.
Thanks.
Sorry, to bother you with this simple task but I can not find a solution. I am having trouble finding an answer connecting “as sysdba” on the command line with sqlcl (SQLcl: Release 4.2.0.15.257.0801 RC). no matter what I try I can not get it to parse correctly.
sql sys as sysdba@orcl
SQLcl: Release 4.2.0.15.257.0801 RC on Fri Sep 18 11:47:45 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Password? (**********?)
Database? (”?)
USER = sys
URL = jdbc:oracle:thin:@localhost:1521/orcl
Error Message = IO Error: The Network Adapter could not establish the connection
Username? (RETRYING) (‘sys’?)
or
sql sys@orcl as sysdba
SQLcl: Release 4.2.0.15.257.0801 RC on Fri Sep 18 11:48:29 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Password? (**********?)
USER = sys
URL = jdbc:oracle:thin:@orcl
Error Message = IO Error: Unknown host specified
USER = sys
URL = jdbc:oracle:thin:@orcl:1521/orcl
Error Message = IO Error: Unknown host specified
Username? (RETRYING) (‘sys’?)
but it work if I do it interactive.
Username? (RETRYING) (‘sys’?) SYS as sysdba
Password? (RETRYING) (**********?) ***********
Database? (RETRYING) (‘orcl’?) orcl
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
as sysdba goes onto the end of the connect string, not the beginning
┌─[12:59:19]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLDev411.app/Contents/Resources/sqldeveloper/sqldeveloper/bin$└─>./sql sys@\localhost:1521/orcl as sysdba
SQLcl: Release 4.1.0 Release Candidate on Fri Sep 18 12:59:59 2015
Copyright (c) 1982, 2015, Oracle. All rights reserved.
Password? (**********?) ******
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select user from dual;
USER
------------------------------
SYS
SQL>
ok thank so much it is now working! don’t know why the second one was not all morning. I appreciate your helpfulness.
Hi,
I have SqlDevelopper installed in my Mac. However, I don’t know how to connect it to an Oracle database. Do I need to install VirtualBox? Is there any database that can be used in Mac without needin VirtualBox?
Thanks so much!
OIANE, I wrote THIS post, just for you.