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,577

  1. Do you know a way to rename a set of columns with a regular expression in Data Modeler?

    I try to explein better with an example I have a set of columns start with XX* and I want to replace XX with YY.

    Do you have any suggestion ?

    Best Regards

    Luca

    1. thatjeffsmith Post
      Author
  2. Hi Mr. Smith

    In SQL Developer 4.1.3, can we have a chart title whose content is based on bind variables or something similar? Let me explain,

    my SQL statement is the following:
    select nom_reseau,
    pct_cpu_used
    from cumulative_by_year_month
    where year = :YEAR
    and month = :MONTH
    order by 2 desc

    the chart report works just fine but i would like the chart title be something like “Cumulative CPU usage – May 2016” where YEAR = 2016 and MONTH = 5

    Regards,

  3. Hi,

    Can we know whether SQL Developer Data Modeler supports stored procedures. What kind of analysis or diagrams it can generate?

    Thanks

    1. thatjeffsmith Post
      Author
  4. Hi,

    I am running unit test in sql developer and my test is failing just because there are additional dashed in the ref-cursor result set in proc out parameter, which is not the case with dynamic value query.
    Can you please help me with this! Below is the test execution output in debug mode.

    The following procedure was run.

    Execution Call
    BEGIN
    “SCHEMA”.”PKG”.”PROC”(I_PAR1=>:1,
    I_PAR2=>:2,
    I_PAR3=>:3,
    I_PAR4=>:4,
    O_PAR5=>:5);
    END;

    Bind variables used
    :1 VARCHAR2 IN
    :2 VARCHAR2 IN
    :3 VARCHAR2 IN (null)
    :4 VARCHAR2 IN (null)
    :5 REF CURSOR OUT (null)

    Execution Results
    ERROR
    O_PAR5 : Expected: [COL1 COL2 COL3 COL4 COL5
    ———- ————————————————————————————————————————————————————————————————————————————————————— —————————— ——————– —————————————————————————
    VALUE1 VALUE12 VALUE13 VALUE14 VALUE15
    VALUE2 VALUE22 VALUE23 VALUE24 VALUE25
    ], Received: [COL1 COL2 COL3 COL4 COL5
    ———- ————————————————————————————————————————————————————————————————————————————————————— —————————— ——————– ——————-
    VALUE1 VALUE12 VALUE13 VALUE14 VALUE15
    VALUE2 VALUE22 VALUE23 VALUE24 VALUE25
    ]

  5. Hi Jeff,
    I am very thankful for all that I have learned in just a few months from your site. I have one more step in my project which I am having trouble with. I have seen many posts with this same question. None have a good answer. Most have some not nice responses. I have an oracle table. Every day I run a SQL script to update the table and then I export that table using SQL developer into an excel file on a shared drive. I use the wizard for that. I have an SSIS package that takes the excel file and completes the project. Using the SSIS to aid in the excel export is not an option. How can I automate the daily process of running the script, but more importantly exporting the table in excel format to the shared drive.

    1. thatjeffsmith Post
      Author

      Hey Josh, thanks for the feedback. It’s cool to know that there are real people out there than are getting something from the blog posts.

      I’m going to ignore the SSIS bits – b/c I work for Oracle AND b/c I don’t know much about it. But.

      I would do this.

      Don’t use SQL Developer.

      Use SQLcl – it’s a command-line interface FOR SQL Developer.

      Write a script – it will be VERY simple.

      Something like…


      set sqlformat csv
      set echo off
      set feedback off
      spool C:\your_drive\your_file.csv
      @your_script.sql
      spool off

      Inside of your_script.sql – you’ll have your query. the output of running this via SQLcl will be a CSV file with your data in it.

      Pretty darn sure SSIS can handle CSV just as well as it can handle Excel.

      Otherwise, there’s no way to schedule SQLDev to start, connect, run query, export results to Excel.

  6. Hello Jeff,
    In sql developer 4.1.3, the db connection box on the upper right corner of a sql worksheet lists the db connections in alpha order before any connections are made. However, once a connection is made on any open worksheet, that connection is then reordered to the top of the list. Is there an option to preserve the alpha order regardless of whether or not a db connection is connected or not?

    Thanks,

    David

    1. thatjeffsmith Post
      Author

      nope, we sorted active connections up top as they were most likely to be used for something like a db compare or running a report, or anything else that uses a connection picker

    1. thatjeffsmith Post
      Author
      1. Won’t I lose all my SQL History if I do that, reset windows to Factory, and I’d have a lot to lose? Is there somewhere on my hard drive where Oracle SQL developer is storing all my SQL, where I can find my previously run queries that way?
        Thanks so much!

        1. thatjeffsmith Post
          Author
  7. Hello Jeff,

    I am trying to export data in the delimited format using sqlcl. I see an option to set the Line Terminator in SQL Developer, and wanted to do the same using SQLcl. Can you please let me know how I can achieve this using SQLcl?

    Thanks in advance!

    1. thatjeffsmith Post
      Author
      1. Thanks for the prompt response, Jeff!

        I am seeing different behavior in how SQL Developer exports data vs using SQLcl to export.

        In the table that I am trying to export, for some of the records in the COMMENTS column, we have line feeds that have been inserted by the end users, and when I export this data from SQL Developer, I see the special character LF in the comments, whereas if I export using SQLcl, I see the special characters CRLF in the comments. Is this a bug or configuration issue? Can you suggest a workaround?

        Thanks!

        1. thatjeffsmith Post
          Author

          I think if SQLcl is running on *NIX it’ll use LF and if it’s running on Windows it’ll use CR/LF pairs.

          We ask the OS what it wants to use for a line terminator via
          System.getProperty(“line.terminator”);

          So if you want CRLFs, run SQLcl on Windows. If you don’t want CRLFs, run SQLcl on Linux/OSX/Unix.

          And since that’s a property, you can actually set that yourself with the SCRIPT command.

          script
          2 var FormatRegistry = Java.type(“oracle.dbtools.raptor.format.FormatRegistry”);
          3 FormatRegistry.setLineTerminator(“?? “);
          4 /
          makes for lots of beer

          Now you have Beer Emoji line terminators.

          1. Thanks again for that response!

            However, i am afraid I’m not familiar with the solution you provided. Also, I am running this from windows, so, if you could provide the solution to setting the Line Terminator from within windows, I would really appreciate that.

          2. thatjeffsmith Post
            Author
  8. Hi,

    Another ERD question. I’ve used the “Responsible Parties” to show ownership (dead good). Is there a way to show that info on the symbol for an entity in a diagram the same way I can choose to see attributes?

    regards

    jsa

  9. Thanks for all of your work on SQLDeveloper and educating us!

    When formatting source code (F7), is there a way to have the parameters line up below each other? i.e.:
    bompexpl.exploder_userexit (verify_flag => 0,
    org_id => in_organization_id,
    order_by => 1,
    grp_id => in_group_id,
    session_id => in_session_id,
    levels_to_explode => 9,
    bom_or_eng => 1,
    impl_flag => 1,
    plan_factor_flag => 2,
    explode_option => 1,
    module => 1,
    cst_type_id => 0,
    std_comp_flag => 2,
    expl_qty => 1,
    item_id => htrs.inventory_item_id,
    alt_desg =>”,
    comp_code => ”,
    rev_date => SYSDATE,
    err_msg => l_err_msg,
    error_code => l_error_code);
    I use the SQLDeveloper format function extensively, and every time I use F7 to reformat code with procedure or package calls, it strings out the parameters on one line.

  10. Hi Jeff,

    I have an application that uses the thick database paradigm and all application users are database users. For the middle-tier connection pool, a separate user called “web” has been created. All application users must connect via proxy to gain access. In SQL*Plus this would be achieved by:

    $sqlplus web[mark]@testpdb

    Ideally however, as well as providing the password for the “web” user, I would also like to provide the password for “mark” as this provides an extra level of security. I know this is possible using JDBC and OCI but this is not possible in the SQL*Plus application. Is it in SQLcl?

    After posting this question on Ask Tom, they suggested raising an enhancement request for SQL*Plus and perhaps SQLcl. By the way, is SQL*Plus now deprecated in favour of SQLcl?

    Many thanks
    Mark

    1. thatjeffsmith Post
      Author
  11. I have several User Defined Reports, each associated with a specific database. Is there a way of having a report associated with a database so I don’t have to choose the database each time I run it?

    1. thatjeffsmith Post
      Author
  12. Hi Jeff,

    In Data Modeler, would it be possible to have the entity names in Logical Model and the table names in Relational Model un-prefixed, whereas in the generated DDLs, all the table names only would get a PREFIX_?

    Thanks,
    V

    1. thatjeffsmith Post
      Author
  13. Hello Jeff,

    I am doing a project where each day the same comma-delimited text file will need to be imported to my Oracle database from a shared drive (same but with new data). A one-off import works fine using the wizard, but I was wondering if it is possible to somehow automate the import process (e.g. by setting up some sort of scheduled job or something like that).

    The idea is to have it so the database imports/updates without my daily intervention — such that it knows to import at 10:00AM, for instance.

    Do you know if this is possible?

    I am very new to Oracle, and I am currently using SQL Developer. Couldn’t figure this out for the life of me!

    Thanks!

    1. thatjeffsmith Post
      Author
  14. Hey Jejj,

    How can I load/unload MS excel datasheet directly into a remote oracle server without any client installation at my laptop/PC ?

    Thanks

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          Define ‘installation.’

          The Instant Client is just a zip file you can put down, no installer to run. It’ll have the ODBC driver for you. Or, get a 3rd party Oracle ODBC driver and use that.

          I’d not do this to begin with, but you’re not asking me for the ‘best way’ to copy data from Excel to Oracle…

  15. Can the SQLDeveloper Report/Chart feature create a line chart where x contains date/time and y the values to be plotted?

    1. thatjeffsmith Post
      Author
  16. I rebooted my Win7 machine today, and when I started up SQLDeveloper (4.0.2.15) it asked if I wanted to import user preferences. I shrugged and clicked no. It started up and all my connections are gone!!! Where did they go, and where can I find them?

    1. thatjeffsmith Post
      Author

      you should have a system4.0.2.15… folder in your window user appdata roaming profiles sqldeveloper folder – of course now it has all brand new settings. you could delete the folder and let it import from an older existing directory there if you have one

  17. I want to do a difference between a single schema in the source database and a single schema in the target database. Can I use the database diff tool to do this? I cannot find a place to specify which schema in the target database I want to use for the compare.

    1. thatjeffsmith Post
      Author

      Are the 2 schemas the same name? If so, then in the compare wizard, set the ‘maintain’ option – that tells us to resolve the objects in the 2nd source using the schema attached to the objects in the first source.

      If they are not the same name, you’ll have to login AS the user you’re trying to compare.

  18. Just saw that there is a new version available for download from a couple of days back. Any information what was changed?

    For the record, “insert” stmts still don’t make it into history even with this version.

    1. thatjeffsmith Post
      Author
    1. I found a workaround – to define 1=” 2=”… in login.sql, and clear them after use. Not elegant, but gets the job done.

  19. I’ve got sqlcl working fine on windows and like it quite a bit.

    I’m also trying to get it running directly in our Solaris (sparc) environment and am having difficulties.

    I’ve tried using both Java 7 and Java 8 JDKs, and I’m getting the same error when starting sqlcl

    May 18, 2016 8:57:30 AM oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli log
    SEVERE: null
    java.lang.NullPointerException

    Any ideas how I might fix this?

  20. Jeff,
    Great articles!! Very low tech question for today.
    Oracle SQL Developer 2.1.1.64
    File > open (or Ctrl+O, or Folder Icon)
    Left side shows your favorite folders. Long ago I managed to add my own folder. Now I cant recall how to add/delete these. Been googling it. See a fw people asking the same, but no answers. Can you shed some light on this please.

    thanks
    Cam

    1. thatjeffsmith Post
      Author

      Not sure how we would have done that in v2.1 – maybe a page in the preferences?

      In version 4.1, each editor’s file/open/save dialog will remember the last 5 or 6 directories you’ve used.

  21. Hi

    Is it possible to connect to SQL Server 2008 with sdcli? I am using SQL developer 4.1.3.20 and jtds1.3.1 and can successfully connect using windows authentication using the GUI. However, the sdcli guide seems to be missing the syntax for SQl Server connections.

    1. thatjeffsmith Post
      Author
  22. Hi Jeff,

    thanks for your great blog with so many helpful tips and tricks.

    Currently we are evaluating a new PL/SQL Development tool as a replacement for a commercial product.

    As i’m working with SQL Developer for some years now, i proposed to use it instead of purchasing another (cheaper) commercial tool.

    During evaluation we came across code completion feature (which i personally do not like too much, no matter in which tool) and we found out, that he behaviour is not really correct when a package procedure or function uses a parameter of %ROWTYPE or Type declared in package header.

    As soon as the round opening bracket for the procedure parameters is written, completion insight pops up and lists the available parameters for the procedure.

    BUT: to my surprise a %ROWTYPE parameter, lets say it’s of type employees%rowtype and p_emp_row is it’s name, will be resolved into single parameters, so instead of
    employees_tapi.upd(p_emp_row employees%rowtype)
    as it is declared in the package spec, the parameters listed are:
    employees_tapi.upd(p_employee_id number, p_first_name varchar2, ….

    IMHO this behaviour is not correct, as it does not reflect the signature of the package procedure and cannot be used in the manner the code would generated.

    I could imagine that internally the rowtype procedure parameter is splitted into it’s fragment because SQL Developer does not pick up the complex argument from all_arguments which is at data_level 0, instead it picks up the single arguments with data_level 1 which all have the complex type parameter as their parent and presents those components as the parameter list for the procedure.

    Is there any way to tell SQL Developer not to resolve type parameters into it’s fragments?

    Best regards and thanks in advance.

    Thomas

    1. thatjeffsmith Post
      Author

      No, I think that’s a bug.

      >>(which i personally do not like too much, no matter in which tool)
      I actually recommend disabling the AUTOMATIC component of the insight, so you only see it when you want it.

      If you drag and drop your package procedure to the Worksheet, does it generate a correct ANON block?

      1. Hi Jeff,

        thanks for the fast response. I tried dragging the procedure from object navigator (from packages node) to anon pl/sql block – but that doesn’t work either.
        This time the procedure has both variants: first the rowtype parameter is shown correctly, but in addition the rowtype parameter gets resolved, so that in fact the parameters are duplicate, the correct one and resolved as well …

        Btw, i’m using the most current version 4.1.3 according to Oracle Technology Network download page.

        Regards
        Thomas

        1. thatjeffsmith Post
          Author
  23. I have installed sqlcl on my Windows 7 machine and can connect to a database but do not gat any output from commands I type in e.g.

    Picked up _JAVA_OPTIONS: -XX:MaxPermSize=512m
    Java HotSpot(TM) Client VM warning: ignoring option MaxPermSize=512m; support was removed in 8.0

    SQLcl: Release 4.2.0.16.131.1023 RC on Thu May 12 14:15:55 2016

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

    Last Successful login time: Thu May 12 2016 14:14:40 +01:00

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

    SQL> tables
    tables

    SQL> help edit
    help edit

    SQL> select * from user_tables;
    select * from user_tables;

    SQL> tables
    tables

    SQL> show jdbc
    show jdbc

    any ideas ?

    Thanks,

    Jim

  24. Hi

    We would like to protect our source in DEV from being edited by the developer until the source has been ‘checked out’. Is this a concept that SQL developer can support?
    Thanks
    Loz

    1. thatjeffsmith Post
      Author

      I would not grant privs to your devs. Instead, they would run local instances with pl/sql they got from source control. They’d make their changes, and commit the source, and then you’d have automated ‘builds’ that updated the the DEV database PL/SQL. There’s a 3rd party extension that does this client side – blocks compiles on objects that are checked out, but it’s not cheap.

  25. Hey Jeff,
    Ok, confession time. I had reverted from 4.0 back to 3.2 after failing to get comfortable with the find/replace ribbon and a general loss in productivity.
    Can you tell me if there’s a config option on 4 to use the find/replace as a popup instead of that sticky ribbon at the top? Maybe I can try again…
    And yes, I know….petty. 🙁

    1. thatjeffsmith Post
      Author

      Sorry, but no. Make sure you were trying 4.1 and not 4.0 as we made several tweaks to get the newer F&R up to snuff.

      Tell me where it hurts, maybe I can provide some relief.

      1. Just muscle memory I guess. You do something long enough…it’s hard to break the habit. ctrl-r/tab/tab/ctrl-v etc…
        I find myself doing a lot of back-tracking in 4.1. Even after getting in the flow of the new tool – I relapse. Gotta retrain the old dog.

        Thanks for the confirmation.
        Cheers!

  26. Hi Jeff,

    I am brand new to SQL Developer and the small taste of power I have experienced so far is incredible.

    I have been saving my favorite SQL statements and noticed the default save directory in the Create SQL File dialog box is not the same as what I specified in Database – Worksheet – Select default path to look for scripts.

    Are these two different animals, is it possible to specify a default path to save new SQL files?

    Your Top 10 Preferences to Tweak post was very helpful and a great place to start!

    Thanks for keeping this great site going,

    Jason

    1. thatjeffsmith Post
      Author

      If you’re using version 4.1, then the directory/file pickers for opening/saving files should remember the last 10 or so locations PER editor.

      So the worksheet should remember this when opening/saving files in the worksheet.

      But the directory picker in the preferences for the worksheet would be a different animal…

      >>Are these two different animals, is it possible to specify a default path to save new SQL files?
      Yes, completely disjoined. But it should remember the last several directories you saved a SQL file to – shown on the left of the dialog.

  27. hi Jeff,

    Am a huge fan of your blogs, which are greatly informative and have the most accurate information.
    I had a question for which I have been hesitating to ask, as it might sound too much to ask for.
    I was reading through about the Code Templates in SQL Developer. Is there a way i can create a shortcut of code template say SS and parameterize it. So if am typing ss-employee , it should result in select * from employee (for example employee table here).
    Today i am able to user SS which brings up, select * from on screen perfectly. I just had a thought if there was a way in which i could pass the table name as parameter along with ss and get the output as select * from table (from the parameter)

    [ This is coming from the .kshrc file i had created on unix box and used $1 to substitute the input passed and that acts as a parameter. ]

    Let me know if the above did make sense or not or I could rephrase it differently.

  28. Hi Jeff Huge fan of your posts…. Can you please post more descriptive information about Connect By in Oracle…how to use it!! When to use it!!!

  29. Hello,

    I’m trying to format the headers of my SQL output so that some really long names can be printed over multiple lines and take up less width on the screen. I’m able to get it to work in the Script Output window:

    SQL> column really_long_name clear;
    SQL> select ‘short’ as really_long_name
    from dual;

    REALLY_LONG_NAME
    ——————————–
    short

    SQL> column really_long_name heading ‘Really|Long|Name’;
    SQL> select ‘short’ as really_long_name
    from dual;

    Really
    Long
    Name
    ——————————–
    short

    But I’m not able to get this to work in the Query Result window. Is there a way to do something similar to this in that window? Ideally, I would have the column header names printing over multiple lines and export the data to my clipboard in tab-delimited format, which I would then paste into Excel.

    I’m currently using version 4.1.2.20, with the pre-packaged Java.

    Thanks!

    1. thatjeffsmith Post
      Author
      1. Okay, I thought that might be the case, but figured I’d ask on the off chance that I was just missing something.

        Thanks for taking the time to answer!

  30. Hi Jeff,

    When running “info v$ v_$ views” it doesn’t work at all or it the output is incomplete.
    Ex:
    SQL> info v$session
    ORA-20005: ORU-10034: context argument must be integral, 0 to 9
    ORA-06512: at “SYS.DBMS_UTILITY”, line 153
    ORA-06512: at line 1

    SQL> info v_$session
    Columns
    NAME DATA TYPE NULL DEFAULT COMMENTS
    SADDR RAW(8 BYTE) Yes
    SID NUMBER Yes
    SERIAL# NUMBER Yes
    AUDSID NUMBER Yes
    PADDR RAW(8 BYTE) Yes
    USER# NUMBER Yes
    USERNAME VARCHAR2(30 BYTE) Yes
    COMMAND NUMBER Yes
    OWNERID NUMBER Yes
    TADDR VARCHAR2(16 BYTE) Yes
    LOCKWAIT VARCHAR2(16 BYTE) Yes
    STATUS VARCHAR2(8 BYTE) Yes
    SERVER VARCHAR2(9 BYTE) Yes
    SCHEMA# NUMBER Yes
    SCHEMANAME VARCHAR2(30 BYTE) Yes
    OSUSER VARCHAR2(30 BYTE) Yes
    PROCESS VARCHAR2(24 BYTE) Yes

    1. thatjeffsmith Post
      Author
  31. Hi,

    Just a few quick questions. I’ve used lots of data modelling tools, including Designer 2000, way back when. I just want to use the ERD. If there is a source of answers for these types of questions, please just point me to it.
    1. Is the info (definitions, diagrams) stored locally or can I point it at a server?
    2. Can the data model be shared with other modellers?
    3. Do we have any form of versioning?

    er, that’s it.

    regards

    jsa

    1. thatjeffsmith Post
      Author

      1. locally, although you can create a reporting repository in an Oracle Database if you want to query your designs
      2. yes
      3. yes, uses subversion under the covers

  32. Hello Jeff, I found you post on font sizing but I am looking for changing interface icons size too. I do not like to use scaling on high-DPI monitor for many reasons and I would like to go native. Look-and-feel: Windows, doubled font size and doubled icons size would do exactly that, unfortunately only first two are possible. It generally helps with most applications. Is there a config file defining icons somewhere? I didn’t have the luck finding it so far.

    1. thatjeffsmith Post
      Author
      1. Hmm. So you really think all icons are hard-coded? That means scaling is the only option how to run SQL Developer on modern screens, that’s quite a bummer.:-(

  33. Hi,
    I’ve just started using SQL Developer after previously using SQL Management Studio. One really useful feature of Management Studio was being able to set up query shortcuts (e.g. Ctrl+4 = select top 10* of highlighted code). Is this possible in SQL Developer – it is so useful for checking tables and joins, and saved me so much time in Management Studio.

    Thanks for your help

    1. thatjeffsmith Post
      Author
  34. Jeff,
    I just upgraded to 4.1.3 and like the new feature to get a popup of the field list when connected to a 12c DB. The feature works when I open a new worksheet on a 12c connection, however, it doesn’t seem to work if I open an existing worksheet and then connect to a 12c connection. Is there a way to get the feature to work in my second scenario above?

    Thanks,

    David

  35. Hi Jeff, I try to use the sqlldr in sql developer tool as you suggested. But I got the below error. Kindly request you to guide me in this regard.

    Error Message
    “The program can’t start because oraodm12.dll is missing from your computer. Try reinstalling the program to fix this problem”

  36. Hi Jeff,

    We have a problem querying a production database from SQL Developer.

    Connected as user X we run a select * from X.TABLE from SQL Developer and the program hangs. From OEM, we saw the following query but we never see the original query.

    SELECT :”SYS_B_0″ type, username owner, username object_name, null column_name, null column_id, null data_type
    FROM all_users
    WHERE rownum <=:"SYS_B_1" and username like :1 union all
    SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
    FROM all_objects
    WHERE object_type =:"SYS_B_2" and object_name not like :"SYS_B_3" and rownum <=:"SYS_B_4" and object_name like :2 union all
    SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
    FROM all_objects
    WHERE object_type =:"SYS_B_5" and object_name not like :"SYS_B_6" and rownum <=:"SYS_B_7" and object_name like :3

    Nevertheless, if we run the same query from SQL PLus it runs well.

    We are using SQL Developer 4.1.3 (latest version).

    Regards,
    Alejandro.

    1. thatjeffsmith Post
      Author
      1. Thank you Jeff,

        We found that the problem could be related with caps and quotes in the query.

        If we run this query it runs well:

        select * from “X”.”MYTABLE”;

        However, if we run the query without the quotes it hungs.

        select * from X.MYTABLE;

        We are connecting to a PDB in an Oracle 12c Database.

        The problem exist only when connecting with the X user (PDB user) and not with CDB user (like system).

        Regards,
        Alejandro.

        1. thatjeffsmith Post
          Author

          those are equivalent queries, in theory…so, weird.

          you’ll want to file an SR with the database team I think though…and try it in SQL*Plus or with a THICK connection in SQLDev to see if it’s a db issue or maybe a JDBC driver issue

          1. The query runs well with SQL Plus .

            We also try the query from SQL Dev with the option Use Oracle Client and use OSI/Thick Driver, but we get the same behavior, the program hungs and the we didn’t get any result.

            Do you still think is a db issue?

          2. thatjeffsmith Post
            Author
          3. Hi Jeff,

            We opened a SR with this issue.

            They gave us this explanation about it, but we still some doubts about the answer and the one that you said:

            “If granting DBA causes the query and all else to work, then that is a requirement for SQLDEV. SQLDEV has no privileges in and of itself since it is just a GUI tool for SQL.
            Sometimes it is a matter of increasing the JVM heap, but if just a privilege grant solves the issue, it is unlikely that it is a configuration issue.
            If you can discover which privileges from the DBA role are required, then you can follow this note to add them to your target users:
            How to provide Data Dictionary access to SQL Developer users without granting the DBA role ( Doc ID 1274851.1 )”

            The query we run doesn’t include CATALOG tables (select * from X.TABLE) but we see in the db a query to ALL_TABLES and ALL_OBJECTS tables (from EM).

            Granting the SELECT_CATALOG_ROLE solves the problem but we think this is too much to resolve the issue.

            Regards,
            Alejandro.

  37. Hi Jeff, I´m using Sql Developer Data Modeler 4.1.3

    In my logical model there is a many-to-many-Relationship between two entities. This Relationship has one additional attribute.
    Forward engineering of the logical model to a relational model leads to

    1) two tables — one for each entity
    2) one table for the many-to-many-relationship
    3) two 1:n – foreign-key dependencies from each “entity”-table to the “relationship”-table

    but
    4) two 1:n foreign-key dependencies from one “entity”-table to the other “entity”-table

    How to supress No. 4?
    In the relational model No. 4 leads to adding primary key of one “entity”-table as foreign-key to the other “entity”-table and vice versa. In my opinion adding the primary keys of the “entity”-tables to the “relationship”-table is sufficient.

    Please help

  38. I’ve been using SQL Developer to try to export data for an old website. For options like JSON or CSV, however, SQL Developer doesn’t escape quotes, which you could imagine wreaks havoc on tag properties in HTML. Are there any settings on exports that could improve the situation?

    1. thatjeffsmith Post
      Author

      I don’t know how you’re storing your websites, one big record, CLOB, per page/site, or many rows to build a page…but take a look at our built-in functions like REPLACE and REGEXP – you can manipulate your data so you can handle the quote characters any which way you’d like.

  39. I was wondering how (or if it is possible) to have tab guidelines (ie for each tab space when set at x) there is a faint line to show the tab spaces ie at each x columns you have a tab guide.

    1. thatjeffsmith Post
      Author
  40. Hi.
    I am using the 5.04 version of the mySQL driver with ver 4.03 SQL D on a Windows 8.1 laptop. I have this 32 bit version of Java installed, jre-7u25-windows-i586. I can run 2 simple queries against a simple table with 20 rows, for example select * from . On the third try I get this error, Query execution was interrupted. I looked online and here but can’t find out why this is occurring. Thanks.

    1. thatjeffsmith Post
      Author
  41. Hi Jeff,
    I am upgrading from SQL Dev 1 (yes 1!!) 32-bit to v 4-64 bit. and I exported the connections from the old version hoping to import them into version 4. The error I’m getting is: “import file is in old format” Now I understand that it is a very old version, but we have about 50 connections, is there a quick way to import them? Or do I have to recreate the connections? I’m dreading your reply 🙂
    Regards
    Vic

    1. thatjeffsmith Post
      Author

      Not so bad news. Go get version 2.1 – you’ll have to click through the ‘older versions’ links on the download pages a few times. Upgrade to that, and then try upgrading from that to version 4.1.

      And then don’t wait 9 years to upgrade again 🙂

  42. I am new to SQL Developer. Your website has greatly helped me get up and running. I am trying to join and query tables from two Oracle connections. I have tried the BRIDGE command, but get the error below. Is there a way in SQL Developer to query on tables from different connections at the same time? I am using Version 4.0.2.15; Java Platform
    1.7.0_55.
    Thanks!
    ORA-00900: invalid SQL statement
    00900. 00000 – “invalid SQL statement”
    *Cause:
    *Action:

  43. At my work, we’ve gone total Virtual Desktop (VDI), using a Citrix Receiver, where all the applications are on layers. For some reason, my Network won’t allow me to utilize Oracle SQL Developer on a VDI layer, so I had to install SQL Developer onto my laptop, which works, but not when I’m at home and want to connect to the network, I have to use VDI. My only way to use SQL developer, is to leave my laptop at work and remote into it from home via VDI. Is there a trick to installing Oracle SQL Developer onto a VDI layer?

  44. Hi Jeff,
    How would you go about Data Modelling and Database Cloud Schema Service?
    I mean, you can’t connect SQL Developer Data Modeler to Schema Cloud and reverse engineer or forward engineer, right?
    I have tables in Schema Cloud and I would like to transform the datamodel into something normalized and so on.
    Should I install Database Express for the datamodel locally and use a cart again to upoad the new? How do I import the tables into a DataModel?
    Or any other tips?
    Thanx,
    Hasso Schaap

    1. thatjeffsmith Post
      Author

      Today, if you wanted to model your schema service, you’d build it locally, and then upload it. You’re right, you can’t model it directly from the service.

      Tomorrow, that limitation will be removed. We’re working on support to do exactly what you are wanting to do for that service.

  45. I have screwed up my SQL Developer? I am seeing a series of unprintable characters when I open a SQL script in SQL Developer 4.0.3.16. I can open the same script in textpad and it displays fine. The file contains some set commands and a SQL script. I was changing things relative to exporting AL32UTF8 data output, so I suspect I brought this on myself. Just not sure how to get it back to normal?

    1. thatjeffsmith Post
      Author

      not sure you’ve screwed up SQL Developer but maybe you’ve screwed up your file? make sure you’re using a Code Editor FONT that supports all the characters you’re using – try to match what you have textpad for example

        1. thatjeffsmith Post
          Author
        2. I found my problem on this link:
          https://community.oracle.com/thread/714713?start=0&tstart=0

          I was dealing with AL32UTF8 data from ASPAC and was trying to influence the output file to stop converting double byte characters into ?. I had changed the Tools> Preferences> Environment> Encoding setting to UTF32. That was a mistake, but everything worked and the data was output retaining the Chinese characters. That is when the issue with opening SQL scripts began, but a few days had passed, so I did not make the connection. Changing the encoding to UTF8 has solved my issue.

  46. I’m replacing my desktops with 64-bit Windows 7, so I also upgraded to SQL Developer 4.1.3 with Java 8 included (up from v.1.5.5). Everything seems to running fine, except:

    • I cannot open a table tab to save my life. Single-Click, Double-Click, Key Return… none of it works. Single-clicking an PL/SQL object in the connections tab opens its read-only tab without issue, and right-clicking –> “Edit” works perfectly too… but clicking on a table or view does nothing. I’ve renamed my appdata folder, reverted to default settings, and tried it with v.4.0.1 as well… no joy.

    Could you help me out? Thank you in advance!

    – Marc

    1. thatjeffsmith Post
      Author

      you didn’t by any chance extract your 4.1.3 directory on top of your 1.5.5 directory did you? that can cause weird things like what you’re describing to happen. if so, take the zip and re-extract it to a FRESH directory.

      1. No, I extracted it to a fresh directory, and since posting the question this morning, I verified the same issue on a coworker’s PC too. She’s been using this Windows install for months on an Intel i3 chipset, and my PCs are fresh installs a week old running on Intel i7 chipsets. We don’t even have the same underlying versions of Java installed, even though we’re using SQL Developer with Java 8 included.

        – Marc

  47. Hi Jeff,
    I’m using Unit Test in SQL Developer and connecting it to Jenkins. I need to do everything from command line and although I don’t have any problems running test or uploading them, I don’t know how to delete them.
    Could you tell me what is the command for deleting test from command line?
    Should it be sth similar to:
    “sdcli unittest -exp -teardown”?
    For this I also do not know the proper way.

    Thank you in advance.

    1. thatjeffsmith Post
      Author

      if you don’t see a delete option in the CLI help, then you’ll need to write a function or run a DELETE against the repository table to manually delete the rows representing your tests

  48. Hi Jeff,

    I have copied a table (CAT_COL_DATA) as CAT_COL_DATA_TMP as first step to generate a temporary equivalent table.

    I have manually changed names but I am getting a length error and I can not find why.

    While previewing DDL for both tables I get the following error in the copied one:

    — ERROR: Column CAT_COL_DATA_TMP.ITEM_CODE check constraint name length exceeds maximum allowed length(30)

    ITEM_CODE column is defined as DOMAIN data type. It works fine for CAT_COL_DATA but not for CAT_COL_DATA_TMP.

    If I change ITEM_CODE to logical NUMBERIC the error goes away so I suspect it is something related to the domain.
    Any tips? Thanks a lot.

    Note: ITEM CODE Domain is defined as NUMERIC Size 4 Values Range between 0000 and 9999.

    CREATE TABLE CAT_COL_DATA
    (
    COL_DATA_ID NUMBER NOT NULL ,
    ITEM_CODE NUMBER ,
    ITEM_EAN13 NUMBER ,
    COL_DATA_01 VARCHAR2 (4000) ,
    COL_DATA_02 VARCHAR2 (4000) ,
    COL_DATA_03 VARCHAR2 (4000) ,
    COL_DATA_04 VARCHAR2 (4000) ,
    COL_DATA_05 VARCHAR2 (4000) ,
    COL_DATA_06 VARCHAR2 (4000) ,
    COL_DATA_07 VARCHAR2 (4000) ,
    COL_DATA_08 VARCHAR2 (4000) ,
    COL_DATA_09 VARCHAR2 (4000) ,
    COL_DATA_10 VARCHAR2 (4000) ,
    COL_DATA_11 VARCHAR2 (4000) ,
    COL_DATA_12 VARCHAR2 (4000) ,
    COL_DATA_13 VARCHAR2 (4000) ,
    COL_DATA_14 VARCHAR2 (4000) ,
    COL_DATA_15 VARCHAR2 (4000) ,
    COL_DATA_16 VARCHAR2 (4000) ,
    COL_DATA_17 VARCHAR2 (4000) ,
    COL_DATA_18 VARCHAR2 (4000) ,
    COL_DATA_19 VARCHAR2 (4000) ,
    COL_DATA_20 VARCHAR2 (4000) ,
    CAT_DESCR_ID NUMBER NOT NULL
    )
    LOGGING ;
    ALTER TABLE CAT_COL_DATA ADD CHECK ( ITEM_CODE BETWEEN 0000 AND 9999) ;
    ALTER TABLE CAT_COL_DATA ADD CONSTRAINT CAT_COL_DATA_PK PRIMARY KEY ( COL_DATA_ID ) ;
    ALTER TABLE CAT_COL_DATA ADD CONSTRAINT CAT_COL_DATA_EAN13_UN UNIQUE ( ITEM_EAN13 ) ;
    ALTER TABLE CAT_COL_DATA ADD CONSTRAINT CAT_COL_DATA_CAT_DESCR_FK FOREIGN KEY ( CAT_DESCR_ID ) REFERENCES CAT_DESCR ( CAT_DESCR_ID ) NOT DEFERRABLE ;
    CREATE SEQUENCE CAT_COL_DATA_COL_DATA_ID_SEQ START WITH 1 NOCACHE ORDER ;
    CREATE OR REPLACE TRIGGER CAT_COL_DATA_COL_DATA_ID_TRG BEFORE
    INSERT ON CAT_COL_DATA FOR EACH ROW WHEN (NEW.COL_DATA_ID IS NULL) BEGIN :NEW.COL_DATA_ID := CAT_COL_DATA_COL_DATA_ID_SEQ.NEXTVAL;
    END;
    /

    CREATE TABLE CAT_COL_DATA_TMP
    (
    COL_DATA_ID NUMBER NOT NULL ,
    ITEM_CODE NUMBER ,
    ITEM_EAN13 NUMBER ,
    COL_DATA_01 VARCHAR2 (4000) ,
    COL_DATA_02 VARCHAR2 (4000) ,
    COL_DATA_03 VARCHAR2 (4000) ,
    COL_DATA_04 VARCHAR2 (4000) ,
    COL_DATA_05 VARCHAR2 (4000) ,
    COL_DATA_06 VARCHAR2 (4000) ,
    COL_DATA_07 VARCHAR2 (4000) ,
    COL_DATA_08 VARCHAR2 (4000) ,
    COL_DATA_09 VARCHAR2 (4000) ,
    COL_DATA_10 VARCHAR2 (4000) ,
    COL_DATA_11 VARCHAR2 (4000) ,
    COL_DATA_12 VARCHAR2 (4000) ,
    COL_DATA_13 VARCHAR2 (4000) ,
    COL_DATA_14 VARCHAR2 (4000) ,
    COL_DATA_15 VARCHAR2 (4000) ,
    COL_DATA_16 VARCHAR2 (4000) ,
    COL_DATA_17 VARCHAR2 (4000) ,
    COL_DATA_18 VARCHAR2 (4000) ,
    COL_DATA_19 VARCHAR2 (4000) ,
    COL_DATA_20 VARCHAR2 (4000) ,
    CAT_DESCR_ID NUMBER NOT NULL
    )
    LOGGING ;
    — ERROR: Column CAT_COL_DATA_TMP.ITEM_CODE check constraint name length exceeds maximum allowed length(30)
    ALTER TABLE CAT_COL_DATA_TMP ADD CHECK ( ITEM_CODE BETWEEN 0000 AND 9999) ;
    ALTER TABLE CAT_COL_DATA_TMP ADD CONSTRAINT CAT_COL_DATA_TMP_PK PRIMARY KEY ( COL_DATA_ID ) ;
    ALTER TABLE CAT_COL_DATA_TMP ADD CONSTRAINT CAT_COL_DATA_EAN13_TMP_UN UNIQUE ( ITEM_EAN13 ) ;
    ALTER TABLE CAT_COL_DATA_TMP ADD CONSTRAINT CAT_COL_DATA_CAT_DESCR_TMP_FK FOREIGN KEY ( CAT_DESCR_ID ) REFERENCES CAT_DESCR_TMP ( CAT_DESCR_ID ) NOT DEFERRABLE ;
    CREATE SEQUENCE CAT_COL_DATA_TMP_COL_DATA_ID START WITH 1 NOCACHE ORDER ;
    CREATE OR REPLACE TRIGGER CAT_COL_DATA_TMP_COL_DATA_ID BEFORE
    INSERT ON CAT_COL_DATA_TMP FOR EACH ROW WHEN (NEW.COL_DATA_ID IS NULL) BEGIN :NEW.COL_DATA_ID := CAT_COL_DATA_TMP_COL_DATA_ID.NEXTVAL;
    END;
    /

  49. Hey Jeff – I left a reply to your reply under a post you made about 6 months ago regarding MS Access and SQL Developer you advised just using an older version of SQL Developer…), so please disregard one or the other of these posts…

    The question that I posed is whether there is a decision about discontinuing support for ODBC/MS Access connections is on the level of policy or is on the level of actions with inadvertent results that will be addressed in future releases? I have written a number of PL/SQL scripts that use this connection for migrations (from MS Access to our Oracle Enterprise DB) and was hoping you had some general suggestions for work arounds…..

    My sincerest apology for double posting, but regardless – much Aloha and Mahalo from Honolulu….

    …and yes I am THAT guy who cornered you at Oracle Open World 2014…

    1. thatjeffsmith Post
      Author

      We need to find a straight up Access JDBC driver. It’s not our policy, it’s that Java removed the ODBC bridge tech in Java 8, which meant we lost the Access support we were using.

      Best workaround is to use SQL Developer 3.2 probably – there’s nothing in 4.1 that would make the Access migration easier, it basically just let you copy tables and data over.

        1. Okay, I take it back – the Access tab is there in SQL Developer, but the ODBC driver is not working (as you knew…) Looks like it’s 3.2 or nothing…..

  50. Hi Jeff,

    While copying and pasting tables in the relational model, I see object names are copied adding “v1” as suffix (applies for tables, primary keys, unique constraints, etc.)

    Is there anyway to specify a given PREFIX (or at least sufix) while copying, like if I have MY_TABLE I would like “FOO_MY_TABLE” instead of “MY_TABLEv1” ?

    Thanks,

    1. thatjeffsmith Post
      Author
  51. Hello Jeff,

    In SQL Developer version 4.0.3.16 with Built main 60-84, when there is an error, it clearly point to the line number as shown below:

    “Error at Command Line : 193 Column : 8
    Error report –
    ….

    Do you any idea if that feature is still available in the newest version 4.1.3.20 – Built MAIN-20-78?
    I try to re-create the error in the new version but it does not show the Error at Command Line: 193 Column: 8 any more.

    Thanks,
    HD

    1. thatjeffsmith Post
      Author
      1. Well, I meant that the “Error at Command Line : 193 Column : 8” which helps for debugging no longer show in the newest version 4.1.3.20.

        Thanks,
        HD

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
          2. I did not turn off any thing. All I did was download that new version then import the configure file to the new version and start using by create a new temp table with a query from different tables. Then I got error ” SQL Error: ORA-00942: table or view does not exist”.
            In the previous version, it shows the line number where that error occurs “Error at Command Line : 146 Column : 3
            Error report – SQL Error: ORA-00942: table or view does not exist” so you can go there and look for that specific line rather then go thru the whole script but for this new version it no longer show like that.

          3. thatjeffsmith Post
            Author
          4. thatjeffsmith Post
            Author
          5. Thanks Jeff! I fixed the error. It was a typo of the table_name in the “from”. I was just curious why the message of “Error at line …” was not show in the newest version. I am sorry about the two postings and have deleted the other one. Thanks again!

  52. Hi Jeff,

    I have a data model and I would like to replicate some tables as temporary tables. (Application will first use temporary tables and then move data to permanent tables once user press SAVE).

    Using sql developer data modeler:
    1) How can you specify that a given table is a temporary one?
    2) is there any way to replicate a subset of the model (say three tables with their relationships) indicating that the replicated subset will be temporary tables?

    Any tip on how to model this using SQL Data Modeler would be great.
    So far I have seen that this is usually done via CREATE TABLE using SELECT, but I guess that misses also sequences and relationships?

    Thanks a lot,

    1. thatjeffsmith Post
      Author
  53. I want to export two columns, doc_id and photo(blob) together so that a person can refer-to or look-up a photo by its doc_id. How to do this (keeping them together) ? Tried SQLDev export and download, maybe I’m not setting the right options?

    1. The workaround we did was to provide hyperlink to open the image file and send the zip file (report + images). Make sure to save in jpg format – .dat will not work on all computers. Is there an easier way?

    2. thatjeffsmith Post
      Author
  54. Hi Jeff, Thank you for your blog and helping us.

    I have on question. I have seen below info many times in results.
    Never got a chance the see the query which displays this into.
    Do you know how to display this?

    ——————————————————–
    Running as: | @
    ——————————————————–
    Query run date-time | Fri 03-28-2014 @ 18:11:56 CDT |
    ——————————————————–

    Thank You.

    1. thatjeffsmith Post
      Author
      1. Thank you Jeff for reply!! I found the code which was used in procedure.

        prompt ————————————————————-
        prompt Running as: | &_user@&_CONNECT_IDENTIFIER
        prompt ————————————————————-
        prompt Query run date-time | &_date; |
        prompt ————————————————————-

  55. Hi, I appreciate the blog, and I greatly appreciate SQL Developer. One frustration – I spent a lot of time when writing packages to include the tags and comments that would be included in the “Generate DB Doc” feature. It worked great in version 4.0, but all my “methods” documentation disappeared in 4.1 (currently running 4.1.1.19 19-59). Did the syntax change or will the feature be back?

    1. thatjeffsmith Post
      Author
      1. How-to:
        1. Create a mini-package header and body with doc tags; in my case I use the package body for the comments, as it is easier to keep in sync with changing logic:

        CREATE OR REPLACE PACKAGE aTEST_FOR_ORACLE
        /**
        * Project: Enrollment Project
        * Description: Data return for reporting and email
        * DB impact: YES
        * Commit inside: YES
        * Rollback inside: YES
        * @headcom
        */

        AS

        END aTEST_FOR_ORACLE;

        CREATE OR REPLACE PACKAGE BODY aTEST_FOR_ORACLE
        AS
        /**
        * Project: Enrollment Project
        * Description: ENRL_REPORTING_PKG – Data return for reporting and email
        * DB impact: YES
        * Commit inside: YES
        * Rollback inside: YES
        * @headcom
        */

        /**
        * Prototype for internal use only procedure – the procedure performs a consistency check between coverage types selected and
        dependents found.
        *
        * @param pI subscript of plan table element to check
        * @param pCvrg_Lvl coverage level to check
        * @param pFound set unconditionally to ‘y’
        */
        PROCEDURE Confirm_Error_Lvl_Chk(
        pI IN INTEGER
        ,pCvrg_Lvl IN VARCHAR2
        ,pFound IN OUT VARCHAR2
        );


        /**
        * The procedure performs a consistency check between coverage types selected and
        dependents found.
        *
        * @param pI subscript of plan table element to check
        * @param pCvrg_Lvl coverage level to check
        * @param pFound set unconditionally to ‘y’
        */
        PROCEDURE Confirm_Error_Lvl_Chk(
        pI IN INTEGER
        ,pCvrg_Lvl IN VARCHAR2
        ,pFound IN OUT VARCHAR2
        )
        AS

        BEGIN
        NULL;
        END Confirm_Error_Lvl_Chk
        ;

        END aTEST_FOR_ORACLE;

        2. In a 4.1 version of SqlDeveloper, right-click the connection, select “Generate DB Doc”, deselect all object types, select package bodies, “OK”, wait for completion, when the results display in the browser there is no method documentation.

        3. Repeat in a 4.0 version, the method documentation displays on the first display after clicking the package name.

        1. thatjeffsmith Post
          Author

          i can’t get your code to compile, it’s not valid PL/SQL

          so all of your procedures are private, no specs? that might be the issue

          if you add your notes to the spec, or even declare your procedures in the spec, I’m guessing it might work

          1. Thanks. It’s not worth further effort.

            Agree, it would not have compiled if pasted into SqlDeveloper as one element – I did not include the slashes following header and body because I typically work with header vs body separately and paste into S.D. Sorry. My original point was that you lost a feature between 4.0 and 4.1, and this is still true – there are reasons why some methods are package-local, and because of that, I document all my methods in the body rather than in the header. I am able to get the method documentation from the header, but not the body. Apparently I am a member of a very small population.

  56. hello, please am new and am just starting ro learn oracle sql. Am having difficulties in connecting a sample table HR..please what sample table can i use for my training and where can i get it

    1. thatjeffsmith Post
      Author
  57. Hi Jeff,

    I’ve enabled the option to display worksheet output in a zebra pattern (Database->Worksheet->’Grid in checker board or Zebra pattern’), but was wondering if it’s possible to change the color theme of the result set. I did some investigation and found information on setting up a system-wide theme, but nothing on the result set theme specifically.

    Thanks!
    Paul

    1. thatjeffsmith Post
      Author
  58. Hi Jeff. I’m new to Oracle and am trying to write a summary report using data from my database. I have a database with about 8 tables in it. I’m trying to write a query that will give me things like total counts and sums of the data that is in the tables. I can write individual queries to get me each report item by itself but is there a way I can get the results with one query or view that shows all of my report items together? Joins allow me to combine fields from multiple tables and show it in one result, but since I am creating new column names or fields with my queries, it doesn’t look like joins are the solution. Unions seem to combine the result of 2 queries but i want to show the result of each individual query inside of my main query as it’s own column. For example, I have 3 tables: A, B &C. Table A has fields w,x,y,z Table B has fields x,v,t,u and Table C has fields t,q,r,s. I want to write a script that will give me the total unique counts of x, total counts of t and the sum of r. The result I’m looking for would look like a table or list with the headers of: total unique count of x, total # of t, and sum of r. How do I do this?

    1. thatjeffsmith Post
      Author
        1. Its a bit hard to glean *exactly* what you’re after, but perhaps something like this:

          select count(distinct a.x), count(distinct b.t), sum(c.r)
          from A, B, C
          where a.x = b.x
          and b.t = c.t

          or perhaps you meant something like this:

          select a.x, count(distinct b.t), sum(c.r)
          from A, B, C
          where a.x = b.x
          and b.t = c.t
          group by a.x

          If you want to elaborate more, give us some sample data and expected output, head on over to asktom.oracle.com and ask a question there, and we’ll help out

          Cheers,
          Connor McDonald

  59. Hi Jeff

    Could you please suggest how to get results using something like this (pipeline + CLOB):
    SELECT * FROM table(dbms_stats.diff_table_stats_in_history(‘ME’,’MY_TABLE’,SYSDATE-1,null,0));
    To obtain results in SQLPLUS I use
    set long 30000
    show long
    and it works.
    But in SQL Developer I get:
    “ORA-20010: DBMS_STATS INTERNAL ERROR in report_line : Length of line “SOURCE A : Statistics as of 11-APR-16 08.48.40.000000 AM AMERICA/LOS_ANGELES
    ” exceeds 80, line length = 82″

    Any ideas?
    Thank you.
    Roman

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Thank you for reply.
        Are you talking about this “linesize”?

        show linesize
        linesize 1000
        SELECT * FROM table(dbms_stats.diff_table_stats_in_history(…));
        “ORA-20010: …” exceeds 80, line length = 82″

        I think It should be somewhere inside SQL Developer. Some limit. But I cannot find out where it is 🙁

        Thank you.
        Roman

  60. Hi,

    I have another question for you today Jeff. I used in the past the ability to add extension in sqldeveloper in regards the context menu. it served me well for Oracle connections.

    I’m right now trying to do the same for mssql and mysql connection unfortunally those do not show up. The need is easy is to let the developer create query as :
    select top 10 * from #OBJECT_NAME# order by id desc
    or
    select * from #OBJECT_NAME# order by id desc limit 1,10

    Thank you for your help.

    Ricardo

    1. thatjeffsmith Post
      Author
  61. Hi Jeff,
    A little question.
    In the editor when I open the sql of a table I see the schema owner. I would love to see a schema owner when I open a package or package body, but I can’t find the property. We’re on Version 4.1.2.20.

    1. thatjeffsmith Post
      Author

      On schema objects, non pl/sql, we use dbms_metadata to generate the DDL. Including the SCHEMA is an option. When we use the PL/SQL editor to pull up PL/SQL, we get the source directly from the _SOURCE views. We know what schema it belongs to, so we don’t need to add the SCHEMA. to the CREATE OR REPLACE.

      Now, if you open a pl/sql file that has it in it, we’ll honor that of course.

      You can also generate the PL/SQL with the SCHEMA. in the CREATE or REPLACE if you use Tools > Database Export. In that way, you could generate all the PL/SQL source files as you wanted, assuming you checked the ‘schema’ option.

    1. thatjeffsmith Post
      Author
  62. Hi Jeff,

    I have about 3 years using Oracle SQL Developer and when I want to turn some code into comments, I had to use the Toogle Line Comment function to do it. However it “comments” a full line or selected lines using the characters ‘–‘.

    Is there any way to “comment” selected text without commenting a full line? using the characters “/* */”? Hope you can help me.

    Thanks.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  63. hi Jeff, I am going to be upgrading my pc shortly and will need to obtain a new copy of Oracle SQL Developer. I am currently using 11g 4.1.2.20.
    It is a x32 pc. My new system will be x64. I see a lot more options for downloads available since the last time I downloaded the software. Is there somewhere you can direct me, or just describe briefly the different verisons. I see a standard, enterprise, express editions. I am wondering if I should get the 12c version instead of 11g. Are you able to provide any insight?

    thank you…

  64. Hi Jeff ,

    happy that you did some of the cosmetic change regarding the search text vs background colors :).

    I m writing you to suggest you a feature in sql developer. I myself keep all the connections for all my database systems ( mainly oracle, mysql, mssql) into the product and try to find the best way to organise those.

    In doing so I noted two little things that could be a good asset to add into the product.

    1rst multiple level of folder as of today it seemed that we can use only one ?

    2nd I notice that sometime I want to put additional context of the name of the connection however it seems to me that it s not a good place to do so. It will be good I think to have a free field as “comment” into the connection editor for us to put some additional information regarding the connection ( i.e what is it , what is for, who is using it etc … )

    Here you go , did not read those suggestion in the blog sorry if it s repetition to you….

    Thanks you.

    Ricardo

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. The goal will be to add some context information about the usage of one connection vs another .

            for instance a have a convention in the naming of the connection using [server name] + [dbname/schema] + [user]

            some time I create multiple instance with same serve name and db schema but with different users that I have some specifications for instance here you have a list of hypotetic comments that could be added to connections:
            * connection used by cron automatic job to process this and that
            * connection for that “kind” of users that have limited access to this portion of the db
            * this connection / server is used to managed that kind of process
            * this connection is used only 2 times a month to process the expenses report of sales representative

            hope that helps clarifying my initial post.

            thanks for reading !

  65. In your email newsletter I see that it always puts an emoji at the @orcl> between the L and the greater than symbol. Example:
    [email protected] < img src=”http://s.w.org/images/core/emoji/72×72/1f37b.png” alt=”?” class=”wp-smiley” style=”height: 1em; max-height: 1em;” %gt; < img src=”http://s.w.org/images/core/emoji/72×72/1f37a.png” alt=”?” class=”wp-smiley” style=”height: 1em; max-height: 1em;” > >

    Is there a deeper meaning here or is it a Microsoft (Outlook) vs Oracle thing? Cheers 🙂

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  66. Hi Jeff, I just moved an Oracle 10g schema to Oracle 12c using the DataPump export and import wizards. Very painless. Much easier than other methods I’ve used over the years. One question, though….Is there a way to see the SQL generated by the wizard?

    Thanks,

    Tom

    1. thatjeffsmith Post
      Author
  67. Having trouble connecting SQL*Developer to a SQL Server database. Downloaded the Third Party JDBC Driver and unzipped in C:\apps\TDS, so the entry in Third Party JDBC Driver is C:\apps\TDS\jtds-1.3.1.jar. Open a new connection, give it a name, enter my username and password, click the box for Save Password and on SQLServer. Put in the Hostname and use the default port (1433). But when I click on Retrieve database (as seen in a few blog posts on how to set this up) I get….nothing. Blank. Nada. If I click on Test I get: Status: Failure – Test failed: Login failed for user ‘dl23’. What did I miss?

    1. thatjeffsmith Post
      Author
      1. Don’t believe there are any rules preventing my login. With the Microsoft SQL Server Management Studio installed on my desktop I can access the remote database no problem. I’m using SQL*Developer 4.1.3.20 with jtds-1.3.1.

        1. thatjeffsmith Post
          Author
  68. Hi Jeff,
    Thanks for providing a great site and lots of SQL Developer tips! I’ve been using Oracle SQL Developer at my work for about 6 years (mostly running queries & reports, but have done a few inserts & update scripts as well). I work as an IT Business Analyst. My only training is taking Oracle Database 11g Intro to SQL and Program with PL/SQL in-classroom courses through Oracle University in 2010, bugging my DBA with my SQL questions and learning that way and reading your blogs on your site, which have been awesome! I want to take my Oracle knowledge further by going the SQL Developer route (Database Application Development – Learning Path and get certified).

    I don’t see too many Oracle training classes in my area (Sacramento, CA), what would you recommend? Self-Study? Practicing in Oracle Live?

    Thanks,
    Catherine

    1. thatjeffsmith Post
      Author

      The Northern Ca Oracle Users Group meets quarterly – big 1 day conferences, in San Jose. I would try to get to one of those, and get some advice from your peers. You can of course always do self-study. There’s so many free resources out there, I wouldn’t limit yourself to formal/$$$ programs.

      1. Thanks Jeff, I will definitely check out the Northern California Oracle User Group! What other free resources would you recommend.

  69. Does SQLcl properly read and “use” NLS_LANG environment variable?

    My test case is as follows:
    1. export NLS_LANG=POLISH_POLAND.EE8MSWIN1250
    2. run sqlcl and connect to DB (11.2)
    3. compile package from the script (Polish characters in CP1250 encoding inside)
    4. open the package from DB in SQL Developer

    Polish characters are lost and substituted with ‘?’. Moreover NLS_LANGUAGE and NLS_TERRITORY (from NLS_SESSION_PARAMETERS) are also different then ones set in NLS_LANG.

    Using SQLcl 4.2.0.16.049.0842 on linux host.

    The same package compiles correctly in SQL Developer (POLISH_POLAND.EE8MSWIN1250 set explicitly in preferences).

    1. Hi, Jeff.
      Could you tell me if I had done anything wrong in my “SQLcl vs. NLS_LANG” case or SQLcl just ignores NLS_LANG? Or any other solution?

      I’ve checked out that compiling (with SQLcl) the script saved in UTF-8 encoding (which is the default one on my computer) leads to proper character conversion and preserves all characters in the database. It suggests that SQLcl ignores NLS_LANG and uses OS’ character set. Am I right?

      Thanks,
      Tomek

      1. thatjeffsmith Post
        Author
        1. Jeff, thanks for your replay.
          I use SQLcl on the Linux machine, not Windows. I’ve tried with setting NLS_LANG *environmental variable* before starting SQLcl. But it doesn’t work. So what does?
          What do you mean by “set them in your login script”?

          Thanks,
          Tomek

          1. thatjeffsmith Post
            Author
  70. Hi Jeff,

    Thanks for a great site! I always catch something new about SQL Developer.

    I have a question about vertical selection in SQL Developer (like in Notepad++).

    Thanks,

    1. thatjeffsmith Post
      Author
      1. hi Jeff, I am using 11g version 4.1 so I cannot explode the * but the ctrl+shift functionality works like you described in your post.
        this is very helpful. i saw in your post from several months ago about adding the ability to drag the cursor down instead of clicking each time. You had said that it was something being considered. I just wanted to see if there was any update on that feature….

        thanks!

        1. thatjeffsmith Post
          Author
  71. Jeff, couple things. Been using the “sqlcl” tool and the potential is excellent. SQLCL has been “early adopter” for quite a while and seems to be suffering from a lack of documention (inline and online).

    I assume it is under staffed since it’s a free tool. I view this product as an important “generational leap” from sqlplus – and I would like to start using it in all my environments. To do that in my mind, it’s got to be bundled with the Oracle DB installs (like sqlplus is), and or it’s got to be a “production” product at very least.

    When will Oracle take steps to make this happen! Hopefully in my lifetime.

    Thanks, Mike

    1. thatjeffsmith Post
      Author

      We’re taking steps today, it’ll happen in your lifetime. Maybe even this year.

      SQL Developer is already bundled with Oracle DB Installs, and SQLcl will be in that.

      The docs will happen. For now, the best resources are the built-in Help topics.

  72. I have to write a short college paper on the comparison and contrast of Oracle and Microsft Access parallel processing. I am not asking you to provide a ton of info, but would apprciate it if you could provide a brief overview of each. I have zero knowledge of Oracle and very limited knowledge of Access. Thank you!

    1. thatjeffsmith Post
      Author
      1. Jeff,

        I really appreciate such a quick response. It’s only a 1 -2 page paper to “compare and contrast the parallel processing capabilities of Oracle and Microsoft Access”. However, I may put a tad of info about SQL server as well, but will mainly focus on the info in the quotes above. Also, would it be okay to reference you and your site with the information? Thanks again.

        Steve

        1. thatjeffsmith Post
          Author
    1. thatjeffsmith Post
      Author
      1. Thanks for quick reply to my question. I’ve been doing that but it takes many hours. I was looking for a process that reads each tab and load the data from each tab to a corresponding table in Oracle.

        1. thatjeffsmith Post
          Author

          If you’re doing this on a regular basis vs ad hoc, then I agree…you need a better solution.

          Setup a command line process that automates this for you, totally possible if the xls file and the tables are always the same.

  73. I was just wondering if there is a reason why SQL Devoloper won’t let you use bind variables in an update statement? Is there a way to make this work?

    1. thatjeffsmith Post
      Author
      1. update ps_y_slr_stu_award set Y_AWARD_STATUS = ‘ACOP’ where emplid = :1 and aid_year = :2;

        whenever I try to do something like this it tells me I have an invalid number

        1. thatjeffsmith Post
          Author
  74. Hi Jeff,
    every time my collegue connects with sql developer with thick client to our 11.2.0.3 Oracle Database Standard Edition One he receives this error message: “ORA-00942: Tabelle oder View nicht vorhanden
    Statement………: SELECT count(REVISION) FROM USER_ORDS_REPOVERSIONS WHERE REVISION >= :”SYS_B_0″ ”
    Do you have an idea what can be the issue here?
    Thanks,
    Best regards,
    Stefan

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  75. Hi, I upgraded today to latest version (v 4.1.3.20) and am noticing that when you search for a package from “Find Database Object” panel and click on your package or package body, it no more displays the package contents, it just displays “create or replace ” on the first line and nothing else, is this a known bug ?
    I can confirm that these packages that I am trying to open does have 1000s of line of code as I can still go back to my v 4.0.0.13 and do the same actions and get to see package contents. Any help/suggestions? Thank you !!

  76. When I click on Tables to view columns, SQL Developer freezes and says, “Waiting for Editor to initialize”. I have to close it and re-open to use SQL Developer. What broke?

    1. thatjeffsmith Post
      Author
      1. If I click a table in my own schema, it returns after 5 seconds. If I close SQL Developer, and re-open it, and click directly on the target table from a different schema, it never returns.

  77. Question: In SQL Developer why is it still necessary to commit after a query that uses a parallel hint (otherwise the parallel sessions are not closed or de-allocated) ? For example, if I select /*+ parallel(5) */ from table; and forget to issue a commit,
    I’ve still got 5 open parallel sessions…using resources both on the DB server and on my local workstation.
    I realize commit is necessary for DML or to let others see new DDL objects but why for a SELECT ?

    1. thatjeffsmith Post
      Author

      probably b/c you haven’t read all the data back…if your query brings back 1000 records but you’ve only fetched the first 50, the other 950 are left waiting on the server to be queued up by your 5 proceses

  78. New tables have been added to a schema for which I’ve been maintaining a Relational Model (with many subviews). I tried to add the tables to the model using the ‘Synchronize Model with Data Dictionary’ option, but the new tables aren’t added to the Tables list of the Rel. model.

    If I start from scratch and re-Import the whole DB from the Data Dictionary, the new tables are listed.

    How can I add the new tables to my existing Relational model?

  79. Hello,
    is there a way to perform a full remote database copy using the command line tools of the sqldeveloper?
    I know there is this database copy wizard, which perfectly suits my needs from the functional point of view.
    I just want to make:
    – a full copy of the database (no selection steps needed)
    – of a remote database,
    – without having admin permissions on the source database,
    – without full oracle client installation (sqlplus, import, export etc.)
    – using the command line.
    Thanks!

  80. Dear Jeff,

    I have started using Oracle SQL Data Modeler recently and I am trying to fully understand how relations are defined in the logical model:

    I have a business entity “Customer” with attributes ID (Primary Key) and NAME and DEFAULT_CURRENCY.

    I have a business entity “Currency” with attributes ID (Primary Key) and CURRENCY_CODE and NAME.

    I would like to make a relationship in Oracle Data Modeler which links DEFAULT_CURRENCY in “Customer” to a given ID in business entity “Currency”, and I am struggling to discover how to do it.

    So far I can only define relations if I use CUSTOMER_ID and CURRENCY_ID then an attribute CURRENCY_ID is created in “Customer” entity, but I would like to be able to choose name of the attribute linking both entities and maybe (not sure if it is a good idea or not) be able to use just ID as primary key in both entities. In this particular example you see that CURRENCY_ID in “Customer” loses the important I formation that we are pointing to the “Default currency for this customer”. Same for other scenarios where you could even want “DEFAULT_CURRENCY” and “BALANCE_CURRENCY”.

    Thanks,

  81. Jeff,

    Thanks for an earlier post(s) on Cart. I have instructed my team to use it a the preferred form for doing deployments. I have one wish for it, when using the Separate Directory or Type Files options it builds the driving script at Generated-20160328080522.sql.

    It would be nice to have it be something more related to the what is being exported. Like the dominant schema, or connection information.

    Thanks again for Carts. It has been a game changer, with sdcli I can do continuous integration with test without much intervention!

  82. I saw somewhere that SQLDeveloper used to include macro recording/playback capability that disappeared around version 4.0. Are there any plans to bring it back?

    1. thatjeffsmith Post
      Author
      1. Thanks for the answer. I’m using version 4.0.0.13, and “macro” doesn’t return any matches. Is there some add-in I need to make it available?

        1. thatjeffsmith Post
          Author
  83. Hi Jeff,

    SQL Developer Version 4.1.3.20

    is it possible to have the SQL Developer data grid show MS Word Formatting characters? I have a XML process that’s failing as two customer addresses have word formatting characters at the end of address – possibly caused by operators copy/pasting from MS Word.

    TOAD displays these in the data grid, but I can’t seem to put my finger on an SQL Developer option to do the same.

    Thanks

    1. thatjeffsmith Post
      Author

      two thoughts come to mind, use the replace() function to pull out the data with a character that WILL display, and/or 2, play around with the fonts to see if there’s one that has something to support the formatting chars…or query out the data in hex. i guess that’s 3

      1. @OP
        select substr( replace( dump( text, 17 ) ,’,’), 15) txt from TAB
        will show non ascii values as control characters or numbers. DUMP displays the bytes stored in the database, the 17 say show the byte as its ascii value

        maybe a better resolution is
        in environment
        NLS_NCHAR_CHARACTERSET = ???? –< whatever the windows client is
        then
        cast( txt as NVARCHAR2(64) ) txt from tab
        should show as pasted.

  84. Thanks Jeff for your support to sql dev user: I am a new user and I reluctantly closed my sql results output where usually I see the results in the table from the queries that I run. Somehow I can’t see it anymore. Please advise.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author

            something, really really bad has happened then. find your system4.1.3… directory in your OS user appdata/roaming folder, should be under SQL Developer, and NUKE it. then restart sqldev and see if that’s fixed it.

  85. We have an Oracle 11g database and have a hung freeze or slow when we try to use data masking, i think maybe is ‘cos a great amount of foreign keys and related tables? Thank u very much in advanced.

    1. thatjeffsmith Post
      Author
  86. I am using sql developer 4.1.3.20.78. I make can make a simple query and save it as .sql.

    When I open it and view the worksheet vie the sql statement is there.
    When I then go to the Query Builder view I can see the header for the table, but the columns do not display. Also if I drag another table to the query builder, it will not display.

    What am I doing wrong?

    Tnx

  87. Just a note to say I’m really enjoying working with SQL Developer, we moved away from a “commercial” IDE and once we worked our way through the myriad of available options and got comfortable it’s a really great tool great to work with – cheers!

  88. Hello Jeff I have a question to you , are you familiar with problem regarding sqlcl (4.2.0.15.349.0706) when you try to display plan of your last query using this one : SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR) ? I get wrong results using this one in sqlcl and proper results using sqlplus ? Do you know why ? When I test it I used the same sql queries in sqlcl and sqlplus.

    Adam

    1. thatjeffsmith Post
      Author
  89. Dear Jeff,
    Thanks for running such a cool site. You helped me out a lot.

    I’m on Version 4.1.2.20 and I found a little problem or is it a setting I can’t find? When you hava a JAVA file and you look at the source, the part “CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED scheme.name as ” is missing. To me it’s like the “create or replace PACKAGE BODY scheme.name ” is missing.
    Perhaps it’s intentional, but I doubt that?
    Can you help me out on this pesky one?

    1. thatjeffsmith Post
      Author

      I don’t work with stored java procedures much, but… ‘When you have a JAVA file and you look at the source,’ => where are you getting this file from? Are we generating it from an existing java class/resource/something?

      1. I made the file myself, to find files on the server in a particular directory and put them in a list .
        this is the complete code, so whit the first part you don’t see in SQLDeveloper. (You can see it in some other tool).
        You have to make a
        CREATE OR REPLACE TYPE T_VARCHAR2_4000_TAB AS TABLE OF VARCHAR2(4000)
        /
        first.
        and then:

        CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED “Files” as import java.io.File;
        import java.sql.*;
        import oracle.sql.*;
        import oracle.jdbc.driver.OracleDriver;

        public class Files {

        public static String dirList( String inBronDir
        , String inDelimiter) {
        StringBuffer dirlist = new StringBuffer();
        File path = new File(inBronDir);
        File[] f = path.listFiles();

        String file_name;
        for(int i = 0; i 0 ) {
        dirlist.delete(dirlist.length()-1, dirlist.length());
        }
        return dirlist.toString();
        }

        public static ARRAY dirList(String inBronDir) throws SQLException {
        // ora:
        Connection conn = new OracleDriver().defaultConnection();
        ArrayDescriptor ad =
        ArrayDescriptor.createDescriptor(“T_VARCHAR2_4000_TAB”, conn); —

        // make dir-listing and save in File[] (for testing isFile)
        File myDir = new File(inBronDir);
        File[] files = myDir.listFiles();

        String file_name;
        String[] fileList = new String[files.length];
        // files with isFile() FALSE
        // go with value NULL in the array.
        for(int i = 0; i < files.length; i++) {
        if (files[i].isFile()) {
        fileList[i] = files[i].getName();
        }
        }
        return new ARRAY(ad, conn, fileList);
        }
        }

  90. Hi Jeff,

    Help! I’ve imported an existing Oracle database into DataModeler, and I’m having trouble explaining what the Relational diagram relationship lines ‘mean’. Here’s the example:

    In Barker-speak,

    a PRODUCT may have 1 or more ALTERNATE_NAMEs

    an ALTERNATE_NAME must describe a PRODUCT

    The Logical diagram shows what I expected (sorry about the rough approximation of the diagram ). Optional at the Product end, mandatory at the Alternate_Name end.

    Product ——– __________< Alternate_Name

    In the actual database, ALTERNATE_NAME.PRODUCT_ID is a Not Null field with an FK on it, referring to PRODUCT.ID (ie. the PK in PRODUCT)

    So far, all good.

    But the Relational diagram's relationship is shown as a solid line connecting the 2 tables. I've visually lost the optionality at the PRODUCT end.
    Product_______________< Alternate_Name

    What am I missing? Is there some way to 'show' the optionality at the Product end? If not, how do I (and the Clients) tell from the diagram that there IS that optionality?

    Tearing hair out,
    Ellen

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Thanks for getting back to me on a Sunday!

        I looked at the Employee – Manager example you mentioned. The difference is – the Manager_id field on which the FK is built is nullable. (ie. Managers can exist without any Employees)

        In my situation, the Product_id on which the FK is built ISN’t nullable (ie. an Alternate_Name cannot exist without being associated with a Product. We don’t want a bunch of orphan Alternate_Name rows in the table.)

        It seems that you get a solid line if the FK field is NOT NULL, and a dashed line if the FK field is nullable.
        Is that right, or am I missing something?

        Thanks,
        Ellen

        1. Ellen,

          Given the notation you describe, you must be working with a logical model. Look in the “Relationship Properties” box. (Double click on any relationship in the model -OR- Right click on the relationship in the Browser and select Properties.) There will be 2 checkboxes called “Source Optional” and “Target Optional”. Use these to control the notation as you described.

          So, why is this not already setup?

          The database import (reverse engineering) is limited by the constraint enforcement in the database. The database can only enforce the mandatory constraint at the foreign key in the ALTERNATE_NAME table. There is no mandatory constraint enforcement at the foreign key table. In other words, the database can’t enforce that at least one ALTERNATE_NAME record must exist for every PRODUCT record. Since this constraint is not in the database, the import can’t produce it.

          If you want to ensure each record in the PRODUCT table has at least one “ALTERNATE_NAME” record holding a foreign key for it, you will need to run a report periodically to confirm all is good. Enforcing this constraint with trigger logic or application logic gets tricky when there are simultaneous DML running against the same records.

          Duane

  91. I’m trying to use SQLcl with OraOpenSource’s apex-diff and Node.js, and I am getting an error that appears to be due to SQLcl’s handling of SELECT CURSOR() statements when SQLFORMAT is set to anything other than default.

    In my example below I create a table, insert a record and then SELECT from it in various ways to show the errors.

    Whenever I set SQLFORMAT to non-default (CSV or JSON) and do a SELECT CURSOR()… I get the following error :-
    “Invalid column type: getOracleObject not implemented for class…”

    Do you have any idea what’s wrong?

    Cheers
    Richard

    e.g.
    C:\sqlcl_test>sql [email protected]:1521/XXX

    SQLcl: Release 4.2.0.16.049.0842 RC on Fri Mar 18 16:10:51 2016

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

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management,
    Oracle Label Security,
    OLAP, Data Mining, Oracle Database Vault and Real Application Testing option

    SQL> create table test1 (col1 varchar2(10), col2 number);
    Table TEST1 created.

    SQL> insert into test1 values (‘a’,1);
    1 row inserted.

    SQL> set sqlformat default
    SQL Format Cleared

    SQL> select * from test1;

    COL1 COL2
    ———- ———-
    a 1

    SQL> set sqlformat json

    SQL> select * from test1;
    {“results”:[{“columns”:[{“name”:”COL1″,”type”:”NUMBER”},{“name”:”COL2″,”type”:”N
    UMBER”}],”items”:
    [
    {“col1″:”a”,”col2″:1}]}]}

    SQL> set sqlformat default
    SQL Format Cleared

    SQL> select cursor(select * from test1) from dual;

    —–
    COL1 COL2
    ———- —————————————
    a 1

    SQL> set sqlformat json

    SQL> select cursor(select * from test1) from dual;
    Invalid column type: getOracleObject not implemented for class oracle.jdbc.drive
    r.T4CResultSetAccessor
    {“results”:[{“columns”:[{“name”:”CURSOR(SELECT*FROMTEST1)”,”type”:”NUMBER”}],”it
    ems”:
    [
    ]}]}no rows selected

    SQL> set sqlformat csv

    SQL> select cursor(select * from test1) from dual;
    Invalid column type: getOracleObject not implemented for class oracle.jdbc.drive
    r.T4CResultSetAccessor
    “CURSOR(SELECT*FROMTEST1)”
    no rows selected

    SQL> set sqlformat default
    SQL Format Cleared

    SQL> select cursor(select * from test1) from dual;

    —–
    COL1 COL2
    ———- —————————————
    a 1

    SQL>

    1. thatjeffsmith Post
      Author
  92. Hi Jeff,

    I’m starting to use the Reports feature more and more of late and am really loving it. It certainly adds to my productivity by not having to issue the same SQL statements over and over with different parameters.

    A couple of features that (imho) would help and add value:

    1) Add a Refresh Report button to the master report that will issue the query with the previously entered bind variables. Currently, to refresh the report one needs to hit the Run Report button which pops up the “Enter Bind Values” window. I know I could set the refresh rate which results in the same query being issued at specified intervals but in many cases this is overkill, I simply want to reissue my previous query without the additional popup.

    2) The “Enter Bind Values” popup is a bit cumbersome. I need to click on each on the configured bind variables to check if it is populated or not. If I have 10 bind variables configured then it requires 10 clicks to check if it has a value. I think that an editable grid/listview would work even better. I would be able to, at a glance, see which bind variables have values.

    Another slight annoyance is when you enter a value for a bind variable that is more than about 50 characters. When this happens the window containing the bind variable names becomes too small and you have to resize the window in order to see the bind variables names again.

    Regards,
    Ron

  93. Hi Jeff,

    Is there a way to order the columns alphabetically in the Single Record View screen ?

    Regards,
    Pradeep

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. Thanks Jeff !! That Works… but it kind of cumbersome. Is this in the feature list for the next releases?

          2. thatjeffsmith Post
            Author
  94. Hi Jeff,
    Thanks for an awesome site. It definitely helps people like me who are trying to use mac for oracle development. Here is my question:

    How do i setup autofill hot key for select * from on sqldev in my mac.

    for e.g. If i type sf and tab i want the select * from to be populated in the editor.

    Is that even possible?

    Regards,
    Pradeep

    1. thatjeffsmith Post
      Author
      1. Wow !! Really appreciate your fast response. I am exploring more on SqlDev everyday.

        Honestly haven’t gone back to Toad for a week now after i installed SqlDev.

        Cheers to the sqldev team !!! Keep up the good work.

  95. Hi Jeff,

    Thanks for a great site! I always catch something new about SQL Developer every time I visit.

    I have a question about the commit icon versus issuing an actual commit command. When you click the icon, it doesn’t show “committed.” in the Script Output window but it does when you issue the command in the Worksheet. Is there a setting to make it show when you click the icon so that you don’t end up clicking twice, just to be sure 🙂

    Thanks,
    Sameer

    1. thatjeffsmith Post
      Author

      It’s there, you just need to know where to look – it’s not in the script output panel b/c you technically haven’t ran anything through the script engine. And btw, I always TRIPLE COMMIT — just to make sure.

  96. Hi,

    I just wanted to try out the new “sdcli utility import” feature to script an Excel import but I cannot find any information about the required XML config file.

    Is there any documentation about it? Or examples?

    Thanks and Regards
    Peter

    1. thatjeffsmith Post
      Author

      You walk the IMPORT wizard in the GUI – on the last page, there’s a button that says ‘Save State’ – that will be the XML file that defines how the IMPORT will run in the SDCLI command line interface.

      this is from the burned in help…

      ┌─[09:37:22]─[wvu1999]─[MacBook-Air-Smith]:/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin$
      └─>./sdcli utility help

      Oracle SQL Developer
      Copyright (c) 1997, 2015, Oracle and/or its affiliates. All rights reserved.

      Invalid UTILITY command: help
      IMPORT Usage:
      utility
      utility -help|h
      Supported commands:
      import -config|c [-data |d] -conn [-table|t ] [-log|logfile ] [-show|s] [-validate|v] -help
      Examples:
      utility import -config /home/import/mydata.xml
      Import the file using the options saved in mydata.xml
      utility import -c /home/import/mydata.xml -data /home/data/importmydata.csv -log /home/import/mydata.log
      Import the file identified by the -data argument using all other options saved mydata.xml. Write logging details to mydata.log
      utility import -c /home/import/mydata.xml -table owner.mytable -cfg /home/import/mydata.xml
      Import into table owner.mytable using all other options saved mydata.xml.
      utility import -c /home/import/mydata.xml -conn myconnection
      Import the file using connection myconnection and all other options saved in mydata.xml
      utility import -config /home/import/mydata.xml -show
      Show the options saved in mydata.xml without doing an import
      utility import -config /home/import/mydata.xml -conn myconnection -table owner.mytable -validate
      Validate the options saved in mydata.xml and the parameters specified on the import without doing an import.
      Invalid UTILITY command: help

      1. wow, this was a really fast reply, thanks!

        I did as you told me – worked perfect in wizard mode from the UI. Then I saved the sdimp-file (the config XML) and tried again via sdcli.

        Now I receive a NullPointerException at oracle.dbtools.raptor.data.readers.DataReaderRegistry.getReader(DataReaderRegistry.java:45).

        Using the -show option worked fine and everything seems to be correct. (-validate produces the error above).

        Better open an SR?

        Thanks and Regards
        Peter

        1. thatjeffsmith Post
          Author
  97. is there a proper way to stop a query once you have started to run it? When I attempt to stop a running a query I invariably have to ctrl+alt+del to get out of sql developer. If i hit the close query button the program freezes

    1. thatjeffsmith Post
      Author
  98. I’ve added a comment on a view. Is there a way to view the view comment in SQL Developer. I’ve found table comments in the Details pane. But not the view comments.

  99. Thanks for your site–it’s helped me a lot.

    I use the keyboard shortcut to comment out code with line commenting (–) all the time. But is there some way to select text and apply block commenting (/* */) around it instead?

    When I’m debugging, it is useful to use this type of commenting to ensure I don’t lose track of the variables I need to put back in when I’m done:

    Select *
    From Scott.Emp
    Where HireDate <= To_Date('06/30/1985'/*p_HireDate*/, 'MM/DD/YYYY');

    1. you can highlight all of the code you want to comment out and press
      “ctrl + /”

      or you can use the block comment you referenced in your question at the beginning and end of the rows of text

      1. Thanks, Bob. That’s actually what I was referring to when I said I used the shortcut for line commenting. But what I’m looking for is a similar shortcut that would wrap the selected text with the /* in front and the */ at the end so that I could block comment it in place. There are some situations where that is a better option for what I’m trying to do.

        I thought I might be able to do it with a macro, but I can’t get it to work in the version I’m using (4.1.3.20).

        1. thatjeffsmith Post
          Author
  100. We can run a query using the F9 function key. Depending on the Preferences setup, when the query is run the Query Result tab will either by pinned or unpinned (ie new tab created). You can manually override this by pinning/unpinning the tab via the tab’s pin button. Now, to the question: is there a way to assign to function keys the ability to run a query so that tab is pinned or unpinned – overriding the Preference behaviour?

    1. thatjeffsmith Post
      Author
  101. Just starting to use SQL Developer Data Modeler and I have a quick question I couldn’t locate the answer to.

    I have a database that does not have all the foreign keys enforced.

    I’m trying to create a logical model and show relationships. When I create the 1-1 or 1-N relationship, I don’t have a choice to pick source field and target field – it only shows the primary key of both tables (or nothing in the two key drop downs if there are not keys defined).

    Thanks for the help.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

      In a corp environment – better to play by the rules.

      However, if you’re on a Win64 machine – as 90% of corp america is, just download the SQLDev package that includes the JDK. No install required, just unzip and run the exe.

  102. Hi! I’m using SQL Developer 4.0.3.16/Build Main 16.84. When Exporting to an .xlsx file, I noted that within the .xlsx file, the headers have freeze pane activated. Is there a way to deactivate this feature or do so for each export? The reason is I use extracted data to import into ACL (Audit Command Language) and the excel file fails the import unless I manually remove the freeze panes within the excel file.

    Thanks for your help!

    1. thatjeffsmith Post
      Author
  103. I am using jdk1.8.0_66 on a Win 7 64 bit machine. I am able to connect to some remote Databases using sqldeveloper v3 and also through custom java applications. But after using sqldeveloper-4.1.3.20.78-no-jre once I am no longer able to connect to any remote databases – vendor code 17002. The same java application is also failing to connect – “Caused by: java.net.ConnectException: Connection refused: connect” . If I add “-Djava.net.preferIPv4Stack=true” , I am getting –
    # A fatal error has been detected by the Java Runtime Environment:
    #
    # EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x000007feff268d2e, pid=7084, tid=1196
    #
    # JRE version: Java(TM) SE Runtime Environment (8.0_66-b18) (build 1.8.0_66-b18)
    # Java VM: Java HotSpot(TM) 64-Bit Server VM (25.66-b18 mixed mode windows-amd64 compressed oops)
    # Problematic frame:
    # C [WS2_32.dll+0x8d2e]

    Before using sqldeveloper v4 , the same application was running smoothly with “-Djava.net.preferIPv4Stack=true”. I have tried removing the instalation folder aswell as %APPDATA% contents but I am unable to revert back to working condition.

    Does sqldeveloper v4 change and Windows dll files ?

    1. Please ignore the above issue. Even after re-formatting and without installing sqldeveloper v4 the issue persists. Thus sqldeveloper is not at fault. Probably a JVM vs Windows thing.

  104. I’ve been able to import a good portion of my data model via the Data Modeler (v3.0.04). However im still lost on how to get grants/privileges imported. Even connecting as the schema owner I still see essentially none of the grants to other database users. Is there some special privilege that the *connecting* user needs in order to see the grants? I see a few of the other database users, but most of them have no grants to my current schema’s objects.

    What im trying to do is import the entire picture of a given schema (all objects and grants to other users) so that I can use this going forward to generate alter scripts for each release.

    1. thatjeffsmith Post
      Author
  105. Jeff,
    If I have the following defined in my server side sqlnet.ora:
    # Server Encryption Params
    SQLNET.ENCRYPTION_SERVER=REQUIRED
    SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)
    SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
    SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(MD5, SHA1)

    What are my possible connection options when using sqldeveloper? Prefer to use the thin drivers, is this possible?

  106. I am attempting to spool the results of a query into csv to be read in an excel file, but I either end up only exporting the sql code itself or at best the sql code followed by the text data.

    my code is

    SPOOL C:\DATA.xls

    select /*csv*/
    table.name,
    table.address
    from
    table;

    SPOOL OFF

    I have looked through the forums and I cannot see what I am doing wrong.

    I wold like just the results to export:

    Name Address
    Bob Alaska
    John Canada

    1. thatjeffsmith Post
      Author

      we can do that..i forgot to do the /*csv*/ bit, but you get the idea, I hope

      SET ECHO OFF is only honored in scripts, so if you want JUST the data, do the spool inside a script as well

      1. Thank you for the quick reply. I found some really useful things here.

        Is this what you mean, because it is still not working for me.

        SET ECHO OFF;
        SET HEAD OFF;
        SPOOL C:\DATA.xls

        select /*csv*/
        table.name,
        table.address
        from
        table;

        SPOOL OFF

        1. thatjeffsmith Post
          Author
          1. so i need to use sqlplus? i am not familiar with this. I will have to look around some more to find out how to incorporate.

            thank you.

          2. thatjeffsmith Post
            Author
          3. i need to execute the file through sqlplus. i am having an issue using it. i see what you did with the “@”

          4. thatjeffsmith Post
            Author
        2. ok, so i wrote my code and saved it as follows:

          SET ECHO OFF;
          SET HEAD OFF;
          SPOOL C:\DATA.xls

          select /*csv*/
          table.name,
          table.address
          from
          table;

          SPOOL OFF

          then I ran the script

          @C:\TEST.sql——THIS IS WHAT I SAVED THE SQL QUERY AS

          it ran, but is still exporting the sql code along with the data at the bottom in an unusable format.

          Is there something else i am missing?

          1. only put the spool commands in the script file. the set commands should be outside the script in sqlplus itself.

          2. SPOOL C:\DATA.xls

            select /*csv*/
            table.name,
            table.address
            from
            table;

            SPOOL OFF

            then I ran the script

            SET ECHO OFF
            SET HEAD OFF
            @C:\TEST.sql

            this is not working; still pulling everything. i am running it in sqldeveloper with f5 as you said.

          3. thatjeffsmith Post
            Author
          4. {
            SPOOL C:\DATA.xls

            select /*csv*/
            table.name,
            table.address
            from
            table;

            SPOOL OFF } this was saved as test.sql

            then I ran the script

            SET ECHO OFF
            SET HEAD OFF
            @C:\TEST.sql

          5. thatjeffsmith Post
            Author
          6. I am running version 4.1.2

            it is returning the sql code and the results:

            SPOOL C:\DATA.xls

            select /*csv*/
            table.name,
            table.address
            from
            table;

            NAME, ADDRESS
            BOB, ALASKA
            JOHN, CANADA

            2 ROWS SELECTED

  107. DM 4.1.3 does not draw the relationships when engineering a relational to logical model.
    Is there a way of convincing it to do it? I did not find any preference or option related to it.

  108. Under Solaris 11 (SPARC 64) sqldeveloper has a yellow outline on all of the text in the UI. Any way to turn this off? It started sometime last year, but I don’t remember the last version it looked normal.

    1. thatjeffsmith Post
      Author
      1. No. All of the text. The menus, the dialog boxes, everything. I can send you a pic if you’d like.

        BTW I’m using version 4.1.3.20.

        I’ll have to try to see which version is started with.

        1. thatjeffsmith Post
          Author
        2. Turns out it is an issue with the gnome window manager under VNC. Not sure why it shows up the way it does because it does not affect jDeveloper. When I tried using twm it looked normal.

          So I needed to change the way fonts are rendered under gnome. I had to turn off the subpixel smoothing (appearance/fonts/rendering) select something other than subpixel smoothing.

  109. Data modeler 4.0 converts unique constraints (defined in the relational model) into indexes after saving the editor changes. The unique constraints disappear and the indexes appear. The DDL preview shows a “create index” statement instead of a “alter table … add constraint unique …”.
    How can the conversion into indexes be avoided?

    1. thatjeffsmith Post
      Author
      1. Thanks, Jeff.
        I just tried with 4.1.3. But the same happens: the unique constraints are converted automagically into indexes after saving the table properties. The indexes are not even unique according to the DDL preview.
        What I do is this: define unique constraints (for a table without indexes), save properties, reopen the properties editor and the unique constraints are no longer there, but indexes with the same names (but not unique).

        1. thatjeffsmith Post
          Author
          1. Cannot say … I don’t see any such preference under Tools > Preferences > DDL . Where is it?
            When the DDL Generation Options dialog appears, the table’s unique constraints are not listed, only the indexes.
            Which means, the conversion happens before the DDL generation.

          2. thatjeffsmith Post
            Author
          3. No automatic index generation for any kind of key was set.
            I found a workaround: replaced the table with the unique constraints with an identical copy.
            DM generates now unique keys instead of indexes.
            Strange …

          4. thatjeffsmith Post
            Author
  110. we are having an issue with is the Document column which is a BLOB data type.

    This field contains a variety of document types that were uploaded via the application to hold correspondence against customer complaints.

    Now using tools such as SQL developer it is possible to view some of these files using the External Editor option, but not all of them – those that won’t open give the following type of view

    view like
    YYYYYYYYYYYYY
    -> YYYYYYYYYY

    have you seen this before, and is there a mechanism that we can use to resolve this; or alternatively is there any script or procedure we can run that could output all of the blobs, in their correct format into a file location as individual documents, but being referenced to the data held in other fields of the table.

    1. thatjeffsmith Post
      Author
  111. please, please, please use pl/sql developer for a short while, i guarantee you’ll be filled with inspiration, it is so much easier to work with in comparison to sql developer, sincere apologies for using this very helpful board in this way. please remove once you’ve read

    1. thatjeffsmith Post
      Author

      I have. I’ve also used Toad for 10+ years. And I’ve used Golden, and SQL*Plus, and OEM, and Access/Excel as a front-end, and KeepTool, and Navicat, and more than a few others.

      You have to give me direct, specific requests. Saying, make it be like tool x, y, z makes no sense. No actionable sense at least. And I would suggest you just keep using pl/sql developer if that’s what you prefer.

  112. Hello,
    I just downloaded Orcale 4.1.3.20 SQL developer on my MAC OS X 10.10.4 and i can’t create a connection. Whenever i do type a username and password a red message showup : “iO Error: The Network adapter Could not establish the connection ” I specifically downloaded Oracle for Mac OS X and i can’t seem to find a proper solution on other websites.

    1. thatjeffsmith Post
      Author
  113. Hi Jeff

    I found I can easily share Reports with team members using Preferences / User Defined Extensions.

    What if one wants to share preferences, snippets, templates and connections? One request is that a new developer (possibly on different OS) gets a standard initial setup. The other request is sharing objects like snippets (without copying files).

    Do you see a better way than links for shared config-files and copying a standard set of config-files to proper location for initial setup?

    Thanks!
    Tino

  114. Is there any option for “database export” to ensure the same sort order of database objects in ddl export files?
    I’d like to create “snapshots” of schema-ddl during development cycle to have a chance to “diff” the changes based on those files. But currently this doesn’t work because the sort order (is there any?) of objects seems not to be fix causing the diff to be (nearly) worthless. Doing some changes e.g. on constrarnts or indexes results in a different sort order in databse export ddl.
    Would be a very helpfull feature!

    Thx,
    Dirk

  115. I am adding new connections to SQL Developer, and saving them. But when I shut it down and go back in it’s gone. The conx I added a long time ago are still there, but not the new ones I’m adding.

    Thanks,
    Jason

    1. thatjeffsmith Post
      Author
  116. I’m working with a database containing more than 400 tables, many of which are of no interest to me or my work. But, I want to see the relationship(s) between the tables I *am* interested in. When creating a Relational model by dragging tables from the database tree view (in SQL Developer), is it possible to remove unwanted tables without deleting them completely from the model? For example, if I’m multi-selecting tables and accidentally select an extra table I don’t care about, then drag them all to the model. I know I can use ‘Delete View’ if I’m working in a SubView, but is there something similar when working in the Relational? Or, should I always just do this sort of work in a SubView?

    1. thatjeffsmith Post
      Author

      I’d definitely create one or more subviews to manage that many objects.

      You can even close the main diagram, so that you only see the subview(s). Use the ‘Delete View’ to only delete it from the SubView vs ‘Delete’ which will completely remove it from the design.

  117. Hi,

    I’m using sqlcl 4.2.0.16.049.0842 RC and that does not put “insert” statements into the command history. Instead, a ‘ctrl-p’ or cursor-up produces the last non-insert statement.

    Another issue is more an usability problem than a bug: i would prefer that hitting would always execute the current line instead of inserting a newline. This is nearly always what I want (e.g. after editing a typo in a stmt). You might use e.g. crtl-v as prefix for quoted insert (so ctrl-v ctrl-j would do what enter does today). This is how e.g. Zsh and Bash work.

    1. Seems there is some HTML-escaping going on. The 2nd paragraph should read: … I would prefer that hitting “Enter” … (I originally used angle brackets around Enter)

    2. Hi Jeff,

      any chance on a feedback wrt. “insert stmts are not inserted into history”? Is that a bug in sqlcl or something I’m doing wrong?

  118. Partial Solution: The Oracle database has over 100 schemas so the popups do not appear (correctly–default is set to 10). I am connecting to only one schema (mine). How do I get SQL developer Completion Insight to “work” only on the objects in my schema?

    1. thatjeffsmith Post
      Author
  119. Using Sql Developer 4.1.3.20 on Windows 7 enterprise. Completion Insight totally dead. Ctrl+spacebar brings up nothing. Saw your video. I’m connecting to database over Juniper Networks Pulse vpn. Can read / write all tables. But no code completion.

    1. thatjeffsmith Post
      Author

      View > Log > Statements

      type in an completely blank and connect SQL Worksheet:

      select * from hr.+

      In the Statements panel, do you see us querying the data dictionary looking for hr objects to populate the insight list? See anything wrong with it?

  120. Jeff,

    I’m using SQL Developer 4.0.0.13. The feature of double click a word to automatically search the text in code editor is not functioning now. How to get it back? Also I used to be able to highlight a word in the text editor. Then when I do a Ctrl+F, the highlighted text will appear in the “Text to Search For” box. Now it is not appearing there. How to fix it?

    Thanks,
    Dongqing

    1. thatjeffsmith Post
      Author
      1. Jeff,

        Thanks for the quick reply. I had used 4.1 previously. But it crashed when the PC was forced to shut down. I’ve lost those Connection windows etc. Somehow I cann’t install a newer SQL developer version. So I copied those 4.1 settings to a 4.0 version and start to work on the 4.0 version. Maybe its time to re-install a newer 4.1 version with your help.

        Dongqing

        1. thatjeffsmith Post
          Author

          we don’t have an install, you just unzip the download and go

          ‘crashed when PC was forced to shut down’ – you mean Windows asked the app to close, and it wouldn’t? Probably b/c it asking to you confirm to save open code editors/files/worksheets. Enable ‘save all on shutdown’ preference to avoid that.

  121. Love your product!
    Can you startup or shutown the database with sqlCL.
    examples
    shutdown immediate
    shutdown abort
    startup
    startup pfile=
    ETC…
    to replace sqlplus you need to go all the way.

    1. thatjeffsmith Post
      Author
  122. Hello Jeff,

    There is another tool used commonly for Oracle that has a “quick browse” feature on the Navigation tree (shortcut key F3). This actually opens a worksheet and pastes in a select from the table similar to what you get when you drag a table name to an open worksheet in SQL Developer. Sounds trivial but it is a great time saver when you need to quickly browse multiple tables and you get a worksheet opened, data returned and all the column names in a single key press. Is there any chance SQL Developer will ever offer a feature like this? Even a right click menu option on a table or view in the Navigation tree when Open Object on Single Click is turned off would be nice.

    1. thatjeffsmith Post
      Author
      1. Thanks for responding. Like I said, maybe kind of trivial, but:
        — With regard to the “open” feature, it is a single key press instead of right clicking, opening the table, and then clicking again on the Data tab to see output.
        — Again, with dragging and dropping, it is a single key press instead of dragging, dropping, then clicking execute or pressing f9 to see data.

      2. Also, two ways that could possibly gain similar efficiency:
        1) Add a preference to allow the Data tab to show first when you click Open on a table or view from the Navigator plane; or
        2) Add a preference to automatically open a new worksheet and execute when dragging and dropping from the Navigator pane.

  123. How do you create a custom report showing Entity, Attributes and Relationships?
    I have tried Entity and Attribute (which is indented) and the Identifiers — which remains at the same level as Attribute. But the example Entity two level has Attribute indented and Identifiers indented further

    Also how to make each value be a column in the spreadsheet

  124. Hi,

    I am using ERwin Data Modeler Tool to generate the DDL for the schema. However, when I Forward Engineer the schema it generates a single DDL file. But I want to generate DDL file for each Table separately, resulting in one action into multiple files for each Table.

    Any help will be greatly appreciated.

    Thanks in advance,

    Jason

    1. thatjeffsmith Post
      Author
  125. Hello Jeff,
    I use “set sqlformat html” in sqlcl to format the output. The result looks good but I would like to modify the table, text, color, page tile,and add some parameters.
    Is what I need possible ? I tried to edit the columns with SQL plus commands but it did not work.
    Thank you

    1. thatjeffsmith Post
      Author
      1. Jeff, Thank you for replying. I’m no familiar with js or perl. I tried not to use “set format html” and used sqlplus liked commands to format columns, size, text. However, it did not work. Does sqlcl support those commands (and I did something wrong ) ? or a script to change the file, after the fact is my only option ?
        thank you again

        1. thatjeffsmith Post
          Author
          1. This is what I had in my .sql file
            ——————————————–
            set termout off
            set pagesize 50000
            set linesize 3000
            SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
            HEAD “This is a test –


            ” –
            BODY “TEXT=’#FF00Ff'” –
            TABLE “WIDTH=’90%’ BORDER=’5′”
            Column feeder_id heading feeder
            column tripsaver heading tripsaver
            column new_time heading newtime
            column new_time_local heading localtime
            /* To name file */
            spool TripSaverReport2.html
            /* query here */
            select yolobo.*, count(*) over (partition by tripsaver) as count
            from
            (SELECT distinct ts.feeder_id, ts.tripsaver, ts.new_time, from_tz(ts.new_time,’GMT’) at local as new_time_local …….. )
            /* end of query */
            spool off
            host start TripSaverReport2.html
            set termout on
            ———————————————

            the result would like like this: [
            feeder tripsaver newtime localtime COUNT ——————– ———- ——————————- ————————————– ———- HUTCH1503 359953873 06-OCT-15 08.43.49.000000000 AM 06-OCT-15 03.43.49.000000000 AM AMERIC 1 A/CHICAGO CRLTN1401 359955124 11-DEC-15 08.03.58.000000000 PM 11-DEC-15 02.03.58.000000000 PM AMERIC 1 A/CHICAGO EULSO9132 658873843 05-OCT-15 06.46.52.000000000 PM 05-OCT-15 01.46.52.000000000 PM AMERIC 2 A/CHICAGO EULSO9132 658873843 08-OCT-15 03.06.31.000000000 AM 07-OCT-15 10.06.31.000000000 PM AMERIC 2 A/CHICAGO HURST1882 658873846 19-OCT-15 03.18.40.000000000 PM 19-OCT-15 10.18.40.000000000 AM AMERIC 1 A/CHICAGO
            ]

            thank you

          2. My first reply got pasted incorrectly. This is what I had after “set markup”:

            ————————

            SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
            HEAD “This is a test –


            ” –
            BODY “TEXT=’#FF00Ff'” –
            TABLE “WIDTH=’90%’ BORDER=’5′”

            ————–
            thank you

          3. thatjeffsmith Post
            Author
  126. Do you have any expertise/suggestions on where to look for Oracle BIEE installation issues? I am getting no where on the Oracle Forums

    1. thatjeffsmith Post
      Author

      My Oracle Support.

      The Forums are places for people of like mind to hang out, ask questions, share opinions. It’s not a support channel. I know of quite a few OBIEE experts that hang out on Twitter and have blogs. And of course, I’m assuming you’ve already Google’d the error messages you’re getting?

  127. I accidentally closed my Browser and Navigator windows, and I cannot get them back! Clicking on Browser or Navigator in the View menu does nothing, nor do the keyboard shortcuts. How can I get my Browser and Navigator windows back? I’ve tried running a fresh copy of datamodeler.exe, but it keeps coming up in the same way (no Browser or Navigator). Sometimes, I get this error: SEVERE: Error during handleEvent on action ‘Browser’ (id=799)

    1. thatjeffsmith Post
      Author

      I’m assuming you’re running the standalone modeler application. If so, i would rename the system directory where your preferences are stored. If you’re running the modeler inside of SQL Developer, try the Help > Reset Windows to Factory Settings menu item.

        1. thatjeffsmith Post
          Author
  128. Hi Jeff,
    is it possible to copy the content of a worksheet-command-area including markup as colors and fonts (e.g. “copy as html”).
    Would be very helpfull for documentation purposes.
    Thx in advance,
    Dirk

    1. thatjeffsmith Post
      Author
  129. I have many (000’s) of case sensitive named procedures in packages that the Run or Debug tool does not recognise. Is there a known work around.
    Example of compiled package:
    create or replace package test_pkg as
    procedure “TestProc”(p_in in varchar2);
    end test_pkg;
    create or replace package body test_pkg as
    procedure “TestProc”(p_in in varchar2)
    is
    begin
    null;
    end “TestProc”;
    end test_pkg;

    When trying to run the dialogue box shows:

    Empty package TEST_PKG definition (no public members).

    1. Hi Jeff,

      Have you any thoughts or workarounds where Procedure names are enclosed in Double Quotes, i.e. case sensitive naming, on how to run/debug procedures?

      thanks in advance
      Liam

      1. thatjeffsmith Post
        Author
        1. Hi Jeff,

          Yes, it doesn’t work. None of the procedure names appear in Run list, even after compile.

          Running 4.1.3.20

          Many thanks
          Liam

  130. I have just one table created but no records on it. Now I have to copy the column names . How to achieve this. Without any data, it is not working Ctrl + Shift + C in the output window

  131. SQL Dev v4.1.3.20.78 not with jdk and using jdk in SQL DM v4.1.3-901

    Run a Query
    Export Results

    v4.java.lang.reflect.InvocationTargetException

    Switching to SQL Dev v4.1.1.19.59 does not give the error

  132. Hi Jeff,

    Why does synchronizing a unit test take so long (can take minutes) to display the package list? It can take upto 7 mins to synchronize a test.

    Why is it slow and how can it be speeded up?

    Thanks.

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  133. Hi Jeff,
    I have installed both SQL Developer (JDK 8 included) and SQLcl (without Java).
    – Could I use the ‘inline’ JDK 8 from SQL Developer for SQLcl too? And if so, what’s the easiest way to set it up?
    – The sql.exe version from SQL Dev says product version 4.2.0.105.1800, the sql.exe version from SQLcl says 4.2.0.349.7.6, but both versions say file version 1.0.0.0. Whats the difference?
    As always, thanks a lot for your help!!
    Regards, Mark

    1. thatjeffsmith Post
      Author

      >>Could I use the ‘inline’ JDK 8 from SQL Developer for SQLcl too?
      Yeah, but it’s not very straightforward. Running SQLcl is just going to look for java on the machine’s path. If you can run java -version from a dos prompt, you’ll probably OK. If you don’t have Java on your machine AT ALL, then you can copy the jdk dir out of your sqldev install and copy it to a jdk directory under the SQLcl directory and then run it.

      The SQLcl from standalone OTN download is newer than the one we shipped with 4.1.3 – both are beta, hence the 1.0.0.0 file properties.

        1. thatjeffsmith Post
          Author

          >>copy the jdk dir out of your sqldev install and copy it to a jdk >>directory under the SQLcl directory and then run it.

          I tested this yesterday and found a bug, darn it. So for now you’ll need to have a Java 7 JRE or higher installed on your machine to run SQLcl.

  134. Jeff,

    could you help us how to configure Flashback data archive features for tables in data model? i searched around physical model (oracle database 12c) in SQL Developer DM – Version 4.1.1.887. but don’t find it. kindly help.

  135. Jeff,
    Started having a problem on a Windows 2008 R2 VM after running patch updates and upgrading to sql developer 4.1.3.20.78. Intermittent crashes, Sql Developer stops working and displays a generic “a problem caused sql developer to stop working” –I’m pretty sure this is a generic OS error message. Anyway, after MUCH searching, reinstalling, trying different version, etc., etc., the problem “appears” to be a missing entry in the path the the “perl” folder under Oracle_Home. Hope this helps someone else –cost me several hours.

    1. thatjeffsmith Post
      Author
  136. Hi Jeff

    Great blog! Lots of inspiration….

    Question: In (4.1.3) Reports, PDF-Settings, only paper formats ‘Letter’ and ‘Legal’ are presented. Do you know how to add additional formats like ‘A4’? (I ‘m betting on a ‘secret’ XML setup file somewhere in .sqldeveloper….)

    Thanks
    Tino

  137. Dear Jeff,

    I work on a few ‘Editor-User Defined Extension’ for the PL/SQL Editor (PlSqlBaseNode).
    And I came across the following ‘interesting’ behavior.
    Version: SQLDev 4.1.3.20 (20.78) on Windows7 64bit, Java 1.8.0_45

    .) Data-Dictionary Views USER_* ALL_*
    I started with your example where you count the number of source code lines
    SELECT count(1) num from all_source …

    I may have missed this in your explanation, but if I change all_source to user_source it just doesn’t work.
    In the Log/Logging Page window the following errors are shown (sequence and elapsed not shown):
    Level: SEVERE
    source: oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1
    message: null at java.util.StringTokenizer.(StringTokenizer.java:199)

    If you try it again (which of course doesn’t make sense) the error is shown again, but sometimes the following message appears:
    message: null at (Internal error) No Throwable Stack Element
    (same level and source)

    I’ve tried it with a SQL on USER_/ALL_identifiers, the behavior is reproducible.

    Interesting to note, if you use all_*, and check the Log/Statements window SQLDev will replace all_* with DBA_*.
    There are 2 statements, first SQLDev tries if it can access dba_*, and than ‘rewrites’ the statement:
    1) select 1 from dba_source where 1=2
    2) SELECT count(1) num from dba_source where …

    My DB-User has access to dba_source.

    Question:
    -) I may have missed this in the examples/tutorials but is it the expected behavior to use ALL_-Dictionary Views and not USER_*?
    -) Why the search/switch to DBA_* Views?

    .) ‘Popup Describe (Shift-F4)’ behavior change as soon as you define an User Defined Extension
    If there are no Extensions defined, and – in a SQL Worksheet – you put your cursor on a PL/SQL-name and hit Shift-F4 a new PL/SQL-Editor-Tab would appear.
    So maybe in the background SQLDev really use ‘Open Declaration’ as some kind of default-fallback-scenario?
    But as soon as an Extension is defined Shift-F4 would show a new ‘Describe-Windows’, but the only Tabs shown there are the ones from the Extensions.
    So this maybe some kind of correct behavior, because if you select a table-name and hit Shift-F4 the ‘Describe-Window’ is shown.

    I’m just curious, why an User-Extension would ‘break’ or change the ‘Popup Describe’ behavior?
    But it’s not a big deal, just assigned a new shortcut to ‘Open Declaration’.

    Thank you in advance for your time and help.
    kind regards,
    Tom

    1. It looks like that site is a bit old and perhaps not being actively maintained.

      Snippets – just copy & paste the code.
      Reports – click “Generate XML” and it generates the report file to import – although I haven’t worked out how to actually import it into SQL Developer yet. The “Open Report” function doesn’t seem to work. You could just extract the SQL out of the XML file and load that into SQL Developer.

        1. For the wayward Googler:

          Looks like the oldest public reports (the ones on the main page) are actually okay, they just need a small adjustment:

          {ORIGINAL XML HERE}

          Newer reports are okay as is.

          The site is basically abandoned though. No new reports since 2010

          1. XML I pasted was stripped out of the comment.

            Essentiall, surround the original XML with 3 additional lines of XML.

            Use an existing report for comparison.

  138. Hi Jeff,

    I have a small issue with SQL Developer installed on OS X and used to access SQL Server. When I try to set a DB as default an error message is returned: incorrect syntax near ‘-‘ Vendor code: 102.
    I manage to figure out that this issue is caused by the DB name format which is like DDD_123-234 (I can not rename the DB because is a production one and this should involve a lot of work)
    Message log is looking like this:
    Error starting at line: 1 in command –
    /*sqldev:stmt*/USE DDD_123-234
    Error report –
    SQL error: database ‘DDD_123′ does not exist

    Is there a set up option in which the SQL Developer will not check DB schema name for “special characters” or at least will ignore the issue or
    I can redefine the command for set default database to look like this: /*sqldev:stmt*/USE [DDD_123-234], for good?

    PS: SQL Developer version is 4.1.3.20, JDK 8 Update 74 and JTdS driver 3.1

    Best regards,
    Catalin

  139. Jeff,

    I am using Oracle RDS instance on Amazon web service and ORACLE SQL DEVELOPER – 4.0.3.16. to connect RDS Instance.

    In my oracle schema i have SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12% tables. All these tables are temp tables.

    I didn’t do any third party migration. I just used third party jdbc driver (jtds-1.2) in ORACLE SQL Developer to connect SQL Server.

    I just want to know, why these tables are created in my schema when i didn’t migrate data from SQL Server and why they are temp tables. Most of the tables are empty.

    If i export my schema, the export log file didn’t show the tables mentioned above. But, after import the schema shows all tables including the temp tables (SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%).

    Does it impact my oracle schema if i delete temp tables(SS2K5%, SYB12%, STAGE_SS2K5%, STAGE_TERADATA%,STAGE_SYB12%)?

  140. Jeff,
    We have an application which runs batch jobs. This application uses DB2 load utility to load the .DAT files into DB2 Database. Now, we need to convert them to Oracle Loader scripts so that we load .DAT files to Oracle Database. Could you please suggest on how to do this and what tools I would need to test this ?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
          1. Thank You, Jeff.

            I did go through that article before. However, my query is how to convert those DB2 load scripts to Oracle load scripts. I have the schema created in Oracle. I have the SQL developer installed and connected to this schema.

            At IBM site, I see that there is a perl script to convert Oracle load scripts to DB2 Load scripts. However, I need the other way.

          2. thatjeffsmith Post
            Author

            we don’t have anything like that that I can tell

            we can connect to DB2 and move your data over using SQL Developer Migrations or GoldenGate

            if your load script have delimited data in them, we can setup loader scripts for them

          3. Jeff,

            I’m trying to import data to a table. When I select the .DAT file, I’m getting an error saying “cannot be opened due to the following error: null”.

            And, I see that file Format fields are not editable. Could we change File format parameters ? Oracle does not support DAT files import ?

  141. I am trying to create new connections on Oracle-SQL developer version 4.1.3.20. My username includes windows domain for e.g. ABC\username. The connection gives invalid username/ password. The username is exactly the way it is on the Oracle DB. I also tried just using OS authentication without specifying username/password. Do you have any suggestions?
    Thank you

  142. Hi Jeff,
    I was wondering if there is a way to apply a filter to a table/view prior to going to the data tab? We have some views that run forever if you don’t have some limiting condition applied to them.

    Thank-you,

    Barry

    1. thatjeffsmith Post
      Author
  143. Recently i have a minor issue when navigating bookmarks within package body. whenever i press strg+q and there is no bookmark after the actual position, some seconds later an error message pops up “Bei der Ausführung des angeforderten Vorgangs ist ein Fehler aufgetreten:

    I/O-Fehler: Unknown host specified

    Herstellercode 17002”
    i have press 2 or 3 times “OK”. Then an new empty tab opens, titled with the name of the package body.

  144. I am getting ORA-01460 errors trying to view object in a 9i database from SQL Developer 4.1.1

    Is there a fi or workaround for this?

    1. thatjeffsmith Post
      Author
      1. Obviously I will need to downgrade SQL Dev. Who among us can simply upgrade the dAatabases we work on at will?

        In trying ti]o get SQL Dev 4.1.1 to run with a 9i client, I went to Tools>Preferences>Database>Advanced and set a value for “Use Instant Client” using the configure button. Now I cannot clear that value, and nothing is working now. ORA-01460 errors everywhere. There appears to be no way to clear this value. Clearing the checkbox has no effect.

        Can you point me to the config file so I can clear this preference setting>

        1. thatjeffsmith Post
          Author

          Um, not a 9i client. That’s not going to work. You need to get an 11.2.0.3 or 11.2.0.4 client.

          I know you can’t simply upgrade your database, but that would fix the issue. And many others that are probably hanging around…but, yeah.

          1. I see that isn’t going to work. But I’m asking now how to UNDO the Preference change that I made to test this. The UI won’t permit me to clear this setting (it was empty before I started).

          2. thatjeffsmith Post
            Author

            You can’t clear it, but can you uncheck the 2 ‘use’ boxes? Or can you use the dialog to point to another client?

            If that all fails, rename your system4.1.X.X… directory.

          3. Clearing the checkboxes has no effect. I’ll look at the directory you mentioned. I was hoping I could edit the config file where this is stored.

  145. Hi Jeff, Thanks for your posts.
    I use SQL Developer daily and I recently upgraded to 413 (from 4119). In both versions I had lost the breadcrumbs in the PL/SQL editor. The preference Display->Show Breadcrumbs is checked. A couple of my co- workers have the same problem too.

    What can I do to bring the Breadcrumbs back ?
    Thank you
    – Neelesh

  146. Hi Jeff,

    I use SQL Developer every day as a DBA for the last several years – it’s great and become better and better with every release. But there are some absent things, which I dream about, like search within connections list (I have hundreeds of them, so folders are not helping much) or some modifications with text, which I’d like to bind to shortcut keys.

    As I see, SQL Developer tend to be developed for the Data Modeling and 12c features and Cloud features, but not the features I particalarily need for my day-to-day job, so, I’d like to develop all this stuff myself.

    When I try to find any info about plugins development, I faced a great problem – there is only bunch of broken links and very few very old articles: about 5-7 years old.

    So, my question is – where should I start to look for info? Is there any ‘Oracle SQL Developer plugina development portal’, or may be some book?

    Thank you!

    Regards,
    Andrey

    1. thatjeffsmith Post
      Author

      Eventually we hope to but a new SDK and examples up on our Github project. But for today, it’s what’s out there plus the SQLDev and JDEv OTN communities where you can post questions or ask for help.

  147. Hi Jeff,

    Any ideas on how to extract the DDLs for ROLES and GRANTS given for specific set of users using SQL DEVELOPER?

    Please help!.

    Regards.

    1. thatjeffsmith Post
      Author
  148. Trying to import a txt file into a table and get a message that file cannot be opened due to the following error: null. If I save the file as an Excel file, the data loads fine. This happened on 4.1.2 and the current version.

    1. thatjeffsmith Post
      Author
  149. Great tool! I am using this more and more, and I love that I can spool my output, just like the old SQL*Plus.

    With the latest build (sqlcl-4.2.0.15.349.0706-no-jre.zip ) sql.exe is flagged by my anti-virus WebRoot for a “W32.Aware.Gen” alert. I reverted back to sqlcl-4.2.0.15.296.0549-no-jre.zip and had no problem.

    Do you have any recommendations?

    Thank you! Please keep up the great work.

    -Ted

  150. Is there a way to get the nice round pig’s ear for self-relationship like we could in Designer?
    Also is there a way to fix relationship lines once positioned differently from the default so that the line does not cross an entity?

    Regards

    1. thatjeffsmith Post
      Author
      1. Not the Arc when you want the either/or scenario

        I have entity where I have Parent_id and Record_Id so I want to show a self-join relationship meaning a record may have a parent record in the same table — Employee and Manager scenario. Currently this kind of relationship shows a a series of straight lines to make up a ” circle”

        1. thatjeffsmith Post
          Author
  151. Hi Jeff,

    I have loaded over a million records (stored as a CSV) into a clob in my database, when trying to unpack this though it is taking 10 mins to do so. The file is likely to grow to 10 mil records over time and, at my projections, will take +- 50 mins to unpack and convert into a table.

    Is there a way to quick retrieve the CSV data from the CLOB and convert it into a table?

    Thanks,
    Warren

    1. thatjeffsmith Post
      Author

      So you’re using a table to store a clob to store 10,000,000 records – why? if you want fast access, store the 10M records as records in a table. There probably are faster ways to get the data out, but I would go ask the storage/compression/OS folks that specialize in this. All I can say is, don’t do that.

  152. Hi Jeff,

    I am trying to run a query on sql developer, I have all the connections set up and it works fine. But when I try to run the query it says either Protocol Violation [67] or no more data to read from socket or some numbers like 20300070000.

    I tried to google the solution and it says something related to java, but could not find an accurate solution to it.

    Can you please help?

    Thanks,
    Hetal

    1. thatjeffsmith Post
      Author

      I would need a lot more info. Versions of everything, etc. In the meantime, I would open a Service Request with My Oracle Support if you have an account/

      I’ve not personally seen this behavior before.

  153. Found out about SDSQL command-line client. Interesting!

    Is there any way to connect SDSQL to MS SQL, which is supported in SQL Developer?

    My use case is automating some SQL scripts that do CSV exports. We found out that SQLDeveloper is the best tool for exporting CSVs.

    I was thinking about automating those scripts via SQL*Plus first, but I don’t know if there is any way to connect to MS SQL from SQL*Plus.

    1. thatjeffsmith Post
      Author

      So we’re Oracle.

      MS SQL is Microsoft.

      We don’t spend much time worrying about SQL Server – unless it’s to help you migrate it to Oracle.

      So short answer to your question would be, ‘no.’

      We care about you, just not so little that we would encourage you to stay on MSFT’s database 😉

  154. Hi, I’m unable to delete the sql developer folder in the roaming profile folder. SOme process is Always recreatin new folders. dropin…. any idea?

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author

          a program is using that dir/file, so windows won’t let go of it

          you have to find the process and kill it

          if you can’t find it, i would try rebooting the box – this isn’t really a SQLDev question though, it’s more about Windows

  155. I am getting a “Invalid column name ‘suid’. Vendor code 207” error when I try to access SQL Server 2014 databases from SQL Developer. I do not have a problem accessing 2008 R2 databases. The databases are visible , but when attempt to drill down to the tables I get the error. Is there a fix for this problem? Your help is appreciated.

    1. Hello I’m having the same issue for the same versions of the various softwares (SQL Developer, SQL Server and JTDS).

      Any solution for this or any clue on what is the origin of the problem?

  156. Hello Jeff,

    Could you please explain why SQLdeveloper does a SVN delete –force?
    I edited a database copy of a package, tried file save as, the file was already open in SQLdeveloper, I closed it and saved the database copy using the same name. The file was no longer under version control.

    Thank you,
    Ruben.

  157. Hi Jeff!

    We are embarking on a BI project using Oracle Data Integrator (ODI). There is an official recommendation for ODI to have all the tables in one schema. We are planing to have 1000+ tables. Such big schemas obviously put a strain on the developer as looking up for a specific table becomes tiresome. We have naming standards and I know of the “just start typing” shortcut in the connections tree or schema browser window and that there is a table filter in both schema browser and connections tree window. However, I’d like to be able to switch the displayed set of tables “instantly” – to have multiple saved table filters and to switch them by a keyboard shortcut. The next best approximation would be to have multiple schema browsers (each with its own filter) – however, right now I can have only one schema browser window for a single schema. Is there any way to come close to the desired setup in current version (using 4.1.2.20), or do you have a suggestion how to be able to quickly deal with such number of tables?

    Thank you very much for your time!

    1. thatjeffsmith Post
      Author
      1. I completely forgot about reports! I like the idea of customized actionable data dictionary reports very much! Although Jerome dismissed the “everything in one schema” approach as the only viable (and so we will probably split data “topics” each in own schema and have a much more manageable set of tables), I can think of other uses of such reports (mostly used objects grouped together and many others). Thank you for your insight!

    2. Hi Aleksander,

      Are the 1000+ tables your target tables on which you are going to do reporting or are they work tables created by the ODI Knowledge Modules?

      In the first case, there is absolutely no harm to have it in different schemas. I would just recommend to use a single Data Server per database in your ODI Topology.

      It is only recommended to regroup all your work tables – created by ODI – in a single schema to avoid polluting your target schemas. These tables will usually be prefixed by prefixes like C$_ for extraction, I$_ for integration, J$_ for journalization or E$_ for errors and you probably don’t want that in a clean data warehousing schema. That’s why a single work schema, usually called ODI_TEMP, is used. But if you have too many work tables, nothing prevents you to have multiple work schemas instead of just one.
      In addition, most of the knowledge modules will drop C$_ and I$_ tables after a successful execution.

      More about the ODI physical topology (and the work schema) here : http://www.rittmanmead.com/2014/12/data-integration-tips-one-data-server/

      Hope it helps.

      Best Regards,
      JeromeFr

      1. We will have around 250 “target” tables (dimensions and facts), others will be some aggregates, archives, staging and helper tables. I’ll also check out the document you suggested! Jerome, thank you for your valuable advice.

    3. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. unit testing , i new to this technology , im eagerly waiting to learn . i want to know how to do unit testing in oracle sqldeveloper software

        1. and also performance tunning ! im thankful to u for making a good website , it is really useful and makes me to understand easy

  158. I am using SQL Developer 4.1.1.19.
    Executing the following query returns no records:

    “SELECT MARVIN.”SAMPLE”.SAMPLE_SEQ_NO,
    MARVIN.”SAMPLE”.FREQUENCY
    FROM MARVIN.”SAMPLE”
    WHERE MARVIN.”SAMPLE”.FREQUENCY IS NULL”

    The query shows all FREQUENCY values when not set to NULL.
    There are nulls in this data element and the this query shows nulls when executed in other applications. Also, nulls will show when looking at other data elements in the table. The “FREQUENCY” field is numeric and is in the last field of the table. Is the name “FREQUENCY” a problem?

    Cheers,

    David

    1. thatjeffsmith Post
      Author
        1. thatjeffsmith Post
          Author
  159. Hi,

    I am trying to connect oracle database via excel and it keeps giving me below error. I use SQL developer. I know this might be a question for a different forum, but still if you could help me with your expertise in this area.

    error as below:
    Driver’s SQLAllocHandle on SQL_HANDLE_ENV FAILED

    I checked and the oracle path is set properly to the bin directory of oracle.

    1. thatjeffsmith Post
      Author
      1. Because, I want to automate the process of refreshing a certain metrics every 2 weeks so that I do not have to runt he query each time and export to excel and then create pivot tables.

        1. thatjeffsmith Post
          Author

          before I tried that, I would create a report in APEX that refreshes the data/metric live or on-demand.

          barring that, first hit on google for your error message says to check your PATH OS Environment Var and put your $ORACLE_HOME first.

          1. Thank you Jeff. Once I changed the user account control to never it fixed the problem because when I checked the PATH it was pointing to the right directory of oracle.

            I do not know what APEX is. but will take a look at it.

            Thanks,
            Hetal

          2. thatjeffsmith Post
            Author
  160. Jeff,
    In Data Modeler Version 4.1.0.19, I cannot find the “View to Table Wizard” mentioned in some earlier documentation. I believe it was a new feature in SQL Dev Data Modeler 3.0? maybe?

    Does this still exist? Why can’t I see it?

    Thank you,
    john3

    1. thatjeffsmith Post
      Author
  161. Hey Jeff,

    I have a problem that I have been scratching my head with. I have an oracle database on my windows PC and I have the oracle sql developer setup with the database connection without any problem. Now , I have Ubunut 15.10 setup on VMware workstation player 12. Here I have setup oracle sql developer too and I am trying to connect to the database. I have looked up the listner.ora and tnsnames.ora files and the host is set to localhost and I changed it to my local host’s IP address(which I dont think would not matter since local host is my PC’s IP address, right?). So when I am trying to make a database connection on Ubuntu on Vmware. It says IOerror: Network adapter could not establish the connection. Why is this so? I am a newbie so any please spell the answer in pieces so I can understand me.

    Thank You.

    1. thatjeffsmith Post
      Author

      Step 1, can you ping the ip address from your ubuntu vm image? If not, your sqldev connection won’t work.

      An easy workaround might be what we do in VirtualBox vm we ship – we setup a port forward that auto takes traffic on 1521 on the host and forwards it to the listener port on the image. You might be able to do the same, but the exact opposite, fwd the image traffic to the host port for the db listener.

      1. I have pinged and there is a reply on the vm image
        As for the second part I am not sure I understand what you said or rather how to do it(newbie here). Is there a way i could upload images here.?

        1. thatjeffsmith Post
          Author
          1. yes the sqldev is on the image, it is and I am able to ping the host from this image. The ping replies are fine
            [email protected]:~$ ping 192.168.233.1
            PING 192.168.233.1 (192.168.233.1) 56(84) bytes of data
            64 bytes from 192.168.233.1: icmp_seq=1 ttl=128 time=0.549ms
            64 bytes from 192.168.233.1: icmp_seq=1 ttl=128 time=0.818ms
            64 bytes from 192.168.233.1: icmp_seq=1 ttl=128 time=0.811ms
            64 bytes from 192.168.233.1: icmp_seq=1 ttl=128 time=0.734ms

            That’s what confuses me. The ping replies are fine but the database connection would not allow me to establish a connection.

          2. thatjeffsmith Post
            Author
          3. Hey ,
            Thank you, I have solved the problem. It was a problem with the IP’s. I was using the wrong IP address. That’s because when I ipconfig on windows, it shows up 3 IP address. One is my localhost’s. The second and third address’s are the Virtual machine’s adapters. I was using one of those!!! Took a while for me to realize. Thanks though!!!

  162. Hello,

    Today I right clicked on a column heading in a SQL results pane. One of the options listed in the pop-up box that appeared was “Delete Persisted Settings…”. My general experience with menu items followed by an ellipses is that clicking on them results in another list of options, so I clicked it, but it executed some action immediately (I’m not even sure what it did). I think convention dictates that the ellipses should be removed from this right-click menu option since it’s not a link to additional options.

    Thanks!
    Dan

    1. thatjeffsmith Post
      Author
  163. I’m trying to print certain Unicode characters like ? (http://www.fileformat.info/info/unicode/char/1f341/index.htm) in SQL Developer. I have no problem with simpler characters like †; “select UNISTR(‘\2020’) from dual” does the job. However, when I try “select UNISTR(‘\1F341’) from dual” hoping to get ?, instead I get something like “☐1”. Could you please help me to get the output I’m looking for?

    By the way, when I execute “SELECT * FROM v$nls_parameters WHERE parameter LIKE ‘%CHARACTERSET'”, my result is
    NLS_CHARACTERSET AL32UTF8
    NLS_NCHAR_CHARACTERSET AL16UTF16

    1. thatjeffsmith Post
      Author
      1. Thank you! That gave me the hint I needed:

        * I changed the Code Editor font to “Segoe UI Symbol” (which I figured out from Microsoft Word can handle that character, and others that I had problems with).
        * I ran “select ascii(‘?’) from dual;” to get the digital code (4036988289)
        * I ran “select chr(4036988289) from dual;” to display the character I wanted.

        Thanks!

  164. Hi Jeff,

    The “View Value” window seems to remember the window size but not the window position. This is a problem if the window size is big as one needs to move the window every time it is opened.
    Any chance some Find functionality could be added to the window.

    Regards,
    Ron

    1. thatjeffsmith Post
      Author
      1. Hi Jeff,

        Sorry, I should have included more details in my description. I’m referring to the windows that pops up when you edit/view the contents of a cell any datagrid.

        I often need to view the contents a cell that contains a clob with 1000+ lines and that is when I need to increase the size of the “View Value” window and where the Find functionality would help a lot.

        Regards,
        Ron

  165. Hi Jeff
    I hope you can help me!
    In our company we use SQL Developer 4.1.2
    When we use “desc” in the Script Output we see only
    the Procedures without the Functions. In a older Version of SQL Developer Procedures and Functions were availible. Is this a Version-Problem or can we handle it in the Settings? And if we can do it in the Settings wich Parameter we need to change?
    Thank you for your Help!!
    Greatings Salvi

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author

      Yeah, it’s a bug.

      Workaround: use the new INFO command.

      INFO

      clear scr

      info sys.dbms_sql_monitor

      Package
      /* Package SYS.DBMS_SQL_MONITOR */
      /* FUNCTION SYS.DBMS_SQL_MONITOR.BEGIN_OPERATION */
      /* RETURN NUMBER */
      v_ret := SYS.DBMS_SQL_MONITOR.BEGIN_OPERATION( DBOP_NAME => p_IN_param0 /* VARCHAR2 */,
      DBOP_EID => p_IN_param1 /* NUMBER */,
      FORCED_TRACKING => p_IN_param2 /* VARCHAR2 */,
      ATTRIBUTE_LIST => p_IN_param3 /* VARCHAR2 */);

      /* PROCEDURE SYS.DBMS_SQL_MONITOR.END_OPERATION */
      SYS.DBMS_SQL_MONITOR.END_OPERATION( DBOP_NAME => p_IN_param0 /* VARCHAR2 */,
      DBOP_EID => p_IN_param1 /* NUMBER */);

      /* FUNCTION SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR */
      /* RETURN CLOB */
      v_ret := SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR( SQL_ID => p_IN_param0 /* VARCHAR2 */,
      DBOP_NAME => p_IN_param1 /* VARCHAR2 */,
      DBOP_EXEC_ID => p_IN_param2 /* NUMBER */,
      SESSION_ID => p_IN_param3 /* NUMBER */,
      SESSION_SERIAL => p_IN_param4 /* NUMBER */,
      SQL_EXEC_START => p_IN_param5 /* DATE */,
      SQL_EXEC_ID => p_IN_param6 /* NUMBER */,
      INST_ID => p_IN_param7 /* NUMBER */,
      START_TIME_FILTER => p_IN_param8 /* DATE */,
      END_TIME_FILTER => p_IN_param9 /* DATE */,
      INSTANCE_ID_FILTER => p_IN_param10 /* NUMBER */,
      PARALLEL_FILTER => p_IN_param11 /* VARCHAR2 */,
      PLAN_LINE_FILTER => p_IN_param12 /* NUMBER */,
      EVENT_DETAIL => p_IN_param13 /* VARCHAR2 */,
      BUCKET_MAX_COUNT => p_IN_param14 /* NUMBER */,
      BUCKET_INTERVAL => p_IN_param15 /* NUMBER */,
      BASE_PATH => p_IN_param16 /* VARCHAR2 */,
      LAST_REFRESH_TIME => p_IN_param17 /* DATE */,
      REPORT_LEVEL => p_IN_param18 /* VARCHAR2 */,
      TYPE => p_IN_param19 /* VARCHAR2 */,
      SQL_PLAN_HASH_VALUE => p_IN_param20 /* NUMBER */,
      CON_NAME => p_IN_param21 /* VARCHAR2 */);

      /* FUNCTION SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_XML */
      /* RETURN OPAQUE/XMLTYPE */
      v_ret := SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_XML( SQL_ID => p_IN_param0 /* VARCHAR2 */,
      DBOP_NAME => p_IN_param1 /* VARCHAR2 */,
      DBOP_EXEC_ID => p_IN_param2 /* NUMBER */,
      SESSION_ID => p_IN_param3 /* NUMBER */,
      SESSION_SERIAL => p_IN_param4 /* NUMBER */,
      SQL_EXEC_START => p_IN_param5 /* DATE */,
      SQL_EXEC_ID => p_IN_param6 /* NUMBER */,
      INST_ID => p_IN_param7 /* NUMBER */,
      START_TIME_FILTER => p_IN_param8 /* DATE */,
      END_TIME_FILTER => p_IN_param9 /* DATE */,
      INSTANCE_ID_FILTER => p_IN_param10 /* NUMBER */,
      PARALLEL_FILTER => p_IN_param11 /* VARCHAR2 */,
      PLAN_LINE_FILTER => p_IN_param12 /* NUMBER */,
      EVENT_DETAIL => p_IN_param13 /* VARCHAR2 */,
      BUCKET_MAX_COUNT => p_IN_param14 /* NUMBER */,
      BUCKET_INTERVAL => p_IN_param15 /* NUMBER */,
      BASE_PATH => p_IN_param16 /* VARCHAR2 */,
      LAST_REFRESH_TIME => p_IN_param17 /* DATE */,
      REPORT_LEVEL => p_IN_param18 /* VARCHAR2 */,
      AUTO_REFRESH => p_IN_param19 /* NUMBER */,
      SQL_PLAN_HASH_VALUE => p_IN_param20 /* NUMBER */,
      CON_NAME => p_IN_param21 /* VARCHAR2 */);

      /* FUNCTION SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST */
      /* RETURN CLOB */
      v_ret := SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST( SQL_ID => p_IN_param0 /* VARCHAR2 */,
      DBOP_NAME => p_IN_param1 /* VARCHAR2 */,
      MONITOR_TYPE => p_IN_param2 /* NUMBER */,
      SESSION_ID => p_IN_param3 /* NUMBER */,
      SESSION_SERIAL => p_IN_param4 /* NUMBER */,
      INST_ID => p_IN_param5 /* NUMBER */,
      ACTIVE_SINCE_DATE => p_IN_param6 /* DATE */,
      ACTIVE_SINCE_SEC => p_IN_param7 /* NUMBER */,
      LAST_REFRESH_TIME => p_IN_param8 /* DATE */,
      REPORT_LEVEL => p_IN_param9 /* VARCHAR2 */,
      AUTO_REFRESH => p_IN_param10 /* NUMBER */,
      BASE_PATH => p_IN_param11 /* VARCHAR2 */,
      TYPE => p_IN_param12 /* VARCHAR2 */,
      CON_NAME => p_IN_param13 /* VARCHAR2 */);

      /* FUNCTION SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST_XML */
      /* RETURN OPAQUE/XMLTYPE */
      v_ret := SYS.DBMS_SQL_MONITOR.REPORT_SQL_MONITOR_LIST_XML( SQL_ID => p_IN_param0 /* VARCHAR2 */,
      DBOP_NAME => p_IN_param1 /* VARCHAR2 */,
      MONITOR_TYPE => p_IN_param2 /* NUMBER */,
      SESSION_ID => p_IN_param3 /* NUMBER */,
      SESSION_SERIAL => p_IN_param4 /* NUMBER */,
      INST_ID => p_IN_param5 /* NUMBER */,
      ACTIVE_SINCE_DATE => p_IN_param6 /* DATE */,
      ACTIVE_SINCE_SEC => p_IN_param7 /* NUMBER */,
      LAST_REFRESH_TIME => p_IN_param8 /* DATE */,
      REPORT_LEVEL => p_IN_param9 /* VARCHAR2 */,
      AUTO_REFRESH => p_IN_param10 /* NUMBER */,
      BASE_PATH => p_IN_param11 /* VARCHAR2 */,
      CON_NAME => p_IN_param12 /* VARCHAR2 */);

  166. Hi

    I am new to oracle VM, installed succesfully and also opened the terminal
    [email protected]: and clicked sql developer and connected but i got ORA12505 error. how to resolve this and connect to sql developer
    please suggest

  167. Hi

    I am unable to login into oracle sql developer. ORA 12505.

    I an new to oracle VM , installed successfully any information can you provide so that i would login in to Sql developer.

    how should i connect succesfully

    ~Mithlesh

    1. thatjeffsmith Post
      Author
      1. Hi

        I am unable to connect:
        there is one default connection system@//127.0.0.1

        and when i click it automatically filled details on connctionname,username,password,servicename as orcl and port 1521but on pushing test button it shows ORA 12514 error. TNS listner error
        what to do sir please help

        1. thatjeffsmith Post
          Author
          1. thatjeffsmith Post
            Author
          2. thatjeffsmith Post
            Author
  168. Hi

    I am unable to login into oracle sql developer. ORA 12505.

    I an new to oracle VM , installed successfully any information can you provide so that i would login in to Sql developer.

    how should i connect succesfully

    [email protected]

    ~Mithlesh

  169. We have VM (VDI) desktops, and have Oracle 11g client with Java 6 installed on our desktops. Recently our SQL Developer 4.0.3 stared crashing consistently running SQL Scripts, or just hovering over connections, or icons, or sometimes just doing nothing. Our team reinstalled 4.1.3 version as well, but still the same issue.

    Any thoughts?

    Thank you.

    1. thatjeffsmith Post
      Author
      1. It doesnt leave any error messages on screen. My admin says SQLDeveloper comes with its own JDK (supported version) within the package, so no need of JDK8 installed on System explicitly.

        My desktop got 8 GB.

        This only happened with bunch of users at the same time a week ago. We are all developers working on some framework that supports only Java 6. FYI.

        1. thatjeffsmith Post
          Author
  170. Hi Jeff

    I am not sure can you help me but i want to ask you did you experience similar problem. I am using Centos7, SQLDeveloper Version 4.1.3.20, Java7.

    When i have stable internet connection everything works fine, but when i work from home where “something” interrupting my internet, sqldeveloper connection drops and then i am in problem, only way to restart SQLDeveloper is xkill command 🙁 . Trying to reconnect or refresh tree will not help me. If i leave it for like 10 or 15 min it will disconnect but is to long for wait.

    Sorry if my Eng is not best one. Thank you for your time, all best.

  171. How to run a script with SQLCL and exit on completion?

    SQLCL allows to execute an sql script from the command prompt, avoiding the interactive procedure of login, execute script and exit.
    After the script execution has completed, SQLCL doesn’t exit automatically.

    In SQLPlus there are two way’s to do this:
    1) put an exit (or quit) statement at the end of the sql script
    or call SQLPlus this way
    2) exit | sqlplus -L -S [email protected] @clean_up.sql
    – or for windows: echo exit | sqlplus -L -S [email protected] @clean_up.sql

    The second option doesn’t work for SQLCL. Is there a way to achieve the same in SQLCL, thus without exit statement within the script?

    1. thatjeffsmith Post
      Author
  172. Hi Jeff,

    I have two questions on the same topic. I would like to compare two packages in the same schema, side by side.
    Also, I would like to compare two packages in different shema’s with different names.

    Schema: Package:
    [email protected] MyPkg
    [email protected] ProdPkg

    [email protected] MyPkg
    [email protected] ProdPkg

    Is this supported?

    Thanks,

    Tim.

    1. thatjeffsmith Post
      Author
  173. Hi Jeff

    When you export DDL scripts into folders in SQL Developer 4.1.3, packages and package bodies are exported with a “.sql” suffix. When you open the file to edit in SQL Developer, it displays it in the SQL editor, rather than the PL/SQL editor.

    Is it possible to configure exports to specify suffixes based on object type?
    e.g. package => “.pks”, package body => “.pkb”

    Also, directories and object names are always exported in upper case, e.g.
    @/Users/mark/TABLES/FLEXFIELDS.sql
    @/Users/mark/PACKAGES/ADMIN_PORTAL.sql
    @/Users/mark/PACKAGES/ALARM_CENTRAL.sql
    @/Users/mark/PACKAGE_BODIES/ADMIN_PORTAL.sql
    @/Users/mark/PACKAGE_BODIES/ALARM_CENTRAL.sql

    Is it possible to include a configuration option for lower-case file names?

    And finally, is it possible to use relative path names in the control script, so for the example above the output would be:
    @./tables/flexfields.sql
    @./packages/admin_portal.sql
    @./packages/alarm_central.sql
    @./package_bodies/admin_portal.sql
    @./package_bodies/alarm_central.sql

    Regards

    Mark

  174. Hello,

    We have oracle 11G installed and in our recent projects we had issues with some of the DB objects getting invalid especially views. The queries inside the views are referencing tables/views between the schemas on the same oracle instace.

    Using SQL developer when we right click on the object and compile, it said it was success but querying the SQL inside the views showed invalid identifier issue. These views originated in 8i->9i and now in 11G

    We spent a lot of time trying to figure out what is going on without much success. Finally, we were able to compile successfully by formatting the entire SQL for the view and replacing it.

    But doing this way resulted in extra space getting introduced on the column names and it took a lot of time to nail that down.

    I have a two part question,
    1. Why and when does the DB views goes invalid?
    2. Are there any issues using SQL formatter in SQL developer (4.0.2.15)?

    Any insights on this would be really helpful.

    Thanks,
    Jagan

    1. thatjeffsmith Post
      Author
  175. Hi Jeff, I’m facing an issue with SQL Developer (versions 4.1.2 and 4.1.3), using JDK 1.8.0_65. I unzipped the program, searched for the JDK directory and then opened it. I tried to create a new DB connection, but when hitting the OK button there (clicking in the new icon -> Database tier -> Database connections) nothing happens and the DB connection creation screen doesn’t appear. I also tried copying the connections.xml file from another machine into the ..\AppData\Roaming\… directory, but the connections are not being displayed either. Any ideas on how to solve this or what else can I do to solve it? Thanks in advance for your reply.

    1. thatjeffsmith Post
      Author
      1. Thanks for your reply Jeff. I did as you told me but had no luck. I even downloaded a previous version (4.1.1), installed it and tried with it but got the same error. My solution was to download SQL Dev with JDK included, then, after unzipping and running it, I was able to see the connections icons in the DB connection panel and could import my previous connections. I suspect then that this is, somehow, related to the JDK version I have or the env configuration. Thanks again. Greetings.

        1. thatjeffsmith Post
          Author
  176. Lazy coder question here.. Is it possible to configure SQLDeveloper to auto-close opening quotes, parathensis, brackets etc? A google search has not turned up anything of substance yet.

    This would be done similar to what something like Notepad++ does when you are writing HTML tags etc.

    1. thatjeffsmith Post
      Author
  177. I tried to post a new report to the SQL Developer Exchange site today. I got an error “ORA-01400: cannot insert NULL into (“DBTOOLS”.”XML_REPORT”.”CREATED_BY”)
    Unable to process row of table XML_REPORT.” Is there an official means of reporting this?

  178. Hello Jeff,

    i’m using sql developer 4.1.3.20.78, the output of the select query is clumsy as sql command prompt

    i would like to see in rows and columns order what should i do..?

    1. thatjeffsmith Post
      Author
  179. The Download page says => Version 4.1.3.901, Updated December 21, 2015
    The About Page says =>
    Oracle SQL Developer Data Modeler 4.1.2.895
    Version 4.1.2.895

  180. Another sqlcl bug: “describe user_objects;” gives

    Object Not Found: ribe user_objects
    Usage: DESCRIBE [schema.]object[@db_link]

    “desc user_objects;” works

  181. When I try to build a User Defined Report with SQL Developer version 4.1.3.20.78 the application freezes. The only way out is to kill it in the Windows 8 Task Manager. Things worked fine with with version 3.2.20.10.

    I have tried a few cleanup and reinstall operations to resolve the issue, nothing works. I have tested 4.1.3.20.78 with and without the UserReports.xml file left over from 3.2.20.10. In either case once I try to add a new report or modify an existing one, I freeze up when I click the Apply button in the Create Report pane.

    1. thatjeffsmith Post
      Author
      1. If there is currently no UserReports.xml and I try to create a reports, when I go to save it, I get the freeze and the UserReports.xml is created with a 0 (zero) length file.

        If I have a UserReports.xml that was created with a v3 SQL Developer and has actual reports defined, then when I go to add or change a report with v4 SQL Developer I get a freeze when I go to save it and the UserReports.xml file again has a 0 (zero) length.

        I’ve looked for a method to debug this before my first posting, and the method that I found where you started it from the command line and from the bin directory showed no extra debugging related to what happened and/or lead up to the freeze.

        I’m totally hosed here, and have to use v3 to create reports, then can run them in the v4 as long as I don’t make changes.

  182. Hi Jeff:
    Thank you for the work you do to maintain this blog.
    I have a couple questions for you… I really like version 4 of SQL Developer but have experienced 2 frustrating things.
    1: I have Completion Insight set to change code to “Upper Keywords / Lower Identifiers”. However, it periodically will reset itself to “Lower Case”. I don’t know if this is bug or if there is something I can to to force it to retain my setting.
    2: I would really, really, really like to be able to tell SQL Developer what I consider a keyword to be. There are certain statements that it doesn’t recognize as being a keyword and will convert to lower case. For example , I want “TO_CHAR” to be upper case. Is there a way to edit the dictionary?

    Thanks in advance!
    Brian Rollins
    Anal Retentive
    Type A, OCD Certified
    Oracle Database Developer

    1. thatjeffsmith Post
      Author

      Exactly what version of 4 are you running? The first thing would be a bug.

      to_char is a public synonym to the SYS function to_char()…so that gets resolved as an IDENTIFIER. If you want it upper cased, you can do that.

      Now, to answer your question, can you edit the list? No. But in version 4.Next, the list is being updated to pretty much pull every single word we can find out of the Oracle Database Docs, so it should be pretty complete.

      1. Hi Jeff,
        Thank you for the quick reply.
        To answer your question, I am using version 4.1.1.19
        It also lists a Build MAIN-19.59
        I hope this helps.

        I haven’t been able to determine what makes it reset, but it does so within the same session usually about once per day.

        I’ll look forward to the expanded “dictionary” in your future release.
        Thanks again and keep up the great work!

        Brian Rollins

        1. thatjeffsmith Post
          Author
  183. Hi Jeff,

    my previous comment was really too vague to be considered, but now I can be more precise. The issue is related with tables created from views using UNION. You can replicate the issue in this way (e.g. on the scott schema):

    create view v_emp_10_20 as
    select *
    from emp
    where deptno = 10
    union
    select *
    from emp
    where deptno = 20;

    create table emp_10_20 as
    select *
    from v_emp_10_20;

    Now if you try to update the table emp_10_20 in the data view of SQL Developer you will not be able to do it.

    According to the Oracle Support this is related to a fairly old (but still unsolved) bug in the DBMS (Bug 7338622). In practice the data in the ALL_UPDATABLE_COLUMNS view are incorrect, when the table is created in that manner.

    Nevertheless, maybe Developer could avoid checking the “updatability” of columns, which seems to be a useless info in the case of tables.

    Thanks for all useful infos provided in this blog!

  184. Jeff,

    Is there something up with your style sheet?

    In the Search box on your page, text entered is white on a white background.

    Regards,

    JT

    1. thatjeffsmith Post
      Author
  185. Hi Jeff,

    First off – I hear great things about this site – very experience IT profs that I work with says this is a very helpful site.

    I have a question about SQL developer – I have a need to connect to DB2 for data extract or just to browse DB2 tables. Is this possible from to do this from SQL Developer? If so, how can this be setup? If not what are my options or alternatives to access DB2 from SQL Developer?

    Any and all assistance will be highly appreciated.

    Thanks

    Okonita

    1. thatjeffsmith Post
      Author
  186. I have version 4.1.1.19 and I and wanting to display the numbers in my results when I export the data to text file. How do I do this? I know in plsql/dev I would right click for and excel file and the row numbers would be automatically included in the results.

    1. thatjeffsmith Post
      Author
  187. Looked; could not find answer.

    I am looking for a replacement to scripts I have the use listagg against the DBA views to build pipe separated value (PSV) exports with headers.

    Dos the CLI suport formatting ‘hints’ /*CSV*/ ?

    What about PSV?

    Headers?

    Thanks! I enjoy your site.

    Rich

    1. thatjeffsmith Post
      Author
    2. thatjeffsmith Post
      Author