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

  1. Hey Jeff,

    is there an extension to format sql files? My team is struggling to keep a standard formatting for these code. We would like a prettier for SQL

  2. Hi Jeff,

    I have a problem with generating a swagger document in oracle Apex. We create a GET handler, which consists of code where the data is parsed:

    SELECT
    JSON_OBJECT(
    ‘name’ VALUE JSON_OBJECT( ‘ID’ VALUE ID,
    ‘FIRST_NAME’ VALUE FIRST_NAME,
    ‘LAST_NAME’ VALUE LAST_NAME
    ),
    ‘data’ VALUE JSON_OBJECT(
    ‘CITY’ VALUE CITY,
    ‘BIRTH_DATE’ VALUE BIRTH_DATE,
    ‘COUNTRY’ VALUE COUNTRY,
    ‘POSITION’ VALUE POSITION,
    ‘COMPANY’ VALUE COMPANY
    )
    ) AS json_data
    FROM test_data_generator

    After generating the swagger, we get an incomplete document that contains only :

    {
    “items”: [
    {
    “json_data”: “string”
    }
    ]
    }

    Can we add something before ours code in hendler to swagger correctly process our data?

    Please help, we have a lot of code and rewriting it is not an option.

    Thank you in advance!

    • For ORDS to do that, it would have to inspect the nature of your SQL statement.

      For the moment, you wouldn’t need to rewrite your rest api, but amend/update the swagger we generate from the api.

      Maybe we can do more however, please drop me a line at [email protected].

  3. Hey Jeff,

    I get a “Error: Authorization failed or requested resource not found” with the source “Oracle SQL Developer Extension for VSCode” when I attempt to edit my Connection Name or delete it in the SQL Developer VS Code extension.

    I’m guessing it’s a bug??

    • Sounds like it. Those connection directory/files are here, for Windows
      C:\Users\YOU\AppData\Roaming\DBTools\connections\

  4. I’ve recently started using the MongoDB API via ORDS, and can use Mongo Shell from a client tool (Compass, in this case) to create what it thinks is a MongoDB collection, but instead creates an Oracle table (a “JSON Data Store”). This collection/table is then visible from the tool. For the solution that we’re looking to use this for, I’d ideally be “pre-creating” such JSON Data Stores in the database, for the application that is being migrated away from MongoDB to be able to resume working against in Oracle. In SQL Developer, I generated a SQL script for the JSON Data Store table, changed its name, and created a second table. However, this doesn’t show up through the MongoDB API. Is there something else that is configured in the Oracle database back-end by the MongoDB API when a “db.createCollection” type of statement is run? I’d like to just take that same basic table form, as generated by SQL Developer for that first “properly-created” table, and put together a script to pre-build my empty JSON Data Store database. Does that make sense, or is it something that *can* be done?

    • Josh Spiegel

      “JSON Data Store” is not a term I’m familiar with but from the context I think you are referring to JSON collections and you would like to create them from SQL in a way that they are accessible from the Mongo API.

      For Oracle Database 23.4 or later, from SQL you can execute:

      create json collection table mycol;

      For Oracle Database 19c->23.3, you can execute the following:

      declare
      col SODA_Collection_T;
      begin
      col := dbms_soda.CREATE_COLLECTION(‘mycol’,

      {
      “contentColumn” : {
      “name” : “DATA”,
      “sqlType” : “BLOB”,
      “jsonFormat” : “OSON”
      },
      “keyColumn” : {
      “name” : “ID”,
      “assignmentMethod” : “EMBEDDED_OID”,
      “path” : “_id”
      },
      “versionColumn” : {
      “name” : “VERSION”,
      “method” : “UUID”
      },
      “lastModifiedColumn” : {
      “name” : “LAST_MODIFIED”
      },
      “creationTimeColumn” : {
      “name” : “CREATED_ON”
      }
      }’);
      end;
      /

  5. Pratik Swami Reply

    Hello Jeff.
    I am trying to invoke a UDSA exposed rest API from OIC (Rest Adapter to be specific).

    https://mpr.datamart.ams.usda.gov/ws/report/v1/cattle/LM_CT100?filter={“filters”:[{“fieldName
    “:”Report%20date”,”operatorType”:”EQUAL”,”values”:[“01/10/2018”]}]}

    The API itself has multiple filters (derived from JSON) and work fine over the browser or on an API client like Postman.
    However, I am unable to model the query in my OIC rest adapter trigger for the same.

    I was trying to pass the filter condition it as a POST request body, but that is not permitted.

    Any pointers?

  6. David Barth Reply

    Hello Jeff, I am hoping that you can help me reolve a startup issue with ORDS. I just installed ORDS 24.1 on a new RHEL8 server which is running tomcat and when I try to access the ORDS url I get a 404. The tomcat localhost log has these messages
    “GET /ords/ HTTP/1.1” 302 5
    “GET /ords/_/landing HTTP/1.1” 404

    The ORDS install log output looked all great, no errors so at a loss for where to go next.
    Thanks for any help,
    David Barth

  7. I am unable generate DDL if object definied in DB with lowercase letters. Please see:

    SQL> version
    Oracle SQLDeveloper Command-Line (SQLcl) version: 24.1.0.0 build: 24.1.0.087.0929

    SQL> select count(1) from dba_tables where table_name = ‘urls_config’;

    COUNT(1)
    ___________
    1

    SQL> ddl urls_config
    Object urls_config not found
    SQL> ddl “urls_config”
    Object urls_config not found
    SQL> ddl OWNER.”urls_config”
    Object OWNER.urls_config not found

    Do know maybe any workaround for this annoying thing? Thanks & Regards!

    • > Thanks, i’ll file a bug.
      Thank you Jeff! This will help in the future.

      > Workaround – don’t use case-sensitive object names?
      Yes, this could help. Or you can consider to surround object name with quotation mark like in typical “OWNER'(dot)”object_name”. Becase `describe` works fine in SQLcl:

      SQL> describe OWNER.”urls_config”
      …. here is output with table description …

      Regards, Damian

  8. Hello Jeff,
    I am a college student, for our final assignment of PL/SQL course, we need to create procedures and packages of a DB.
    We are a team of 3 members and I would like the 3 of us to work on the same autonomous DB which I have already created. The 3 of us are working on desktop SQL developer.
    So far I have managed to create their users on my DB and grant them PDB_DBA.
    When we go to SQL developer, they can create new schemas and connections but we have been unable to “download” the existing schemas from the autonomous DB to the desktop SQL developer.
    Is there a procedure or protocol that you could point out so the 2 of us can work on the same DB?
    Thanks very much,
    David

    • When you connect to your database using SQL developer, you should see the 3 schemas and their objects/data.

      Your DAVID account should be visible to your two partners if you’re connected to the same database.

      Send me an email if you’d like more assistance than this mechanism can provide –
      [email protected]

  9. Hey Jeff,

    When will Oracle SQL Developer Data Modeler will support 19c models?

    I connected to my 19c database, but I only see 12cR2 as the target.

    Thanks in advance.

    • as far as the modeler is concerned those are functionality equivalent, where 19c is basically the terminal update for the 12.1/12.2 release family of the database.

  10. Mauricio Fernández Reply

    Hi Jeff, I hope you can answer a question about editions (EBR). Which is the max number of editions in 19c ?? I’ve followed Doc ID 1507188.1 thechnique and I could create 15.000 editions without problem. In 11g max number was 2.000.

    I’ve search into Oracle documents and could’t find that limit

    Thank you very much

    • There’s not always an answer, but I will endeavor to find one for you.

      That being said, how many do you actually need?

    • Mauricio Fernández

      Thanks Jeff for the interest.

      I really don’t know what could be the number.

      The situation arise´d when we’ve exposed the edition’s main features and benefits to a client, and exposed the 11g limits, then they were cautios of using EBR.

      They periodically (weekly) upgrade their applications…

      15.000 editions is near editions for 95 years, asuming 3 editions a week and 53 weeks a year. I thinks there are enough editions, but to know the limit is a inquisitiveness

  11. Miguel Esca Reply

    Hey Jeff,

    Is the SQLDeveloper plugin for webstorm in your roadmap?

    Thanks in advance

    • No, not currently. If it were free and being used by tens of millions of developers, it would definitely be something to think about though.

  12. Hi Jess,
    Do you have a post about create a structured type using DataModeler? I see we have the option on the physical model but option to create is not there. We have in ther XMLTYPE & SDO_GEOMETRY. I can’t create a new one.

    Any helpful link that describes how to do that?
    Regards,

    • I don’t think I do, but just go look at the Browser, scroll down to ‘Data Types Model’ – use that to create a new Structured Type.

    • Hi Jeff,
      Unfortunately, I don’t have the option to create a new one in there either. When I right click on Structured Types, I don’t have anything happening. On distinct type, if I right click, I have the possibility to create one. Same for Collection types. But, not on structured types. or predefined structured type.
      Thank you anyway.

  13. Thanks Jeff.
    Can you please explain or give an example for “you need the employ url rewrites somewhere in front of ORDS/apex.”

    Regards.

  14. Hi,
    How can I ask a question?
    I trie several time but my question is not displayed.
    Thanks and regards.

    • Here is my question please:
      Hi,

      EBS 12.2.9, DB 19c on AIX

      our E-business is protected by a web application firewall. Then a reverse proxy (I think).

      From our E-Business we can conect ton an APEX interface.
      We configured both EBS and web application firewall on HTTPS and it works fine.

      We have a VIP URL to access EBS application

      https://virtualserver.domain/
      and from there by VIP URL to APEX
      https://virtualserver.domain/ords

      But
      When we access APEX page we see backend (physical) server name in URL zone of brawser:

      https://physicalserver.domain/ords

      Is there any way to hide physical server name and display virtual server name for APEX?

      Thanks and regards.

    • My spam filter flags anything with more than 1 link in it, so I had to manually approve the post.

  15. Xenofon Grigoriadis Reply

    Hi Jeff.
    Trying to install ORDS on windows. I am going through the installation guides of Apex 23.2 and Ords 23.4, and the java -jar ords.war install command, which would work on windows, is not supported/recommended anymore.
    But… I cannot find a windows installation process instead.
    I am referring to chapters 2.1.2 and 2.1.3 in
    https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.4/ordig/installing-and-configuring-oracle-rest-data-services.html
    (No mention of config folder preparation for windows…)

    Thanks in advance

    • Yeah we made a real cli, now you run
      ords install

      Did you read the installation and configuration guide?

      I have blog posts showing how to install ords here (look for the 22.1 one), and I do also everything on Windows.

  16. Hi Jeff,

    Need a help, We have migrated ORDS 19 from HTTP to HTTPS with certificate from CA few month back by following below doc which was shared by Oracle SR.

    https://blogs.oracle.com/fusionmiddlewaresupport/post/simplified-ssl-configuration-for-ords-a-howto-guide

    Post this configuration few application were able to connect but few are not connecting they are failing in cert chain and they are saying root and intermediate certificates need to be added along with server certificate.

    I have concatenated root, intermediate and server .crt files into single file servercert-chain.crt and passed this to ssl.cert=servercert-chain.crt parameter in standalone properties and bounced the ORDS but no luck when we try to connect via browser getting connection is not private. please help

    • You really want to avoid having to deal with multiple/chained certs for your webserver. ORSD Standalone via Jetty can work with these certs, but it would be loads easier to setup a loadbalancer in front of ords, and then terminal HTTPS/SSL at the LB and do http between the lb and ords – then you manage your certs all on the lb.

  17. Hey Jeff,

    Hope that all is well!

    In the SQL Developer application, you can download query results into .xls and .xlsx format but looks like you can’t do it in the VS Code Oracle SQL Developer extension.

    Is this on the radar to be in a future release??

    Thank you so much in advance for your time and consideration!

Write A Comment