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’ve read some just right stuff here. Certainly value
bookmarking for revisiting. I wonder how so much attempt you set to
create the sort of wonderful informative website.
Hi Jeff,
First of all, congrats for the SQLDeveloper and Data Modeling. Great tools :).
The question/problem. I’m using SQLDeveloper for sometime and there is a thing that drives me crazy…when i use the CTRL-R (a.k.a. SQL Formatter) to format the code, when the cursor go to the last line? Why does not stay in the line where i’m in?
When you got 2 to 3 queries…it’s annoying but ok…but when you are editing a 100000 lines package … it’s possible to dibable this? 🙂
Thanks in advance
One more time, great job 🙂
Hi Jeff, Is it possible to maintain a Design in DataModeler that has been created via importing from Designer as well as partly reversed engineered from a database implementation? I had to do it this way, because our Designer-model was quit out-of-date, however lots of valuable information in the LDM we didn’t want to loose. Now, I am trying to get a proper mapping between the LDM and RDMs, but I have problems mainly on entitities with hierarchical structures in it. They seem to be mapped wrongly. Do you have some tips for me? Thanks, Karina
Hi Jeff
is it best to use Subversion on Datamodeller or with Developer..
I am trying to setup APEX apps to be put into subversion.. but struggling to see how subversion even works with SQL Developer
Dean
if you’re just going to use SVN to control your APEX applications, stand up an official SVN server and get a SVN client like TortoiseSVN…I imagine you’re not coding against APEX app ‘files’ directly in SQL Developer? If you are, then you could use SQLDev’s SVN interface I suppose.
I am running SQL*Developer, version 4.0.3.16, and have several issues with Find/Replace.
Issue 4: The default setting for “Selected Te_x_t Only” in the Find/Replace dialog is unschecked. I am pretty sure that in ver 3 it defaulted to checked when I had a selection active before pressed F3. Normally, SQL*Developer uses safe defaults, but this choice is disastrous because it is easy to accidentaly change every instance of your text (rather than only those instances in a selection) by accident.
It seems to me that the presence of a selection should automatically cause this option to be activated. At the very least, there should be an preference to change this behaviour.
Can anyone help make this friendlier?
What happens if you have 1 letter selected, one word? Two words?
In v4.1, selected text defaults as the search string.
I think this will be more common than defining the search field. You can always toggle on the selected text only search button – it is much more prominent in version 4.1 at least.
No matter how much, or how little, text is selected, the “Selected Te_x_t Only” box is unchecked when you press F4.
I am running SQL*Developer, version 4.0.3.16, and have several issues with Find/Replace.
Issue 3: The range of lines searched seems to have changed since ver 3. Now, when I select text and search forward, I might decide that I need to see instances of that text which occurred earlier in the file. Yet, when I change the direction of the search using Shift-F3, the search will not go back to the original text, nor will it search anywhere in the file before the line that contained the original text.
To demonstrate this for yourself, use the following lines:
one one
two one
three one
four one
five one
First, highlight the “one” after “three” and press Ctrl+F. Select Direction:_F_orward (also Highlight All _O_ccurrences, if you like).
Click on the “OK” button… you will go to the “one” next to the “four”.
Press F3 … you will go to the “one” next to the “five”.
If you had checked the highlighting option, you should observe that only three instances of “one” are highlighted — those on the lines with “three” through “five”.
Now press Shift-F3… you will go to the “one” next to the “four”.
Now press Shift-F3 again… you will hear an error chime and go nowhere.
IN SQL*DEVELOPER 3, YOU WOULD HAVE GONE TO THE PREVIOUS INSTANCE OF “one”, POSSIBLY ALL THE WAY UP TO THE TOP!
Note that the behaviour is slightly different if you position your cursor before the “one” on the line with “three” and then press F4, in that you can get back to the original “one”, but not above it.
I can’t find a preference to govern this. I desparately want to go back to the ver 3 behaviour, because I am missing things, thinking they are not there, when they are!
BTW, this misbehaviour is symmetrical, in that if you perform a search upwards, you cannot search below your starting point.
I am running SQL*Developer, version 4.0.3.16. And have several issues with Find/Replace.
Issue 2: The block of code highlighted when I search with a regular expression that starts with ^ and ends with $ (the customary beginning-of-line and end-of-line metacharacters) spans multiple lines and may begin in the middle of one and end in the middle of another.
What is the correct way to limit a regular expression match to a code line’s boundaries?
I am running SQL*Developer, version 4.0.3.16. And have several issues with Find/Replace. I’ll post them individually, so as to create clearer threads.
Issue 1: I cannot find a section of the User’s Guide which discusses how the Find/Replace dialogue is supposed to work. Can anyone provide a link to this information? Who knows, it might answer all my other questions.
Appreciate your feedback Bob, but this isn’t really a ‘question’ best suited for my blog. I would direct you to the Forums, but be aware that Find/Replace is being retooled for v4.1. We’ll have a new EA for folks to test shortly. Check back here for when EA2 is available, and try your scenarios there.
Are you saying that there is currently no documentation on how to use Find/Replace?
Hi Jeff,
I’m running SQL Developer 4.0.2.15 (b 15.21). SQL Developer is able to show whitespace characters in the SQL Worksheet. Is there any way to also show whitespace characters in the Query Result tabs?
Thanks for a great site.
Kyle
I have tried on several versions of SQL developer reports to get the drill down to work. I can find very little information online. Mostly do these 3 steps and bang it works. Well not for me. It appears to not pass the bind variable to the drill down no matter what I do. If I leave a default in there it will always pull the default but never the correct variable. Child reports works fine. Any ideas or places to get trouble shooting info. Or just simply tell me it really doesn’t work.
There was a bug where it wouldn’t pass the bind down from a chart. What version are you on? It was fixed in v4.0.3.
I tried 4.0 and went back and tried it on 3.0. Neither would work.
It’s where you get the results from the first report. Click the drop down a result window for the drill down report and select it. The value is supposed to be passed to the drill down but does not appear to do so.
ah, a drill down report, so not a child report, but opening a new report and passing a value over…
So, in version 4.0.3 – again, what EXACT version are you running, I can do this no problem.
I simply have a :BIND reference in the drill down report main query.
So I references hr.departments.department_id by saying :DEPARTMENT_ID in my WHERE clause for the drill down report, and it passes perfectly.
I need more details from you to help you any further.
I am now on 4.3. I created 2 reports with a bind variables. The first is select Customer information. The second is Asset information. The AssetID is returned in the first report. The second report is set up to use AssetID as the input from the first report but does not accept it. If I put in a default in the second report it returns the default information everytime. If I leave the default NULL it returns nothing in the drill down. Even if the AssetID is present in the main report.
Please show me your queries for both reports, or just the SELECT bits.
Report1:
Select * from DAL.CUSTOMER_CASE where CUSTOMER_CASE_ID = :CUSTOMER_CASE_ID (returns ASSET_ID in results)
Report 2:
SELECT DAL.ASSET.ASSET_ID,
DAL.ASSET.ASSET_STATUS_CODE,
DAL.ASSET.CLIENT_ACCOUNT_ID,
DAL.ASSET.PARENT_ASSET_ID
FROM DAL.ASSET
WHERE DAL.ASSET.ASSET_ID = :ASSET_ID;
So…is ASSET_ID a column in DAL.CUSTOMER_CASE?
Yes ASSET_ID is a column in the results from CUSTOMER_CASE.
I can’t be sure that that is a column in the table, i have to know that that is part of the SELECT and available to send to the drill down.
As a test create a report like select first_name, department_id from hr.employees, and then send :DEPARTMENT_ID as a bind to a linked report to get data from HR.DEPARTMENTS
Okay, I see what the issue is. The report results have to be in a Horizontal format. It will not work it the first report is in a Vertical format. Thanks for your time.
Hi Jeff, great site, thanks for all you do.
I’m running sqldeveloper 4.0.3.16.84 with JDK 1.7.0_65, and am getting the following error in the Log window when I attempt to start a Remote Debug session from the connection window.
SEVERE 116 0 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 com/sun/jdi/Bootstrap
There’s a dirth of info about this particular issue out there. Any idea what I’m doing wrong?
Best,
Mehr
Did you happen to stumble upon this?
Yes – that was one of a number of links that I ran across in a search for others having this issue. The closest link I could find that addresses a similar issue is here: https://community.oracle.com/thread/1040348
The error I listed in my original post shows up in place of any remote debugger window popup, down in the ‘Logging Page’ window. A restart of the program and a reboot of the entire machine has no positive effect.
-M
Jeff,
I am using DECODE in my select statement (shown below). Specifically where s.saleval=’1′, I would also like to include a condition that column s.mktval=’0′. In other words, where s.saleval=’1′ AND s.mktval=’0′, label as ‘Green’.
Is it possible to use two conditions in a DECODE? And, if not, is there another way to achieve this?
Thank you,
Doug
decode(s.saleval,’0′,’Green’,
‘1’,’Green’,
‘5’,’Orange’,
‘Red’ )
I have a hard time answering all of the SQL Developer questions I get here, I can’t take on SQL ones too. But, instead of DECODE, check out CASE.
CASE worked. Thank you!
CASE WHEN s.saleval=’0′ THEN ‘Green’
WHEN s.saleval=’1′ and s.mktvalid=’0′ THEN ‘Green’
WHEN s.saleval=’5′ THEN ‘Orange’
ELSE ‘Red’
end “Dot”,
Hi Jeff,
I have several Child Reports that I’ve created for a frequently used report. I have added these Child Reports at various times over the past month. I would like to reorder these so that the Child Reports are grouped more logically. Is it possible to reorder these Child Reports or is the only way of doing so deleting / recreating each Child Report in the desired order?
Thanks.
Save the report – it will go to an XML file. Open that and re-order the child report XML sections as desired. Then ‘open’ the report (referencing the XML file) back into SQL Developer.
Hi Jeff,
Error parsing ERwin 7.x File when I tried to IMPORT repository format xml file(relatively large file 1.7GB) in SQl Data Modeller
I have modified the C:\Users\\AppData\Roaming\datamodeler64\4.1.0.866\product.conf file & datamodeler64.conf config file for 5 GB in the line AddVMOption -Xmx768M but still I am facing parsing Error .
I also noted that in the task manager SQL Data modeller is not using memory greater than 2.9 GB , it throws parsing error at this point. Any thing else can i change to increase the memory usage ??
I am on ver 4.1 and I don’t see Snippets as an option to view. How can I see the code snippets I created on older version 4.0.3 in 4.1.
Something is wrong, try Window > Reset to Factory
Then does View > Snippets appear?
Great… that did it.
Is it possible to provide a feature that pops up snippet edit dialog when I double click in snippet column with in “Edit snippets dialog”. Right now it goes into inline edit mode, which is useful for other columns but not for snippet text.
Thanks.
I made some other nice mods to the “Monitor Sessions”, and would like to send those to you. What is the best way to get that to you?
One of the mods is a child report which runs SQL Monitor for the selected session.
you can email it to me at [email protected]
Hi Jeff
When I use SQL tuning advisor in sql developer it gets times out in 2 mins. When I run sql tuning advisor in OEM, I am able to change the timing to 60 mins. How can I do that in sql developer.
Thanks
Do you really want to tie up your IDE for an hour?
We run this:
DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => ‘select * from dba_data_files’,
user_name => ‘HR’,
bind_list => null,
scope => ‘COMPREHENSIVE’,
time_limit => 1800,
task_name => ‘staName12734’);
END;
I think that’s 1800 seconds, which is more than 2 minutes…by a lot. What version are you running?
I am on 4.0.2.15 of sql developer. Thanks
Jeff,
Is there any way to run saved Reports in an Unshared Worksheet. We have reports which are long running that we want to run in an Unshared Worksheet (another session). Unfortunately the connection dropdown you get when running a Report does not include the Unshared Worksheets/connections in the list.
Thank you,
Terry Baker
no, the unshared connections are dedicated to those new worksheets
So are you wanting to run other things in a worksheet, or you want to be able to run other reports or click around the tree/object editors?
Run multiple reports concurrently against the same database. So, they would need to be separate connections. We could create connections for the same database like, dbm-a, dbm-b, dbm-c (the database being dbm), then open a connection for each of those and then run 3 reports each in the different dbm-x connection. That just seems more work and will clutter the connections list. It would be nice to be able to select and unshared worksheet connection to run a report in.
Yeah, that’s the only solution I know of. Remember, these are more of the ad-hoc variety. We’re not a enterprise reporting solution…
When will SQL Developer Data Modeler 4.1 be officially released (not beta)?
Hi Jeff,
I downloaded SQL Developer 4.1.0.17, a new EA version, it seems to work fine except that when I try to view the table I got the below error:
“An error was encountered performing the request operation: ORA-01460: unimplemented or unreasonable conversion request.
…
…
Vendor code 1460”
I did enable the option “Include Synonyms” as someone suggested it online. It worked for a day and now the error come back. I even delete the folder, re-downloaded and re-extracted to a brand new folder but it does not help.
Do you have any idea on how to fix it? Please adviseT
Thanks.
what version oracle database are you working with?
Hi,
I’m too get message “ORA-01460: unimplemented or unreasonable conversion requested”, when I try unfold DB objects tree.
This error apper only in SqlDev 4.1.0.19, when connect to oracle DB 9.2.0.8, while with 10 and 11 it works fine.
How I can fix this error?
Thanks.
PS: Combination SqlDev 4.0.3.16 / DB 9.2.0.8 works fine too..
Hi Jeff,
I saw you were a TOAD user, Can you tell me when you changed your mind to become a SQL Developer one ? I have got a comparison between the two to make and I’m not a database developer ;-(
Thx a lot for your help!
I changed my mind when Oracle asked if I wanted to be the Product Manager for SQL Developer.
As a Quest employee for 10 years, I had quite enjoyed working with the product and our customers. But I have always wanted to work with Oracle – or at since being exposed to the Database product while I was in college.
Having the ability to so directly impact the daily lives of so so many users was what ultimately led me to ‘switch.’ So now instead of helping just the T*** users, I can now reach every single one of our Oracle Database customers.
This won’t help you with your task, but that’s my personal answer.
I can tell you this – I’ve yet to find a customer who wasn’t able to make the switch based on ‘missing’ features. It comes down to is the cost of having your users go through the learning curve of picking up a new tool outweighed by the amount of $$$ you will save by not having to pay for Oracle tools.
thank you for your directness ! It’s now clear for me.
Sophie
Hi,
I’m trying to reverse enigineer Oracle (12c) Spatial tables into a SDDM datamodel (version 4.0.3). The reverse engineering process misses the spatial index completely. After adding the spatial index to the model manually, the index creation DDL is shown in the DDL preview, but is not included when synching the data dictionary with the model.
Do I overlook something of is this functionality not implemented ?
Richard.
Hi Jeff! Thanks for the great information that you have on your site; it has made me much more efficient in my day to day tasks.
One nagging question that I have in SQL Developer is related to creating the DDL for code migration. Often the objects have dependencies and they need to be exported in a certain order. I am currently using SQL Dev 4.0.3 and I use the Tools>Database Export>Export DDL to export all my objects that I need to migration from one instance to another. What I’ve noticed is that the objects do not export in the order that I list them on the Specify Objects page. Mostly I see the issue when I mix object types in one export such as exporting procedures and packages.
Do you have any suggestions for me related to this scenario?
We order it as best we can. Have you tried the CART? The object creation order has been optimized there to pretty much ALWAYS work as we use it to upload/migrate objects to ‘The Cloud’
Hi Jeff,
a qestion about reverse engineering: Importing a table from data dictionary into relational model and further into logical one, all (column) attributes are logical types.
How can I achieve that all column-attributes will take the appropriate domain if the type exists within the current domain list and if it matched.
With other words: reverse engineering seems to destroy the connection of the domain type, always the logical type will be preferred.
is there or was there, ever a connection? i don’t know if we recognize types of similar definition to something in a domain when doing a reverse engineer…and I’m not sure the domain would carry over from a relational to a logical since the datatypes are different between the two.
Might be a better question for the Forums.
Hi jeff, I have a question about debbuger function. If a like to debug a function or procedure inside of the main procedure, I have to set a breakpoint to the first line of that inner function or procedure? When I press Step Into only execute the function, but don´t get inside
Thanks
I’m able to step into a nested procedure…on the nested procedure call, I hit ‘step into’ and it immediately goes up to the nested procedure block for execution-step-debugging. Did you compile the procedure for debug?
Thanks that’s the perfect answer, I made the test and it works!!!!