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
is there a way to set a shortcut to automatically write “select * from “
Yes, see this post:
Hi Jeff,
Do you have any tweaks regarding the connection timeouts?
Thanks
Hi Jeff,
I want to generate a glossary from my logical model in SQL Datamodeler via the menu File>Data Modeler>Reports. When I choose in the field available reports “Glossaries” and I click on button ‘Generate Reports’, the tool asks for a .glossary file. What is meant by this? When I continue I receive an error.
Thanks.
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 ??
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 ??
Hi Jeff – when we want to export the DDL from one environment to other, I use Export DDL feature. I get everything but the Synonyms attached to that particular table. Is there a way i can get synonyms into my ddl??
Please, Let me know.
Hi Jeff
We want to run some of test cases / sites automatically in a night job. We have written a shell script that calls sdcli unittest part. That script requires a parameter which is name of unittest. Later on, we will create a pl/sql function, that will invoke the shell script. Then, we will make some queries in order to find which tests have been failed.
Our problem is that, sdcli prompts oracle username and password in interactive mode. We are not able to supply these uname/pwd information in batch job. We also check connection xml file, but unfortunately we were not able solve this.
Is there a configuration or a feature that does not ask password during sdcli? If we can save the password in somewhere(configuration file etc), it will be fine for us.
Best Regards,
Mennan
…..
Running unit test for DUMMMY_unitTest
Oracle SQL Developer
Copyright (c) 1997, 2013, Oracle and/or its affiliates. All rights reserved.
WARNING: This product is certified on JDK 1.6.0_35. You are attempting to run on JDK 1.6.0.05. This product may not run correctly on this version of the JDK.
Password for dev_UT_basic?
Password for dev_basic?
Success!
Done…
Is there a way to write scripts in Oracle Data Modeler that can generate DDL for objects not directly modeled in the diagram, but based on another table’s DDL? For instance, in ERwin, I can write code to generate an audit table using some or all of the fields from another table that exists on the diagram. Also, I can generate PL/SQL and triggers also, using table DDL in the diagram using ERwin’s scripting language.
yes, the transformation scripts will let you do that – in 4.1 you can specify scripts to be ran/generated before and after specific objects are generated even
Hi Jeff,
I’m having a somewhat annoying issue in SQL Developer, Version 4.0.3.16. When editing a table in the data tab it will sometimes register the first character I type twice. So if i type 012345, it will show 0012345 in the column. This only happens in the table data editor, and I have not been able to find a pattern to this behavior. On average it happens in 1 out of 5 columns when editing several columns.
BR
Einar
Hey Jeff, I’m creating a package that may end up being a bit large, with multiple private and public procedures. I’ve created a script file (*.sql) that I can edit and run in SQL Developer. This lets me use version control, etc.
The problem is that this is a large single file that gets cumbersome to edit, and I’d like to split it into multiple smaller files. Is this possible?
This would allow me to, for example, open two files at once to view them side by side. Or let two people work on separate parts simultaneously without worrying about merge conflicts later.
It is frustrating that I can keep stored procedures in separate files, but if those procedures are part of a package they have to be in one giant file!
If this isn’t possible, what workflow do you typically use in this situation?
Thanks!
One file per object. A package spec and body are technically two objects. Save your plsql objects as one of the defined file extensions in the preferences:
If you save it as a .sql file, it will open in the worksheet and you won’t get the full IDE support for PL/SQL code.
Thanks. I was hoping to be able to split into multiple files, but this still helps. Especially the tip on the pre-defined file extensions.
Follow-up Q: How are the different extensions typically used?
I can guess .pkb = package body and .pks = package spec. What are the others?
– addition to above comment
Everything has to be achieved by writing a java program, including the conversion of json to xls. No external software or tools are required. it is a project requirement.
You can do that. We are building tools to do this for customers. There are libraries out there that can help you. Check out Apache POI.
Thanks a lot. That was really very helpful!
Hi Jeff,
I was wondering if you could guide me in the right direction regarding the following situation:
I am creating a html form(using jsp) whose data would be stored in the sql developer database. I want to know whether there is any method to send my data into an excel spreadsheet(preferably new), without using TOAD (i read that post and really liked it.)
Is there a way to achieve this by writing a java program?
* also can json files be converted into xls(specifically xls, and not csv!)
Any help would be obiliged.
Thank you!
Hi Jeff,
How can I change the naming standard (e.g. foreign key column) in Oracle Data Modeler 4.0.3 ?
Thanks
Reinhard
Hi Jeff,
How to get all rest records by one click in the Query Result? Now it could only fetch 50-200 rows each time I scroll it to bottom.
Thanks!
Leo
Hi Jeff,
Why the Save button is disabled when I working in worksheet?
I have to copy all the scripts to another editing tool to save them.
Thanks!
Leo
Hi Jeff,
This is a great site. Learnt a lot on Sql Developer. I have trouble running SQL*PLUS scripts in sql developer especially when I have to run Oracle provided scripts for debug from oracle support, which generate a HTML file. How do I run SQL*PLUS scripts in sql developer.
your success will depend on the commands you’re using in your script and whether SQL Developer will support them. as of version 4.0, we don’t support BREAK or COMPUTE. But in version 4.1, we will.
Check the help topic on supported SQL*Plus commands.
To run a script, open your script. Set your connection, and hit ‘F5’
Hi Jeff,
I’m running sqldeveloper 4.0.3.16. I’m trying to migrate a MySQL database to Oracle.
I started the migration and sqldeveloper reported the migration is complete. However, nothing has been migrated to Oracle.
When I checked the log file (c:\users\xxx\AppData\Roaming\SQL Developer\system4.0.3.16.84\o.sqldeveloper.12.2.0.16.84\projects\project_name\generated\2015-02-20_13_05_11\project_name_2015_02_09_13_07_17-18.log), it said Unable to open file: “C:\Users\xxx\AppData\Roaming\SQL.sql”
In the same folder, there are 5 sql files: .wrapper.sql, dropallschemas.sql, master.sql, passworddefinition.sql, reportallstatus.sql.
In the master.sql, it has all scripts to create tables, constraints, indexes, etc. But, I don’t see any scripts that move data.
I tried with a few different versions of sqldeveloper (4.0.x, 4.1), and they all have the same problem.
I hope you can help on this problem.
Thanks.
Hi Jeff,
I am running 4.0.3.16 on a win 8.1 enterprise.
When I try to export a dataset from SQL Developer to an Excel sheet (2003), it starts running as normally, but where it normally would start counting rows exported, it just stops running? (There are no Excel file either).
When I try to search LOG in the SQL Developer library, I can not find any logs that are updated today, so no logentries seems to have been made. (Probably me that needs to set some flag somewhere 🙂 …)
I hope that you can help me, even though I am a NewBee at this 🙂 …
Kind regards’
Kenneth
Found it – I just needed to enable (View) the Task Progres window in order to see the counter, and now it produces the file correctly.
Why it did not create the file before I am unsure about – probably a Noob error 😉 …
no, it should have worked w/o that open…although be sure to use the XLSX format either way 🙂
In datamodeler is there a report that will list all views in a relational model that have errors? I have inherited a model that has many parsing errors and would like to fix them, but I want to be sure I have them all.
No, and from what I can tell, we don’t pull into the data dictionary to see if there are compilation errors…
But when you say views that have errors, what do you mean exactly? Do you mean the views that haven’t been validated or parsed – that have the little warning/error message on their header in the diagram?
Sorry, yes, I was trying to talk about the little warning/error messages on the views in the diagram NOT from the database.
Hi Jeff,
i’m working on linux with sqldeveloper-4.0.3.16.84-1.noarch.
I have problems with the domain.xml file (domain Subdirectory).
I have created a dedicated file beside the default domain.xml.
Sometimes when the model is saved, the domain file is reset to an historical state, sometimes the file is deleted.
Any idea what’s worng here?
Hey Jeff, great site. I’ve come here many times and have found many answers to questions that aren’t addressed anywhere else!
I’m one of those ‘no mouse’ guys. One thing I’m can’t seem to figure out in sqldeveloper is how to switch between, say, a code editing window to the Connections view without having to use the mouse.
It doesn’t look like the keyboard shortcuts menu has an option to add specific commands, and under the View category, Connections isn’t shown. I’m using v4.0.3.16.
Thanks much,
Mehr
you can ctrl+tab into it
hello sir,
i am developing a mobile application,right now i m working on sql database and using IDE i.e eclipse for app development .i need to save and retrieve image in sql database.
please tell me the steps to store the image in sql database.
SQL Server database, or Oracle Database?
If Oracle, try BLOBs. If SQL Server, you’re asking the wrong person.
Jeff,
say I have two tables: users and contacts, both have a numerical PK called “ID”
when I now want to make a foreign key between the two datamodeler links the two ID columns instead of adding a “usr_id” column in the contacts table.
I’m sure this is a setting somewhere, but I have no idea where i can find it.
Could you please help me out?
Normally you don’t have two PKs together forming a relationship. You want every user to be a contact or every contact to be a user?
You’re in a relational model it sounds like?
Jeff, I’d like every user to have multiple contacts. both tables have a primary key called ID.
Indeed I’m in a relational model.
In the past this worked fine, but now I get a 1:1 relationship instead of a 1:n.
regards and thanks for your effort,
Richard
Hi Jeff, I’m trying to explore the report generated by Oracle Data Modeler, after the execution of the design rules available in the product.
It would be possible to maintain the traceability in order to identifier the rule after export the report to CSV?. For example, adding the identifier of the rule at the same way of errType and ruleMessage variables or enabling a similar mechanisms for this traceability?
My Oracle SQL Developer 4.03.16 is not using the Java from the Windows 7 Environment variable. Here is the error I am receiving from Oracle:
Testing the Instant Client located at C:\Program Files (x86)\Oracle\app\instantclient_12_1
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.1.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
the expected native library directory C:\Program Files (x86)\Oracle\app\instantclient_12_1 is present and precedes any other client installations.
java.library.path = C:\Program Files (x86)\Oracle\sqldeveloper4\jdk\jre\bin;C:\WINDOWS\Sun\Java\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files (x86)\Oracle\sqldeveloper\jdk\bin;.
C:\WINDOWS\system32;C:\WINDOWS and C:\Program Files (x86)\Oracle\sqldeveloper\jdk\bin;. do not exist on my computer.
The Environment variable is C:\Program Files (x86)\Oracle\sqldeveloper4\jdk\bin
Is this such as easy problem to fix that no one has had to put it into Google?
The problem is it can’t find the instant client on your $PATH$. Did Google not find this?
So I put the Instant Client in the Windows Environment Variable? The Instant Client is listed in the Developer in Tools>>Preferences>>Database>>Advanced the “Use Oracle Client” I have the “Use OCI/Thick Driver” checked. I copied and pasted the path to the instant client. I receive the error when I test the configuration.
My Oracle SQL Developer 4.03.16 is not using the Java from the Windows 7 Environment variable. Here is the error I am receiving from Oracle:
Testing the Instant Client located at C:\Program Files (x86)\Oracle\app\instantclient_12_1
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.1.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
the expected native library directory C:\Program Files (x86)\Oracle\app\instantclient_12_1 is present and precedes any other client installations.
java.library.path = C:\Program Files (x86)\Oracle\sqldeveloper4\jdk\jre\bin;C:\WINDOWS\Sun\Java\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files (x86)\Oracle\sqldeveloper\jdk\bin;.
C:\WINDOWS\system32;C:\WINDOWS and C:\Program Files (x86)\Oracle\sqldeveloper\jdk\bin;. do not exist on my computer.
The Environment variable is C:\Program Files (x86)\Oracle\sqldeveloper4\jdk\bin
Is this such as easy problem to fix that no one has had to put it into Google?
Yes.
The instant client is just a zip, no installer.
If you read the docs, it says to make it available on your machine, you have to add its location to the OS $Path variable.
So we need that. We look for the binaries on that path.
That was it. Thank you.
Hi Jeff,
Can you tell me if the bug with OCI thick client and XMLTYPE columns has been addressed in 4.1?
Thanks for all your help!
Barry
No, but MOS should be able to. That one’s out of our particular hands, but I’ll ask the XML team in our next group call.