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
And more thing when I checked the version SQL Developer Tools 4.0.3 in 64bit machines.It is running perfectly.
Then why the version 4.1.1.19.59 is not running on the same machines.
And I compare the files in both 4.0.3 and 4.1.1, there is no much differences on it.
Please someone address this issue.
Hi,
I’m from Application Packaging Team.
I have an application SQL Developer Tools – 4.1.1.19.59.(64bit application)
When I run the sqldeveloper64w.exe in the source folder ,it throwing an error states that “This program can’t find MSVCR100.dll”.Please reinstalling the application to fix the issue”.
Please someone help me to solve this issue.It is very urgent requirement.
Thanks in advance.
The error tells you everything you need to know – find that DLL and copy it to either the sql developer bin folder, or into the user’s PATH
Hello, please help me.
I am using Oracle SQL Developer Data Modeler 4.1.1.887
How can automatically update all attributes ( FK) from related tables (entity) , with commentary amended as PK in the master table (entity)?
TKS
Luis
Hi Jeff
I just downloaded SQL Developer 4.1.1.19 and when I open several packages there are times that it opens the package in a new tab and some other times it uses an already opened package tab
I do have the Database -> Object Viewer -> Automatically Freeze Object Viewer Windows
Would you happen to know if this is a bug or I am missing some setting?
Thanks and Regards,
Isaac
There’s a setting for max number of editors, check that
Hi.
When importing csv and other files since 4.1.1 and there are columns containing numbers, the decimal points are ignored and end up with just integers. Import doesnt fail but if there is 64.123 it imports it as 64123. I tried changing the nls but got the same results.
We are working in a team environment and using Subversion for versioning. One of the developers locked the entire Design and then we were unable to Unlock it or Cleanup from Modeler. While working on resolving this from the command line we concluded that the issue appears to be related to spaces in model file name. Files, 200 or so, were eventually unlocked from the command line using %20 to substitute for spaces. Everyone of these locked files had a space somewhere in it. Since the Data Modeler file had spaces in it, the entire project was locked until it was cleaned up. Using Unlock or Cleanup form Modeler under the user with the lock made no difference.
Are there any known restrictions on file naming when using Subversion?
In SQLD3, the headers of a search result would be the column name. In SQLD4, these are abbreviated to the size of the data. The result is that if the data size is shorter than the column name size, the whole column name is not printed as the header. How do I fix this?
what search feature are you using?
I don’t think I understand your question. I press the 2nd icon, which looks like a page with a triangle, and I get results below the sql code pane.
ah, so you’re running a query, using the word ‘search’ confused me
on the columns, just right-click and change it to fit your needs using the AutoFit options
I cannot UNZIP the latest sqldeveloper-4.1.1.19.59-no-jre.zip download. With or without JRE. 7Zip and Windows say the file ‘is invalid’. Tried different downloads several times.
I was fine with the previous downloads, and I’m running v4.0.3 happily now.
But I’d like to see v4.1..cause I presume it’s better ;>) .
Do i need WinZip… or any other theories on what may be the issue ?
thanks…. and your blog is excellent !!!
i think you just need to try downloading it again, one of the hosting servers is probably just hiccuping
Hi Jeff,
I am using SQL Developer 4.1.0.19, Build MAIN-19.07.
I was using the Cart feature to create a deployment script. When I tried to drag and drop a Public Synonym from the Navigation Pane, It would not let me. I also tried right-clicking on the Public Synonym, which gave me no option to add to Cart.
Is there something special that I need to do in order to put these into the Deployment script? Is there a setting that I need turned on?
Any help would be appreciated.
Thanks,
Jim Porter
don’t think the cart supports those, BUT, you can save your synonym DDL to a file and include that file in your cart, when you do the deploy the code from that file is included when the ddl is generated
Thanks Jeff.
EDIT:
Perhaps my example was a bit of a drawback, consider these examples instead:
GRANT SELECT on Schema1 to Schema2;
GRANT UPDATE on Schema1 to Schema2;
GRANT INSERT on Schema1 to Schema2;
…etc.
Hello Jeff !
I’ve been searching around here but couldn’t find it.
How to do this via SQL Developer (GUI way, mind you):
Grant CONNECT on Schema1 to Schema2;
Thanks in advance 🙂
you grant CONNECT to a user, it allows you to connect to the database
do you mean granting SELECT on a table to a user?
Well I’m trying to grant full access to a user so that it can be the DBA (sort of) to another Schema. UserA can (insert,update,select,delete,…) on another Schema.
Is that possible to do in a GUI way like when assigning grants to one User/Schema in DBA>Security>Users ?
Two ways to go…directly grant the privs, have them do the work as their own account against the other schema’s tables
OR
Use a proxy authentication scheme…i think this will give you what you want, login as X but act as user Y
First option is what I’m looking for, thanks so much Jeff !
If Oracle is an ocean of knowledge, SQL Developer is a sea. Wish there were SQL Developer Documentations/Guides oriented toward DBA’s with fancy how-to pictures (we’re all children at heart :P) … thatjeffsmith.pdf (thumbs up).
Have a nice day!
we do have our own Oracle Docs book..but Google works best 🙂
Hi,
I want save a PL/SQL script(its not a select statement to make as report) and run it as and when required. how to save the script in SQL developer.
File, Save? I’m not sure I understand your question.
What is my requirement is –
we have daily clone database where after refresh I have execute certain scripts post clone on regular basis. so looking for option where I can keep the script at shortcut(like we have select statements as Reports..)
Hi Jeff,
I recently started using the Cart for consistent builds of supporting objects for APEX applications.
I have a cart that includes a materialized view. Even though when I export the cart I have un-checked Storage, the storage and other physical parameters are included in the output.
Example:
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “XXHPVA5_TS”
Is there a way to suppress the storage parameters?
Thanks,
Skip
I don’t think so, even if I set STORAGE, TABLESPACE, and EXTENT_PROPERTIES to FALSE using DBMS_METADATA.SET_TRANSFORM_PARAM(), getting the DDL for a MV returns that info. From what I can tell reading the docs, those settings don’t apply to MV’s.
This would be a database enhancement request/issue, not a SQL Developer one.
Jeff – thanks for checking.
Skip
Here’s a boring, product management-type question for you… is there any way to determine (or modify!) the version of sql developer that’s incorporated into the Oracle Client install?
I keep having colleagues see me using the tool, getting excited, then having a drawn-out installation-fest when we find that they’re using sql developer 3.0!
i would pretty much never rely on what ships with the database – we only get to update the sqldev version on major releases, so you can just pretty much assume that it’s always going to be out of date
Jeff, I’ve searched for this but haven’t so far found an answer.
Can you confirm that exporting the repository schema and importing it a sufficient and supported way to move the unit test repository between databases?
(I know I need additional permissions as documented several places including here: https://community.oracle.com/thread/1006686)
I also know about sdcli cart, but believe that to be for unit tests and groups of tests inside the repository, not the whole repository, right?
Regards,
John
I placed all my tests under one test suite and exported it to XML. I have been successfully exporting/importing those changes between my workstation and build server using Subversion as the code repository.
http://reldesgen.blogspot.com/2015/04/ci-with-sqldeveloper-jenkins-and-gnu.html
https://www.dropbox.com/sh/72o1ey7je0f66eo/AADCnfBu3IZ5wOxyWx_b1rC2a?dl=0
Thanks, that helps!
Do you have are plan to release any update for fix the error included in my comment.
Or
Do we have any possible to avoid the mistake in current version
Error, what error?
STATEMENT :=
‘
CREATE UNIQUE INDEX DV.LISTCONS_ID_I ON DV.D_EB_BASICLISTCONS
(
ID ASC
)
;
‘
this script fail with error ORA-00911
If you want to report bugs and get release dates, you need to open an SR with MOS. I don’t understand why you’re talking with them and me on the same issues.
ODM advanced script generate script for unique index
DECLARE
STATEMENT VARCHAR2(32000);
comments VARCHAR2(200);
BEGIN
STATEMENT :=
‘
CREATE UNIQUE INDEX DV.LISTCONS_ID_I ON DV.D_EB_BASICLISTCONS
(
ID ASC
)
;
‘
;
comments := ‘CREATE UNIQUE INDEX DV.LISTCONS_ID_I ‘;
adv_scripting.EXEC_STATEMENT(10,STATEMENT,comments);
END;
/
But this script fail with error ORA-00911 on redundant symbol ;
What I can do?
sql developer data modeler is generating that?
Yes! Version Version 4.1.1.888
Only for unique index 🙁
how are you generating the DDL?
How add screen??
you mean a monitor? that’s a computer question, not an Oracle one
Not ddl preview and not Regular ddl
I use DDL File Editor -> Generate
Choose Advansed Interactive DDL -> OK
Hi Jeff,
Right now when Comments and Notes span more than one line, you can’t see in the Excel report that there more lines below. Any idea how to get around this?
You have an SR open with MOS, so I’ll let them handle that there. But it sounds like you just need to format your excel sheet cells. You’ll need to tell them HOW you’re getting it from the modeler to excel in the first place though…
Yes, I also opened and SR.
Is it possible to format the spreadsheet? It is locked for editing.
maybe, although i think we just throw the data at excel
I should also add to answer your question, the Excel file is generated using a template in Reports in Data Modeler.
I was also hoping to generate a report that shows only the visible entities and attributes that are on a subview. Doesn’t seem to be possible. In other words, if e.g. I have an entity in a subview and I want to only see five attributes (including on the report), but it has 100 attributes (95 set to invisible) the report lists all of them. Tried to use it as-is in a meeting with business users and it was difficult given the extra attributes in entities.
subviews can be used to limit the objects that are included in the report, but not for formatting display – except for that if you include the diagram itself, it should export as is
“if you include the diagram itself” – I am not sure I understand this. The “include diagram”. Whatever I tried I seemed to get everything that was defined for the entity. Maybe I missed something…..?
hi, I have a question. I am using Sql Developer. How can I track data changes in tables?
suppose I have run a command, query,… and I know which tables are changed and which rows are updated/added/removed?
Is it possible?
Thanks.
Tracking changes to a table is a database feature – auditing. Your DBA can enable that for you. As you can imagine, it’s expensive in terms of the additional work the database does. But for the people that need it, it’s well worth the cost, esp considering the alternatives is writing your own Triggers to capture it the changes.
You can also check out the Flashback panel of your table editor. It will show changes over time to your data by SCN – but will not track WHO is doing the changes.
Hi Jeff,
i have defined in my Preferences\Web Browser and Proxy a “Manual Proxy settings” with unchecked “Proxy Server Requires Authentication”.
At startup of sql developer and data modeler i get a modal window “Enter username and password for webproxy” – sadly data modeler sometimes freeze and it’s not possible to close the window.
The only workaround i’m currently aware of is to delete my C:\Users\[myuserid]\AppData\Roaming\\Oracle SQL Developer Data Modeler\system4.1.1.887 directory. Any advice how to suppress the popup of the modal webproxy login window ?
Regards Günter
Jeff,
I have an annoyance where every time I open SQL Developer, an automatic pop-up box asks me to sign in to a database that I am unfamiliar with. I just hit cancel and go on about my business.
I am not sure how I got this pop-up to start showing up, but how can I make it stop appearing each time I open the program?
do you have a file or report open by default, maybe we’re trying to re-connect that document?
can you send me a screenshot the next time it happens? [email protected]
Hi Jordan,
please take a look at the following post about manuell configured webproxy – is your problem the same ?
Regards Günter
Export/import to Excel for model review with business – versioning?
Is there a way to version what was exported and imported back? Great way to collect/review requirements. I am on a project with a good number of stake holders and expect to have many spreadsheets going back and forth. Is there perhaps a way to track/version what goes out & and back, in Modeler. We are also using Subversion, plus have another versioning tool for general use.
Hi Jeff,
a question about the data modeler predefined design rules. There is a rule “oracle.dbtools.crest.model.designrules.storage.oracle.DRReservedWordNameOracle@efe45cb” under Rules\Physical\Database\General – in our data model we have a table with column name VALUE – this rule violation is not reported ???
If i create following table at design rules check following errors are reported:
Warning: FROM: The name is a reserved word: FROM
Warning: FROM.ROWID: The name is a reserved word: ROWID
Warning: FROM.WHERE: The name is a reserved word: WHERE
But at DDL generation data modeler surrounds the reserved words with double quotes and reports no error or warning.
CREATE TABLE “FROM”
(
“ROWID” VARCHAR2 (500 BYTE) ,
“WHERE” VARCHAR2 (255 CHAR) ,
“AND” NUMBER
)
LOGGING ;
Version 4.1.1.887
Regards Günter
Yeah, that’s a really bad table name. So would “TABLE” , “SELECT” , and “COLUMN.” Anything that requires quoting is probably a big warning from the database that your name is not a good idea.
My question is why does the design rule not report all reserved words (VALUE/AND) ?
Hi Jeff,
I am using SQL Developer 4.1.0.19.07.
How do I get to see the foreign key columns in the logical data model generated from relational data model?
Thanks in advance.
Is there a report in SQL Developer that will show me which indexes for a specific table are/are not being used over a period of time?
We’re using Oracle 11g2 and SQL Developer Version 4.1.1.19