Mostly Making oracle easy, mostly:
Search for:
ThatJeffSmith

Ask A Question

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

  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!

7,091 Comments

  1. Avatar
    Mauricio Fernández Reply

    Hi Jeff, when I generate DDL with Oracle SQL*Data Modeler, it is adding “SUPPLEMENTAL LOG GROUP ” clause. I don’t wanted.

    I can’t find where to set up DDL’s generation not include that clause

    In Tools –> Preferences –> DDL there isn’t nothing like that

    Thank you for your help

  2. Avatar
    Janp Reply

    Hi Jeff.

    Is there a way to keep the code outline visible even when there are errors in the code? I really like this feature but it turn out almost not usable when editing the code.

    Thank you

    • thatjeffsmith
      thatjeffsmith Post Author

      We try, but if the code can’t be parsed because of keywords or syntax issues, then we can’t generate the outline. It’s a chicken-egg or Catch-22 problem.

    • Avatar
      Janp

      Maybe parsing pl/sql is harder than parsing, e.g. java, but taking a java class and removing a semicolon anywhere does not break the entire code outline, which is what unfortunately happens when you remove a semicolon in a sql developer editor.

    • thatjeffsmith
      thatjeffsmith Post Author

      I can tell the parser guy he’s doing it wrong but he literally has a degree and teaches uni classes on writing parsers.

    • Avatar
      Janp

      An apology if I was perceived as rude. I am not criticizing your great work but would just like to suggest a way to improve the user experience.

  3. Avatar
    Mark Reply

    Hi Jeff,

    I’m new at using ORDS/JSON and find the Oracele docs on REST query filter options a bit difficult to understand … I saw your beer-related post which was helpful. Could you post or point to a couple examples of stringing together a few AND conditions and one or more OR conditions in a query?

    Can you also clarify additional REST parameters such as pagesize and offset?

    Much appreciated,
    Mark

    • thatjeffsmith
      thatjeffsmith Post Author

      I can do up a more complicated set of predicates for you, no problem.

      What specifically are your questions around limit, offset, and pagesize?

    • Avatar
      Mark

      Thanks Jeff,

      I would like to know about any other parameters for a REST query in addition to query. I thought I read that there was offset and pagesize, but not sure about other parameters. If pagesize is set to 0, can I assume that the # of rows returned will equal the maxPageSize setting that has a default value of 500?

      Thanks again,
      Mark

    • Avatar
      Mark

      Hi Jeff,

      Please don’t forget about a couple more query filtering examples please!

      Like : field1 = a and field2 = b and (field3 = c or field3 = d) and field4 = e

      Thanks again,
      Mark

    • thatjeffsmith
      thatjeffsmith Post Author

      I spent hours on this already. How about you give it a try and let me know where you get stuck?

  4. Avatar
    Dennis G. Reply

    Hi, I’m working in an environment where developers are using TOAD, SQL Developer, and probably something like SQuirreL. I am trying to get everyone using Git for version control. Both SQL Developer and TOAD have built-in interfaces for Git. Are you aware of anyone using them together or any issues trying to get them to work in conjunction with each other as well as possibly a third, non-integrated tool interface? Basically, I wan’t to be able to work in any of them without stepping on each other. I hope my description and question make sense.

    Thanks in advance!

    Dennis…

    • thatjeffsmith
      thatjeffsmith Post Author

      They’re all 3 indpendently developed and produced software products/projects. The thing with folks using different IDEs to contribute code on a project comes down to

    • make sure folks aren’t writing code that only works from their tool. For example, if code is submitted to production via SQL*Plus, then all code MUST be tested for SQL*Plus
    • make sure devs have some sort of coding standards they’re living up to, so that code DIFF reports work fairly well.
  5. Avatar
    JuLayne Reply

    Hi, Is there a way to update an Oracle table by simply typing in the grid view of results after you’ve done a query of the table?

  6. Avatar
    Antoine Reply

    Hi,

    I’m trying to export a table with multiple columns. One of the columns has BLOB in it. The other columns have string of int values in them.

    The BLOB seems to be in the XML format.

    I tried exporting it using the procedure from your blog :https://www.thatjeffsmith.com/archive/2014/05/exporting-multiple-blobs-with-oracle-sql-developer/.

    However, I only have .ctl, .ldr and .sql file in the zip file and I don’t have any file with BLOB data.

    Is there an easy way to export the table with blob data (XML format) using SQL developper?

    Thank you

    • thatjeffsmith
      thatjeffsmith Post Author

      Yeah what you read/tried should have given you what you’re needing with addition files representing the BLOBs in the output directory. If you’re not getting that, it sounds like a BUG.

      I would need your scenario spelled out so I could test it locally to confirm what you’re seeing.

      If you’re moving the data from one Oracle DB to another, then Data Pump is always a great route to travel.

    • Avatar
      Antoine

      Well, finally, I tried the same procedure without checking the compressed option and it did work. Don’t know why the compressed option created a problem.

      Thank you again for you BLOG!
      Antoine

  7. Avatar
    Mark Reply

    I have a table with multiple CLOB columns that I export to a 2003 xlsx file format using SQL Developer. Works great, can save the xlsx as a csv if required. Unfortunately, when exporting as a CSV, then attempting to save as an xlsx doesn’t work as well. CLOBS seem to wrap, etc.

    I want to automate the export of data to an XLSX file — can you add this SQLFORMAT option to SQLcl?

    Thanks,
    Mark

  8. Avatar
    Mark Reply

    Can the delete, put, and post verbs be disabled for an auto-rested table?
    Thanks,
    Mark

    • thatjeffsmith
      thatjeffsmith Post Author

      No, but put a VIEW over your table, and you get what you’re looking for…IF you don’t add a primary key constraint to the VIEW.

      REST enable the view and you have just st the GET endpoints for your data.

  9. Avatar
    Pluto Reply

    I noticed a feature called CONNECTIONS/CONNS in the latest version of sqlcl that lets you import connections from SQL Developer, but it doesn’t seem complete enough to be useful yet. Any chance this is coming in 23.1 and is the timing of the next version linked to SQL Developer’s integration into VS Code?

    • thatjeffsmith
      thatjeffsmith Post Author

      In 23.1, you’ll be able to bring in your SQL Developer connections AND you’ll be able to make a database connection via the name of the connection.

      And when you create new named connections, that will automatically be seen by SQL Developer Next (coming later this year.)

    • Avatar
      Pluto

      Yes that’s awesome! And good to have a name to refer to it by.

  11. Avatar
    Doug Rutledge Reply

    Hi,

    I just upgraded to 22.2.1.234 from 21.4.3.063. When I try to export anything, I get a very long hang (a couple of minutes). Any ideas? I have reinstalled a couple of times, so I am guessing it is something specific to my machine?

    Also, when going to Preferences, Database, and then anywhere, it hangs for several minutes (maybe 10 or 15).

  12. Avatar
    Geoff Reply

    Yes and no to suggestions.

    Used:
    curl -i -k http://localhost:8080/ords/sunseo/sign-in/?username=SUNSEO&r=_sdw%2F

    Where I have defined:

    BEGIN
    ORDS.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => ‘SUNSEO’,
    p_url_mapping_type => ‘BASE_PATH’,
    p_url_mapping_pattern => ‘sunseo’,
    p_auto_rest_auth => FALSE);

    COMMIT;
    END;

    And can see services are published in sql developer.

    And get 404 error…

    curl -i -k http://localhost:8080/ords/f?p=100:101.:0:::::

    gives a 200 ok.

    Am puzzled.

    Thanks for help.

  13. Avatar
    Geoff Reply

    Hi, I have a config built on AWS with an Ords standalone EC2 instance connecting to an Oracle SE RDS instance. Have an Apex app working fine. But can’t get either Web Sql Developer or access a REST published service. Have also tried configuring SQL Developer REST connection and in every case get the 404 error. I know this needs a more in depth analysis and have opened a case with AWS support, but wanted to be sure wasn’t missing any AWS EC2 to RDS connectivity specifics or user privs. Any gotchas that might apply to this config you might be aware of. Many thanks Geoff

    • thatjeffsmith
      thatjeffsmith Post Author

      First things I’d check are
      1. ORDS config, is sqldev web enabled?
      2. SSH into your machine, can your cURL localhost and get a rest API to work?

Write A Comment