Search Results

105 Comments

  1. Hi Jeff,

    I am using 19.1 (but the same issue was in previous versions as well).

    I wanted to run AWR report from DBA section, AWR>AWR Report Viewer, but I couldn’t see any thing, I mean no time to select or StartID or End ID to select from the pull down menu.

    So I checked Database, there are multiple DBIDs, even from old databases like dev, or prod even from old servers which are decommissioned years ago.

    Even I selected the current dbid of the database I was connected to and wanted to run AWR report, still no luck.

    Where SQL Developer is pulling out the DBIDs from ?
    (One thing to note, all these databaseIDs are related to same database (in different stages like 11g GIS, 11g GISDV, stag 11g GISDVX (used for upgrade), 12c GISS and 12c GISQA etc. – it is not from other databases.

    Please let me know how to remove the old entries of DBIDs or incarnation from SQL Developer.

    Thanks and regards in Advance.

    Shoaib

    • Hi Jeff,

      Any update regarding “How to remove old entries of DBIDs or incarnation from SQL Developer” question?

      Thanks.

      Shoaib

    • Hi Shoaib and Jeff

      To get the the list of snapshot, you can use select * from dba_hist_snapshot

      Jeff : it sounds like a bug, e.g. Bug 8919225, which is occurring when the database was cloned in 10g/11g . It is not “cleanup” by a 12c upgrade. Just doesn’t reproduce with a fresh 12c.

      There is a note Doc ID 1251795.1 on how to remove awr from previous incarnation
      SQL> select distinct dbid from v$database;
      DBID
      ———-
      1945815826
      SQL> select distinct dbid from dba_hist_snapshot;
      DBID
      ———-
      2524588244
      1945815826
      SQL> alter system set “_AWR_RESTRICT_MODE”=TRUE scope=spfile;
      System altered.
      SQL> shu immediate
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup quiet
      ORACLE instance started.
      Database mounted.
      Database opened.
      SQL> exec dbms_swrf_internal.unregister_database(2524588244)
      PL/SQL procedure successfully completed.
      SQL> alter system reset “_AWR_RESTRICT_MODE” scope=spfile;
      System altered.
      SQL> shu immediate
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup quiet
      ORACLE instance started.
      Database mounted.
      Database opened.
      SQL> select distinct dbid from dba_hist_snapshot;
      DBID
      ———-
      1945815826

      Cheers
      Laurent

    • Phil Winfield

      Hi Jeff,
      Very quick Q for you regarding the formatter if you have a sec please?
      If I enter the following which is what I’d like to preserve

      FOR x IN 1..10 LOOP
      NULL;
      END LOOP;

      I get a break as follows which is annoying!

      FOR x IN 1..10 LOOP NULL;
      END LOOP;

      Can you format this as the first option please?

    • thatjeffsmith

      My default formatter settings take your code and give me back –

      BEGIN
          FOR x IN 1..10 LOOP
              NULL;
          END LOOP;
      END;
      /
  2. Hi Jeff,
    If I open a script directly in SQL Dev with French characters I get the line below. The correct line is the 2nd one which I open in Notepad++ and then paste into SQL Dev. Is there a setting somewhere in SQL DEV that I am missing?
    I’m on verion 19.1

    Informations sur la société’

    Informations sur la société’

    Regards,
    Vic

    • Amin Adatia

      If you use the US-International Keyboard then the French characters will show up correctly. All you have to remember is that the accented characters need the use of two keys. ‘+e gives the accented e. To use the punctuation by itself you need to use a space

  3. Jack Applewhite Reply

    SQLcl 19.1 Error “Could not initialize class oracle.jdbc.OracleDriver”

    I have had and love sqlcl 4.2 for about 2 years. Now on the same OEL 11gR2 database servers. I’m trying to get sqlcl 19.1 running, but keep getting this error “Could not initialize class oracle.jdbc.OracleDriver”. When run from my Ubuntu 18.04 client, 19.1 works fine, I *think* because no ORACLE_HOME is set. I found a couple of old posts on this on TechNet, but those suggestions don’t work for me.

    In the 19.1 “sql” shell script, this function definition forces JAVA_HOME to be the one in the ORACLE_HOME tree, but that’s the 1.7 JDK and not the 1.8.0 version in /opt/java I’ve been using for sqlcl 4.2. That forcing a JAVA_HOME isn’t in the 4.2 sql shell script.

    function checkADE {
    #
    # Resolve java path for development builds
    #
    if [ “m$ORACLE_HOME” != “m” ]; then
    if [ -d “$ORACLE_HOME/jdk/jre” ]; then
    JAVA_HOME=$ORACLE_HOME/jdk/jre
    PATH=$JAVA_HOME/bin:$PATH
    fi
    export SQLPLUS_CLASSIC=true
    fi
    }

    So, what do I do?
    Thanks.
    Jack

    • Jack Applewhite

      Further testing results on the DB server, trying to connect to a local DB.

      I realized that I may need to install the exact same JDK that I installed on my Ubuntu machine for SQLDev and sqlcl 19.1, so I installed it on the DB server. No luck, same error.

      If I run the sqlcl/bin/ sql script, as provided, I get this:
      Exception in thread “main” java.lang.UnsupportedClassVersionError: Bad version number in .class file
      Which is to be expected, since the ORACLE_HOME JDK is 1.7.

      If I force JAVA_HOME to the correct one for sqlcl by doing this in the checkADE function in the sql script
      # JAVA_HOME=$ORACLE_HOME/jdk/jre
      JAVA_HOME=/usr/java/jdk1.8.0_211-amd64
      I still get this:
      ERROR: Could not initialize class oracle.jdbc.OracleDriver

      Now, if I force JAVA_HOME to be the correct one, and unset ORACLE_HOME in the script, I can connect to a remote DB. Also, I can connect to a local DB if I pretend it’s remote by using “[email protected]”. It works, but will require us to modify all our scripts that use sqlcl for the special reasons it’s superior to sqlplus.

      So, being a DBA and not a Java-head or super-scripter, I don’t know what to do next to allow “normal” connection to a local DB. Advice welcome.

      Also, in the “run” function in the script I tack on this to run glogin to set the command prompt automatically.
      @”$ORACLE_BASE/product/11.2.0.4/dbhome_1/sqlplus/admin/glogin.sql”
      I was using $ORACLE_HOME/sqlplus/admin, but can’t now because I have to unset ORACLE_HOME for sqlcl to even work. I’ve looked at the sqlcl docs and can’t see any sqlcl equivalent to glogin.sql. Is there?

      Thanks.
      Jack

  4. Hi Jeff,

    when I want to create a similar user with the same object rights (in DBA/security/right mouse on user) the SQL for the object rights isn’t build up correctly. The new username is put in first place instead of the last. e.g.

    “newuser”GRANT SELECT ON “SCHEMA”.”TABLE” TO ; instead of
    GRANT SELECT ON “SCHEMA”.”TABLE” TO “newuser”;

    Could you please fix this?

    Thank you
    Stefan

    BR
    Stefan

    • thatjeffsmith

      Thanks! I logged a bug, although we’re probably too close to get it in for version 19.1.

  5. Data Modeler > Reports > Tables generates nice HTML documents – but how can one include functions, packages, procedures, sequences, views ?
    Currently we are using TOAD Schema reports to generate a documentation which includes above mentioned objects , but like to migrate this last, final task to SQL Developer.
    Is this possible with SQL Developer ?
    Regards,
    Martin

  6. Hello!
    Important: I am a Customer working from Latin America, with programs installed in spanish/ latin formatting preferences. Yes, annoying.
    Considering that, I imported a bunch of cvs files with numeric values that I prepared in Excel. Seems like Oracle did not read correctly the decimal separator, as in my imported tables I don´t see any. Question: Is it that it did imported the separator or maybe is it that my SQL Developer is not showing it because its installed in Spanish (and considers the point as thousand separator)?
    I tried changing the NLS preferences but I dont see that changes what I see. Shoul I reinstall SQL developer in English/US preferences?
    Thank you,
    Julia.

    • Hi Julia
      working on a CITRIX Desktop with GERMAN Setup – but prefer SQL Developer in english.
      There is a JAVA Option, which overrides the user language. Made a small .cmd file which starts it in english:

      “C:\Program Files\SQLDeveloper\sqldeveloper.exe” –AddVMOption=-Duser.language=en

  7. Andreas Maehling Reply

    Hi Jeff,
    we have a big SQLDeveloper Report with 4 Levels (built according to your instructions in one of your Posts). Now we would like to re-arrange the Windows (not just one over the other), because the top one (parent) has just two columns and is just for Navigation, so we would like to have this one on the left side rather than on top. Is there any way to do this?
    Thanks in advance.
    Andreas

    • thatjeffsmith

      No, it’s just as you see it.

      Fancy reports = I would consider using ORDS/JS+HTML or use APEX.

  8. Hi Jeff, is there any way to change the colors in headers in excel exports?

  9. Steve Fenwick Reply

    Hi,
    In the latest version, the option under Code Editor – Completion Insight – “Change Case as you type” is missing.
    If it has been moved, where to?
    If it has been removed, please could you put it back.

    Thanks

    Steve

    • thatjeffsmith

      Removed in 18.1

      If you want to change the case of your code, use the formatter.

      You can setup the formatter to ONLY change the case of your code

  10. David Bast Reply

    Jeff,
    Forgive me if this has been made available elsewhere, but I didn’t find it. Regarding frequent updates/upgrades of sql developer, is there a way to update an existing version without having to download and install a new version in a new location? So 17.2 gets installed in 17.2 folder, the same for 17.3 or can a new version be installed to the same folder as the older version and the new files will overwrite the old ones?

    • thatjeffsmith

      No.

      Put down new version, fresh folder. Start. Say ‘yes’ to migrate over previous settings. Use it for a day or two. If happy, delete old version.

  11. I believe that I found a defect in SQL Dev 17.3.1.279.
    Where should I go to determine if it has already been reported, and failing that what is the correct way to report it?

  12. Hi Jeff,

    I think I’m losing the plot here…..
    When looking at stored procs in the schema browser, there is a little red ‘X’ icon for ‘broken’ stored procs. What does the little green icon signify?

    Thanks
    Vic

  13. Hi
    I remember in earlier versions of sqldeveloper there was “check-in”, “check-out” for stored procedures.
    I can’t find it now.
    Does it mean I have to buy Git or other source control software?
    Thanks

    • thatjeffsmith

      Git/SVN/CVS/Perforce support is still there. It’s not for procedures though, it’s for files? Can you show me what you’re not seeing?

  14. Mark Hawker Reply

    Hi Jeff,

    I have just download the latest EA 4.2 build of SQL Developer (4.2.0.16.356) and noticed the following issue with the format option. I did post something similar to this in December last year and the fix was put into a new EA release. Apologies that I haven’t had the time to retest until now.

    Unfortunately the issue is still there. When installing our Oracle product, we make use of substitution variables from SQL*Plus. Below is an exert of a piece of installation DDL.

    CREATE TABLE &&lrc_owner..alm_alarms_delete
    (
    ald_id NUMBER(10,0)
    ,alm_id NUMBER(10,0)
    )
    TABLESPACE &&dictionary_ts.
    /

    When attempting to format this, the formatter raises the following error even though it is valid syntax in SQL*Plus and SQLCL.

    CREATE TABLE &&lrc_owner.
    /*** Syntax Error at line 0, column 25

    CREATE TABLE &&lrc_owner..alm_alarms_delete
    ^^^

    Expected: identifier,colmapped_query_name,
    ***/.alm_alarms_delete

    Any chance of getting this fixed please as I would like to use the SQL developer formatter for the entire project.

    Many thanks

Reply To Jan Cancel Reply