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

  1. Avatar
    Alexandre hadjinlian guerra Reply

    Hello Jeff

    Im looking towards a way to programatically call SQLDeveloper, more specifically the export database function to generate the whole DDL for a given schema, with the option to skip data
    I got the impression that its feasible. I gave a quick shot for SQLDeveloper API, but couldnt find it yet.
    have i misunderstood the link below?

    https://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable

    My pet peeve is that DBMS_METADATA is surely great. Hands down, but big definitions gets truncated, and besides this. scripting a bunch of objects which depends on each other (views,functions,procedures, and so on) is not a get-go script

    Way over the top to ask if DBMS_METADATA could be expanded to add this functionality? to generate the right sequence of object definition?

    Thanks a lot for your time 🙂 Appreciated

  2. Avatar
    Sebastian Hurley Reply

    Hi Jeff,

    This might be a slightly strange query but I am currently looking to connect Azure DataFactory to an Oracle APEX Restful Service. I’m happy that everything is working as intended and have confirmed access with authentication in postman.

    However, as far as I can tell, Azure DataFactory is rejecting the authorization procedure to the REST service because the response from APEX has “token_type” as “bearer” and Azure is explicitly looking for “Bearer” (uppercase B).

    The response that APEX provides is as follows;

    {“access_token”:”redacted”,”token_type”:”bearer”,”expires_in”:3600}

    and my error on Azure is

    “The toke type ‘bearer’ from your authorization server is not supported, supported types: ‘Bearer’.

    I have raised a case with Microsoft but thought I would see if anyone has run into the issue before and if there is any method to get around this on the Oracle APEX side? It seems odd that the response would be lowercase if the standard notes it should be “Bearer”.

    Would appreciate any thoughts you might have,

    Seb

    • thatjeffsmith

      It’s NOT an APEX RESTful Service, it’s an ORDS REST API. APEX is just your current front-end for building the APIs.

      if the standard notes it should be “Bearer”
      Please share the standard you are referring to, and I’ll take a look.

    • Avatar
      Sebastian Hurley

      Apologies for messing up the terminology.

      My understanding was taken from the RFC documents here; https://www.rfc-editor.org/rfc/rfc6750.html
      is the reference to “Bearer” across it.

      Admittedly, it does not expressly state that the authentication response should be capitalised, only that when passing the token type post authentication it should be capitalised as “Authorization: Bearer [TOKEN]” (2.1).

      It does seem that Azure takes a strict approach of the authentication response needing to be capitalised as far as I can tell. I suppose my use case is slightly niche and I will continue to follow up with Microsoft but if you do have any other insight it would be appreciated.

    • thatjeffsmith

      You could ask them to file a bug? Hopefully they’ll understand where you’re coming from.

  3. Avatar
    Jean-Claude Hasoon Reply

    Hi Jeff, a quick clarification for me please.
    Is this SQL Developer Web (via ORDS) is not only just for the OCI autonomous database? sorry I am getting mixed info from various blogs on this.
    say I have an on premise RAC database can I have this SQL Developer Web running from it . The data can stay where it is – don’t need to move it into OCI and probably stays on premise. interested in using the latest features regarding scheduling automated jobs and pipelines (chain). Can we grant specific access to a role on only a certain schema(s) etc (ie if different teams are using different schemas – I see why not right?)?
    Or does the desktop SQL Developer has these features already too?
    Thank you in advance.

    • thatjeffsmith

      Is this SQL Developer Web (via ORDS) is not only just for the OCI autonomous database? sorry I am getting mixed info from various blogs on this.
      No, no, no. Which blog or blogs are leading you astray? Let me know so I can fix that.

      If you have exec privs on DBMS_SCHEDULER, you’re going to be able to use our scheduler interface in both the desktop or Web versions. Much of what is in the Web interface is available in the Desktop – including visualization of Scheduler Chains. The Web version is more powerful, has more features than the desktop version.

    • Avatar
      Jean-Claude Hasoon

      Thanks for your reply and answers for my initial confusion, I am all good now after further reading last night.
      I was initially reading them in various order I think and when I first read the one https://www.thatjeffsmith.com/archive/2019/12/sql-developer-web-is-now-available/ and before reading further all the whole details I got this and thought oh this is not good for me
      “Wait, what is SQL Developer Web?
      We built it FOR the Cloud, the Oracle Cloud. It was in available in our first DBaaS offering in OCI-Classic, and now it’s available in the Oracle Autonomous Database. We have plans to make it available throughout ALL of OCI, regardless of where or how your Oracle Database is running, but for now, you can also stage your own copy of ORDS and host SQL Developer Web wherever you’d like.”
      yes so the first sentence ‘We built it FOR the Cloud….” got me off further reading, But then later when i got more time, I read further and concluded I should be able to get this even from my SQLDev desktop – just a matter of getting the right privileges for dbms_scheduler etc to use the visualisation of scheduler chains.
      Yes and I then also read the home page here for SQL Developer Web” and got it confirmed “SQLDev Web (Docs) is available automatically in the Oracle Cloud for Autonomous Database subscribers (presented as Database Actions), but it’s also available for any customer who has ORDS configured for their databases. Some features in SQLDev Web may not be available in the Autonomous Database.”
      So Thank you very much. All good. I just need to convince the DBA team now and try this out if I can.
      Just 1 last question on this. Can I use the Web version to connect to an inhouse on prem database as we do not have OCI databases? probably not straight from OCI but may be on inhouse installed SQL Developer WEB on an app server
      ?

    • thatjeffsmith

      It started in Cloud at first.

      Yes, you can use it for any Oracle Database, anywhere you have access for config and network.

  4. Avatar
    Aditya Saharia Reply

    Jeff:

    I am trying to use SQL Developer 22.2. It starts to load but then stops and another window opens which looks like a text editor.

    I have jdk 11 installed. I have SQL Developer 21 on the machine which work fine.

    Thanks.

  5. Avatar

    Hi Jeff,

    Thanks for the wonderful session. Am trying to use SQL join using ORDS setup. How can I use multiple aliases on same table in ORDS sql query ? Am facing error like “ORA-00918: Column ambiguously defined” . For example have country table which has states and cities maintained in the same table. So want to use join query on the same table with different alias to fetch the countries, states and cities on different inputs. Am unable to define like below.
    Country table has id, name
    select a.name, b.name, c.name from country a inner join on country b where b.id=’STATEXXX’ inner join on country c where c.id=’CITYXX’;

    Here I want to use alias a to fetch country name , alias b to fetch state name, alias c to fetch city name. Something like this. If I use same query on sql developer it gives me correct output without any error.

    Only on ORDS sql query setup gives me this error “ORA-00918: Column ambiguously defined”. Please let me know how to resolve this.

    Thanks.

    Regards,
    Srinivas J.

  6. Avatar

    Hi Jeff,
    On my setup it seems that the auto-completion (ie completion insight) does not work.
    I am on Mac version 21.4.2.018 build 018.1606.

    Would be good for me to be able to use Control + Space.

    My question is, does completion insight work on Macs?
    If so – what setup do I need to set?
    Jennifer

    • thatjeffsmith

      On my Mac with 22.2, it seems to be working as expected.

      To help you i’ll need an example.

      Can you try in a blank worksheet, with the most basic of queries, say

      select * from a — invoke insight here – do you get a list of views/tables in your schema and others starting with the letter A?

    • Avatar
      Jennifer

      Hi Jeff,
      Thanks for your reply and suggestions.
      It seems now it works!
      Sorry for the trouble.
      Jennifer

Write A Comment

RSS
Follow by Email
LinkedIn
Share