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!

6,082 Comments

  1. Avatar
    Jan Carlsson Reply

    Using SQL Developer you can take DDL:s for different tables and take out the data for the same tables. But it’s tedious to repeat the tables every time. There exists a CLI for SQL Developer that I want to use. However, I haven’t found documentation that describes the CLI for SQL Developer (sometimes called SQLcl) in detail. Where is the detailed documentation which for instance describes what happens when you invoke “set ddl segment_attributes on”?

    • thatjeffsmith

      Docs

      SQL> help set ddl
      SET DDL
      SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
      CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
      SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
      BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
      ] | ON | OFF ]

      These are transforms for DBMS_METADATA which are documented here.

  2. Avatar

    OK, here’s what happens with SQLcl. (I am not sure how this will be rendered on this page, though)
    When I connect to a UTF8 instance, the output is returned fine. When I connect to a 1251 local charset instance, it is broken.
    —–
    SQL> select ‘ж’ from dual;

    ‘ж’
    _______
    ж
    —–
    SQL> select ‘ж’ from dual;

    ‘?▒’
    _______
    ?▒

  3. Avatar
    Peter Haastrup Reply

    Hi,

    I have read:
    SQL Developer: BLOBs and the External Editor

    I am looking for a solution to the more basic question:
    I have a BLOB as a field in my oracle database, and I want to extract this
    to a PDF file.

    How is that done ?

    • thatjeffsmith

      With code? In the GUI you can just hit the download/save button and make sure you use a pdf file extension.

  4. Avatar

    Hello Jeff,

    With sqldeveloper Version 20.2.0.175, where is the option to specify the autosave tabs and how often it should save

    Thanks
    Paul

  5. Avatar

    Hey, Jeff,
    We have a problem with character format when it is sent from ORDS to the Oracle DB.
    We have ORDS 20.3 under Tomcat 8.5.
    When we connect to an Oracle 11 DB, text is sent in the local encoding of the DB (not UTF8).
    When we connect to an oracle 19 DB, text is sent as UTF (expected).
    We need to force ORDS to work in UTF8 mode when connected to Oracle 11.
    Is that possible and if yes, how do we do it?

    • thatjeffsmith

      ORDS is a java application, so everything it does by it’s very nature, is unicode.

      I believe you have an issue, and ORDS might even be at fault somehow, but I’m going t need a test case.

    • Avatar
      Branimir

      Yeah, having a test case is quite complex to arrange.
      I was just wondering if ORDS (via jdbc) is somehow determining the character set in which the DB is created. It does say in the ORDS docs that it only works in Unicode.
      We have a database in Russian charset 1251. And we expect from OWA to get the letter in Unicode, but it comes as a single byte. Let’s say Ж (sent from a web form) is coming to the database as C6, but when we connect to Oracle 19 or even to Oracle 11 instance which is created in UTF8, this same letter shows up (properly) as D096.
      We don’t know whether this conversion is happening on DB side or is done by ORDS/JDBC.
      We tried starting tomcat with some java parameters like “-Duser.country=us -Duser.language=en” but at no avail. We also tried setting a global “NLS_LANG=American_America.UTF8” on the Tomcat host.
      There is this ORDS parameter thin.
      Would it help if we change to “oci8”? However there is no info or example in docs what else we need to set to make it working. Right now it says the pool is misconfigured.
      Sorry if all this sounds confusing!

    • thatjeffsmith

      so you’re using the plsql gateway, and you’ve pointed it to a db with charset 1251, and the output coming back is ‘garbled?’

      what happens if on same machine as ords, you connect with SQLcl and run a query against the same database? does the same thing happen?

    • Avatar
      Branimir

      OK, here’s what happens with SQLcl. (I am not sure how this will be rendered on this page, though)
      When I connect to a UTF8 instance, the output is returned fine. When I connect to a 1251 local charset instance, it is broken.
      —–
      SQL> select ‘ж’ from dual;

      ‘ж’
      _______
      ж
      —–
      SQL> select ‘ж’ from dual;

      ‘?▒’
      _______
      ?▒

    • Avatar
      Branimir

      OK, If it is in the DB, then even better. But what should we do with it? How to force OWA to read the values as Unicode?
      BTW, when we were using Webtier with mod_plsql, this all worked fine. We sent UTF8 from the browser and we received UTF8 in the DB, regardless of its encoding. ( then we do validaton and convert to 1251, but that’s another story).

  6. Avatar

    Jeff – We need to write a unix script to call ORDS API and for that we have to store client_id and client_secret somewhere to be able to pass them to script in order to generate token. Is there any way to protect client_id and client_secret as we do don’t want to store them in plain text. Will wait for your feedback Thanks.

    • thatjeffsmith

      I’m sure there are ways, but this isn’t really an ORDS question…there are lots of solutions out there for doing similar with oracle username passwords in unix shell scripts for say SQL*Plus/SQLcl, this would be similar, yes?

  7. Avatar

    Hi Jeff,

    I am using Sql developer 20.2 and, upon disconnection I get sometimes the popup dialog asking me to commit or rollback. The strange thing is that I am not updating or deleting anything. I am sure that I issued only Select statements. How this can be explained?

    • thatjeffsmith

      Best guess is at one point you queried over a DB_LINK, which triggers the session flag that you have uncommitted work.

    • Avatar

      that defintely could be, thank you very much

  8. Avatar
    mary bagir Reply

    Hi Jeff,
    oracle db 12.2+apex 20.1+ords 20.3;
    I register 2 schemas through apex restful services: a1 with database schema a1 and a2 with database schema a2.
    running http://<server_name/ords//a1/metadata-catalog — I see list of all examples and I can test any example;
    running http://<server_name/ords//a2/metadata-catalog — no lists of examples and test any example give error 404;
    I already twice de-registry a2 and registry again. No fix. I checked oradata_metadata schema data looks good. maybe it is some registry in sys or system
    Thanks Mary

    • thatjeffsmith

      APEX RESTful Web Services have been deprecated and soon to be desupported completely.

      You should be moving those over to ORDS…now, based on your URLs it sounds like you’re just just using the APEX Web interface for managing ORDS for a schema, so that’s OK.

      Your URL should be
      http://server_name/ords/a1/metadata-catalog/

  9. Avatar
    Amin Adatia Reply

    Database 19.3 and ORDS 20.2 and APEX 20.2

    Why does it feel as if the connection requests go to Timbuktu and back before a response comes through. I am trying to work only on my Desktop. What settings am I missing?

    • thatjeffsmith

      Try enabling trace for your apex sessions and you can see where the db work time is being spent.

  10. Avatar
    Aris Green Reply

    Is the goal of being able to save an entire database as scripts in version control, to be able to be created in an automated or semi automated reproducible manner through script a realistically achievable good practice?

    It seems that DBAs often painstakingly maintain and nurture base versions of databases in binary form and evolve them in ways somewhat analogous to bakers maintaining a sourdough mother to bake new batches of artesian bread, or abbey monks maintaining rare stains of yeast to make expensive Belgian ale. Lose you starter or yeast, you ain’t backing bread or brewing beer for a while– then when you get back to it, your product might not ever be the same.

    So, deploying new instances means copying and plugging in the base database a new site as a binary. If a developer does not have access to one for local experimentation and testing, the are SOL.

    It seems with DB links; materialized views; cyclic dependencies between oracle column aliases, materialized views, package procedures and other items between schemas make saving and recreating your base DB as code next to impossible.

    Should we be happy just to rely on an previously created base kernel DB to be able to make new ones? I suppose one can always save exported DDL, but will never be able to create a new one easily from scratch.

    What are your thoughts?

    • thatjeffsmith

      Is the goal of being able to save an entire database as scripts in version control, to be able to be created in an automated or semi automated reproducible manner through script a realistically achievable good practice?

      Yes, but it takes work, discipline, and good communication. Our tools are attempting to make this more viable, and we’re spending a lot of time and resources to make it as easy as possible.

      ‘in binary form’ – sounds like you’re talking about a cold backup?

    • Avatar
      Aris Green

      Thank for responding.

      Yes, something like that. The ‘base’ has all the code, possibly static data. Might be a backup that gets restored, or a pluggable database that gets unplugged and plugged back in again under a different root container. I think the latter. My Oracle experience is not that deep and I am learning on my own. I’ve worked with SQL Server and others though. Basically the base is what you need to plug in, restore, etc. into a new server in order to create a new copy of the database. Where the DB and datafiles are all kept and maintained I am not sure.

    • thatjeffsmith

      We have a similar ‘plug’ data model where your database can be cloned for dev/test… etc

  11. Avatar
    Duke van Leeuwen Reply

    Is Database DIFF available through command line to do this on connection a + b for more than 60 schemas?

  12. Avatar

    Hi Jeff,
    I am working on one automation to copy data from PROD to lower environment. I see oracle copy command is used underline by sql developer also. Currently COPY command support only CHAR,DATE,LONG,NUMBER,VARCHAR2.
    But when i ran copy command from sql developer where one of the column have datatype as timestamp it worked but same command not working from sql plus. I want to know how it is working from sql developer when it is not working from sql plus. Does sql developer have some underline logic for COPY command ? Please let me know , I googled lot but no luck.
    Thanks
    Kundan

  13. Avatar
    Duke van Leeuwen Reply

    HI Jeff,

    I tried liquibase within SQLCL it doesn’t work for me tablespace names are committed and there are several other issues.
    How would you go about programmatically apply changes from DEV / TST / ACC / PRD?

    Duke

    • Avatar
      Duke van Leeuwen

      I found the database diff, I’d like to use that just programmatically so I can do that in an overnight build.

  14. Avatar
    Shaounak Shantanu Nasikkar Reply

    Hi Jeff,

    Wondering if you tried to use the JavaFX within Oracle SDDM Scripting. I was trying the same but got stuck in one place. I was trying to execute a simple Hello World example using JavaFX. Please find the code below –

    load(“fx:base.js”);
    load(“fx:controls.js”);
    load(“fx:graphics.js”);

    function copyErrorToClipboard(msg) {
    var clipboard = java.awt.Toolkit.getDefaultToolkit().getSystemClipboard();
    var stringSelection = new java.awt.datatransfer.StringSelection(msg);
    clipboard.setContents(stringSelection, null);
    }
    function start(primaryStage) {
    primaryStage.title = “Hello World!”;
    var button = new Button();
    button.text = “Say ‘Hello World'”;
    button.onAction = function () print(“Hello World!”);
    var root = new StackPane();
    root.children.add(button);
    primaryStage.scene = new Scene(root, 300, 250);
    primaryStage.show();
    }

    try {
    start($STAGE);
    } catch (error) {
    copyErrorToClipboard(error);
    throw new Error(error);
    }

    Based on the Nashhorn documentation, the $STAGE is available by default but when I run the same in Oracle SDDM, I get the error that the $STAGE is not defined.

    • thatjeffsmith

      I have not, the thought never even occurred to me – yet that’s not a good reason not to try 🙂

      If you’re interested in having a go at this, suggest you post this to the Modeler forums.

  15. Avatar
    Amin Adatia Reply

    Using SQL Data Modeler 20.3
    I have several SubViews in the Logical Model.
    How do I generate an All Entities Report for just one SubView

Write A Comment