Mostly Making oracle easy, mostly:
Search for:

Ask A Question

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.

  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,793 Comments

  1. Odile Reply

    Hi Jeff,

    I’m trying desperatly to find an explanation in the Oracle documentation regarding the exclusion of views including database links. Oracle specifies that these views are absent but does not give the reason. Even if remote dependencies are not “managed” by Oracle (except rpc), existing dependencies between an object and a dblink are included in the dependencies view of the Oracle dictionary. I do not understand the reason for this exclusion.
    One explanation could be the lack of management by Oracle. “These views or procedures and their dependencies to the database link are not managed by Oracle: In such cases, the absence of dependency management is preferable to unnecessary recompilations of dependent objects.” (Dependencies Among Other Remote Objects, Oracle documentation) The problem is that procedures and their database links are included in the dictionary dependencies view while views are not. This explanation is therefore not complete or accurate. Oracle treats view in different way and I cannot find an answer.

  2. Robert humbert-Hale Reply

    Hi Jeff. We are using SQLDeveloper 3.1.07 on Win7-x64 machines., We will be upgrading to Win10-x64. Will v3.1.07 run on Win10? If not, do we need to sequence through versions until we get to v19.2. Will 19.2 run on Win7 so we can upgrade before doing the Win10 upgrade? Note: I do not use SQLDeveloper – I’m merely tasked with determining upgrade options. Thanks much! Robert.

    • thatjeffsmith Post Author

      It’s so old, WIN10 didn’t even exist at that time.

      Just upgrade everyone to SQLDev 19.1/2 or even 19.3 depending on your upgrade timeframe.

      You’ll need a more modern Java too, like 8 or 11/12

  3. Sachi Reply

    How do I export an entire table except for the BLOB column using SQL Developer Cart functionality. If i use the export wizard functionality then I can specify the columns to be exported, but then I need to automate it and not sure how to do that without Cart.
    I can’t use DATA_PUMP and external tables concept as i do not have authorization for that

    • thatjeffsmith Post Author

      create a view, that does a select on every column but the blob, and then export the view in the cart

    • Sachi

      Forgot to mention that we don’t have create view authorization as well. Is there any other way ?

  4. Jim R. Reply

    When giving SQL Developer to a new user for the first time, is there a way to set default preferences (i.e. path to TNS_NAMES, read-only mode, object types to filter, max editors, etc)? This would allow us to build a company standard rollout that works best for our environment. Users could customize from there if they choose.
    Thank you.

    • thatjeffsmith Post Author

      Each user gets their own set of preferences.

      You just need to give your users a pre-canned set of those files.

      However, I’m not sure what you mean by ‘read only?’

  5. William Tucker Reply

    Good stuff. Not a lot about JSON generation with Oracle SQL Developer, any where. Must not be possible. I don’t see a JSON package in the “Features” list though I do find XML schema listed…. There are no online examples of anyone using SQL Developer to generate JSON from Oracle SQL Developer….so there must not have been a support package written for it….. Doesn’t seem too smart to ignore those people that have purchased Oracle database and tools to leave them hanging for a number of years….. What gives?????

    I have worked for the FAA, and several fortune 50 companies…in Washington DC. This isn’t a trivial thing, advertising something without offering support in practice… This is very 80’s….

    • thatjeffsmith Post Author

      There’s two things, the database doing the JSON generation – and that’s just SQL Developer exercising those package/function calls, and then there’s client side JSON generation.

      Here’s what I’m talking about:

      I know that we have lots of examples around Oracle DB and JSON w/ or w/o SQL Developer. What exactly are you looking for, and I’ll send you the details.

  6. Anderson Bestteti Santos Reply

    Hi Jeff,

    I’d like to congratulate you and your development team for the Git’s support implementation in SQL Developer Data Modeler 19.2.0. I am planning a SQL DM hands-on session for 80+ people, among developers and system analysts, and the integration with Git could not arrive on better time. The company just switched from Subversion to GitLab and the Git native support in DM will be super handy.

    Best,
    Anderson.

  7. Ali Sheikh Reply

    Why can’t I use the “/” at the end of the package in SQL developer to compile my package. Its becomes so difficult when you are switching between SQL plus and SQL Developer. Forward slash “/” works fine in the worksheet but doesn’t work in the package editor.

    • thatjeffsmith Post Author

      Because that’s not valid PL/SQL – it’s a SQL*Plus command. And when you do that in a SQL Worksheet, you’re effectively running a SQL*Plus script.

      When you’re in a Code Editor, you’re changing the PL/SQL code, directly. And the ‘/’ is illegal there, hence why it doesn’t work.

  8. Jim R. Reply

    Probably the most popular use case is just browsing objects. When selecting views or tables (or most objects), the display tab on the main window is re-used. However, when browsing through code (proc, pkgs, functions), a new tab is opened each time. Can the code tab be re-used in the same manner?

    In many cases the login user will not own objects in the database they are browsing. Is there any way to not display all the object types under my user when I login, since they’re all empty? OBJECT_COUNT > 0 works for Other Users but there doesn’t seem to be any customization for current user.

    Are there shortcuts for collapsing current connection, or collapsing all other connections in the tree view, or going up/down a level?

    Wondering if there are some options I’m missing? Thanks again, this blog is a great resource.

    • thatjeffsmith Post Author

      Preferences > Code Editor > Max Open PL/SQL editors > 1

      The only way to hide schema level object navigators is to permanently disable them in the preferences (Database – Navigation Filter).

      Left/right arrow will collapse/expand, down arrow will navigate, but you can also KB your way from one type of object to the next, as well as to the contents of those nodes.

  9. Chase Marler Reply

    Hey Jeff-
    Seems like there’s a potential bug running around that has to do with setting the appropriate Java environment in a Linux OS. This issue is still present in the latest release as far as I can tell: https://community.oracle.com/message/15051625#15051625

    Seems like this has to do with checking to see if the server is a part of an Application Development Environment, but the logic looks like its stepping on other common server configurations. Any chance Oracle Devs can find some different logic for checking if this is ADE or not or should we modify the sql file and maintain a custom build if we need to?

  10. Chris Gould Reply

    Hi Jeff –
    Not sure if this is the right place to post, but I upgraded sqldeveloper recently from 19.1 to 19.2 on two macs (an older one running OSX 10.13.6 and a brand new one running 10.14.6). However, 19.2 takes more than a minute to connect to a schema before it shows the tables/views/other objects in the schema. 19.2 also seems to be very heavy on CPU while this is going on. With 19.1 the schema connections happen almost immediately. This is the same on both machines.
    I’ve reverted to 19.1 and connecting to the same databases is virtually instantaneous as it always has been before.

    The databases I’m connecting to are all versions of Oracle XE (11 and 18) running on a mix of platforms (natively on Linux and also in Oracle Virtualbox VMs on the macs themselves).

    Is there any known issues around this? Unfortunately as it stands, the schema connections with 19.2 are just too slow to make it useable.

    Thanks
    Chris

  12. Trey Stevens Reply

    Hello Jeff,

    I’m not a SQL developer but I’m trying to get an answer regarding a security issue I’m working and have hit dead ends in all my searches. Our McAfee antivirus has a rule that blocks the creation of a file with a “.locked” extension. In the logs I see reference to the SQL Developer Performance Log (located in the o.ide\diagnostics directory) has a .txt.locked extension. I think this is expected behavior for this log but need confirmation from an expert. Can you help by telling me if that this is normal or not?

  13. Michael Taylor Reply

    I’ve really found this site useful for deep tricks on using/sharing domains in oracle sql data modeler and other tips. Do you know how we can easily share custom Types between users? For domains we can use a domains file as you outline in your article but I cannot locate where/how custom types and mappings are stored – each time we upgrade the tool, it seems that the custom types are lost and have to be reentered.

  14. John Reply

    Hi Jeff,
    If I export the DDL for a specific table, I don’t see anything in the DDL output for the synonyms on that table, nor do I see any option under “Export DDL” to select synonyms.

    Are synonyms not included in the DDL output or am I missing something?
    Thank you.

    • thatjeffsmith Post Author

      It’s a bug. If you have dependent objects enabled for the export, synonyms on that object should be included. It’s not. I’ll file a bug for you.

  15. Jim Reynolds Reply

    Former TOAD used here. SQL Developer has really caught up and this is a great resource for learning more. Thanks for all your work! A few questions:

    1- Is there any way to make Schema Browser the default view (or at least auto-open)? This is overwhelmingly the preferred view in our company, even with all the keystroke shortcuts for getting around in the tree.

    2- Schema browser provides no drill-down into the objects like the tree view. For example, you don’t see table columns and details of packages when viewing from SB. This makes for an inconsistent user experience, different functionality depending on how you browsed to the object.

    3- Filtering schemas and objects in schema browser is less robust than the tree view. For example, in the tree view you can combine multiple conditions (allow SCHEMA1 or SCHEMA2) and object_count > 0. Use case: in databases with hundreds of users but only a few with objects, users want to customize by DB which schemas they will browse. Filtering currently appears to be sticky by DB which is great.

    4- Is there a way to customize the data results grid beyond zebra-colors? Looking to set the zebra colors, use a font different than the editor, tweak column sizes based on type, etc.

    Thoughts? Using 18.2 for reference.

    • thatjeffsmith Post Author

      1 – No
      2 – Correct
      3 – SB actually gives you more filtering options, you can have a global fuzzy filter at the bottom in addition to the regular filters
      4 – I wish, but no.

      More on requests 1-3…the schema browser is ok if you don’t like the tree, and believe me, I understand why many people do not like trees, especially nested trees. I recommend, to folks that bring this up, that:

  16. RAMESH P K Reply

    Hi,

    We are running an application in the local network which needs an Oracle database instance on which the data is written. We have a oracle cloud instance and also a database instance running. I am unable to connect it to our local application. Any suggestions..

  17. Dana Day Reply

    Hi Jeff,
    Can you do a remote presentation on SQL Developer? I work for a multi-billion dollar company, with many of the IT staff involved in Oracle. I reviewed some other SQL developer slide shows. I think we would benefit from a remote presentation.

    Thanks,
    Dana

  18. Stefan Heublein Reply

    Hi Jeff,

    I am using Windows 10 (64 Bit) and SQL Developer 19.1
    My sql files are associated with SQL Developer.

    When I click on an SQL file SQL Developer is opening and the file is shown in SQL Developer.
    When I click on another SQL file, a “working sign” is shown for a second and then nothing.
    The file is not opened.

    Dragging and dropping the file into SQL Developer worksheet is working, but that is a bit cumbersome.
    The problem was discussed in different forums, but no general solution was given.

    Do you have some magic to share?

    Kind regards
    Stefan

  20. Dean Miller Reply

    trying to post an ORDS rest service that executes a procedure with one field being an output type of table. Not having much luck. (405 error). So the procedure being executed has an input field for part number, …supposed to return back in the field with table type the org code and description (so there can be multiple rows returned), another output field is the count of how many rows are in the table.

    Here’s the code from postman:

    setUrl(‘http://oracletest.ofsbrands.com:7203/ords/orbit/dpm_ords_test3/’);
    $request->setMethod(HTTP_METH_POST);

    $request->setHeaders(array(
    ‘Postman-Token’ => ‘a10c51ce-8c25-4353-9507-1b4b00ce1f61’,
    ‘cache-control’ => ‘no-cache’,
    ‘Authorization’ => ‘Basic b3JiaXRfdXNlcjpvZnM=’,
    ‘Content-Type’ => ‘application/json’
    ));

    $request->setBody(‘{ “p_part”: 1052168 }’);

    try {
    $response = $request->send();

    echo $response->getBody();
    } catch (HttpException $ex) {
    echo $ex;
    }

    Any advice appreciated…thanks.

    • thatjeffsmith Post Author

      What’s setup on the ORDS side for your service though? The 405 indicates you haven’t declared a POST handler for /ords/orbit/dpm_ords_test3/

      Is this a an Auto PL/SQL enabled procedure, dpm_ordstest3?

    • Dean Miller

      Forgive my use of terminology, but dpm_ords_test3 is a stored procedure that has been REST enabled (using SQL Developer)…the object alias is the same name…and the authorization required checkbox is unchecked. Hope that is what you are looking for. Note that I can run a similar procedure where the output variable is not a table type.

      Here’s the procedure script:

      CREATE OR REPLACE PROCEDURE dpm_ords_test3 (p_part IN VARCHAR2, p_result OUT dpm_test.test_tbl_type, p_count OUT NUMBER)
      IS
      x_count NUMBER;
      BEGIN
      DBMS_OUTPUT.put_line (‘start’);

      FOR rec IN (SELECT segment1,
      apps.ofs_get_org (organization_id) org,
      description
      FROM apps.mtl_system_items
      WHERE segment1 = p_part)
      LOOP
      x_count := NVL (x_count, 0) + 1;
      p_result (x_count).org_code := rec.org;
      p_result (x_count).description := rec.description;
      END LOOP;

      p_count := NVL (x_count, 0);
      END;

    • Dean Miller

      fyi, I wasn’t able to return a table-type field from a stored procedure, but I was able to return a system cursor (SYS_REFCURSOR) field …which will do what I need.

  21. Dagmar Reply

    “How to maintain one central aliases.xml file?”

    Hello,
    I have installed sqlcl centrally on an NFS share for all of my 8 database nodes (Linux), so I have to update and install it only once.
    But I could not find out, how to maintain a central aliases.xml file. I want all aliases to be present on all database nodes.
    Symbolic link didn’t work, hardcoding SQL_HOME (pointing to central directory) in file ‘sql’ didn’t work.
    Any ideas? Has anybody found a solution?
    Regards, Dagmar

    • thatjeffsmith Post Author

      You need to set for the jvm this property:
      AddVMOption -Duser.home=/central/config/dir/sqlcl

  22. Rasik Reply

    Hi,
    I am trying to automate the following things:-
    1) automate the query and send its output to the excel/csv file .
    2) Send the file generated in the above step to user via mail.
    Can you please help me with scenario.
    Note- Using Oracle Sql developer.

  23. T_Van Reply

    Quick question on advanced format, custom format: I’m used to using Toad and we’re switching to Oracle SQL Developer (18.3). For any query, Oracle SQL Query Analyzer code formatter puts spaces before and after any period with a field name and alias. For example, if I have a piece of code that says “where ipa.ipa_id = clm.ipa_id”, when I hit Ctrl-F7, it gives me “where ipa . id = clm . ipa_id”.

    I’ve done searches all morning as to how to remove these spaces before and after the period and can’t find any help on this. Any help would be greatly appreciated!

  24. Keith Reply

    Is there anyway to get the CTRL-Click, when used on a view, to tunnel to the table and show the data from there?

Write A Comment