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!

Comments 3,957

  1. hi
    so whenever i am trying to open any table after making the connection and i am clicking on a table its giving me an error saying ora: 01460
    unimplemented or unreasonable conversion requested
    please help with an action on what to do

    1. o whenever i am trying to open any table after making the connection and i am clicking on a table its giving me an error saying ora: 01460
      unimplemented or unreasonable conversion requested
      please help with an action on what to do

      1. thatjeffsmith Post
        Author
  2. Hi Jeff,
    how can I define the date format in “File panel”, e.g. when I open a saved file for my sql files in SQL-Developer? It is by standard in US notation (e.g. “11/26/17 10:59 PM”) and so it’s hard for me to interpret the date for my location area.
    I would like to see them e.g. in format ‘yyyy.mm.dd HH24:MI’ to see which file is most recent. It’s much easier for me to read.

    Thanks for a good hint
    Dieter

    1. thatjeffsmith Post
      Author
      1. Thanks, but unfortunately it doesn’t.
        I’m running Windows 7 Enterprise and with language and region setting to German.
        Date-format: TT.MM.JJJJ is definitely different to the US-region used by SQLdev.
        Any java config I could change? I’ve a separate JDK installed to get it work, because of no admin permission on my PC.

  3. Hi Jeff,

    I’m currently working to enable SQL*Developer for Radius Authentication to the database (enabled Active Directory authentication and authorization). In order to do so, I need to figure out how to cast the setting to every client PC to change from JDBC use to using OCI calls / Thick client.

    Can you point me to where that configuration is housed? It would be incredibly helpful.

    1. thatjeffsmith Post
      Author
  4. Hi,
    I’m using SQL Developer version 17.4.0.355.2349 with a high resolution/dpi monitor.

    SQL worksheet works fine but when I go to the “Real Time SQL Monitor” the SQL Developer window shrinks to about 1/4 the size and becomes unusable.

    I have tried using JDK 9 but this didn’t help.

    Any ideas on how this can be fixed?

  5. Hi Jeff,
    I’m using version 17.3 of SQL Developer and have noticed that the query screen doesn’t paint well when the query contains a string that exists over several lines. So the opening quote for one string is on (say) line four and the matching closing quotes lives on line seven. Sometimes these are strings with the new ” q’ ” syntax.
    Have you experienced that before, or is it just my platform that’s the problem?
    Thanks

    Mungo

    1. thatjeffsmith Post
      Author
      1. Who said “output screen”? I’m not at work, so I cannot remember the name you give to the “query screen” – the rectangular text area that I type my SQL into.
        Mungo

      2. thatjeffsmith Post
        Author
  6. Hi Jeff,
    Matter of your website here: are you aware that your “Unsubscribe” page looks darn ugly with the labels and the radio buttons askew? It’s not the easiest of sites to navigate, but I guess you don’t have the time to polish it.
    When I saw the ugliness on my iPad, I thought that it would be fine on a Windows PC. Nope.

    Don’t shoot the messenger…

    Regards

    Mungo Henning

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author
  7. My Question is: How do I easily find what is at column “30” ? Something to turn on in preferences ?
    I know I could arrow over counting a column for each arrow click.

    ORA-00936: missing expression
    00936. 00000 – “missing expression”
    *Cause:
    *Action:
    Error at Line: 51 Column: 30

    1. thatjeffsmith Post
      Author
  8. Sorry if asked befor but I only find answers for export on data.

    How do I export objects (ex. packages) with standard no double qoutes enclosing schema and objectname? (adding the schema is fine)

    so now I alway get
    create or replace package body “schema.”package” as
    but I want
    create or replace package body schema.package as

    1. thatjeffsmith Post
      Author
  9. Sorry if this question has been answered somewhere on your forum, I can’t find the answer. (On the bright side, I found about 50 things that make life better in SQL Developer while searching for this answer.)

    If I’m typing a statement in a SQL worksheet and use the auto complete on a table that is for the login user, the fully qualified schema disappears when I hit enter. Is there a setting to retain the fully qualified schema while keeping the auto complete/suggestion feature?

    Example if I logged in as user ‘HR’:

    SELECT * FROM HR.EMPLOYEES

    SELECT * FROM EMPLOYEES

    How do we keep the fully qualified schema (HR) in the statement when working in the SQL Worksheet?

    1. Looks like it removed the markup in my example…

      Example if I logged in as user ‘HR’:

      SELECT * FROM HR.EMPLOYEES
      –type in fully qualified table name using auto complete

      SELECT * FROM EMPLOYEES
      –auto corrects to this after hitting enter with auto complete

      1. thatjeffsmith Post
        Author
  10. (Formatter Question)

    Hey Jeff,
    I am using SQL Developer 17.3.1, which has the new formatter tool. I read through the ‘Advanced Format: Custom Format’ template that comes default, and I am having a hard time learning how to edit the document to format queries to my preference. I went the the WordPress site mentioned in the document, and I am still lost. Is there a page / document out there that can help me understand how to tweak the format to my liking?

    I am trying to format my SQL as follows:

    Default:

    SELECT
    *
    FROM
    dba_audit_trail
    WHERE
    username = :Username
    AND timestamp > trunc(SYSDATE)
    ORDER BY
    timestamp DESC;

    Desired:

    SELECT
    *
    FROM
    dba_audit_trail
    WHERE
    username = ‘lkup’
    AND timestamp > trunc(SYSDATE)
    ORDER BY
    timestamp DESC;

    As you can see all I want to do is double indent anything that follows the initial select statement. This helps me visually group queries in a worksheet.

    Your help and consideration is appreciated.

    1. Well, looks like the page stripped out the extra spaces. I hope my description was enough. Basically the default formatter lines SELECT, FROM, WHERE etc… along the same edge. And then indents the variables. What I want is to double tab everything after the select.

      1. thatjeffsmith Post
        Author

        you were almost there…

        Leave a comment on Vadim’s blog post. He’ll reply with an answer.

        18.1 does offer this solution, but i don’t think that’s what you’re looking for.

      2. thatjeffsmith Post
        Author
  11. Just downloaded sqldeveloper 17.4.0.355.2349. When launching, it appears to launch then dies. I was attempting to upgrade from 17.2.0.188.1159. The 17.2 version still launches and runs just fine, but 17.4 will not. Is there an easy way to kill any remnants of the prefs for 17.4 to rule out any corruption or the like?

    1. thatjeffsmith Post
      Author

      yes, delete your system17.4 directory – not sure what OS you’re on to tell you where it’s at, but assuming windows, it’ll be under your OS User AppData folder.

      If it gets to the launch/splash screen, then it’s not a Java issue. It’s probably something like you’re guessing, the system prefs directory is FUBAR.

      1. yep, deleting AppData\…\SQL Developer\system17.4.0.355.2349 did the trick.
        I now have a different issue… app is dying when using the REST Service Developer module… but I’ll see if I can isolate before posting more unless you are aware of known issues with the module in 17.4?

      2. thatjeffsmith Post
        Author
      3. Using the view > REST Services Dev panel. Here are my steps:
        1- Connect
        2- Expand Modules Node, Expand Privileges Node
        3- Right click on “REST Data Services” node, and do nothing while viewing the right click context menu
        4- …. after a few seconds, Oracle SQL Developer crashes and creates windows crash dump.

      4. thatjeffsmith Post
        Author
      5. So, related to why I’m using the REST Services Dev panel is the need to migrate Rest services defined in the APEX repo to the ORDS repo. I’ve worked out a methodology that works using that tool as I have been unable to find an automated method during ORDS upgrade.

        Here’s the Windows Event data or the crash when using that REST Services Dev panel in 17.4:
        Log Name: Application
        Source: Application Error
        Date: 2/15/2018 12:14:41 PM
        Event ID: 1000
        Task Category: (100)
        Level: Error
        Keywords: Classic
        User: N/A
        Computer: Win7-64-Stacy
        Description:
        Faulting application name: sqldeveloper.exe, version: 17.4.0.355, time stamp: 0x58ac981a
        Faulting module name: ntdll.dll, version: 6.1.7601.24024, time stamp: 0x5a58e1b4
        Exception code: 0xc0000374
        Fault offset: 0x000ce9fb
        Faulting process id: 0xc9c
        Faulting application start time: 0x01d3a6804823e2dc
        Faulting application path: C:\Users\Siena\Downloads\sqldeveloper-17.4.0.355.2349-no-jre\sqldeveloper\sqldeveloper.exe
        Faulting module path: C:\Windows\SysWOW64\ntdll.dll
        Report Id: b545308c-1273-11e8-9cd0-00505600d102
        Event Xml:

        1000
        2
        100
        0x80000000000000

        67379
        Application
        Win7-64-Stacy

        sqldeveloper.exe
        17.4.0.355
        58ac981a
        ntdll.dll
        6.1.7601.24024
        5a58e1b4
        c0000374
        000ce9fb
        c9c
        01d3a6804823e2dc
        C:\Users\Siena\Downloads\sqldeveloper-17.4.0.355.2349-no-jre\sqldeveloper\sqldeveloper.exe
        C:\Windows\SysWOW64\ntdll.dll
        b545308c-1273-11e8-9cd0-00505600d102

  12. I care not about the retrieval of the data; I care about the displaying of the data. Can you point me in the direction regarding displaying of NULLs please?
    Ta again
    Mungo

    1. thatjeffsmith Post
      Author
  13. Hi Jeff,
    Showing my ignorance of the SQL Developer tool here, and hoping you can educate me.
    I’m using version 17 of sql developer on a legacy oracle database. I’m only in my job by six months, hence I am doing a lot of “select *” from tables of interest, just to get the hang of the application and its design.
    Some tables have 200 columns or so. Often these have one or two crucial columns, plus a lot of optional attributes that don’t interest me.
    What I’d like in SQL Developer is some button that implements “suppress null columns”. So whilst all the data shown for a column is null, hide it from view. If I do decide to scroll down to the next fifty rows and a column has data in that field, then suddenly show that column.
    Can you do this otherwise in SQL Developer? If not, and if you sympathise with my desire, add this to the wish list please.

    Secondly, some queries that I write take a few minutes to process. So I might kick off a query and then wander off for a coffee. When I return it would be useful to see the date and time of when the first row was displayed. Just so I can visually “date” the query results.
    There’s plenty of room on the top bar that shows the query elapsed time, so I would suggest adding this there please.

    Thirdly, better handling of query cancellation would be nice: if I press the “cancel query” I want to be able to move on immediately, not after two minutes of slow deliberation.

    Great website Jeff; answer my questions and the level of praise goes up!

    Regards

    Mungo Henning

    1. thatjeffsmith Post
      Author

      Sorry, no way to do the NULL thing. And imagine how expensive that would be – reading all the values just to make sure there were no NULLs, before returning the resultset.

      I would go for, never using SELECT * FROM. Always just ask for the columns you’re really interested in seeing.

      On the query cancellation, we’re at the mercy of the database and the jdbc driver. While that query is running, the connection is busy. If you want to do something else, like run another query, fire up an unshared worksheet. Or esp, if you know it’s going to be bad in advance, run the long running queries in an unshared worksheet. That way, you can do all your normal clicking around and running queries while it’s busy.

      1. Ta Jeff,
        Surely the NULL column suppression is purely in the domain of the java program? I was not expecting a full resultset and then suppress null columns.
        Obliged again
        Mungo

      2. thatjeffsmith Post
        Author
  14. I have installed ORDS 17.4.1.352 into an existing APEX 5.1 environment. At the time of the ORDS install, I did not migrate the existing APEX REST definitions. How can I migrate the APEX REST defs after the fact?

    1. thatjeffsmith Post
      Author
  15. The @file.sql trick can only be used in certain places, but not, say, in a DECLARE section. I’m looking for a way to merge files together when I compile, so a second file will be inserted into the first file, as if the two files are, in fact, one single file. Similar to the pre-processor directive #include in C/C++, that can appear anywhere in code, as long as the content of the #included file is valid at that point.

    Is there a way to merge a .SQL file into another .SQL file?

    1. thatjeffsmith Post
      Author

      Declare block is invoking the PLSQL engine… @ is a sqlplus command, the DB has no idea what that is.

      You can definitely have @script calls inside/nested in your scripts, but not as part of a SQL or PLSQL block.

  16. I’m getting a bit lost in trying to determine where to create new REST services. In the past, we have using APEX to do this… but now I’m reading that it may be preferred to do outside of APEX? Can you point me in the right direction here? APEX or straight up ORDS for REST service creation now? Is there a document that talks about this migration in general?

    1. thatjeffsmith Post
      Author

      Pick one or the other – do not mix.

      There was a time when there was only APEX available.

      Then APEX Listener was rebranded to Oracle REST Data Services and included the REST code path. So you could use either.

      You can migrate your RESTful Services from APEX to ORDS.

      At some point you will HAVE to migrate your APEX REST Services to ORDS.

      You’ll get the best REST support from ORDS.

      APEX 5.2 does offer ability to work with your ORDS defined services though, and it should be a very nice feature. They just won’t be stored in the APEX repository.

  17. Jeff, I love “SQLcl” and I greatly appreciate the work you’ve done to share what it can do. Because of that promotion I’ve been trying to use it more for supporting hundreds of dbs. My question is how best can I share various issues/bugs I run into? They’re mostly simple items (like “def ” does not display the variable’s value and definition) but I’m starting to get a list and not sure where to start posting them. I’m using 17.4, btw. Thx.

    1. thatjeffsmith Post
      Author
  18. Question regarding SQL-Developer 17.4 and explain Plan.

    In the versions <= 17.3 you could create an execution plan just by hitting F10. If you do so in 17.4 the Execution Plan window appears empty. In order to get a plan, i have to run the cursor once and then select the appropriate child cursor from the drop-down menu. Is it intentional behavior that you have to run the cursor once to get a plan displayed?

    Thanks

    1. thatjeffsmith Post
      Author
  19. Hi Jeff,
    I am working on unit testing using SQL Developer. The procedure has one input variable of type VARCHAR2 – PV_PNUM and output variable is of type REF CURSOR – C_OUT_CUR. It returns a set of records based on the input.

    To test if the procedure is working fine for the input passed and is expected number of returning rows for given input. I am writing a pl/sql block in Process Validation to access the output refcursor variable and loop through it / fetch data and count the no of records. Have tried accessing the variable as – {C_OUT_CUR}, {$C_OUT_CUR} and {C_OUT_CUR$}. I am able to access the input variable using {PV_PNUM}

    However, it doesn’t work and i’m getting error – Validation User PL/Sql Code failed: Unable to convert to REF CURSOR.

    Request you to suggest a way to validate the output of the procedure with ref cursors, as i do not want to write the whole procedure logic again in the dynamic value query in the test implementation block. It would be difficult to maintain the code at different places.

  20. Hi Jeff
    Please can you help.
    How can I remove query from spool output in sql developer.
    I am using Oracle SQL Developer 4.1.0.19 to spool the results of a query to a csv file.
    I want to hide the statements and query from the output file.
    Thank you.

  21. Fayyaz
    JANUARY 29, 2018 AT 1:37 AM
    While loading BLOB to one of the column in SQL DEV 17.4 and at the time of saving I get below error. This works fine in SQL Dev 4.1 though – is this due to JDBC version or something else please?

    UPDATE “ECHNWLT”.”IMS_IMAGEDETAILS” SET WHERE ROWID = ‘AAAhYbAARAAAkVJAAA’ AND ORA_ROWSCN = ‘179639352771’

    One error saving changes to table “ECHNWLT”.”IMS_IMAGEDETAILS”:

    1. thatjeffsmith Post
      Author

      One error saving changes to table “ECHNWLT”.”IMS_IMAGEDETAILS”:…. is that all you see? What’s in the log panel?

      Does this happen for any/all tables/blobs? If you create a new table to test, does that work?

  22. Hi Jeff,

    I’m using 17.4.0.355 and the SQL tab for materialized views results in:
    — Unable to render MATERIALIZED VIEW DDL for object DWPRES.MV_DIM_LM_QUIZ with DBMS_METADATA attempting internal generator.

    The SQL tab works fine for views, tables etc. including the tables underlying the MVs and I can use DBMS_METADATA to extract the sql at the command line.

    This didn’t happen with 4.2, and started when I upgraded to 17.

    Any thoughts?

    1. thatjeffsmith Post
      Author

      We run a TON of queries to grab everything for the MV DDL, one of those is probably not working for your environment. If you go look at the Log panel, there’s a ‘Statements’ page. You should see the queries we run when you hit the ‘SQL’ page.

      As a workaround, if you use the Tools > DB Export wizard, and can you get the DDL for your MV that way?

      1. Thanks Jeff, here’s the logged error:
        SEVERE 1428 234469 oracle.javatools.db.AbstractBuildableObject$BuildablePropertySupport Error : “java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
        ” building MV_DIM_DATE, executing sql:
        SELECT /*OracleDictionaryQueries.ALL_TABLE_TYPE_QUERY*/
        A.TEMPORARY, A.DURATION, A.IOT_TYPE, A.PARTITIONED, A.NESTED,
        ( SELECT 1 FROM SYS.DBA_EXTERNAL_TABLES E WHERE E.OWNER = A.OWNER AND E.TABLE_NAME = A.TABLE_NAME ) EXTERNAL_TAB
        ‘N’ SHARDED, ‘N’ DUPLICATED
        FROM SYS.DBA_TABLES A
        WHERE A.OWNER = ? AND A.TABLE_NAME = ?

        I got the same when I included the owner and MV name and ran the sql at the command line. Adding a comma after the EXTERNAL_TAB fixed that.

        So why is the comma missing in the constructed code?

        I thought that it may be a database version issue, but it’s the same against 11.2.0.4 and 12.1.0.2.

        Regards,
        Stuart.

    1. thatjeffsmith Post
      Author

      Who knows. Maybe SQLDev isn’t using the same tnsnames file that SQL*Plus is. You haven’t told or shown me anything that would allow me to help you.

      In the worksheet, run…

      ‘show tns’

      Does it list the ‘correct’ TNSNames file?

      If ‘yes’, then tell me what you mean by ‘spinning wheels.’

      1. Sorry to not have given you enough information

        I am using Windows 10

        in the User Environment Variables I have added an Entry for TNS_ADMIN => Directory where the tnsnames.ora file is located

        From the command line I get a response from tnsping

        D:\KTScase\Dba>tnsping P202

        TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 – Production on 28-JAN-2018 19:14:41

        Copyright (c) 1997, 2014, Oracle. All rights reserved.

        Used parameter files:
        D:\Oracle\Ora12.1.0.2\NETWORK\ADMIN\sqlnet.ora

        Used TNSNAMES adapter to resolve the alias
        Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = kts64)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = P202.knowtech.ca)))
        OK (0 msec)

        In the Preferences for Database => Advanced, I specify use OCI Thick Driver and point to the same directory for TNSNAMES Directory

        Who knows. Maybe SQLDev isn’t using the same tnsnames file that SQL*Plus is. You haven’t told or shown me anything that would allow me to help you.

        In the worksheet, run…

        How does one open a worksheet without connecting to any service??

        ‘show tns’

        Does it list the ‘correct’ TNSNames file?

        If ‘yes’, then tell me what you mean by ‘spinning wheels.’

        There is a little bar at the bottom showing Running and the blue spot keeps moving left to right and back

        Currently, I am trying to open a connection by clicking on the + . A folder symbol appears with Loading … and the spot is moving from left to right and back

      2. thatjeffsmith Post
        Author

        Your connection is working. The ‘loading’ message means that we’re in your db, and running a query to get a list of tables.

        Disable the thick connections option in preferences – you probably don’t need it – and try again. Also, open the View > Log panel. There should be a statements pages there. It will show which query is hanging.

      3. This in the logging page

        SEVERE 223 0 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.revalidateRowMap(BasicView.java:1546)

      4. thatjeffsmith Post
        Author
      5. thatjeffsmith Post
        Author
      6. On another environment against Oracle 12.2

        LEVEL SEQUENCE ELAPSED SOURCE MESSAGE
        —— ——– ——- ——————————————- ——————————————-
        SEVERE 1355 0 oracle.dbtools.raptor.phighlight.TableAdvice Failed to obtain database connection

        SEVERE 1354 251253 oracle.dbtools.raptor.phighlight.TableAdvice select owner,object_name from all_objects
        where object_type in (‘TABLE’,’VIEW’)
        and object_name in (?)

      7. thatjeffsmith Post
        Author
      8. How do I cleanly uninstall SQL Dev and then reinstall from scratch?
        Di I need to get rid of nything in the Users directory — ..\Roaming?

  23. I work for a corporation which has Oracle 12c, 11g, etc.; My role is developer. The corp requires 3rd party software to be vetted before it is permitted to be installed on its desktop workstations. I am the Oracle SQL Developer “sponsor” and a user of this product. One of the benefits I cited when promoting its use is that is supported with an enterprise license. I am now the product “owner” and the 1st point of contact for questions from anyone. I assume that the preferred forum for posting reproducible bugs is “My Oracle support” and I do not have an account. Using my pubic account, Ive posted to both SQL Developer Forum & Exchange ( someone kindly added two of them to “Bug -Log in Metalink”.) As an employee of an enterprise customer ( with multiple Oracle versions & licenses) I would prefer to use the “My Oracle Support” for reporting reproducible bugs rather than the public forum. Can you suggest a course of action how to get an account?

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  24. Hi Jeff,
    stumbled upon your site and will be a regular reader from now on….
    I have one question for you.

    it is about the Gutter in SQL Developer.
    when viewing or writing code, using the default syntax color, the gutter shows a blue-ish highlight to show the code that ‘falls’ within the block you are in. Moving the mouse over in the gutter, we get a thin blue line identifying the block or sub-block (whilst the blue-ish highlight exists as well) . This is great, especially trying to read-decipher existing ‘complex’ code
    .

    However, im quite fond of the twilight syntax. when using the twilight syntax highlighting, the highlight color of the gutter to identify the current block is the same blueish color as the default syntax , this making it ‘invisible’ againest the black background color of the gutter . moving over the gutter with the mouse still gives the blue thin line to identify the block or sublock which is fine….but the real ‘problem’ is the high light of the gutter. i have checked in the preferences but cannot alter this behaviour…. any ideas please !

    Thanks,

    George

    1. thatjeffsmith Post
      Author
      1. Goodmorning Jeff (morning in US I Believe)

        Thanks for your brief reply.
        however this does not do the trick. Maybe its my fault, as what i am describing is not the actual Gutter,
        but rather the thin strip on its right side, and on the left of the dotted vertical line !!!
        its where the boxed + or – appear for expanding and collapsing the code blocks !!!

        I hope i make myself more clear now.
        sorry for throwing you of course with the ‘gutter’ !!!

      2. thatjeffsmith Post
        Author
      3. hi

        I hope this comes through.
        i have painted arrows. the top and bottom ones show the bleuish identification of the block the cursor is clicked in.
        you can also see the thin blue line if you move the mouse in the thin strip area .
        the bluish line for clikcing in the code is visible (in the default syntax) and you also get an overlay of the thin blue line. so you can still see general – outside block with the shaded bluish background and the sub blocks with the thin line.

        lets see…

        https://drive.google.com/file/d/1TDJ7GzBX3zyyqhGF3iSwo0yuZJquKLRJ/view?usp=sharing

      4. thatjeffsmith Post
        Author

        Ok, that is hard to describe, thanks for the picture!

        I don’t have an answer unfortunately. I also don’t see a way to control the display of that particular 4 pixles-wide bit of the editor itself

  25. Hi,
    Used to use version 4.2 of SQL Developer…
    Now our DB is hosted on Oracle 12c, and would like to use the latest version 17.x.
    How can I migrate my preferences and connections info to the new version?

    can’t figure that out… thanks

    1. thatjeffsmith Post
      Author
  26. How do you create a connection in sqldeveloper using a proxy?

    The proxy has been created and I can login outside of sqldeveloper using the format me[proxy_name]@db_name successfully.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. But, If I try to execute the query, and close the sql developer, the query still works? or it’s killed. I need to execute the query in the server, not in the client…

      2. thatjeffsmith Post
        Author

        The only way to guarantee what you want is to login to the server and write a nohup shell script for sqlcl or sqlplus

        Or…you could write a database job to run the query.

  27. Hi Jeff,
    When upgrading from 17.2 to 17.4, do we need to download the version with the JDK or can we simply use the non JDK version since the JDK is already installed from a previous version install?
    Looks like the JDK version has sqldeveloper64W.exe and the non JDK has sqldeveloper.exe.
    Is there a difference. It looks like the non-64W version utilizes less memory.
    Thanks,
    David

    1. Both packages contain both versions of sqldeveloper. So my question should be, what’s the difference. We are using on Windows Server 2012.

      Thanks

    2. thatjeffsmith Post
      Author

      we don’t install any jdk. we EMBED a java home inside the zip…nothing else can use it but that copy of sqldev

      so if you only have java from the embedded jdk’s then you would ALWAYS have to grab the sqldev with the embedded jdk

      stay out of the bin directory, just run the EXE in the top level dir

  28. Hello, Im a beginner on SQL, self taught, and learning a lot from your page. That said, I need to load a lot of data from excel to my Oracle database. I read all your posts about the different methods BUT my problem is I am not able to preview the data as my excel file has 60,000 rows. I get the error message GC overhead limit exceeded. I do use xlsx format. What is the best approach? reduce the size of the file somehow?
    Thanks,

    1. Update. Just by converting to CSV file I was able to preview and import some of the data. Not all. Despite defining every column type and size, there were error messages related to data exceeding the size limit for them. Will try again…

  29. Hi Jeff,

    it’s not possible to modify a tables partitions by clicking on edit table, after installing the latest version of the SQL Developer 17.4 . The entry “Partition” is not visible anymore in the menue. Is it a bug?

    Regards

    Ulli

    1. thatjeffsmith Post
      Author
      1. The previous version was 17.3.1.279. The current version is 17.4.0.355. There was an entry in the edit table menue on the left hand side, together with other entries like Columns, Constraints,…
        All other entries are still there in the new version, but no way to edit the partitions at all? We can only add or modify the partitions when creating the table.

        ——————————————————–
        — DDL for Table PARTITION_TEST
        ——————————————————–

        CREATE TABLE “ZPDB”.”PARTITION_TEST”
        ( “COLUMN1” NUMBER
        ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        STORAGE(
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB”
        PARTITION BY RANGE (“COLUMN1”)
        (PARTITION “PARTITION1” VALUES LESS THAN (10) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB” ,
        PARTITION “PARTITION2” VALUES LESS THAN (20) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB” ,
        PARTITION “PARTITION3” VALUES LESS THAN (30) SEGMENT CREATION IMMEDIATE
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
        NOCOMPRESS LOGGING
        STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
        BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
        TABLESPACE “T_PUFFERDB” ) ;

      2. thatjeffsmith Post
        Author

        Yeah, your table works just fine for me. I’m on Oracle Database version 12.2.0.1.

        View > Log > Statements. Observe what the Edit dialog runs to build the screen. Take those queries, and run them in a SQL Worksheet. Any problems?

      3. We are using 11.2.0.4 with the latest patches.

        It seems to be a problem with the query to get the parameter “PARTITIONED”?!? There is a missing comma between “…EXTERNAL_TAB” and “‘N’ SHARDED…”!

        Please see the findings below:

        Error : “java.sql.SQLSyntaxErrorException: ORA-00923: Schlüsselwort FROM nicht an erwarteter Stelle gefunden
        ” building PARTITION_TEST, executing sql:
        SELECT /*OracleDictionaryQueries.ALL_TABLE_TYPE_QUERY*/
        A.TEMPORARY, A.DURATION, A.IOT_TYPE, A.PARTITIONED, A.NESTED,
        ( SELECT 1 FROM SYS.DBA_EXTERNAL_TABLES E WHERE E.OWNER = A.OWNER AND E.TABLE_NAME = A.TABLE_NAME ) EXTERNAL_TAB
        ‘N’ SHARDED, ‘N’ DUPLICATED
        FROM SYS.DBA_TABLES A
        WHERE A.OWNER = ? AND A.TABLE_NAME = ?

        This one works fine:

        SELECT /*OracleDictionaryQueries.ALL_TABLE_TYPE_QUERY*/
        A.TEMPORARY, A.DURATION, A.IOT_TYPE, A.PARTITIONED, A.NESTED,
        ( SELECT 1 FROM SYS.DBA_EXTERNAL_TABLES E WHERE E.OWNER = A.OWNER AND E.TABLE_NAME = A.TABLE_NAME ) EXTERNAL_TAB,
        ‘N’ SHARDED, ‘N’ DUPLICATED
        FROM SYS.DBA_TABLES A
        WHERE A.OWNER = ‘ZPDB’ AND A.TABLE_NAME = ‘PARTITION_TEST’;

        Is there any simple way to fix it?

      4. thatjeffsmith Post
        Author

        OK, so that query is wrong for a non-12c db. It’s already been logged as a bug on 17.4 and FIXED for 18.1 – which will be available later this quarter.

        In the meantime, best to work with v17.3 of the tool to get around this current limitation.

  30. In Sql 4.2.0.1 I can’t copy and paste into a script row. When I click on the row the first word in the row is highlighted. In older versions the first word wouldn’t highlightc rather, the cursor would click into a space in front of the first word and then I could add content as needed. Gosh. I

    1. thatjeffsmith Post
      Author

      In 17.4, I just ran via F5
      SELECT * FROM HR.EMPLOYEES

      In the script output, i triple-clicked to select the row.

      Ctrl+C

      And now I paste here…since you can’t see me paste into my worksheet.

                100 Steven        King          SKING      515.123.4567         17-JUN-87   AD_PRES         24000                                            90 
      
      1. I have the selected info that I want to paste but I can’t get the cursor to sit where I put it so I can copy and paste in front of it. I am adding to a row but when I click on the row the first word is highlighted and I can’t paste in front of the first word.

      2. thatjeffsmith Post
        Author
    1. thatjeffsmith Post
      Author
  31. Hello experts,
    I cannot find out a difference between to kind of exporting data from Oracle SQL Developer.
    When I exported Query from Oracle SQL Developer manually and then import this output to MS Access, there is no problem here.

    I create BAT file with SQL PLUS commands , which execute this SELECT with this parameter :

    set termout off
    set tab on
    set colsep “;”
    set pagesize 0 embedded on
    set linesize 8000
    SET FEEDBACK OFF
    set underline off
    SET TRIMSPOOL ON
    spool C:\…..\MY_Output.txt
    SELECT
    ;
    spool off

    Then I import this output file to MS Access, system show me this message : The search key was not found in any record”
    I try to google it, but I do not find any relevant answer for my issue. Can you please help me ?

    thank you

    Tomas

    1. thatjeffsmith Post
      Author
      1. Yes, I try to import data to MS Access via CSV file or txt file. When I do it manually, everything works well, when I try to automate this proces by sql plus executed by BAT file, system show me this error message in MS Access, so I think it related with SQL PLUS command.

      2. thatjeffsmith Post
        Author
  32. Hello,

    I would like to ask how to generate e.g. table names in DDL script in lowercase/uppercase/ … ? I generate script from Data Modeler.

    An hour ago I had found such option somewhere and I changed it to lowercase and it works. There were also other options (uppercase/mixed/”keep as it is”, etc.), unfortunately I’m not able to find this setting again.

    Thanks in advance.

    Regards,
    PeterR

    1. thatjeffsmith Post
      Author
  33. Jeff,

    Any plans to allow the Query Results tab to act as a floating window? Sometimes I need to move the results to my large, portrait oriented monitor. I’d rather continue my workflow in SQL Dev than export to Excel.

    Connor

    1. thatjeffsmith Post
      Author

      No, it’s a customized editor type in the framework…which means it does what we need, but it also means it’s REALLY hard to let it do things like this, without breaking other stuff.

  34. I cannot see in the Navigator tree synonyms for other users.
    My account has grants to use the synonyms and related objects in the other schema, I can run SQL statements to use the data, and I can see the list of available synonyms when I query ALL_SYNONYMS .

    But when I expand the Navigator tree, go to “Other Users”, select the other schema, and select the node “Synonyms” the list is always empty. The node “Tables” under the other schema shows the available tables. I have filtered the tables (under the other schema) and enabled “Include synonyms” but it did not make any difference. It looks like the option to “Include synonyms” works only in the node “Tables” of the current account.

    Am I missing a setting? I see the small problem in two versions 4.1.5.21 and 17.3.1.279

    Thanks

    Witold

    1. thatjeffsmith Post
      Author

      If i go to Other Users, Synonyms tree, and expand – I see the synonyms for that schema.

      This is the SQL we run

       
      SELECT * FROM ( SELECT t.*, 
      substr(concat,1,instr(concat,'/')-1) TABLE_TYPE,
             substr(concat,instr(concat,'/')+1, LENGTH(concat)-instr(concat,'/')) TABLE_ID
      FROM (
       SELECT s.synonym_name, s.table_owner, s.table_name, 
        o1.object_id,
        o1.object_type || '/' || o1.object_id concat
       FROM Dba_synonyms s, sys.Dba_objects o1  
       WHERE s.owner = :SCHEMA
       AND s.owner = o1.owner 
       AND o1.object_type = 'SYNONYM' 
       AND s.synonym_name = o1.object_name 
      ) t )

      If you don’t have access to the DBA_ views, it’ll fall back onto ALL_ views.

      1. Thanks for the quick reply and SQL.

        I will have to work with DBAs to check permissions. I don’t have access to DBA_OBJECTS and ALL_OBJECTS does not show me the synonyms from the other schema. But ALL_SYNONYMS has the synonyms. It looks like something is filtered out in the query for ALL_OBJECTS

        Thanks
        Witold

    1. thatjeffsmith Post
      Author

      I don’t speak on behalf of Oracle.

      But.

      Is your machine secure? If it is, you’re fine. If it’s not and you think someone other than you can get access to your machine, then you shouldn’t save any passwords.

      Or let Google/Chrome save your passwords.

      1. When you say “someone other than you can get access to your machine” , do you mean with my Windows login and password?
        If they got the file that has the passwords, could they import it and use it?
        Are the passwords themselves encrypted in a manner that is very difficult to decrypt?

      2. thatjeffsmith Post
        Author
    2. thatjeffsmith Post
      Author

      If it weren’t considered safe by us, we wouldn’t offer the feature. You need to decide if the feature is safe for you.

      We do encrypt the passwords. You can’t simply copy the connections file off of someone’s machine and use it – we’ll strip out the passwords unless you use the connection export/import routine in the UI.

  35. Good afternoon from Australia

    Using:
    Java(TM) Platform 1.8.0_151
    Oracle IDE 17.4.0.355.2349

    Just updated to 17.4. All went smoothly. Connected to one of my 11GR2 databases and in the DBA section then chose Security/Users and get

    An error was encountered performing the requested operation:

    ORA-00904: “ALL_SHARD”: invalid identifier
    00904. 00000 – “%s: invalid identifier”
    *Cause:
    *Action:
    Vendor code 904

    Although I can continue every time I select the General tab of Users I get the same error.

    A bug or do I need to make a change elsewhere?

    Kind regards

    Steve

    1. Hi All

      Minor update: Installed this on Windows 10 and Widows 7 desktop machines with the same result.

      Regards

      Steve

      1. thatjeffsmith Post
        Author
    2. thatjeffsmith Post
      Author
  36. Hi Jeff,

    I am using Oracle SQL Developer 17.3.1.279 and would like to copy my query from the Developer to MS Word keeping the query ‘formatted’ when I paste it into Word. (keeping the colors/fonts). Unfortunately, when I paste the query into Word, it drops the formatting. I’ve searched the blogs for a resolution to this issue, but have been unable to find the answer.

    Can you tell me how to keep the SQL Developer format (font/colors) of the query when copying/pasting into another application such as MS Word?

    1. thatjeffsmith Post
      Author
  37. Hi Jeff,

    The enhancement request that I referred to in my first post was #45481 – the ability to catch PLSQL named exceptions in the unit tester.

    I hope this helps,
    Mark

      1. Hi Jeff,

        I have noticed some more enhancements in addition to the granting of roles to stored PL/SQL units that would aid the modelling of an application that uses the smart-database model and CBAC:

        *) When comparing two models, the tool identifies objects that have been created/modified/removed and it is possible to generate the differential DDL. For some reason role objects whilst are identified as different, are not considered for DDL generation. I wonder if other objects are also excluded in compare mode?

        *) Support for granting roles to users in DELEGATE mode rather than ADMIN.

        *) A way of generating an editioning view based upon a table definition for the purposes of EBR.

        Do you see the benefits of the above and will they make their way into the product?

        Many thanks in advance,
        Mark.

      2. thatjeffsmith Post
        Author

        I don’t see much uptake in these features, or even folks asking for the support. For now, it’s you. Some of this can be done already with custom scripting that the tool supports. Have you considered asking for help building those in the forums?

  38. Hi Jeff,

    I am currently using Oracle SQL data modeller 17.3 and am trying to set up an Oracle 12cR2 physical model to support code based access control and invoker rights.

    I can create roles and am able to assign privileges to that role but I cannot find a way to assign the role directly to a procedure/function/package definition which is required for CBAC to work. Is there a way to do this in the tool or is it a feature request?

    Slightly off topic, is the Oracle SQL developer exchange still active? I posted some feature requests back in August that haven’t changed status (as far as I can tell anyways).

    Many thanks in advance and keep up the good work!!
    Mark.

      1. thatjeffsmith Post
        Author

        >>to assign the role directly to a procedure/function/package definition
        I read this and immediately thought, I don’t think you can assign a role to a PL/SQL object. I’m happy to admit I’m wrong though – can you show me a docs link describing what you’re looking for?

  39. Hi Jeff,

    I have an issue with the SQL DEVELOPER unit test repository. I am running a unit test against a function that returns REF CURSOR. I do not need to test the returned REF CURSOR data so have left the ‘Test Result’ box unchecked. This succeeds until I attempt to run a subsequent ‘Process Validation’ step, when I then get the error below. Please advise.

    Regards,
    Steve.

    The following procedure was run.

    Execution Call
    BEGIN
    :1 := “SETUP”.”RPT_GL_PKG”.”SPS_BALANCE_EXPORT”(P_SYSCM=>:2,
    P_GLP=>:3);
    END;

    Bind variables used
    :1 REF CURSOR OUT (null)
    :2 NUMBER IN 1
    :3 NUMBER IN 155

    Execution Results
    ERROR
    : Expected: [Any value because apply check was cleared], Received: [SYSCM GLP ACCOUNT_NUMBER SEGMENT0 SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4 SEGMENT5 SEGMENT6 SEGMENT7 SEGMENT8 SEGMENT9 ACCT_DESC ACC_TYPE TYPE_DESC PTD YTD PRIOR_PTD PRIOR_YTD
    —…
    ]
    Validation User PL/Sql Code failed: Unable to convert to REF CURSOR.

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *