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

  1. Hi Jeff ,

    I have a question in relation to the use of F10 (explain plan) As we implement least privileges principle, we try not to provide to much privs to a user. But we can across an issue and I hope you can help.

    When press F10, get an explain plan, it reports with ORA-01039 insufficient privileges.
    But when using a query to get the explain plan it works fine.
    So we are wondering which privileges are required to be able to make use of the F10 button?

    Was not able to find this in the documentation.
    Thanks.

  2. Marc Thannhauser Reply

    Hi Jeff,

    I want to reorganize a database with apex/ords installed in it. The reason is that we would like all our tablespaces (including SYSTEM/SYSAUX) to be of type bigfile. So general idea was to user full expdp and full impdp. However, this is not really working for an account like ORDS_METADATA.
    Looking at the source database I tried to look at the ords components using sqlcl with sys as sysdba as logon. Then i see:

    SQL> rest modules
    NAME PREFIX STATUS ITEMS_PER_PAGE
    —- —— —— ————–
    SQL>

    EMPTY!

    But a “rest schemas” is resulting in a list of enabled owners.

    When i connect to the database using sys as sysdba (using sqlplus) i can do:
    ***
    SQL> select ords_metadata.ords_admin.INSTALLED_VERSION() from dual;

    ORDS_METADATA.ORDS_ADMIN.INSTALLED_VERSION()
    ——————————————————————————————————————————————————————————————————–
    19.4.0.r3521226
    ***

    but trying to export results in:

    ***
    SQL> select ords_metadata.ords_admin.export_schema() from dual;
    select ords_metadata.ords_admin.export_schema() from dual
    *
    FOUT in regel 1:
    .ORA-00904: “ORDS_METADATA”.”ORDS_ADMIN”.”EXPORT_SCHEMA”: ongeldige ID
    ***

    Is this due to an somehow invalid install of ORDS in the source database?
    Where should i look for the cause and how in general should I migrate my database including ORDS?

    kind regards, Marc

    • Data Pump isn’t a great way to work with apex and ords in general…

      Your version of ORDS is 5 years old, so you have maint work to do anyway.

      How many rest apis do you have that need rescuing?

    • Marc Thannhauser

      Hi Jeff,
      if rest apis is equivalent to enabled schemas….around 5-10.
      I will inform our project/customer on using the older versions of Apex/ORDS. Thanks for bringing that detail under my attention.

      Kind regards,
      Marc

  3. Hi Jeff,

    A question about paging in ORDS and do I need to use analytic functions?

    I’m using ORDS 21.4.3. I’ve got a GET handler with a SQL statement. I’ve defined it as a ‘Collection Query’ with pagesize 1000. At the moment it just has a simple sql statement in it extracting rows from a single static Materialized View.

    I’ve read your article about ‘how-paging-works-in-ords’ using analytic functions to ensure objects do not “slip” between pages as the queries are executed. So it it still the case with the version of ORDS I’m using that I need to wrap my simple sql query with this:

    SELECT *
    FROM (
    SELECT Q_.*,
    ROW_NUMBER() OVER(
    ORDER BY 1
    ) RN___
    FROM (

    ) Q_
    )
    WHERE RN___ BETWEEN :1 AND :2

    Dan

    • nooo…we do that for you

      so you defined your SQL query simply as ‘select whatever you want from table’ – then we behind the scenes amend the query for paging.

    • Mike Peacey

      Sorry – ignore the APEX reference.

      Question still stands though. Where does an ORDER BY statement get applied – and how is this done? Or isn;t one needed?

    • Mike Peacey

      Issue is: when doing paginating on a large data set I believe that somewhere in the query an ORDER BY is required so that a consistent guaranteed non-duplicate data set is delivered.

      The original post and your response suggests that some magic does this – when stated Oracle position is that the order in which rows are returned is not guaranteed without one – notwithstanding that it may ‘probably’ be ok.

      Really just need some clarification. Or is this an AskTom question?

  4. Hi Jeff,

    Does the SQL Developer extension for VSCODE supports running utplsql tests, coverage, etc?

    I like to use sql developer tools to connect to the database and for modeling, but my teammates complain about it not being responsive or modern enough.

    • There’s no explicit support for utplsql, YET. But we’re gonna publish APIs the project maintainers can use to extend our features.

      Of course utplsql uses plsql to test plsql and our extension supports running plsql and SQL scripts…

  5. Taylor Vance Reply

    Hey Jeff,

    I would like to use SQL Developer to test my packages with utplsql. What is your development flow to add or modify packages, procedures, and functions and applying them using liquibase? I know liquibase exports them as xml files. But how do you add or modify packages and test them in a way that you can apply them to a different database using liquibase? I have seen some people treat packages, procedures, and functions as code. Different from we normally treat tables and views!

    • Plsql is code, and should be treated as such. Our liqiibase integration generates files you can put through your pipelines to build and deploy environments.

      Right now this is all done via our cli (sqlcl), but next step is to build a proper experience in VS Code with our new SQLDev extension.

  6. Jeff,

    I just created a JSON autonomous database for APEX development. The APEX object browser in the workspace looks very similar to the relational database objects. It has tables, views, etc. Does it mean that I can treat it as a relational database and oracle takes care of the translation layer? What are your thoughts on the JSON database for APEX development? pros, cons, limitations. etc.

    Looking forward to reading your thoughts!

    • It’s an Oracle Database:)

      But relational features are limited to a certain amount of storage space (see terms), as the JSON service is cheaper than a transaction processing (atp) service.

      A traditional apex app doesn’t normally fit into here unless you’re also working completely based on JSON objects and collections – SQL or no SQL.

  7. Hi Jeff –

    Would it be possible to get a wallet connection to the database with an APEX workload type? The JSON and ATP workload types have a wallet connection which allows me to connect with sqlcl and import and export schema changes with liquibase. It would make APEX development on relational databases more affordable for thouse who cannot pay for the ATP or ADW

    • The apex service by design doesn’t support sqlnet connectivity…it’s the tradeoff for the vastly reduced rate were offering to run your apps on a hosted, exadata-powered environment.

  8. Howry Jeff, does OCI have a more affordable pricing for non-profit or tiny businesses.? There is a lady in my neighborhood that makes and sells cakes. I would like to create simple APEX app where her customers can place an order of the cake, maybe connect it to stripe, send an email, approve and reject orders, send invoices and receipts, etc. This lady is a single mom and struggles.

    The Workload type: APEX is $264 a month
    The Workload type: JSON is $264 a month
    The Workload type: Transaction Processing is $264 a month

    She cannot paid that. I cannot paid that. I think that $0 to $60 would be perfect. I get the security and autonomous part, but it still needs to be more affordable for people without resources.

    I would love to know your thoughts on this!

  9. Miguel Escamilla Reply

    Hello Jeff,

    Thanks for delivering the message about updating APEX in the container image. Question about installing utPLSQL in the cloud for development. I normally install it using sqlcl. I haven’t tried installing it using sqlplus because of the setup slqplus needs. Is one faster than the other? Is there known performance difference between the two?

    • I’m the product manager for SQLcl, I’m going to tell you to use SQLcl. It has more Cloud support/features than SQLPlus.

      Performance of, what? SQLcl takes a second or 2 to startup, that’s the main perf difference.

  10. Hey Jeff, can someone at APEX push for this image to have the latest version of APEX. I am not asking for the latest patch. Just to stay current. Right now it is APEX 23.2 .https://container-registry.oracle.com/ords/ocr/ba/database/adb-free

    ADB-FREE is a podman image that has the same setup of the OCI Autonomous Transaction Processing Database. It would be great for APEX development locally. The issue is that it has 23.1.5. I found this image months later after I started developing on OCI. I need the 23.2 features.

    Please, talk to the right people so that they update this for the APEX community!

  11. Hi Jeff –

    Is “set define off;” functional in SQL Developer for VS Code? I’m unable to get it to work. I’m on 23.4.1 and latest VS Code. See below for VS Code version.

    I’ve got “set define off;” in my SQL Developer for VS Code startup script (and other entries in the startup script are effective), but “set define off” appears to be ignored. If I “SELECT ‘ab&c’ FROM dual;” for example and hit CTRL-ENTER, a tab titled “Substitutions” opens up requiring me to enter a Value for Name “c”. I tried executing “set define off;” as part of a combined statement with my SELECT, but no joy there either.

    Related (perhaps) links: https://forums.oracle.com/ords/apexds/post/set-define-off-issue-5720 and https://forums.oracle.com/ords/apexds/post/literal-in-the-query-text-causes-sql-dev-for-vscode-to-pro-0274#comment_20894363106257450555707506781508498896

    Thanks!
    -Frank.

    ps. SQL Developer for VS Code is AWESOME. I recognize the kinks are still being worked out, but it looks great so far.

    VSCode:
    Version: 1.86.2 (user setup)
    Commit: 903b1e9d8990623e3d7da1df3d33db3e42d80eda
    Date: 2024-02-13T19:40:56.878Z
    Electron: 27.2.3
    ElectronBuildId: 26908389
    Chromium: 118.0.5993.159
    Node.js: 18.17.1
    V8: 11.8.172.18-electron.0
    OS: Windows_NT x64 10.0.19045

  12. Stuart Higgins Reply

    Hi Jeff,
    What is the earliest version of Oracle DB that I can connect to using the SQL Developer Extension for VSCode ? (Would like 8.1.7, but I know it’s very old.) Thank you.

    • 24 years isn’t ‘very old’ in software time frames, it’s prehistoric.

      11gR2 is the minVer required for connectivity with all of our tools.

  13. Hi,
    While working on SQL developer today morning. I got a wierd output as “PL/SQL procedure successfully completed. Alias sqlvl_int_runme dropped” . I’m not sure why this came. Can you please help me to understand.

  14. Saiteja Kongala Reply

    Hi Jeff,
    I’m working on ORDS 22.4 on a server in standalone mode. I have enabled the https with a self signed certificate from openssl. It is working fine with the https://localhost:8443/…… api. but when I tried with the hostname instead of localhost it is throwing me an HTTP 400 error Invalid SNI. I have checked the certificate and made all the necessary changes in the settings.xml file as shown below:

    8443
    /path/certificate.crt/
    /path/private.key/

    I made sure that the base name given while creating the certificate is same as the hostname also.
    when I tried to use wget –no-check-certificate url with hostname from the server, I’m getting the error as:-

    -The Certificate owner does not match the host name
    -Error 400 Bad request

    Please help me with this.

Write A Comment