Search Results

105 Comments

  1. Carlos Rocha Reply

    1->I would like to know if it is possible to connect Oracle SQL Developer to SQLite DB using the apropriate JDBC ( https://github.com/xerial/sqlite-jdbc )

    2->I also would like to know how Oracle SQL Developer adds the connection tabs to Connection Window (New / Select Database Connection) , when i add the mysql JDBC to Oracle SQL Developer, reset it and open the Connection Window it will appear a new tab with the name of MySQL.
    How Oracle SQL Developer creates that tab ?
    Is the mySQL JDBC that tells to the Oracle SQL Developer the content of that tab or the content of that tab is in the Oracle SQL Developer code?

    • Carlos Rocha

      It’s sad … that Oracle SQL Developer is not compatible with SQLite because i like the environment and I am used to it and didn’t want to change.
      I would like that in the future Oracle SQL Developer would be compatible with SQLite because that way I wouldn’t need to change my frontend to connect to database.
      Thank you very much for your response.

    • thatjeffsmith

      Even if we did, many of the features you love wouldn’t be available… They’re coded specifically for an Oracle Database.

    • Carlos Rocha

      You said “They’re coded specifically for an Oracle Database.” but if I am not wrong Oracle SQL Developer works as a frontend to Microsoft SQL Server, MySQL and Sybase and they aren’t Oracle Database 😛
      That said I ask to myself: SQLite is so diferent from the other Databases supported that isn’t compatible to the logic of Oracle SQL Developer?

    • thatjeffsmith

      b/c no one (and by ‘no one’, not enough to spend the time building out support for it) is migrating sqllite databases to oracle database – see this post

    • Carlos Rocha

      Thank you very much for your time, attention and to share these useful information that allowed me to understand better the world of Oracle SQL Developer.
      Best regards

  2. Sam Powell Reply

    Hello, I just downloaded
    Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.308.0750. When I start sqlcl it states that “New version: 4.2.0.1 available to download”, however, I cannot find this version to download.
    Thanks for you blog, it is most interesting.
    Sam

    • thatjeffsmith

      Oracle SQLDeveloper Command-Line (SQLcl) version: 4.2.0.16.308.0750 IS the latest version. We’re trying to get the new version messaging in the app working, sorry for the confusion.

  3. Betty MacEwen Reply

    I found a bug with the formatter in 4.2
    Version 4.2.0.16.260
    Build 16.260.1303

    Code before SQL Developer formatter is run

    DECLARE
    v_string VARCHAR2(4000);
    v_value varchar2(4000) := ’10’;
    BEGIN
    v_string := ‘The value ‘
    || ””
    || v_value
    || ””
    || ‘ is in the variable v_value.’;
    dbms_output.put_line(v_string);
    END;

    Run it and the output is:
    The value ’10’ is in the variable v_value.

    Code after SQL Developer formatter is run, notice that the formatter has removed a single quote from two lines of code
    DECLARE
    v_string VARCHAR2(4000);
    v_value VARCHAR2(4000) := ’10’;
    BEGIN
    v_string := ‘The value ‘
    || ”’
    || v_value
    || ”’
    || ‘ is in the variable v_value.’;
    dbms_output.put_line(v_string);
    END;

    Run it and the output is:
    The value ‘
    || v_value
    || ‘ is in the variable v_value.

    This changes the meaning of the concatenation and introduces an insidious bug that might be hard to track down.

    • thatjeffsmith

      latest dev build, appears to be good

      post format

      SET SERVEROUTPUT ON
          
      DECLARE
          v_string   VARCHAR2(4000);
          v_value    VARCHAR2(4000) := '10';
      BEGIN
          v_string := 'The value '
           ||  ''''
           ||  v_value
           ||  ''''
           ||  ' is in the variable v_value.';
          dbms_output.put_line(v_string);
      END;
      
      executes...
      
      The value '10' is in the variable v_value.
      
      
      PL/SQL procedure successfully completed.
      
  4. Hi Jeff,
    We have a user running Oracle SQL Developer 1.5.5 with a 64 bit version of Excel 2013. He says nothing happens when exporting his query results to Excel.

    Is it possible that his ancient version is not compatible with the 64 bit version of Excel? Do you think a simple upgrade will fix?

    Thanks in advance.

  5. Amin Adatia Reply

    Hi Jeff

    How do I completely remove anything to do with SQL Developer so that I can re-install? How does the n-jre version find the jdk directory? Should I not be getting a dialog box asking where the jdk folder is? And how does it find all the connections?

    Regards

    • Amin Adatia

      I install jdk_8u-102-windows-x64.exe
      installs in Program Files\Java\\jdk1.8.0_102
      I point to that for the no-jre version 4.1.3.20-78
      Error => Unable to create JVM at
      C:\Program Files\Java\jdk1.8.0_102\jre\bin\server\jvm.dll

    • thatjeffsmith

      you need to nuke the AppData folders for SQL Developer and sqldeveloper under the roaming profiles directories

  6. Antonin Pokorny Reply

    Hi Jeff,
    would you like help me with look and feel of the Oracle Developer ?
    My problem is very very simple.
    On the right up corner is a comboBox with known connections.
    Unfortunately, in case of high number of connections it is become unusable
    because of limitation of displayed records (currently it is 8). Yes, it has a scroll bar, but in a huge list of connections it is not providing an easy survey.
    Do you happen to know, if there exists any way, how to resize this combo ?
    I would like to see more times records in it then it is right now.
    Thank you for answer and tip
    Regards
    Antonin Pokorny

  7. hello,

    have followed your guidance and can spool out results to files etc but what im now trying to do is to spool some results which come as a result of several subqueries..

    example.
    with sub1..,
    with sub2..,
    with sub3..

    select blah from sub1, blah from sub2, blah from sub3..etc

    how do i spool the ‘select’ statement into a csv please?
    do i put the spool command before the ‘select’ or before the 1st ‘with’?
    I’ve tried both but i get a multiline java/ oracle error.
    thanks for any help..

    Andy

  8. Brad Simmons Reply

    Random extensibility thought (and extreme laziness) …

    Could the SD Help Center be extended to include other documentation like SQL Reference, etc? I love Mr. Google, but highlighting regexp_like and hitting to see the syntax or usage of the function would be cool and make SD even more accessible to learners.

    As an extension, is it possible to roll-my-own content that would be available through SD Help Center?

    • thatjeffsmith

      That’s supposed to be already there a la

      But I can’t get it to bring up the docs link for REGEXP_() .. gonna file a bug.

      You can also ask the sqldev help search to go across OTN and the Database Docs too

    • Brad Simmons

      Thanks Jeff! Enabling this wasn’t clear to me, so I’m sharing…took me a bit of digging to find the option. For the record, it’s here: Open Help Center > click on search magnifying glass (little menu icon next to it!) There are options for searching.

      sheepishly greatful!

  9. Hi Jeff, at Timekiller’s suggestion,

    I raised this question on stackoverflow about a week back.
    It’s to do with substitution variable behaviour in SQL developer (V4.1.1.19.59 on windows 7 enterprise service pack 1).
    Basics are: I want to have 1 parameter which contains a year i.e. YYYY.
    Then I want to dynamically use this parameter to make a 2nd parameter YYYY2 which is YYYY – 1.
    Now I can do this and make the equation explicit using the select from dual method or the new_value style method but when I actually call it to name a table in a select * from i get the sql developer popup asking me to enter a value even though I’ve already set the value.

    One kind responder on stackoverflow has suggested it’s a bug as in his version the popup does not trigger and all is well.

    So before i raise it as a bug I was wondering if you’d observed this behaviour before and know about it and how to overcome it?

    http://stackoverflow.com/questions/37427468/sql-developer-substitution-variable-dynamic-creation-based-on-equation/37440575?noredirect=1#comment62569295_37440575

    Regards,
    Clancy.

    • thatjeffsmith

      Haven’t observed that personally..and I would need a code sample to have an opinion. But, if it works in sqlplus and not in SQLDev then it’s a bug. Probably.

    • clancy birrell

      Here’s the code sample…

      define YYYY = 2014;
      column dual_x new_value YYYY2;
      select &&YYYY – 1 dual_x from dual;

      select * from cb_nsl_&&YYYY2 ;

      This last line triggers the popup.

  10. Jeff, I find sqlcl to be slower overall than sqlplus. For example logging in via sqlplus is pretty quick but sqlcl takes few seconds. Is this a known limitation or can something be done to make it faster?

    • thatjeffsmith

      is it connect time to start-up time that you’re seeing? for example, does running sql /nolog take the same amount of time as a connect hr/oracle after sqlcl has already started? part of the issue is that SQLcl is a java app, it’s not a compiled native C application, so it’ll probably never start as fast as SQL*Plus. and the first time it’s started might take more than a few seconds, but if you close it and start it again, will and should start much faster

    • sql /nolog takes a sec or two and connect takes more than that. Yes, closing and reconnecting is better than first time. Why is it slightly better when relogging in?

      Is the overall slowness due to drivers (i assume jdbc drivers are being used) or java being slower than C? Today I was doing desc dbms_stats and it took ages for sqlcl to show the output whereas in sqlplus its super quick. This slowness is making me go back to sqlplus though i like features of sqlcl. Any settings that can be made to make it faster?

  11. Hi, Jeff,

    I tried to connect timesten from sql developer (4.1) but get a problem “..no ttjdbcCS” in java.library.path.

    I have that library in my local, and know I need to add its location to java.library.path. But I could not find a way to do it on SQL developer. I tried to set via $PATH, $CLASSPATH, $DYLD_LIBRARY_PATH, and even add it directly on sqldeveloper.conf. But nothing work.

    Can you tell me how I can configure the value of java.library.path on SQL developer?

    Thank you very much for the nice help.

    Jian

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

  13. i want to add radio button to table on my website back end if it checked yes the row get the color light green if it is not checked or checked no the row color is the table default color the checked data stored in database

  14. 4 years ago I imported our data models from Designer 2000 and opened it up in Data Modeler. I saved the out, zipped it and saved it in Perforce.

    Now I am trying to open it but really don’t know how to do.
    Shall I use import or open?
    Which file, .dmdz?

  15. Amin Adatia Reply

    SQL Dev 4.1.1.19.59
    I am trying to migrate from MS Access into Oracle 12c. Connections only shows SQL Server and Sybase. Where do I get the required drivers for MS Access? In the Third Party stuff I added

    file:/C:/Users/Amin/AppData/Roaming/SQL Developer/system4.1.1.19.59/o.sqldeveloper.migration.msaccess.12.2.0.19.59/

    • thatjeffsmith

      Access is no longer supported. You need an older version of sqldev and java 6 or 7.

      Java 8 no longer has a odbc bridge which makes access connections impossible.

    • Amin Adatia

      That is just too bad as I have a lot of MS Access “databases” to migrate. I would prefer they use Oracle but likely will go with MS SQL Server.

      I was able to open the MS Access file and then export e3ach table via an ODBC Connection.

    • Kevin Trick

      Hey Jeff, is this something that will be addressed in future releases or is it now a policy?

  16. Dean Geary Reply

    Jeff, I just installed a vbox appliance for DB development and SQL Developer does not even have a “Containers” tree in the DBA navigator. Is this a bug? It correctly displays it as a CDB. Lost in Atlanta…

    • thatjeffsmith

      you built your own vm? if so, did you create a 12101 or 12102 db? and you made sure to create it with the multitenant architecture? and if yes to that, are you connected to the Container and not a Pluggable?

    • Dean Geary

      No, it is the pre built from oracle. I was doing the sqldeveloper lab that came with the pre built. I just wanted to get up to speed with the features for 12C

    • Dean Geary

      P.S., the NLS_RDBMS_VERSION parameter shows 12.1.0.2.0

  17. virendra singh Reply

    I’m using Sql Developer Version 4.1.1.19 and java version was jdk-8u60-linux-x64.rpm and using in linux, am able to see the list of objects where in am unable to view the procedure scripts. It shows only “create or replace” text alone in the window. please reply ASAP..

    • thatjeffsmith

      I’ve seen this before, but it always went away on an app restart

      is this for your stored procs or a foreign schema?

      for the best support, open a SR with MOS

    • virendra singh

      actually I have create pluggable database from dfb file from another windows system to linux system and not create a new user for that . and also on that system sql developer was response properly with stored procedure on old database but on my linux system after createting pdb from dfb file. when i goes for editing stored procedure It shows only “create or replace” text alone in the window. please reply ASAP..

    • thatjeffsmith

      stop with the ASAP stuff, this is a blog, not a hotline

      this is the query we run to populate that window

      WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
      WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
      SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
      WHERE origin_con_id = max_orig
      ORDER BY LINE

      you can see what’s going on under the covers when you open View > Log > Statements

      and like i said, are you logged in as the code owner or as someone else?

    • virendra singh

      am sending statement log where owner is relyc that is correct
      and m connected with relyc . I think that was not a problem of permission .. please reply..
      120 MultiPlay 88 SELECT data_type, argument_name name
      FROM all_arguments a, all_objects o
      WHERE a.object_id=o.object_id
      AND o.object_name=? and o.owner=? and a.package_name is NULL
      order by position 1=”PROC_ERRORLOGS”, 2=”RELYC”
      119 MultiPlay 1 SELECT /*OracleDictionaryQueries.ALL_ORACLE_SINGLE_OBJECT_QUERY*/
      O.OBJECT_NAME, O.OBJECT_TYPE, O.OBJECT_ID, O.LAST_DDL_TIME
      FROM SYS.DBA_OBJECTS O
      WHERE O.OWNER = ?
      AND O.OBJECT_NAME = ?
      AND O.OBJECT_TYPE = ?
      1=”RELYC”, 2=”PROC_ERRORLOGS”, 3=”PROCEDURE”
      118 MultiPlay 26
      WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
      WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
      SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
      WHERE origin_con_id = max_orig
      ORDER BY LINE
      “OWNER”=”RELYC”, “TYPE”=”PROCEDURE”, “NAME”=”PROC_ERRORLOGS”
      117 MultiPlay 2 SELECT LINE,POSITION,TEXT,ATTRIBUTE FROM USER_ERRORS WHERE TYPE=? AND NAME=? 1=”PROCEDURE”, 2=null
      116 MultiPlay 31
      WITH src AS (SELECT ROWNUM,LINE,TEXT,origin_con_id FROM SYS.Dba_SOURCE
      WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME)
      SELECT text FROM src, (SELECT max(origin_con_id) max_orig FROM src)
      WHERE origin_con_id = max_orig
      ORDER BY LINE
      “OWNER”=”RELYC”, “TYPE”=”PROCEDURE”, “NAME”=”PROC_ERRORLOGS”

  18. Hi,

    I’m using Sql Developer Version 3.2.09, am able to see the list of objects where in am unable to view the objects scripts. It shows only “create or replace” text alone in the window.

    Could you pleae help in fixing this issue?

    • thatjeffsmith

      First thing to try, upgrade.

      Since your version, we have released:

      1. 4.0
      2. 4.0.1
      3. 4.0.2
      4. 4.0.3
      5. 4.1
      6. 4.1.1
  19. I am excited about this sql developer 4.1.1 db instance viewer, but when I tried to open it, it remains blank. I connected with sysdba privilege.

    can you look into this? and how can I log a bug ?

    • thatjeffsmith

      what version of Oracle?

      what version of Java?

      do you see queries running in the View > Log > Statements panel?

    • java should be 8, because I downloaded 4.1.1 with jdk 8. oracle version is 11204 enterprise with diagnostic and tuning package. yes, I see queries running in the view > log statement panel. queries like select fixed_sg_size……

    • thatjeffsmith

      and if you run them?

      Also, check this:

      Help > About

      Properties tab

      Type in ‘fx’ in the search panel, do javafx.runtime.version and javafx.version show up?

      Also, are we going to have this conversation here or on the forums – pick one 🙂

  20. When I add comments as First Line(Top most) in my Stored Procs, they are being removed on reopening, cant we have comments as the First line?

    • thatjeffsmith

      they have to go after the CREATE or REPLACE…if you go look in ALL_SOURCE, you’ll see we’re not removing anything

  21. Phil Huval Reply

    How does one get the Current Statement on a session when if run “so fast” that you don’t have time to do copy/paste to notepad ??

  22. Dwight Maxwell Reply

    I am a contractor working on a large government site. Most of the other developers use a different tool. But in an effort to save money, the government has decided to limit its purchases of this well known but expensive tool. So I am using Sql Developer. I have been unable to get the debugger to work because of a permissions error that the DBA hasn’t solved.
    So I have been using lots of DBMS_OUTPUT.PUT_LINE statements to try to trace the execution of the program. The problem is that after the execution of a SQL statement (select, insert etc.), the program loses its way and jumps out of the current procedure and control returns to the calling procedure.

    • thatjeffsmith

      >> So I am using Sql Developer. I have been unable to get the debugger to work because of a permissions error that the DBA hasn’t solved.
      Permissions or Networking? I can help with that perhaps…should be the exact same privs required to use the debugger in the ‘well known but expensive tool’

      >>The problem is that after the execution of a SQL statement (select, insert etc.), the program loses its way and jumps out of the current procedure and control returns to the calling procedure.

      I’m confused – are you in the debugger or not?

  23. Hi
    Some Data Modeler questions:
    1: When Syncronizing Data Model With Dictionary I would like to to that without Storage and Schema name.
    I have de-selected every option under
    Data Modeler –> DDL/Storage and unchecked Storage options under Data Modeler –>DDL. Still I get diff on Storage Clause for PK and FK Contraints
    2: I have a model where the default all-Objects are missing (don’t know how I have disabled it). I only see my Subviews, but would like to enable this all-Objects again if possible?

  24. how do I get SQL developer to stop dropping our zero’s when pasting into Excel?

    • thatjeffsmith

      We’re not dropping zeros – Excel is. Make sure your target cells are formatted as Text first. Paste the same data in Notepad to see what’s actually on the clipboard.

Write A Comment