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!

6,632 Comments

  1. Avatar

    Log4j.jar is a vulnerability. Does deleting this java folder break sql dev?

  2. Avatar

    Great product and customer support Jeff!

    When viewing the error logging page in SQL Developer, how do I determine where to find the source code that is generating an error? For example, I get the same error repeatedly under the source: “o.d.db.DBUtil” When I copy and paste out of the grid, that value is expanded to “oracle.dbtools.db.DBUtil” This error statement is generated

    I’ve diagnosed the problem: a statement issued by SQL Developer is looking for the “type_object_type” column in the data dictionary view ALL_ARGUMENTS — but that column was introduced in 18c and I work on 12c. I just can’t figure out how to find the source code and comment out that column in the SQL statement. I think it’s related to the code editor’s completion insight feature because errors are logged when I am typing into the SQL worksheet, even prior to executing a command.

    Cheers and thanks for all the hard work your team does to provide an excellent product at zero cost to Oracle users.

    • thatjeffsmith

      Can you grab 21.4.2 and try again?

      I’m connected to a 12.2. instance and I’m not seeing any queries to all_arguments or errors around type_object_type. What are you doing when you see this query pop up?

  3. Avatar
    Muhammed Emad Reply

    Hi Jeff,
    Update regarding the below case:
    We have figured out the issue,some tables have policies defined on it , this causes our problem , we exempted the user X (which is REST enabled schema ) from the policy and the issue was solved,but this is not an applicable solution because these polices are important and required in our business , please note that this is only happening the ORDS roles/privileges are defined.
    So, in conclusion, there is a conflict between ORDS roles and privileges and the policies defined on the table.

    Below the case:

    We are having a strange issue with ORDS we used to run ORDS 19, and then decided to upgrade to ORDS 21.4, the installation went fine and our stored pl/sql packages ran as the suppose to , but after we build our previous role and privileges for our authentication OAUTH 2 some of our REST services are returning null objects although the HTTP response code is 200 (ok), this is only happening when i built the role/privilege in the new ORDS

    – My rest service PL/SQL :

    DECLARE
    OUT_OBJECT CUSTOMER_TYPE;
    REF_CUR SYS_REFCURSOR;
    RETURN_STATUS NUMBER;
    BEGIN
    OUT_OBJECT := CUSTOMER_TYPE ();
    PW04CUSA0.W04CUSA0 (INP_TELL_BRA_CODE => :INP_TELL_BRA_CODE,
    INP_TELL_ID => :INP_TELL_ID,
    OUT_CUSTOMER => OUT_OBJECT,
    RETURN_STATUS => :RETURN_STATUS);

    OPEN REF_CUR FOR SELECT * FROM TABLE (OUT_OBJECT);

    :CUSTOMERS := REF_CUR;
    END;

    when :CUSTOMERS is an out string of type RESULTSTRING

    -and the response i get from POST MAN: {
    “CUSTOMERS”: []
    }

    – My role / privilege:

    BEGIN
    ORDS.create_role(
    p_role_name => ‘360_role’
    );

    COMMIT;
    END;
    /

    DECLARE
    l_roles_arr OWA.vc_arr;
    l_patterns_arr OWA.vc_arr;
    BEGIN
    l_roles_arr(1) := ‘360_role’;
    l_patterns_arr(1) := ‘/JET_360/*’;

    ORDS.define_privilege (
    p_privilege_name => ‘360_priv’,
    p_roles => l_roles_arr,
    p_patterns => l_patterns_arr,
    p_label => ‘360 Data’,
    p_description => ‘Allow access to the 360 data.’
    );

    COMMIT;
    END;
    /

    Note:The reason we decided to upgrade to ORDS 21.4, is that we noticed the performnace is very bad when we went live with our production environment.
    Note:Our database version is 11G and our application server is TOMCAT 8.5.58 and java JDK 1.8.0_261

    Also, is there any prerequisite for ORDS 21.4 that might cause such an issue.

    Your help is appreciated
    Muhammed Emad

    • thatjeffsmith

      I need more info on this, like an example of your table and policy.

      some tables have policies defined on it

    • Avatar
      Muhammed Emad

      Dear Jiff,
      We have opened a SR with Oracle and they responded that “There is a existing bug for similar issue and it is not yet fixed BUG – ORDS OAUTH TOKEN EMPTY RESPONSE IN ADB(S) “.

      Best Regards.

  4. Avatar

    Hi Jeff
    Your blogs are brilliant and extemely helpful. I want to show my students how they can do minimal formatting with oracle sql developer. The example I am using is as follows: select ‘A’ || chr(10) || chr(13) || ‘B’ from dual; what I am getting is all on one line as follows: “AB” instead of
    A
    B
    Any help you can offer would be outstanding
    thanks in advance

    • thatjeffsmith

      The query results try to show you as much as possible, on a single, line, in the Excel style data grid. If you pop open the single record view or double click on the cell to get the text editor, you’ll see the new lines. Or, if you just run your query as a script, you’ll get exactly what you’re looking to show your students.

      Do you mind if I ask where you’re teaching?

    • Avatar

      I am currently at Northern Virginia Community College (NVCC).
      Just as an fyi, I have taught oracle and pl/sql at Drexel University for approx 18 years.
      Also, teach at LaSalle University
      Of course NVCC is the only out of state location.

      I have one more question if you don’t mind.
      I am using sql developer (21.4.1 I think because I am not on my teaching computer) and when I go to create an erd, per your outstanding blog, I don’t get relationship lines. I checked and have PK, FK, etc all correctly defined and I think I have tried ever option available…again any assistance you can give would be greatly appreciated. Many thanks in advance.

    • thatjeffsmith

      Ping me when you get back to your teaching computer, if the foreign key constraints are there, when you import your objects from the database, those lines should be drawn.

      Do you have access to a sample schema like HR, SH, or OE? Testing those would be a good way to see if there’s a problem with your environment, our software, or something else.

    • Avatar

      Jeff
      I will log on to it around 5 pm east coast time today.
      we can screen share if you like
      somehow, since I am using xe (because it has a smaller footprint and easier for the students to install by themselves) none of the standard example (play) databases got loaded down.

    • thatjeffsmith

      Those should be on the latest XE by default…and sorry, I don’t have the ability/bandwidth to to 1:1 support/zoom calls…

    • Avatar

      Jeff, I agree it always used to be part of, I think, all versions of oracle. Kind of dismayed that the sample databases did not download with xe. I used all the defaults so I have no idea what went wrong.

  5. Avatar

    When I click on a function in SQL Dev, I only see “create or replace” and nothing further. In Toad, I do not have this issue. What could be the problem? Thanks.

  6. Avatar
    Audun Hansen Reply

    Hi!

    We are using SQL*Developer 21.1.4

    Since we are using proxy-users, our private user only has create session privilege.
    The trouble is when trying to change the password using the Reset password function in SQL*Developer.
    Logged in as my own user (not with proxy user) I only got “ORA-01031: insufficient privileges” hven changing the password. I’m quite sure it worket in an earlier version.
    Changing the password through sqlplus and the password function works fine.

    Regards
    Audun

    • Avatar
      Audun Hansen

      SQL*Developer is the correct version v. 21.4.1.349

  7. Avatar

    Hi Jeff

    I’ve followed the hints in your note Oracle SQL Developer: Make it Run Faster! but comparing the performance of even the most simple SQL statement between Version 20.2.0.175 and Version 21.4.1.349 , V21 is much much slower .
    Thinks taking sub second in V20 are taking multiple second in V21 to return.
    It does not look like it’s DB time, but rather the time in the UI

    Are their are other tips to for making SQL Developer faster?

    • thatjeffsmith

      You’re running into a bug with 21.4 and 21.4.1

      Go into preferences, and the Insight page. Disable the last check box, Semantic…, Click OK, restart SQLDev.

      We’ll have a 21.4.2 out next week to remedy this.

Write A Comment

RSS
Follow by Email
LinkedIn
Share