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,615

  1. Hi Jeff,

    I am not able to run this statement in the Oracle APEX 18.1 , in page designer, but working perfectly in the SQL commands window –
    select distinct instance_number D , instance_number r
    from SYS.dba_hist_snapshot
    order by 1;

    It throws the error “table or view does not exist”

    Would appreciate your help on how to approach this issue.

    Thanks,
    Rads

  2. Is a select sql statement available which can report a list of objects and their corresponding types which are referenced in a specific sql statement of interest?

    Thank you.

    1. thatjeffsmith Post
      Author
  3. hi Jeff

    how do I create a master-detail report?

    like toad has an option where you choose a master table and then a child table.

    is there something in the pipeline for such functionality?

    it would be great if it is.

    regards

    1. thatjeffsmith Post
      Author
  4. SSH Hosts
    Hi Jeff, is there a way to export and import SSH hosts connections on SQL Developer?
    Should be nice to have this feature on next release.

  5. Hi Jeff.
    There is some way to “compress” the json that returns ords (I mean, without indentation or line breaks).
    When reviewing in postman the response headers ‘Transfer-Encoding’ is chunked.

    PD: I build the json from plsql with the apex_json package.

    Thank you.

    1. thatjeffsmith Post
      Author
    2. Hi Jeff, thanks for the reply.
      What I do is build the json manually with the apex_json package. I mean,
      apex_json.open_object;
      apex_json.write (‘a’, ‘value’);
      apex_json.close_object;
      Then from sqldeveloper I create the Modules, URI Templates and Resource Handlers.

    3. thatjeffsmith Post
      Author
  6. How do you get the list of tables you have access to to show up in the Tables object under each database? I click on the PLUS sign next to Tables to expand but there is nothing showing.

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  7. Hi Jeff,
    I was executing a Stored procedure in Oracle SQL developer. I enabled the DBSM output to monitor the logs in my PLSQL script. It took around 2.5 hours to complete running the script and meanwhile the SQL developer window got frozen. From another session i could run some sql scripts and made sure that the execution of the stored procedure is over(this is an archiving process where a bulk set of data get moved to archive tables and gets removed from the original tables so by observing the original tables i can decide whether the SP is completed running or not). The problem is since my original SQL develoepr window where i ran the SP having DBMS output on freezed i want to know is there a way that i can view them from another session. Will it be saved in something like a log file.
    Thank you

    1. thatjeffsmith Post
      Author

      Is the main query still busy? can you open a worksheet and run a query on the same connection you executed the stored procedure?

      Most folks will write messages to a logging table as access to dbms_output messages is generally not ideal for real world scenarios, although quite useful in dev/test

  8. Hi Jeff,

    The company I work for there are some developers that still use SQL Developer 3.2 (too old I know). Here is the point, when I finally have got them to SQL Developer 18.3.0 we learned that the export connection file from SQL Dev 3.2 isn’t recognized by SQL Dev 18.3 import connection tool. When I tried to import the connection file, SQL Dev 18.3 says: No connections are defined in C:\TEMP\CONNECTION_SQL_DEV32.xml

    Jeff, is there a way to import the exported connection file from SQL Dev 3.2? If not, is there a workaround to overcome this issue?

    Thank you in advance for your help!

    Regards,
    Anderson

    1. thatjeffsmith Post
      Author
  9. Hi Jeff,

    I did some changes to custom format in V18.3. After applying changes format does not work and i cannot load format window in preferences tab. How do i reset my changes. ?

    1. thatjeffsmith Post
      Author

      Can you share what the changes were? Then we can file a bug and make sure this same change doesn’t break others’ formatting rules.

      You might need to delete the format.arbori file in your AppData SQL Developer system18.3 directory.

  10. Hi Jeff,

    A question regarding the formatter.
    I am trying to get the first (named) paramater in a function or procedure call to align with the rest of them, but I cannot seem to get that working.
    e.g.:

    begin
    do_something (
    p_param1 => ‘value1’
    ,p_param2 => ‘val2’
    ,p_param3 => ‘val3’
    );
    end;

    where I would like it to be:

    begin
    do_something (
    p_param1 => ‘value1’
    ,p_param2 => ‘val2’
    ,p_param3 => ‘val3’
    );
    end;

    Can you help? Thanks in advance

    1. thatjeffsmith Post
      Author
  11. Hey Jeff,

    I have another one for you. I REST enabled a table that has sysdate set up to fill the column with a default value and it’s set to not null. I get an error when making a POST call. Any ideas what’s up?

    Thanks!,

    Wayne

  12. Hi Jeff,
    I have an APEX app, ORDS and Glassfish. I need to access this app through an Apache Web Server on DMZ using Reverse Proxy. I couldn’t find any significant documentation so could you help me with some tips?

    Thanks.

    Diego.

    1. thatjeffsmith Post
      Author
  13. Hi Jeff,

    If I’m running on 11g using ORDS. How would you suggest I post a record to a master table and return the sequence created PK_ID to be used as the FK in the detail table POST?

    Wayne

    1. thatjeffsmith Post
      Author
  14. Hi Jeff,

    Will the new Oracle Java licensing cost policy impact those of us using SQLDev? Specifically if one of our users adhoc updated the version of Java shipped with SQLDev+Java download bundle.

    Tim…

    1. thatjeffsmith Post
      Author
  15. Hello again,

    Thanks for the help last time, we got the SYS password and everything worked well and I’m testing out ORDS and its working great, One Question I have I can’t seem to find the answer to online,

    In I GET Collection Query there are “Links” for next, prev and so on, but I would love to have a “$last” but that is not by default, is there any way to add a link to the “Base” links?

    I know how to add $link to each item in the items resaults, but I want to the links of the base json object like this image shows https://imgur.com/a/kDwRaPa

    1. thatjeffsmith Post
      Author

      The problem is that we do not know how many records are in the result set – we read them one page at a time, plus one record. If the +1 record is not there, we know we’re at the end.

    2. So there is no way for me to do that manualy in my GET handler?
      Add a “$last” under “links”?

      it would be easy to get the offset number for the last page by Floor(rowCount() / :offset) * :offset

    3. thatjeffsmith Post
      Author

      Right, you’d just use sql to count the number of rows and then join/union that to your main query and add a link to the end that does the page with the proper offset

      It can take as long to do a rowcount as it does to run a normal select, so you’d be potentially doubling the amount of time on the call.

    4. thatjeffsmith Post
      Author

      I take it back, I dont’ think you can do this..can’t think of a way to do this in SQL, you’d need to use PL/SQL and there’s no paging for PL/SQL, only SQL

  16. Hi Jeff:
    We user SQL developer to export DDL from the and save the output in separate directories. We have been noticing that it only creates directires for database links, sequences, tables and types for a particular user that has DBA role.

    but it works for other userids without DBA role. Is there anyway to debug this issue ?
    Thanks

    1. thatjeffsmith Post
      Author
    2. Thanks Jeff for the response. All the individual object types open without any issues in sql developer and I can see the DDL. Is there any way to trace the export queries that SQL developer runs ( in debug mode) and find out what is running and what is failing that is causing not all types to be exported in their own individual directories.

      Thanks

    3. thatjeffsmith Post
      Author

      So looks closer to a bug on our side now…

      no need to do a trace, you can see all the SQL/plsql we submit on the View – Log panel. There’s should be a page there that says ‘Statements’ – look at that.

  17. Hi Jeff,
    many thanks for your very informative posts and replies!
    Need your help regarding using Oracle rest data services 18.3 with Transport Layer Security (TLS) – is there a manual or just an example how to set up them to work together?

    1. thatjeffsmith Post
      Author

      You can follow the directions in the install guide regarding standalone and SSL, paraphrasing here:

      …After doing this, you can include the path to yourdomain.der when prompted by Oracle REST Data Services, or you can modify the following entries in conf/ords/standalone/standalone.properties:

      ssl.cert= ssl.cert.key= ssl.host=yourdomain
      Also, ensure that jetty.secure.port is set.

      Otherwise if you’re using WLS or Tomcat, that happens at that level, and you just tell ords NOT to accept requests on http, only https

Leave a Reply

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