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,555 Comments
I recently wrote a view that included the Interval keyword and discovered that this makes the Query Builder fall down!
SELECT SYSDATE + INTERVAL ‘1’ DAY AS Tomorrow FROM DUAL
The query runs just fine and gives the result you expect, but switching to Query Builder gives a parsing error message: “Invalid SELECT statement. Unexpected token “‘1′” at line 1, pos 27. Query Builder disabled.”
Is this just too complicated for the Query Builder to handle or is my syntax really invalid?
It’s not you and your query, it’s the Query Builder
Hi Jeff
Having so far failed to convert anything, I am trying to simplify things. So I am now trying to convert a very small sql database (it has 1 table in it, with 3 columns, and 2 rows).
Capture and convert claim to have worked, it has created several script files (although I haven’t run these), it has also created a log file that contains the following
Error starting at line : 7 in command –
@@C:\temp\Migration Test\books_test\generated\2014-06-17_08-16-25\master.sql
Error report –
Unable to open file: “C:\temp\Migration.sql”
is this significant (usually inclined to think that all errors are)?
Are the tables listed under ‘Converted Database Objects’ real tables or just definitions?
If they are real, how do I get data into them (I’m trying this online, although all tutorials show it being done offline)?
If they are not real, where does it put the real tables?
Thanks for any help, was thinking that such a small test should have been simple.
Paul
Real or just definitions – well, connect to your target Oracle database – do you see the table?
That answer presuposses that I know what is the target database. The connection detailed in step 8 (Target Database) of the migration wizard as seen using SQL Developer doesn’t show the tables.
Yes, you should know what database you want to migrate your tables to.
Have you seen this OLL Hands On Lab? It walks you step-by-step on how to do a migration. The example if for Sybase, but the steps are identical for SQL Server.
I haven’t, the ones I’ve seen were for 11g, wanted you to create a user called MWREP (which I couldn’t create as it didn’t start with c##), and didn’t have the right privileges.
I’ll try going through this one, thanks
Hi Jeff
I haven’t even got pass the first section and I have found 2 issues. The first is that it is talking about doing an offline capture (like every other conversion I have seen) but doesn’t tell how to create the files. The second is that is talking about unix and I know nothing about unix.
It says open the terminal, is this the same as a dos window? and commands can’t start with a full stop
We could keep going – but do you really want to trade blog comments for the next 2 weeks?
Instead, let’s do this. Screenshot/detail every single thing you’ve done, put it in a word doc, and email it to me.
So start over, try again, and document EVERYTHING.
That would be a good idea, if I had your email address (can’t seem to find it on this site). You have mine so if you email me I can send you the first roadblock.
Thanks
[email protected]
Everything you need to know – logs/errors/etc should be detailed in your migration project panel.
I would go back to the very beginning and check the prereqs – does your MSSQL user have the proper privs? What about your Oracle user? You successfully created a migration repository?
The migration panel tells me nothing. It shows the captured database objects, and converted database objects, but it’s doesn’t have any logs/errors etc.
The migration repository has the migrlog table in it, which is usually full of information but in this case just has the 2 rows
Catalog BookShop, Schema dbo coalesced to single schema BookShop
Convert completed in 1 seconds
and the target status tab only lists 2 objects, both of which have a status of missing
Hi Jeff
I have an issue where importing any more tables into the data model will execute but freeze the application. This is what happens I select the objects to import, this launches the progress bar, the progress bar continues till completion, at this point the cpu utilization goes up and the app does not respond. I do not see any imported tables and have to manually kill the app. I am using it with Oracle 11.2.0.3.0 and windows 7 Pro. The memory utilization remains steady throughout. Please help
Thanks
Isingh
Even if you try to add just a single table, this happens?
Yes, I just tried to import a very small table from the db. It did make it to the merge window and barely did a merge over two minutes. The application is really slow and any clicks will shoot the cpu to max out. I only loaded the relational model for the db and the physical model was not loaded before the operation. This is the only model currently open in the SDDM. I have more than 1500 tables in the db and am currently stuck at around 1100 imported into the model.
Ok, that’s a ‘big’ model and you’re exhausting the amount of memory available in the JVM. You need to find the product.conf file and edit it to give SQL Dev say 2GB of RAM.
Thanks That helped. I just upgraded the defaults to 4 GB no issues now.
And thanks to you – realized I didn’t have a post on how to do this. Read today’s post and let me know if I left anything out.
Okay 🙁
I have tried that link Jeff, but that links helps to add the columns to all the entities(tables) which is present.. But I want to update certain tables only using different columns.. The table name and column name differs from one another..
There is no common columns nor tables..
Thanks in Advance,
Niharika
Then you’ll need to add each column to each table manually – just as you would when doing your design from scratch.
Hi Jeff,
As per your suggestion I have did export excel option where I’m able to rename and modify globally.
But How to remove and add few attributes from few entities globally?
you can use table templates and transformation scripts to add/remove columns to multiple tables. @krisrice talks about it here a little bit
Or look at the Table Template – uses column name custom transformation script
Step by step instructions here actually
One of my comment was eaten … sql was actually following, since I used angle brackets i guess
{yyyymmddhh24miss}.{dbconnection}.{dbname}.{schema}.{object_type}.{“object_name”}.sql
note that object_name can be case sensitive inside the file
with the { } – they are just for emphasis
thanks!
/S/
Hi Jeff,
few suggestions, if they are not already covered eariler –
(1) commit or rollback via checkmark,anticlock-arrow
Can this be logged in the status bar with exact time and if in “Script output” tab with similar message?
And of course it will be nice to gray out the icon’s untill some change happens again
Scope – if I have multiple tabs open, the commit/rollback scope is the window that is selected. So do you track the icon’s in context of active window? I am sure you do, just checking..
(2) DDL generation:
If selected seperate file option – it will be very helpful if file name has following convention to get rock-solid identification
……sql
This will allow one to no scratch head to understand where this came from … I have
(3) Can commit & rollback be ketp far apart! I mean first they are tiny
and second a slight shake of hand and boom you are toast – plus no real purpose served in keeping them next to each other – we know commit or rollback – just buttons are too close
Thanks for keeping product super current!
/S/
Hi Jeff
I have now got SQL Developer trying to convert our MSSQL Database and have 2 questions.
Firstly it doesn’t seem to attempt to convert the CLR Stored Procedures (we’ve got 12 of them), is there a migration path for these, or will I have to rewrite them?
Secondly when trying to move the data I get many (770) ‘Failed to disable contraints: Data Move’ errors followed by some (78) ‘maximum open cursors exceeded’ errors. Do I have to convert the tables one at a time (is this possible), or have I missed a setting?
Thanks
Paul
We don’t do CLRs, just straight up T-SQL.
Something is wrong with the data move, obviously 🙂
Convert the tables – convert and migrate are two separate steps. Have the new tables been built in Oracle yet?
If you can post your question to our Migrations forum, I can get one of our engineers to take a look at your logs.
Thanks, I’ve done that, but no answer yet.
yeah, don’t use SYSTEM. I can ask a developer to take a look at the post, but they will probably have more questions as I don’t see many details in your post.
Questions are good. Don’t know enough to know what is relevant
Question:
Is there a method to find specific code text within a schema via a search?
For example, search for all ‘Update tblPerson where person_id = ‘ I would like to know all package bodies, functions, and procedures where that code text is ound
I want to be able to export multiple Query Result Tabs into one Excel spreadsheet with corresponding tabs. I don’t know if this is possible in SQL Developer. I am on version 4.0.0.13
Not today.
O.K. I’ll do that. Thank you.
C
Hi Jeff,
I have just started using version 4.0.2.15, Build 15.21.
When I RMC on a table (for example) I can select Edit but then I get a popup saying I have insufficient privileges. I don’t and can do a direct edit via a worksheet.
Also in Scheduler->Job, when I RMC on a job the context menu was disabled. Now it is back. I may have closed and reopened but not sure.
Christine
For a table or a view? For a table, this works fine for me.
For a view, I’m able to reproduce and this is a known bug.
It is happening to me for a table.
It is also happening to me for an MV.
Both of which I can edit in a worksheet/SQLPlus.
C
Fair enough. Exactly what privs do you have then?
I am connecting via a proxy user and have all privileges granted via roles.
C
That changes things a bit, the proxy user detail that is. I’ll need to know which privs are granted, explicitly or via roles for that setup still.
You might want to open an SR with MOS as this is starting to go beyond a normal Q&A on a blog site though 😉
Hi Jeff,
Am trying to find shortform words in sql developer where its available in TOAD.
eg.
In TOAD, when we type ss it ll produce SELECT
2. ff it ll produce FROM.
Do we ve this type of settings in SQL DEVOLPER ?
You can get close, see this post.
Ty Jeff. Got the solution 🙂
Hi, Jeff!
I fear I’ve goofed up with versioning. I ended up with having specified
an invalid GIT repository. How can I reset the “Team” menu to it’s original
contents so that I can start again? Is there some file I need to edit?
What is the recommended procedure here?
SqlDeveloper version is 4.0.2.15.21
All the best
Michael
Hi Jeff,
I have just started using SQL Developer 4.0.2.15. How do I open a package body.
When i use ctrl+click the package spec opens and there is a “Open Body” button, but it is greyed out. Can you please help me in making the settings so that the button is accessible.
Regards
Anoop Dangui
2 things:
Otherwise this works for me. As a test, you could try this for sys.dbms_output – does the toggle button work there?
Hi, I’ve been using SQLDeveloper for a few months now (after using TOAD for years 😉 ) and I have this annoying thing happen randomly. I launch SQLDeveloper and it hangs while launching.
I’ve done a lot of research on this, and I haven’t found any great solutions. Initially, I would download and re-install SQLDeveloper, but that got old.
I poked around the internet and found that I should check to make sure it’s trying to connect to the correct jdk. I had to clean up my classpath, I made sure the jdk was enabled from control panel, and I also make sure that the product.conf file has the right jdk
The last two times, my solution has been to make a copy of the “SQL Developer” folder, delete the original “SQL Developer” folder from the %appdata% area and relaunch SQLDeveloper. I then go back and copy in the connections.xml from the copy into the new so I at least my connections defined. But this is also just basically re-installing SQLDeveloper, and any other settings I had aren’t carried over. Any ideas as to what else I should be checking/doing? Thanks!
For starters – so sorry you’ve been struggling with this for months. Not good.
I wouldn’t re-install the app when it hangs though – we don’t know why it’s hanging, so we don’t know if/how that would fix the issue.
The next time it hangs on startup, run the jstack.exe out of your jdk/bin directory and attach to the sqldeveloper.exe PID. Then send that stack dump to me at [email protected] – hopefully that will tell us what’s borking up your SQLDev ‘install.’
Ok, will do! I just “fixed” this today, so not sure when it will happen again. Hope it happens soon 😉
Hello Jeff,
I read about your article on DBDOC. I want to use the utility for my SQL project. Can you please share the utility?
Thanks.
Are you wanting to extend JAVADOC to MS SQL Server?
DBDOC and JAVADOC aren’t they different utilities?
I use Doxygen for Java APIs and I want to use DBDOC for SQL APIs. DBDOC looks to be pretty cool and easy to use.
Thanks,
Gouri
‘for SQL APIs’ – like for SQL Server?
Hi Jeff,
Where can I find class/object documentation that I can be used in transformation script..
For eg, for classs model.. what are method and properties available.
Cheers
Jeff,
I have a question regarding Table abbreviation.
I created relational model using reverse engineering and now would like to find out missing foreign key using Discover Foreign Key feature.
Updated naming standard template to have my foreign key constraint name as {Table Abbr}_{Reference Column}_FK.
Next step would be to set table abbre for all tables..
My relational model has 500+ tables and want to know, is there an easy way like template where I can update Table Abbr for all the tables instead of going to each table properties and updating the abbr.
Cheers
yes – you can use the Tools > Name Abbreviations feature. You can use code in your templates to determine what the abbreviation would be. Check out this white paper on the subject.
i have this content of file.bat
cd /
sqlplus scott/tiger
spool C:\Users\imrane\Desktop\Résultat01.txt
select iamj.numA||’;’||iamj.numB||’;’||iamj.dateA||’;’||iamj.heureA
from iamj left join janmt on iamj.numB=janmt.numB where janmt.dateA is null;
spool out
and after it’s stoped after connexion to sqlplus without complete sentences
help meeeeee,you d’ont answer to my question
your question is about SQL*Plus, not SQL Developer…but i would put your sql*plus script in a file and then change your bat file to be something like sqlplus scott/tiger and then next line @your_script.sql which does the spool and your queries
I want to integrate SQLPLUS into the latest version of SQLDeveloper. Is there a way to invoke SQLPlus similar to how I can open a new SQL Worksheet for a specific schema? What I really want to see, is sqlplus running in its own tab.
Thank you, Ken
No, esp not what you want with it running in its own tab. You CAN do this though.
I changed the parameter to “/ nolog” without quotes. I get a Sqlplus tab in the Messages – Log window. I just can type anything.
C:\users\kenlee>
C:\oracle\app\product\11.2.0\client_1\BIN\sqlplus.exe /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 5 10:09:03 2014
Copyright (c) 1982, 2010, Oracle. All rights reserved.
idle>
good moorning,
I have this file.bat
cd /
sqlplus scott/tiger
spool C:\Users\imrane\Desktop\Résultat01.txt
select iamj.numA||’;’||iamj.numB||’;’||iamj.dateA||’;’||iamj.heureA
from iamj left join janmt on iamj.numB=janmt.numB where janmt.dateA is null;
spool out
and when I call this file.bat he stoped after connexion without taking the spool content in consideration
help mee please
Fairly novice user of SQL Developer. Connecting to an instance of Oracle working mostly with EBS data. I’m not getting any tables in the tree when I connect. Already enabled “Include Synonyms” in the filters per your posting that I found, still nothing. Using SQL Developer 4.0.0.13 and connecting to an 11g database. I don’t know what information would help so just let me know what you need and I will get it.
Thank you,
Kris
I’m guessing you’re logging in as someone who doesn’t own anything. Select * from user_tables – does that bring back anything? If not, you’ll need to go into the Other Users bit of the tree and drill down into the schema where your application objects actually are.
You were correct. So easy once you know where stuff is!
This is a common area where folks trip up a bit – so don’t feel bad about it!