4.5+ million 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!
Comments 4,710
Hello Jeff,
I observed one this.If i do commit Using F11/Commit button it is not getting captured in SQL Developer SQL History Log.
I think this also need to monitored?
Please guide
Thanks in advance
Author
we don’t issue a commit as a statement to be executed when you use the COMMIT button, that’s why you don’t see it on the statements panel – now if you were to type commit and hit the execute button, you would
both have the same net effect, even if you get there slightly different ways
I recently upgraded to SQL Developer 18.4. When I type dbms_output commands the editor turns the font gray, even with serveroutput on. Why is that? How can I fix this?
Thanks.
Author
Because this.
You can change the color or just remove the rule.
Why does this work in Pl/SQL:
BEGIN
FOR i IN 1..2 LOOP
dbms_output.put_line(‘Times through Loop: ‘ || i * 5 );
end loop;
END;
But this does not:
BEGIN
FOR i IN 1..2 LOOP
dbms_output.put_line(‘Times through Loop: ‘ || i + 5 );
end loop;
END;
The second produces:
Error report –
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 3
06502. 00000 – “PL/SQL: numeric or value error%s”
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.
However, this does work:
BEGIN
FOR i IN 1..2 LOOP
dbms_output.put_line(‘Times through Loop: ‘ || (i + 5) );
end loop;
END;
Clearly, in my error version, the ‘+ 5’ is getting passed to the procedure, before the math is done, unlike the version that multiples, but I can’t seem to find any docs on the dbms_output.put_line procedure that indicates the plus sign is special to it …
So, what’s going on and where did I miss finding it documented?
Author
I’m guessing it’s an order or operations maths thing in PL/SQL.
I successfully imported a CSV file into Oracle using SQL Developer. I can query the new imported table with no unexpected results. However, when I try to join this table to an existing table in an Oracle DB, it returns an error when it gets to the end of the imported records (or subset of records if I include additional filters). I have tried to join the tables, and I have used an “IN” statement. How do I make the join resolve before returning an “invalid number” error?
Author
What’s at the end of your table that’s ‘not a number?’
What are the data types for the two columns you’re joining?
Both columns are defined as NUMBER. I am using the imported table to identify the specific records that I want pulled from the Oracle DB. The imported table is clean. However, the DB table has some non-numeric values in this field. To eliminate those rows, I have to filter on a value of IS NULL in a table change indicator field. When I execute the query to join the tables, it appears to be processing the data correctly, e.g., the respective IDs match, but when the execution nears the end of the rows of the imported table (13,772) (or the number of rows filtered 1525) it bombs.
Author
How can you have non numerics in a number column?
I’m going to need to see your data to provide any insight or assistance.
First, thank you very much for your replies. Your willing assistance is greatly appreciated.
I have no idea how non-numeric data came to be included in the oracle DB table, but there it is. Our programmers have a work around that I tried to explain in which the record that has not been changed is selected (the null value is always on the row with the correct value), but that only seems to be working as a filter within the database tables. The imported table that is not technically a DB table seems to be causing issues when it is joined with a DB table. If an aggregate function is included in addition to the joins, it seems to cause even more issues.
I am unsure what data I could send that would generate the issue I am having. Would you please offer some guidance in that regard?
Thanks much, Reed
Author
Well, if it’s a numeric column, it’s impossible for something other than a number to be stored there. So, before I can help you, you need to show me exactly what is happening. The tables DDL, sample data, and any SQL.
Hi Jeff,
I’m writing some extra design rules and transformation in order to automate QA regarding the relational models maintained by SQL Developer Data Modeler. There is a particular design rule which aims to check the properties “Data Compression” and “Compression type”. Depending on the values of those properties the design rule must show a error.
Having said that, I started the design rule script development and soon I got stuck in the Class documentation. I didn’t find which class represents the Physical Model within a Design. Putting it simple, I’d like to know how do I get a table list from a given physical model, and iterate over this physical table list in order to check the values of the properties “Data Compression” and “Compression type”. Jeff, could you give me some idea which class to use to achieve my goal, please? I have been writing some design rules and transformation (https://github.com/ABestteti/SQLDataModeler) using only the relational model. This is the first time ever I have to navigate deeper in the design model.
Thank you in advance for your help.
Best regards,
Anderson
Hi Jeff,
Sorry bother you, but would you be able to provide me a sample code?
Best,
Anderson
Now that 18.4 is out the Oracle SQL Developer FAQ (https://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-newfeatures-v181-4423630.html) hasn’t been updated since Sept. 2017 and mainly references SQL Developer 4.1. Any plan to update this?
Author
None of those items refer to 4.1 features, that’s all 18 stuff.
18.4 is just bug fixes, so nothing to add there.
I do not know if anyone has already given that suggestion. Or if something already exists in Oracle SQL Developer.
In a file with multiple queries (or package with functions, etc) there could be a way to navigate them easily with a pattern to be defined:
–#QUERY_TODAY
SELECT SYSDATE FROM DUAL;
–#QUERY_TOMORROW
SELECT SYSDATE + 1 FROM DUAL;
It could have something similar in other IDEs that you can find a method and go directly to it.
Thank you.
* Sorry for the translator
Author
Today’s post is just for you! Make sure you’re using version 18.4.
HI,
I’m unable to import connections from oracle sqldeveloper-3.2.20.09.87 to sqldeveloper-18.3.0.277.2354-x64.
Also, when i try to open sql getting an error.
Unable to launch the Java Virtual Machine located at path ..\..\jdk\jre\bin\client\jvm.dll
Can someone please help?
Author
That was a bug in 18.3 – it should be fixed for 18.4
Hi Jeff,
I’m using SQL Dev Data Modeler Version 18.3.0 and I have noticed that DDL Preview does not represent changes made in the model when you run it again after the first time. It seems that the DDL Preview is always showing its first DDL production. The only way to see the right DDL statement is close the model and open it again, and then execute DDL Preview.
On the other hand, the Generate DDL works just perfect, I mean, it always produces a DDL script that represents the changes made in the model, even when the changes haven’t been saved.
Is there any issue already open regarding this behavior on MOS?
Best,
Anderson
Author
I don’t see that happening in 18.3 or 18.4…and it’s not a known issue.
I created a new model, imported objects from an oracle db. Took a table’s column, changed it’s data type and added a comment. Immediately went to preview the DDL – it reflected those 2 changes, w/o doing a save or close/re-open.
Hi Jeff,
I just saw that the SQL Dev e SQL Dev DM 18.4.0 were released! That’s a great news!! To add to my post, I have made the changes in Physical Models due to specific properties related with LOB storage and table compression supported by Oracle 11g R2 (11.2.0.4.0).
Additionally, I am using SQL Dev DM 18.3.0 on a Windows 8.1 Pro 64Bits, and Java(TM) Platform 1.8.0_181, JAVA_HOME pointing to C:\Program Files\Java\jdk1.8.0_181\jre.
Jeff, if you do need further information, please, let me know.
Regards,
Anderson.
Author
OK, I see what you’re saying now. I don’t think that’s a known issue, I’ll log it for you.
Hi Jeff,
I really appreciate your help on this matter. I’ve just downloaded SQL Dev DM 18.4.0 and I’ll do the same test case to check whether the issue is reproducible or not.
Kind regards,
Anderson
Hi Jeff,
I have downloaded SQL Developer Data Modeler 18.4.0 in order to test the “DDL Preview” at Physical Model level. Unfortunately, at Physical Model level the “DDL Preview” does not show the changes made in the model after the first time you use DDL Preview. Here is the test case:
Test case setup:
First of all, download the HR sample model from https://www.oracle.com/technetwork/developer-tools/datamodeler/hr-model30-247282.zip.
Uncompress the downloaded Zip file, and then open HR.dmd file with Oracle Developer Data Modeler 18.4.0. Then save the model using “Save as…” option
in order to overwrite the original file and update the model”s XML metadata. Finally, close the converted model.
Now proceed with the following test case:
[1]
1.1 Re-open HR model and check Relational and Physical check-boxes
1.2 Navigate to Physical Models and open Oracle Database 11g
1.3 Open Tables node, then select COUNTRIES table, right click on it and select “DDL Preview”
1.4 You shall see the following DDL
PROMPT CREATING TABLE ‘COUNTRIES’;
CREATE TABLE countries(
country_id CHAR(2 BYTE)NOT NULL,
country_name VARCHAR2(40 BYTE)NULL,
region_id NUMBER NULL
)
ORGANIZATION HEAP NOCOMPRESS
NOCACHE
NOPARALLEL
NOROWDEPENDENCIES ENABLE ROW MOVEMENT;
<>
[2]
2.1 Close “DDL Preview” window
2.2 Right click on COUNTRIES table, then select “Properties”
2.3 In the “Table Properties – COUNTRIES” change change the following properties:
2.3.1 * “Data Compression” to YES
2.3.2 * “Compression Type” to OLTP
2.4 Click OK button
2.5 Save the model
2.6 Right click on COUNTRIES, then select “DDL Preview”
2.7 You shall see the following DDL
PROMPT CREATING TABLE ‘COUNTRIES’;
CREATE TABLE countries(
country_id CHAR(2 BYTE)NOT NULL,
country_name VARCHAR2(40 BYTE)NULL,
region_id NUMBER NULL
)
ORGANIZATION HEAP NOCOMPRESS
NOCACHE
NOPARALLEL
NOROWDEPENDENCIES ENABLE ROW MOVEMENT;
<>
[3]
3.1 Close the HR model
3.2 Open HR model again
3.3 Navigate to Physical Models and open Oracle Database 11g
3.4 Open Tables node, then select COUNTRIES table, right click on it and select “DDL Preview”
3.5 You shall see the following DDL
PROMPT CREATING TABLE ‘COUNTRIES’;
CREATE TABLE countries(
country_id CHAR(2 BYTE)NOT NULL,
country_name VARCHAR2(40 BYTE)
NULL,
region_id NUMBER
NULL
)
organization heap compress
for oltp nocache noparallel norowdependencies enable row
movement;
<>
Outcomes: As you can see, there is no change in DDL code generated in steps [1.4] and [2.7], even though changes have been made and the model was saved.
The DDL Preview was able to show the expected DDL code when the model was closed and opened again, as shown in step [3.5].
Hi Jeff!
My workstation updated to Windows 10 build 1809 (no escape from that) and now right mouse click does not work correctly any more in SQL Developer.
First right click on anything selects the item underneath the cursor, the second right click (on the selected item) always produces the same menu that is shown normally when right click on a folder in connections tree. Even if you get to open a connection by using keyboard navigation and expand a node (for example “Tables”), right click on any item shows the same “folders” menu.
I tried to reinstall SQL Developer with no success, I downloaded separately latest JDK and SQL Developer for other platforms with no Java – no success.
This is my work machine, so no messing with it – just the regular system updates. Other colleagues here at work are experiencing the same problem. A temporary workaround we use is a virtual machine with Ubuntu and SQL Developer.
Needless to say that right mouse click works correctly in all other apps.
I explained the issue a bit more in detail on community portal: https://community.oracle.com/thread/4194840
Please help, as we are seriously hindered.
Kind regards, Aleksander
Author
Does your org have a My Oracle Support contract? If so, please open a Service Request for SQL Developer.
I have windows 10 update 1803, and it’s working there as expected. I’m unable to put down update 1810 on my own to try to reproduce it locally. Maybe someone on the forums can chime in if they’re in the same setup.
The only thing I know you could try is in Preferences, change your Look and Feel from Oracle to Windows and see if that makes a difference in the mouse.
One other thing – are your machines setup for HiDPI? If so, change the application settings for SQL Developer to disable HiDPI and see if that helps.
Hi Jeff,
thank you for your suggestions, however neither setting “windows look and feel” nor playing with DPI, scaling and such options helped. SQL Developer still just “repeats” the last right mouse menu it managed to show on any node you right click on. I’ll see if I can open a MOS issue, or should (as nothing else changed) be nagging Microsoft…
Thanx again.
Kind regards, Aleksander
Hi Jeff,
sorry to bother, but this seems will be a widespread issue – more Windows updates to Builld 1809 had happened throughout our organization and more and more coworkers are facing the same issue. Looks like Build 1809 breaks SQL Developer.
Kind regards, Alex
Author
I found this, worth a try.
Hi Jeff,
Is there an example of how to upload xml data via ORDS to a XMLType column?
We have an ORDS 3 installed (to use APEX). I am not able to connect from SQL Developer. I always get response 403 and checked all possible users. Which user is to used? Any from APEX? Using REST directly in APEX works fine.
Thanks!
Reinhard
Author
You’re mixing problems…Apex based rest services, ords users, and xml.
Let’s tackle xml first. I can try to show you how to do this with an ords based service, but you’ll need to go to the Apex team for help with Apex based rest services.
Let me know.
Hi Jeff,
Let’s ignore APEX and please let me know how to deal xml with an ords based service. I’ll use SQL Developer included ords service…
Thanks
Reinhard
Author
One way to go..treat it as a file, post it up and catch it as a clob, for a simple insert/update
That already worked for me. I want to use advantages of XMLType data type…
Thanks
Reinhard
Author
XML Type can be stored internally as a CLOB. ORDS can deliver the XML to your table as a CLOB. Best of both worlds, yes?
You are right. That was my gap. Thanks
but one thing:
“Starting with Oracle Database 12c Release 1 (12.1.0.1), the unstructured (CLOB) storage model for XMLType is deprecated. Use binary XML storage instead.”
https://asktom.oracle.com/pls/apex/asktom.search?tag=clob-vs-binary-xml-storage
Binary seems to be not working…
Thanks
Reinhard
Author
You haven’t shown me what you’re actually trying..also ords is just as happy to treat your post body as a blob
When I save DDL to a file (right click on an object, point to Quick DDL, and select Save To File), the file looks okay in SQL Developer. However, if I open the file in Notepad and some other programs, it’s a jumble with the line breaks missing. How do I get the file to export so it retains the line breaks?
Use Notepad++ or some other editor. They can handle CR+LF problems.
Author
What is line Terminator preference set to? You should see it on the export dialog.
When I use Quick DDL > Save to File and Export, neither has an option for setting the terminator preference.
I looked in Preferences to see if there are any options in there. Under Environment I changed the Line Terminator option from Platform Default to Carriage Return and Line Feed (Windows). Under Database > Utilities > Export, there are a number of options and all of them I changed Line Terminator from Environment Default to Windows CR LF.
Then I tried the Quick DDL and Export again and the files are still a jumble when opened in Notepad (I closed and opened SQL Developer and that didn’t make a difference). Interestingly, if I select Quick DDL > Save to Clipboard and then paste in Notepad, the formatting is okay. So it seems like it’s an issue with how the file is being saved. I can use the save to clipboard as a workaround for exporting to a file.
Good afternoon. We’re trying to get ORDS standalone up and running without APEX. The DBAs installed ORDS on the desired AIX enterprise database, then sent me the ords.war to run locally on my windows laptop. I’m getting a 404 “Request could not be mapped to any database. Check the requested URL is correct, and that the URL to database mappings have been correctly configured” error. Are we thinking about this correctly – is this setup possible? I’ve been trolling the web looking for tips & tricks to no avail. Have any thoughts given this limited info? Thanks!
Author
So they got the ords DB users configured, and then sent you the passwords for ords public user to run ords from your laptop?
Yes, DB users are configured; however they haven’t send passwords, I’m simply running ‘java -jar ords.war standalone’. It definitely feels like we’re missing something. Your reply implies I’ll need the passwords to run setup or config on my laptop?
Author
It’ll be hard for ORDS to create the connection pools using the ORDS PUBLIC USER database account if it doesn’t have the password for that user.
OK, I’ve emailed my DBA coworker, he’s on the west coast. Poking around I will set the username/password in ords_params.properties and see what happens. Thanks.
I added the username and password to ords_params.properties. A little more digging, I found I need to create an ords.war user. Once I created that, from SQL Developer tool I could connect to REST Data Services, however it gave me three 404 errors: two for retrieving RESTful services, one for retrieving Role. One more bit of info, since you mentioned pools, when I startup ORDS I get an INFO message that no pools are configured.
Thanks for helping an ORDS rookie.
I appreciate your website so much!
In SQL Developer, is there a preference for the default appearance of the File::Open dialog box. I would much prefer to have it default to showing the file details, not just the names. I would also prefer that it only show me .sql files by default.
I’ve googled and used the search in the Preferences–no luck
Thanks
Author
You should at least be seeing the file details there for every time you go to open a file after you’ve turned it on once – yes?
I’m guessing if you were to restart SQL Developer, you’d have to toggle it on again though.
There’s no way to tell it to always just show you *.sql files…but for me, what I do, i just type *.sql in the input box and then scroll down and pick what I’m looking for. Much faster than using the file type drop-down control.
Yes, the file details are there when I go back, but not after I restart SQL Developer.
Usually, I’ve started scrolling through the way-too-long file list before I actually realize that I only want .sql files, but I’ll try to train myself to do that (type *.sql) so that I can skip that wasting time step.
Thanks. It’s good not to spend more time looking.
Hi ThatJeff, thanks for all the great work you do here – from an Oracle Newbie! 🙂
I’ve looked around but can’t find an answer to this anywhere, which I’m sure is the answer that I’m not wanting to hear.
Running multiple SELECT queries at once returns multiple Query output tabs, predictably. Is there anyway to export all of the tabs at once to .xlsx files, rather than exporting each tab individually?
I saw posts about spooling to .csv files, or using the Cart function for doing entire tables, but not in terms of query result tabs.
I appreciate the CSV spool method greatly, however this is for providing data to a software vendor when troubleshooting issues, and that is how they require the data (individual XLSX files).
Thank you again, and have a great day!
Author
I’m guessing your vendor could handle CSV files, in which case spool is the answer.
But if they play dumb, it’ll have to be a right-click, export to XLSX set of steps for each query you execute.
I don’t know of a better way to do it.
Hi Jeff,
Is there a way in sqldeveloper to use datapump beteen tenancys in the cloud? Export from one tenant and import in other tenant?
greetings,
Martien
Author
You can use dbms_cloud package to read the pump files from your OSs (s3) location. I think a few people have blogged this.
Hi Jeff, Happy 2019!!!
Is there any plan to implement Git support on SQL Developer Data Modeler?
Thank you in advance for your attention.
Best,
Anderson
Author
No immediate plans.
Hi,
I have a CORS problem with ords. I have installed ords and Tomcat on a SLES 12 system. Runs fine so far.
But no matter what I try, my xhr request through chrome-engine fails with “preflight doesn’t pass access control check: No “Access-Control-Allow-Origin” header is present.
I configuered the Tomcats web.xml as described in https://tomcat.apache.org/tomcat-7.0-doc/config/filter.html#CORS_Filter with the complex options, added cors.allowed.origins (also tried with *).
Nothing works.
Any suggestions ?
Thanks in advance
Klaus
Author
Sorry, but that’s a Tomcat issue, not ords?
Hello,
finally I could identify the problem.
In my tests I user a search query like
http://srv01:8082/ords/fo95/medical_device_problem_codes?q={%22fdacode%22:%221112%22}
This works fine with e.g. Postman.
But with JS-Chrome, it fails because of the { } characters. Chrome delivers a missleading error message, that “Access-Control-Allow-Origin” header is missing. Thats not wrong, it’s not there, but that wasn’t
the problem.
After encoding the { } chars, everything works fine.
I don’t know, where exactly the { } are disturbing, wether its in the chrome engine, Tomcat itself, or the cors-filter for tomcat.
Thanks
Klaus
Hi Jeff,
Ultimately I am trying to schedule a query to run at a certain time daily and export the subsequent data to an excel file to a file share however I can’t find a way to view the code that is created when you export a data set, do you know if there is a way to view the background code that is generated by the dialog box? I have tried spooling but I keep getting “SP2-0556: Invalid file name” errors.
Thanks, Neil
Author
We don’t have a way to spool to Excel directly.
I’d look at setting up a SQLcl script to spool to a csv, then have your OS scheduler kick that bat/bash script that calls SQLcl.
Or, show me what you’re actually trying.
Jeff, This seems to be an issue with PL\SQL apps which are newly being accessed with ORDS authentication in 12c. I ask here what a gentleman had asked in the Oracle User Group Community (https://community.oracle.com/thread/4089250) … Is there a limitation on the size of the array that ORDS can handle to port to the database? Or perhaps a memory issue in the Exalogic mid-tier? The app is being ported from its current production 11g WebLogic.
Here’s an edited stack trace. (This particular package.procedure uses many “IN OWA_UTIL.IDENT_ARR” parameters.)
InternalServerException [statusCode=500, reasons=[]]
…
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 4
…
at oracle.dbtools.apex.OWA.execute(OWA.java:158)
[END OF STACK TRACE]
Thanks for any suggestions.
Author
it’s more likely the web server is puking on the amount you’re trying to send it…that many form items not really ideal for building a web app in 2019
better to put that info together as a single json doc and put that in your POST body
Before closing sqldeveloper I always close the package specs/bodies that I finished working on.
However when restarting sqldeveloper it often shows again the tabs with those specs and bodies.
Can I influence this behaviour ?
How do I remove white space around the period in a table name? Example:
SELECT * FROM schema . tablename <– what I currently have
SELECT * FROM schema.tablename <– what I want
Author
Just reset your formatter preferences, this was a bug when folks upgraded
did we ever say thank you?
can’t remember ;]
hope to see a lot of you in 2019 as well