Ask A Question

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.

  1. 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.
  2. 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.
  3. 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

  1. 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

    1. thatjeffsmith Post
      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

  2. 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.

    1. thatjeffsmith Post
      Author
  3. 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?

    1. thatjeffsmith Post
      Author
  4. 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?

    1. thatjeffsmith Post
      Author
    2. 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.

    3. thatjeffsmith Post
      Author
    4. 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

    5. thatjeffsmith Post
      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.

  5. 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

    1. Hi Jeff,

      Sorry bother you, but would you be able to provide me a sample code?

      Best,
      Anderson

    1. thatjeffsmith Post
      Author
  6. 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

    1. thatjeffsmith Post
      Author
  7. 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?

    1. thatjeffsmith Post
      Author
  8. 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

    1. thatjeffsmith Post
      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.

    2. 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.

    3. thatjeffsmith Post
      Author
    4. 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

    5. 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].

  9. 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

    1. thatjeffsmith Post
      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.

    2. 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

    3. 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

    4. thatjeffsmith Post
      Author
  10. 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

    1. thatjeffsmith Post
      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.

    2. 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

    3. thatjeffsmith Post
      Author
    4. thatjeffsmith Post
      Author
    5. thatjeffsmith Post
      Author
  11. 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?

    1. thatjeffsmith Post
      Author
    2. 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.

  12. 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!

    1. thatjeffsmith Post
      Author
    2. 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?

    3. thatjeffsmith Post
      Author
    4. 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.

    5. 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.

  13. 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

    1. thatjeffsmith Post
      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.

    2. 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.

  14. 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!

    1. thatjeffsmith Post
      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.

  15. 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

    1. thatjeffsmith Post
      Author
  16. 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

    1. thatjeffsmith Post
      Author
  17. 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

    1. thatjeffsmith Post
      Author
    2. 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

  18. 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

    1. thatjeffsmith Post
      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.

  19. 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.

    1. thatjeffsmith Post
      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

  20. 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 ?

  21. 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

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *