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!

5,209 Comments

  1. Our development team has hundreds of packages/procedures that have been developed over the years and formatted with the PL/SQL beautifier embedded in the competing product PL/SQL Developer from Allround. The formatting was 100% based on GUI options, check boxes for this, radio button for that, etc… for different types of structures. We’d like to move to Oracle SQL Developer, but one of the sticking points is the code formatter. Looking for someone or someplace to go for helping tune the SQL Developer Custom Format to adhere to our long standing code format standards. Currently, the simple options dont allow me to match up, and the complete custom option is a bit much to digest without guidance. Anyone willing to take a stab on your team Jeff?

    • thatjeffsmith

      Our gui checkbox items are meant to cover the 95% of users…and the custom stuff for what’s ‘missing’

      We’re not going to copy another product’s Formatter, so take your best stab at ours, and then let is know what else you need.

      We’ll either enhance the formatter or give you a custom rule.

  2. Mauricio Silva Reply

    Running Multiple Queries in Oracle SQL Developer, but to differents connections

    Hi Jeff, Can I run a simple query to different connections?. The schemas and Bds have the same names, just change IP server in the connections setting

    I’m using SQL Developer v19.1.0.94

    Thanks a lot!!

    • thatjeffsmith

      We don’t have a multi-connection execution feature, so you’d have to have a connection to both and run the query in each.

    • Mauricio Silva

      ******
      thatjeffsmith POST AUTHOR

      We don’t have a multi-connection execution feature, so you’d have to have a connection to both and run the query in each.
      ******

      Thanks Master !

  3. Hi Jeff,

    I created a Web Source Module in Oracle Apex 18.2 in a Development database.

    The Base_URL is defined based on the Dev database.

    When exporting the application and importing this into a Test environment the Base_URL is still pointing to the Development Database.

    Is this a bug?

    Or do i have to define this as a variable?

    Any help or hint will be much appreciated.

    Regards,

    Anibal

    • thatjeffsmith

      ORDS Service URIs aren’t tied to a database…they’re available in the database they’re defined in.

      So since I don’t understand what might be happening here, I’m going to guess this is an APEX issue.

  4. below is a snip from a sqlplus session. It showr a lot on invalid password 1017 and account locked. The only process on the machine was sql developer. and the login attempts stopped once I exited sqldev .
    any idea what caused this? Been seen before?

    1 select USERHOST, TERMINAL, ACTION#, RETURNCODE,ntimestamp# from sys.aud$
    2 where NTIMESTAMP# > sysdate -1 and USERID=’MY_PROD’
    3* order by ntimestamp#

    TUS114035904 unknown 100 1017 12-JUN-19 03.45.59.541197 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.46.04.508358 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.47.01.011931 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.47.06.719161 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.48.04.539455 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.48.11.041766 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.49.10.146022 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.49.17.639592 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.50.17.645569 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.50.18.812180 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.51.18.532852 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.51.19.734862 PM
    BHS\TUS114035904 TUS114035904 101 0 12-JUN-19 03.51.49.388711 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.52.25.173911 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.52.26.363945 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.53.26.401239 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.53.27.597728 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.54.36.329373 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.54.37.154791 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.55.36.922181 PM
    TUS114035904 unknown 100 28000 12-JUN-19 03.55.37.551698 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.56.38.817370 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.56.39.386218 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.57.39.337458 PM
    TUS114035904 unknown 100 1017 12-JUN-19 03.57.40.823877 PM
    BHS\TUS114035904 TUS114035904 102 0 12-JUN-19 03.58.33.519271 PM

    • thatjeffsmith

      Not much to go on there, what’s your question though – what caused SQL Developer to try to login to your database?

    • TR Rudkin

      Jeff,

      Yes , why might sqldeveloper be connecting and WHY with a bad password.

    • thatjeffsmith

      bad password part is easy – your connections have saved passwords, and one has changed

      why it’s trying to connect – i would ask the person using the machine/program what they’re doing

  5. Beatriz Stratton Reply

    Hello,

    I’d like to know how to model attributes and columns that are of the GEOMETRY data type in SQL Developer Data Modeler (for both Oracle and SQL Server if possible).
    It seems that there is no logical or physical datatype available for this out-of-the-box?

    If there are blogs with a how-to, could you please point me to them.

    Thank you so much. The content in this site is greatly appreciated!

    Beatriz

  6. Daniel Looby Reply

    In SQL*Developer 19.1 I can access Edit User and see information about the user, Granted Roles, System Privileges, Quotas and associated SQL. Under Granted Roles I can see which role they’ve been granted, whether it is a default role or not and if it was given with ‘ADMIN OPTION’.

    So where would I see table grants for the user and which privileges (i.e.: SELECT, UPDATE, etc.) they have? It doesn’t have to have every table in the system… as roles appears to have. Just the actual tables the user has been given direct access to.

    Dan

    • Daniel Looby

      The SQL has four sections: User SQL, Quotas, Roles and System Privileges. Each section has a bit of SQL… except for System Privileges which has nothing under it.

  7. HiJeff,
    Is there a way in SQLDeveloper Data Modeler to generate DDL directly into a GIT branch and commit so that the scripts can be used as part of an auto-build CI process ?

    If not, are there any plans for it ?

    It would help massively with Agile database/DW development.

    • thatjeffsmith

      We’re building a CLI for the modeler now, so you could generate DDL scripts for this purpose.

      Right now it’s interactive, push button.

  8. Jerry Chick Reply

    Jeff;
    If you’ve already provided information on this, please point me to the article.
    We store a lot of XML data in CLOB’s in various tables. I need to be able to run a select for a particular tag within a CLOB. In the past I’ve used something like this:
    where XMLPARSE(CONTENT bo_data_area WELLFORMED).EXTRACT(‘/utilityDeviceIdentifierNumber/text()’).getStringVal() = ‘F76481594’
    If you have other suggestions, please let me know.

  9. I want to run the SQL Developer database diff feature on schemas different than the schema associated with the SQL Developer connection (my userid). I log in with my userid but want to use one of the production schema for the comparison. The DBA’s control the credentials for the production schemas; my userid has privileges to see the production schema objects. we have multiple production databases and I want to check for differences in the objects’ DDL across the various production schemas.
    I tried sofar: alter session set current_schema = prod_schema; This did not work and the diff report is based on objects under my userid ( I have no objects under my userid in production).
    I use the GUI for the “diff wizard” in SQL Developer. No code.
    Desired results would list the differences for the production schemas. Actual results are 0 results since my userid in the production database and does not have any objects

  10. Hello Jeff,

    First, thanks for this wonderful blog !

    Right now. I am experimenting with the unit test feature of SQL Developer.

    I like it but…

    What annoys me right now is that there is no possibility of duplicating a test implementation.

    By right clicking on a test implementation, It’s only possible to :
    -Open
    -Delete implementation
    -Rename implementation
    -Debug implementation

    Can you tell me why ?
    Is there a hidden way to do it ?
    Would you have a script to do it automatically ?

    For information, I know it’s possible to reuse startup/teardown procedures using the unit test library.
    But I’m tired to rewrite time and time again the same function parameters, which change only slightly on each of my implementations.

    Thanks !

    Alexandre

    • Hello,

      Since I did not get an answer, let me answer it myself 😀

      I discovered my approach was wrong. I needed to duplicate tests instead of implementations.

      And then create a suite for the tests to have a global startup and teardown procedure.

      Also, I noticed the lookup functionality might be the reason why there is no duplicate feature on implementations.

      Cheers,

      Alexandre

  11. Hi Jeff,

    My objective is to Export the results of Multiple queries to a single excel file. I couldnt find solution for this to be precise. So tried if I can write a spool statement with each query and specify the Worksheet path against each one.

    When I tried for the very first query, I am getting both query and results in my CSV file. I just want the results in csv file, can that be done?

    spool S:\myresults.csv
    select /*csv*/ * from EMPLOYEES;
    spool off

    • thatjeffsmith

      Works for me

      SQL> cd c:\users\jdsmith
      SQL> SET sqlformat csv
      SQL> SET echo off
      SQL> spool two_tables.csv
      SQL> SELECT * FROM locations;
      "LOCATION_ID","STREET_ADDRESS","POSTAL_CODE","CITY","STATE_PROVINCE","COUNTRY_ID"
      1000,"1297 Via Cola di Rie","00989","Roma","","IT"
      1100,"93091 Calle della Testa","10934","Venice","","IT"
      1200,"2017 Shinjuku-ku","1689","Tokyo","Tokyo Prefecture","JP"
      1300,"9450 Kamiya-cho","6823","Hiroshima","","JP"
      1400,"2014 Jabberwocky Rd","26192","Southlake","Texas","US"
      1500,"2011 Interiors Blvd","99236","South San Francisco","California","US"
      1600,"2007 Zagora St","50090","South Brunswick","New Jersey","US"
      1700,"2004 Charade Rd","98199","Seattle","Washington","US"
      1800,"147 Spadina Ave","M5V 2L7","Toronto","Ontario","CA"
      1900,"6092 Boxwood St","YSW 9T2","Whitehorse","Yukon","CA"
      2000,"40-5-12 Laogianggen","190518","Beijing","","CN"
      2100,"1298 Vileparle (E)","490231","Bombay","Maharashtra","IN"
      2200,"12-98 Victoria Street","2901","Sydney","New South Wales","AU"
      2300,"198 Clementi North","540198","Singapore","","SG"
      2400,"8204 Arthur St","","London","","UK"
      2500,"Magdalen Centre, The Oxford Science Park","OX9 9ZB","Oxford","Oxford","UK"
      2600,"9702 Chester Road","09629850293","Stretford","Manchester","UK"
      2700,"Schwanthalerstr. 7031","80925","Munich","Bavaria","DE"
      2800,"Rua Frei Caneca 1360 ","01307-002","Sao Paulo","Sao Paulo","BR"
      2900,"20 Rue des Corps-Saints","1730","Geneva","Geneve","CH"
      3000,"Murtenstrasse 921","3095","Bern","BE","CH"
      3100,"Pieter Breughelstraat 837","3029SK","Utrecht","Utrecht","NL"
      3200,"Mariano Escobedo 9991","11932","Mexico City","Distrito Federal,","MX"
       
      23 ROWS selected.
       
      SQL> SELECT * FROM departments;
      "DEPARTMENT_ID","DEPARTMENT_NAME","MANAGER_ID","LOCATION_ID","EXTRA_COLUMN"
      10,"Administration",200,1700,""
      20,"Marketing",201,1800,""
      30,"Purchasing",114,1700,""
      40,"Human Resources",203,2400,""
      50,"Shipping",121,1500,""
      60,"IT",103,1400,""
      70,"Public Relations",204,2700,""
      80,"Sales",145,2500,""
      90,"Executive",100,1700,""
      100,"Finance",108,1700,""
      110,"Accounting",205,1700,""
      120,"social media",101,1700,""
      130,"Corporate Tax",,1700,""
      140,"Control And Credit",,1700,""
      150,"Shareholder Services",,1700,""
      160,"Benefits",,1700,""
      170,"Manufacturing",,1700,""
      180,"Construction",,1700,""
      190,"Contracting",,1700,""
      200,"Operations",,1700,""
      210,"IT Support",,1700,""
      220,"NOC",,1700,""
      230,"IT Helpdesk",,1700,""
      240,"Government Sales",,1700,""
      250,"Retail Sales",,1700,""
      260,"Recruiting",,1700,""
      270,"Payroll",,1700,""
       
      27 ROWS selected.
       
      SQL> spool off
      SQL>

  12. Vivek Gupta Reply

    Hello Jeff – Firstly thanks for this miraculous ORDS product. I really love working on it!!
    Is there any way we can export ords_metadata and later on import same ords_metadata without issues? We are on 12c DB and received a request to clone database from prod. In order to preserve the metadata we took export of ords_metadata, cloned database from Prod and then reimported ords_metadata but after import found that none of the objects imported. Found metalink note 1912162.1 that schemas with ‘ORACLE_MAINTAINED’ bit set cannot be exported and ords_metadata is one of them!
    Is there any way to preserve metadata post clone? If not then it’s a good amount of work that we have to do to reinstall ords, create all security, services from scratch after every clone which is truly a laborious job!

    Thanks

    • thatjeffsmith

      Absolutely there’s a way, you would use the PL/SQL API for example to get your MODULES out to a set of SQL scripts you’d run on your target system to copy things over. I talk all about this here.

  13. Robert Paterson Reply

    I’ve downloaded SQL Developer and SSMS however I’m an error message appears saying the Network Adaptor can not establish a connection. Several sites have suggested I enable the Oracle services however no Oracle Services are installed. Where can I get the correct Oracle services which will solve this problem?

    Thanking you in advance…

    Bob

  14. Hi
    How can I save bind variable values between sessions?
    I have a query with 20 bind variables and I have to enter each manually.
    When I close sqldeveloper and re-open the bind variable values are gone!
    Is there a way to save and re-use?
    Thanks

    • thatjeffsmith

      I have logged a bug. If it were an easy fix/implementation, it would have happened already. I’ll see if I can light a fire and get it done for 19.3.

    • Thanks but I need this in order to EXPLAIN PLAN
      and when I run the sql as a Report I don’t have the explain plan icon

    • thatjeffsmith

      you can have your cake and eat it too.

      pull up the query in a worksheet, use the DBMS_XPLAN plan feature

      pull up the report, run it

      power-up your sqldev experience by creating a new tab group for the report so you can see both, side-by-side

  15. Wayne Clarke Reply

    Hi Jeff,

    When calling an autoRest api through my app I get blocked by CORS policy saying the response to preflight request doesn’t pass access control check. The API works fine through postman and I don’t get any errors calling ords calls to a PL/SQL api.

    Any thoughts?

    Thanks,

    Wayne

    • “pull up the query in a worksheet, use the DBMS_XPLAN plan feature” –
      But that is the problem! when I run the query in a worksheet I have to re-enter all the (20) bind values !! It does not get them from the values I already entered in the report

      “pull up the report, run it” – yes but no explain icon there

  16. Amin Adatia Reply

    SQL Dev v19.1
    Tools/Monitor Sessions
    Looking for ACTIVE_SQL
    The Log shows the query had an elapsed time of 382657 — a long time
    The code that was running
    select
    replace(q.sql_fulltext,chr(0)) sql_text
    from gv$session s, gv$sql q
    where s.sql_address = q.address
    and s.sql_hash_value = q.hash_value
    and s.sql_child_number = q.child_number
    and s.sid = :SID
    and s.inst_id = :INST_ID;

    should there not be an equate of s.INST_ID = Q.INST_ID
    and maybe s.serial# = :SERIAL (that is displayed column_name)

    running the modified query with the s.inst_id = q.inst_id gave a much better response

  17. Hello Jeff,

    Great stuff on SQL Developer 19.1 New Connections screen! However, with the constantly growing number of connections (thanks to PDBS 🙂 ), we are missing the ability to search and filter connections on the left side list of the New Connection modal. I have about 60 or so of them and need a way to deal. Even an option to organize connections by folder, just like you have in a stand alone connection list would be helpful.

    Thank you.

    • thatjeffsmith

      I suggest this instead…find the connection you want to base your new one on. Edit it – give it a new name and click Save. You’ve just cloned your connection 🙂

  18. hi,
    i want connect sqldeveloper 19.1 to database 19c but this error on connection :

    Status : Failure -Test failed: IO Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond, Authentication lapse 0 ms.

    why?
    thanks

  19. Hello Jeff !

    How are you my friend ??

    Jeff,

    How I can use the Option Oracle Advanced Network Compression in SQL Developer when using MACOS with JDBC ?

    Remember…I can use this now in Oracle 18c XE !!!

    Thanks and congratulations for your BLOG !!!

  20. In our production environment, SQL Developer cannot see packages – in the Connection Navigator, when trying to expand the Packages node, the packages list remains empty. I can see Tables and Views etc. I have access to DBA_SOURCE so that’s not the issue.

    We can’t execute procedures in Prod so if SQL Developer is using dbms_metadata to extract the source code to display then maybe that’s the issue.

    Do you know if SQL Developer is using dbms_metadata to extract the source code to display?

    Thanks.

  21. Just getting started. Would appreciate some tips on what to do next. I installed oracle 12c release 2 and can get to the SQL> prompt but it shows database idle. What is the easiest way to get sqldeveloper connected to say the sample databases? Also SQL> help is not available for some reason.
    [[email protected] ~]$ sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Fri May 24 16:16:49 2019

    Copyright (c) 1982, 2016, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> HELP INDEX
    SP2-0171: HELP system not available.

  22. I’m trying to run an export in sdcli like you do above but keep getting the error: CART parameter value is missing: cart. Do you know how to resolve this error?

    • thatjeffsmith

      can you show me what you’re running and what you get back?

      Check your connection. Check your cart. Check your export operation settings.

    • Thad Limer

      Sure, I’m running this code
      sdcli cart export -cart C:\Users\tlimer\CART FOR EXPORTDSRIP_POP_G1.xml -config C:\Users\tlimer\CART FOR EXPORTDSRIP_POP_G1_CONFIG.XML -target C:\Users\tlimer\CART FOR EXPORTDSRIP_POP_G1_REPORT.xlsx

      getting back this message.
      CART parameter value is missing: cart

    • Thad Limer

      Ok, removed the spaces and it’s saying Command completed. I watch the folder where the report should land and it shows up for a couple of seconds and then disappears.

    • thatjeffsmith

      Ok, SQLDev would not be creating and then DELETING anything…so if you see it..something is stepping into between SQLDev and the output when you see it disappear

  23. Hi Jeff,

    I noticed that in later releases of SQL Developer (certainly 19.1) the tree within the connections pane does not have “Editioning View” node. Is there an option to get this important EBR category back?

    Many thanks,
    Mark.

    • thatjeffsmith

      Editioned based views are really just…views. There’s no object of type ‘EDITIONING VIEW’ in the database.

      We originally put them in as separate objects to advertise the feature. But they’ve been out for awhile, and most of our users are not using them (based on usage data).

      If you want a way to key in on just those views, apply a filter, you have Exclude Editioning and Show Only Editioning Views options available.

  24. Hi Jeff,
    I am confused by the migration tool when moving mysql to oracle. source and destination is clear, however when you perform the migration it creates a schema with the name of the mysql database you are migrating.
    So if my target would be schema ABC, and the database in MySQL is called FRED, the migration creates a schema in Oracle named FRED. Schema ABC just remains empty and is effectively only a facilitator.
    Is there a way to get it actually into schema ABC.

    • thatjeffsmith

      The intent is to create an Oracle schema based on the database or user you are migrating over.

      The oracle account you specify in the wizard is used to connect to said database and create all those objects. Easiest way to get what you want may be to rename the schema after it’s been brought over.

Write A Comment