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,788 Comments
Using Sql Developer 4.1.3.20 on Windows 7 enterprise. Completion Insight totally dead. Ctrl+spacebar brings up nothing. Saw your video. I’m connecting to database over Juniper Networks Pulse vpn. Can read / write all tables. But no code completion.
View > Log > Statements
type in an completely blank and connect SQL Worksheet:
select * from hr.+
In the Statements panel, do you see us querying the data dictionary looking for hr objects to populate the insight list? See anything wrong with it?
Jeff,
I’m using SQL Developer 4.0.0.13. The feature of double click a word to automatically search the text in code editor is not functioning now. How to get it back? Also I used to be able to highlight a word in the text editor. Then when I do a Ctrl+F, the highlighted text will appear in the “Text to Search For” box. Now it is not appearing there. How to fix it?
Thanks,
Dongqing
I dunno, that version is pretty old. We’ve had 4.0.1, 4.0.2, 4.0.3, 4.1, 4.1.1, 4.1.2, & 4.1.3. Here’s how it looks today – just as you expect.
Jeff,
Thanks for the quick reply. I had used 4.1 previously. But it crashed when the PC was forced to shut down. I’ve lost those Connection windows etc. Somehow I cann’t install a newer SQL developer version. So I copied those 4.1 settings to a 4.0 version and start to work on the 4.0 version. Maybe its time to re-install a newer 4.1 version with your help.
Dongqing
we don’t have an install, you just unzip the download and go
‘crashed when PC was forced to shut down’ – you mean Windows asked the app to close, and it wouldn’t? Probably b/c it asking to you confirm to save open code editors/files/worksheets. Enable ‘save all on shutdown’ preference to avoid that.
Love your product!
Can you startup or shutown the database with sqlCL.
examples
shutdown immediate
shutdown abort
startup
startup pfile=
ETC…
to replace sqlplus you need to go all the way.
Yes 🙂
Hello Jeff,
There is another tool used commonly for Oracle that has a “quick browse” feature on the Navigation tree (shortcut key F3). This actually opens a worksheet and pastes in a select from the table similar to what you get when you drag a table name to an open worksheet in SQL Developer. Sounds trivial but it is a great time saver when you need to quickly browse multiple tables and you get a worksheet opened, data returned and all the column names in a single key press. Is there any chance SQL Developer will ever offer a feature like this? Even a right click menu option on a table or view in the Navigation tree when Open Object on Single Click is turned off would be nice.
It sounds exactly like dragging and dropping the table to a worksheet – so why not just do that?
also, how is that different than just opening the table?
Thanks for responding. Like I said, maybe kind of trivial, but:
— With regard to the “open” feature, it is a single key press instead of right clicking, opening the table, and then clicking again on the Data tab to see output.
— Again, with dragging and dropping, it is a single key press instead of dragging, dropping, then clicking execute or pressing f9 to see data.
Also, two ways that could possibly gain similar efficiency:
1) Add a preference to allow the Data tab to show first when you click Open on a table or view from the Navigator plane; or
2) Add a preference to automatically open a new worksheet and execute when dragging and dropping from the Navigator pane.
How do you create a custom report showing Entity, Attributes and Relationships?
I have tried Entity and Attribute (which is indented) and the Identifiers — which remains at the same level as Attribute. But the example Entity two level has Attribute indented and Identifiers indented further
Also how to make each value be a column in the spreadsheet
Hi,
I am using ERwin Data Modeler Tool to generate the DDL for the schema. However, when I Forward Engineer the schema it generates a single DDL file. But I want to generate DDL file for each Table separately, resulting in one action into multiple files for each Table.
Any help will be greatly appreciated.
Thanks in advance,
Jason
Sure thing – stop using ERwin. Use SQL Developer. The Modeler has an option to create one file per object when generating DDL.
Hello Jeff,
I use “set sqlformat html” in sqlcl to format the output. The result looks good but I would like to modify the table, text, color, page tile,and add some parameters.
Is what I need possible ? I tried to edit the columns with SQL plus commands but it did not work.
Thank you
You’d have to write a js/perl/bash script to go change the file, after-the-fact.
Jeff, Thank you for replying. I’m no familiar with js or perl. I tried not to use “set format html” and used sqlplus liked commands to format columns, size, text. However, it did not work. Does sqlcl support those commands (and I did something wrong ) ? or a script to change the file, after the fact is my only option ?
thank you again
I’d have to see your code JD – we should be supporting everything SQL*Plus is doing.
This is what I had in my .sql file
——————————————–
set termout off
set pagesize 50000
set linesize 3000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “This is a test –
–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
Column feeder_id heading feeder
column tripsaver heading tripsaver
column new_time heading newtime
column new_time_local heading localtime
/* To name file */
spool TripSaverReport2.html
/* query here */
select yolobo.*, count(*) over (partition by tripsaver) as count
from
(SELECT distinct ts.feeder_id, ts.tripsaver, ts.new_time, from_tz(ts.new_time,’GMT’) at local as new_time_local …….. )
/* end of query */
spool off
host start TripSaverReport2.html
set termout on
———————————————
the result would like like this: [
feeder tripsaver newtime localtime COUNT ——————– ———- ——————————- ————————————– ———- HUTCH1503 359953873 06-OCT-15 08.43.49.000000000 AM 06-OCT-15 03.43.49.000000000 AM AMERIC 1 A/CHICAGO CRLTN1401 359955124 11-DEC-15 08.03.58.000000000 PM 11-DEC-15 02.03.58.000000000 PM AMERIC 1 A/CHICAGO EULSO9132 658873843 05-OCT-15 06.46.52.000000000 PM 05-OCT-15 01.46.52.000000000 PM AMERIC 2 A/CHICAGO EULSO9132 658873843 08-OCT-15 03.06.31.000000000 AM 07-OCT-15 10.06.31.000000000 PM AMERIC 2 A/CHICAGO HURST1882 658873846 19-OCT-15 03.18.40.000000000 PM 19-OCT-15 10.18.40.000000000 AM AMERIC 1 A/CHICAGO
]
thank you
My first reply got pasted incorrectly. This is what I had after “set markup”:
————————
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “This is a test –
–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
————–
thank you
got it
>>SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
We don’t support that…we have
select /*html*/ … OR
set sqlformat html
Jeff
Do you have any expertise/suggestions on where to look for Oracle BIEE installation issues? I am getting no where on the Oracle Forums
My Oracle Support.
The Forums are places for people of like mind to hang out, ask questions, share opinions. It’s not a support channel. I know of quite a few OBIEE experts that hang out on Twitter and have blogs. And of course, I’m assuming you’ve already Google’d the error messages you’re getting?
I accidentally closed my Browser and Navigator windows, and I cannot get them back! Clicking on Browser or Navigator in the View menu does nothing, nor do the keyboard shortcuts. How can I get my Browser and Navigator windows back? I’ve tried running a fresh copy of datamodeler.exe, but it keeps coming up in the same way (no Browser or Navigator). Sometimes, I get this error: SEVERE: Error during handleEvent on action ‘Browser’ (id=799)
I’m assuming you’re running the standalone modeler application. If so, i would rename the system directory where your preferences are stored. If you’re running the modeler inside of SQL Developer, try the Help > Reset Windows to Factory Settings menu item.
Thanks for the quick response … I’m running standalone. Where will I find the directory with the preferences?
On Windows:
C:\Users\$USER$\AppData\Roaming\Oracle SQL Developer Data Modeler\systemX.Y.Z
Awesome! That worked like a charm. Many thanks!
Hi Jeff,
is it possible to copy the content of a worksheet-command-area including markup as colors and fonts (e.g. “copy as html”).
Would be very helpfull for documentation purposes.
Thx in advance,
Dirk
It’s a standing enhancement request, but it’s not yet available as a feature.
I have many (000’s) of case sensitive named procedures in packages that the Run or Debug tool does not recognise. Is there a known work around.
Example of compiled package:
create or replace package test_pkg as
procedure “TestProc”(p_in in varchar2);
end test_pkg;
create or replace package body test_pkg as
procedure “TestProc”(p_in in varchar2)
is
begin
null;
end “TestProc”;
end test_pkg;
When trying to run the dialogue box shows:
Empty package TEST_PKG definition (no public members).
Hi Jeff,
Have you any thoughts or workarounds where Procedure names are enclosed in Double Quotes, i.e. case sensitive naming, on how to run/debug procedures?
thanks in advance
Liam
are you saying that doesn’t work? if not, it’s a bug. what version are you running?
Hi Jeff,
Yes, it doesn’t work. None of the procedure names appear in Run list, even after compile.
Running 4.1.3.20
Many thanks
Liam
I have just one table created but no records on it. Now I have to copy the column names . How to achieve this. Without any data, it is not working Ctrl + Shift + C in the output window
SQL Dev v4.1.3.20.78 not with jdk and using jdk in SQL DM v4.1.3-901
Run a Query
Export Results
v4.java.lang.reflect.InvocationTargetException
Switching to SQL Dev v4.1.1.19.59 does not give the error
Hi Jeff,
Why does synchronizing a unit test take so long (can take minutes) to display the package list? It can take upto 7 mins to synchronize a test.
Why is it slow and how can it be speeded up?
Thanks.
Could be stale data dictionary stats?
Open View > Log.
Observe the statements panel.
Find the query that’s causing the delay when the package list is populated.
It’s slow accessing the data dictionary. I understood it that the stats are auto updated for this.
>>that the stats are auto updated for this
from what i’ve seen, data dictionary stats are rarely auto-updated
stats on your data, often are
Hi Jeff,
I have installed both SQL Developer (JDK 8 included) and SQLcl (without Java).
– Could I use the ‘inline’ JDK 8 from SQL Developer for SQLcl too? And if so, what’s the easiest way to set it up?
– The sql.exe version from SQL Dev says product version 4.2.0.105.1800, the sql.exe version from SQLcl says 4.2.0.349.7.6, but both versions say file version 1.0.0.0. Whats the difference?
As always, thanks a lot for your help!!
Regards, Mark
>>Could I use the ‘inline’ JDK 8 from SQL Developer for SQLcl too?
Yeah, but it’s not very straightforward. Running SQLcl is just going to look for java on the machine’s path. If you can run java -version from a dos prompt, you’ll probably OK. If you don’t have Java on your machine AT ALL, then you can copy the jdk dir out of your sqldev install and copy it to a jdk directory under the SQLcl directory and then run it.
The SQLcl from standalone OTN download is newer than the one we shipped with 4.1.3 – both are beta, hence the 1.0.0.0 file properties.
Hi Jeff,
thanks a lot.
>>copy the jdk dir out of your sqldev install and copy it to a jdk >>directory under the SQLcl directory and then run it.
I tested this yesterday and found a bug, darn it. So for now you’ll need to have a Java 7 JRE or higher installed on your machine to run SQLcl.
Jeff,
could you help us how to configure Flashback data archive features for tables in data model? i searched around physical model (oracle database 12c) in SQL Developer DM – Version 4.1.1.887. but don’t find it. kindly help.
Jeff,
Started having a problem on a Windows 2008 R2 VM after running patch updates and upgrading to sql developer 4.1.3.20.78. Intermittent crashes, Sql Developer stops working and displays a generic “a problem caused sql developer to stop working” –I’m pretty sure this is a generic OS error message. Anyway, after MUCH searching, reinstalling, trying different version, etc., etc., the problem “appears” to be a missing entry in the path the the “perl” folder under Oracle_Home. Hope this helps someone else –cost me several hours.
Weird, we don’t use the Oracle Home or perl… We’re you using thick connections with that client?
Hi Jeff
Great blog! Lots of inspiration….
Question: In (4.1.3) Reports, PDF-Settings, only paper formats ‘Letter’ and ‘Legal’ are presented. Do you know how to add additional formats like ‘A4’? (I ‘m betting on a ‘secret’ XML setup file somewhere in .sqldeveloper….)
Thanks
Tino
Dear Jeff,
I work on a few ‘Editor-User Defined Extension’ for the PL/SQL Editor (PlSqlBaseNode).
And I came across the following ‘interesting’ behavior.
Version: SQLDev 4.1.3.20 (20.78) on Windows7 64bit, Java 1.8.0_45
.) Data-Dictionary Views USER_* ALL_*
I started with your example where you count the number of source code lines
SELECT count(1) num from all_source …
I may have missed this in your explanation, but if I change all_source to user_source it just doesn’t work.
In the Log/Logging Page window the following errors are shown (sequence and elapsed not shown):
Level: SEVERE
source: oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1
message: null at java.util.StringTokenizer.(StringTokenizer.java:199)
If you try it again (which of course doesn’t make sense) the error is shown again, but sometimes the following message appears:
message: null at (Internal error) No Throwable Stack Element
(same level and source)
I’ve tried it with a SQL on USER_/ALL_identifiers, the behavior is reproducible.
Interesting to note, if you use all_*, and check the Log/Statements window SQLDev will replace all_* with DBA_*.
There are 2 statements, first SQLDev tries if it can access dba_*, and than ‘rewrites’ the statement:
1) select 1 from dba_source where 1=2
2) SELECT count(1) num from dba_source where …
My DB-User has access to dba_source.
Question:
-) I may have missed this in the examples/tutorials but is it the expected behavior to use ALL_-Dictionary Views and not USER_*?
-) Why the search/switch to DBA_* Views?
.) ‘Popup Describe (Shift-F4)’ behavior change as soon as you define an User Defined Extension
If there are no Extensions defined, and – in a SQL Worksheet – you put your cursor on a PL/SQL-name and hit Shift-F4 a new PL/SQL-Editor-Tab would appear.
So maybe in the background SQLDev really use ‘Open Declaration’ as some kind of default-fallback-scenario?
But as soon as an Extension is defined Shift-F4 would show a new ‘Describe-Windows’, but the only Tabs shown there are the ones from the Extensions.
So this maybe some kind of correct behavior, because if you select a table-name and hit Shift-F4 the ‘Describe-Window’ is shown.
I’m just curious, why an User-Extension would ‘break’ or change the ‘Popup Describe’ behavior?
But it’s not a big deal, just assigned a new shortcut to ‘Open Declaration’.
Thank you in advance for your time and help.
kind regards,
Tom
What’s the deal with the Oracle SQL Developer Exchange?
https://apex.oracle.com/pls/apex/f?p=43135:1
How do I download and use the snippets and reports that I see there?
It looks like that site is a bit old and perhaps not being actively maintained.
Snippets – just copy & paste the code.
Reports – click “Generate XML” and it generates the report file to import – although I haven’t worked out how to actually import it into SQL Developer yet. The “Open Report” function doesn’t seem to work. You could just extract the SQL out of the XML file and load that into SQL Developer.
Looks like you got just about as far as I did. 🙂
Manually re-creating the reports seems to be the only option.
For the wayward Googler:
Looks like the oldest public reports (the ones on the main page) are actually okay, they just need a small adjustment:
{ORIGINAL XML HERE}
Newer reports are okay as is.
The site is basically abandoned though. No new reports since 2010
XML I pasted was stripped out of the comment.
Essentiall, surround the original XML with 3 additional lines of XML.
Use an existing report for comparison.
Hi Jeff,
I have a small issue with SQL Developer installed on OS X and used to access SQL Server. When I try to set a DB as default an error message is returned: incorrect syntax near ‘-‘ Vendor code: 102.
I manage to figure out that this issue is caused by the DB name format which is like DDD_123-234 (I can not rename the DB because is a production one and this should involve a lot of work)
Message log is looking like this:
Error starting at line: 1 in command –
/*sqldev:stmt*/USE DDD_123-234
Error report –
SQL error: database ‘DDD_123′ does not exist
Is there a set up option in which the SQL Developer will not check DB schema name for “special characters” or at least will ignore the issue or
I can redefine the command for set default database to look like this: /*sqldev:stmt*/USE [DDD_123-234], for good?
PS: SQL Developer version is 4.1.3.20, JDK 8 Update 74 and JTdS driver 3.1
Best regards,
Catalin
Jeff,
I am using Oracle RDS instance on Amazon web service and ORACLE SQL DEVELOPER – 4.0.3.16. to connect RDS Instance.
In my oracle schema i have SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12% tables. All these tables are temp tables.
I didn’t do any third party migration. I just used third party jdbc driver (jtds-1.2) in ORACLE SQL Developer to connect SQL Server.
I just want to know, why these tables are created in my schema when i didn’t migrate data from SQL Server and why they are temp tables. Most of the tables are empty.
If i export my schema, the export log file didn’t show the tables mentioned above. But, after import the schema shows all tables including the temp tables (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%).
Does it impact my oracle schema if i delete temp tables(SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%)?
Jeff,
We have an application which runs batch jobs. This application uses DB2 load utility to load the .DAT files into DB2 Database. Now, we need to convert them to Oracle Loader scripts so that we load .DAT files to Oracle Database. Could you please suggest on how to do this and what tools I would need to test this ?
If a DB2 .dat file has a delimited for fixed width data format, then yes. If the .DAT files are proprietary/specific to DB2 softwares, then ‘no.’
Thank You Jeff.
So, if yes, what is the next step in converting DB2 load scripts to Oracle Load scripts ?
so if they are delimited files, you can use SQL Developer to create EXTERNAL TABLES or SQL*Loader scenarios to load the data
Thank You, Jeff.
I did go through that article before. However, my query is how to convert those DB2 load scripts to Oracle load scripts. I have the schema created in Oracle. I have the SQL developer installed and connected to this schema.
At IBM site, I see that there is a perl script to convert Oracle load scripts to DB2 Load scripts. However, I need the other way.
we don’t have anything like that that I can tell
we can connect to DB2 and move your data over using SQL Developer Migrations or GoldenGate
if your load script have delimited data in them, we can setup loader scripts for them
Jeff,
I’m trying to import data to a table. When I select the .DAT file, I’m getting an error saying “cannot be opened due to the following error: null”.
And, I see that file Format fields are not editable. Could we change File format parameters ? Oracle does not support DAT files import ?
I am trying to create new connections on Oracle-SQL developer version 4.1.3.20. My username includes windows domain for e.g. ABC\username. The connection gives invalid username/ password. The username is exactly the way it is on the Oracle DB. I also tried just using OS authentication without specifying username/password. Do you have any suggestions?
Thank you
Hi Jeff,
I was wondering if there is a way to apply a filter to a table/view prior to going to the data tab? We have some views that run forever if you don’t have some limiting condition applied to them.
Thank-you,
Barry
Sorry, no. You could create reports (client side) or views (database side) to make browsing these tables less painful though.