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

  1. Hi Jeff,
    I have found that “set sqlformat json” or “set sqlformat json-formatted” does not return all the data when selecting from a clob column.
    Interestingly “set sqlformat xml” does return all the data

    Is this expected?

    Below is a test case to reproduce the issue.
    Thanks,
    Dan

    DROP TABLE DAN_CLOB;
    CREATE TABLE DAN_CLOB
    (ID NUMBER NOT NULL ENABLE,
    CONTENT CLOB
    ) SEGMENT CREATION IMMEDIATE
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE USERS
    LOB (CONTENT) STORE AS SECUREFILE (
    TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK 8192
    NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
    STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

    REM INSERTING into DAN_CLOB
    SET DEFINE OFF;
    Insert into DAN_CLOB (ID,CONTENT) values (7,’Dear Customer, (line 1)
    Some text.. (line 2)
    Some text… (line 3)
    Some text…. (line 4)
    Some text….. (line 5)’);
    commit;

    clear screen
    set echo on
    set sqlformat json
    select * from dan_clob;

    set sqlformat json-formatted
    select * from dan_clob;

    set sqlformat xml
    select * from dan_clob;

    And the results

    SQL> set echo on
    SQL> set sqlformat json
    SQL> select * from dan_clob;
    {“results”:[{“columns”:[{“name”:”ID”,”type”:”NUMBER”},{“name”:”CONTENT”,”type”:”CLOB”}],”items”:
    [
    {“id”:7,”content”:”Dear Customer, (line 1)\n Some text.. (line 2)\n\t\t “}
    ]}]}
    SQL>
    SQL> set sqlformat json-formatted
    SQL> select * from dan_clob;
    {
    “results” : [
    {
    “columns” : [
    {
    “name” : “ID”,
    “type” : “NUMBER”
    },
    {
    “name” : “CONTENT”,
    “type” : “CLOB”
    }
    ],
    “items” : [
    {
    “id” : 7,
    “content” : “Dear Customer, (line 1)\n Some text.. (line 2)\n\t\t ”
    }
    ]
    }
    ]
    }
    SQL>
    SQL> set sqlformat xml
    SQL> select * from dan_clob;

    <![CDATA[Dear Customer, (line 1)
    Some text.. (line 2)
    Some text… (line 3)
    Some text…. (line 4)
    Some text….. (line 5)]]>

    • My first thought is to make sure LONG is set high enough to retrieve the entire LOB

      SQL> select * from dan_clob;

      ID CONTENT
      _____ ___________________________________________________________________________________
      7 Dear Customer, (line 1)
      Some text.. (line 2)
      Some textà (line 3)
      Some textà. (li

      Elapsed: 00:00:00.159
      SQL> set long 1000
      SQL> /

      ID CONTENT
      _____ ______________________________________________________________________________________________________________
      7 Dear Customer, (line 1)
      Some text.. (line 2)
      Some textà (line 3)
      Some textà. (line 4)
      Some textà.. (line 5)

      And once I do that, JSON sqlformat seems to work just fine.

      SQL> set sqlformat json
      SQL> /
      {"results":[{"columns":[{"name":"ID","type":"NUMBER"},{"name":"CONTENT","type":"CLOB"}],"items":
      [
      {"id":7,"content":"Dear Customer, (line 1)\nSome text.. (line 2)\nSome textà (line 3)\nSome textà. (line 4)\nSome textà.
      . (line 5)"}
      ]}]}
      Elapsed: 00:00:00.011
      SQL>

  2. Hi Jeff,

    Is it possible for SQL data modeler when generating DDL for a user definition to allow editioning for that user? E.g.,

    ALTER USER ENABLE EDITIONS;

    I cannot find this in the user properties nor a way of extending the DDL generation to include it.

    Also when is the next version of SQL data modeler going to be released?

    Many thanks,
    Mark.

    • Hi Jeff,

      Thanks for the update.

      Is this something (along with wider edition-based redefinition support such as granting privileges to packages) that could be added to this product?

      In the meantime, do you have any practical examples on the “custom DDL” feature that would help in my use case?

      Many thanks,
      Mark.

  3. Hello Mr. Smith,
    One of my collegs ords developers asked me about the p_priority to ORDS.DEFINE_TEMPLATE parameter. The documentation only says: “The priority for the order of how the resource template should be evaluated: 0 (low priority. the default) through 9 (high priority).”
    I googled it, I even searched in Oracle metalink, but nowhere did I find the use of this parameter with a value other than the default 0. Can you give me some more information on the use of this parameter?

    • Template patterns can be overloaded, if ords finds a request that can be mapped to more than one endpoint, this priority picks ‘the winner.’

      So, avoid conflicts, and you have nothing to worry about.

  4. Hi,
    I’m using SQL Developer to query user_source. The text field is truncated to 80 characters. How can I change my settings so that, say, 200 characters are shown in my output?
    Thanks.

    • We don’t truncate varchar2(4000)s to 80 characters, I can’t reproduce what you’re reporting. Are you sure the grid isn’t just hiding the text, and you need to double-click to get the full cell editor?

      If you’re looking at a LONG, then you need to use SET LONG 1000, for example. But we should always print the entire varchar2 or clob. I’m in version 23.1, by the way.

  5. Hi Jeff,
    I am facing an issue when I try to modify a view . The error message is” modification is not supported for view objects whose source exceeds 32kb ”
    I am running a shared Citrix SQLDEVELOPER version 21.4.0.346
    Thanks inadvance.
    Nabil.

    • As the error implies, the GUI component can’t handle src text that large, so instead you’ll need to edit the view via SQL in the SQL Worksheet. You can get the source SQL for the view from the VIEW page, so copy/paste it over to a worksheet, update as necessary, and then run via ctrl+enter.

    • Thank you Jeff for your quick response.
      As a DBA, I know the workaround , however, it is difficult to change user habits ;).
      I don’t get any errors if I run SQLdeveloper on my laptop. The problem only occurs on Citrix.
      I was just wondering if there was a possibility of modifying SQLDeveloper configuration parameter (java memory…).
      Thank you anyway

    • It’s old JDeveloper code – no easy workaround. Another reason we’re rebuilding the house in VS Code!

  6. De Groote Mike Reply

    HiI Jeff

    I have a question about showing xml content in a query. We know that the older version had a bug concerning this .
    We performed a transition in tools to sql developer 23.1 (build 097.1607) in combination with a instant client basic windows 21.10
    and for JDK we went with openjdk 17+35 for windows x64.
    But we are unable to show xml content in a query in the datagrid.
    Is there a way to have a query show its xml value in stead of NULL . The option to show xml in the database – advanced tab is selected and activated with a restart of sql developper. This issue manifest itself on win 10 and win 11.

    • OpenJDK isn’t supported and neither it Java 17, although our next update will update to Java 17

      Can you give an XML query example?

    • De Groote Mike

      Jeff

      This is the current used sql
      select sequpdmsglog,status,usr,dat,message,answer from geoms.dms_updmsglog

      in the table structure message and answer are “MESSAGE” “SYS”.”XMLTYPE” , “ANSWER” “SYS”.”XMLTYPE” => both securefile clob

      even when we open the tabel via the tree browser those 2 fields show as (null)

    • I see the same, as soon as I unset the ‘use oracle client’ option, everything comes back as expected.

      My test case:

      SELECT XMLELEMENT ("Emp", XMLATTRIBUTES (
      e.employee_id as "ID",
      e.first_name ||' ' || e.last_name AS "name"))
      AS "result"
      FROM employees e
      WHERE employee_id > 200;

    • De Groote Mike

      Jeff

      We can confirm the same result. Not using the instantclient allows us to see the xml content. Problem is that without the instantclient we can’t create sso connections. And we need both. After checking the current java we are using I can confirm that this is our current JAVA_VERSION=”11.0.18.0.2″. . Is there a way to make this work with the use of an instantclient .

      The strang thing about this all is ,I’m getting reports on win 11 that some users can use the instantclient and still see the
      xlm values. Where others can’t .

    • De Groote Mike

      Jeff

      When we deactivate the usage of the instant client in our sql developer and try to open a previously working os connecting to a database we get an error that password is wrong.
      In effect the sso connection doesn’t want to open.
      Where as when the instantclient is checked to be used ,it does work , But then the xml is empty in the query

      We have oci thick driver checked in our config.
      The instant client field is filled with the following value :
      Instant Client: file:/C:/Program Files/sqldeveloper/instantclient/

      W have put the instant client under the sql developper folder to keep cohesion between the different parts

      -Mike de Groote

    • sso = wallet, not sso = single sign on?

      sso wallets totally are supported by straight up jdbc driver (thin connections)

      But rather go down that path – I suggest you open a Support ticket with MOS, so they can properly allocate the resources needed to get you going.

      The path going forward is NO CLIENT, ONLY JDBC, for simpler rigs/setups, with full functionality. That is the future…and it should be mostly ready right now.

    • De Groote Mike

      Jeff

      with SSO I meant single single on (os login in the connection menu). we use the instant client and the oci thic driver to make these os (single sign on) connections possible. But that makes the xml results come back as NULL

  7. 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.

  8. 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

  9. 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?

  10. 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…

  11. 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.

  12. 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.

  13. 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.

  14. 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!

  15. 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.

  16. 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!

  17. 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

  18. 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.

  19. 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.

  20. 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.

Reply To Michael Moser Cancel Reply