Search Results

141 Comments

  1. How do I set the Region and Language for the connection in Oracle SQL Developer Extension for VSCode (v24.2.1)?
    When I use my region, in Windows, I get an error when connecting to the database. However if I change the Region in Windows to United States it works.
    In SQL Developer I just added these two lines to sqldeveloper.conf:
    AddVMOption -Duser.language=en
    AddVMOption -Duser.region=US
    and the connection works.

    • It makes no difference what I set in the Extension preferences, Database > NLS: Language. The driver seems to look up the Region and language settings from Windows. This is what I need to override.
      If I change the region and language in Windows to United States I can start up VSCode and connect to the database.

    • Right, that’s how the JDBC driver works.

      What locale is your OS running in, and what error message are you getting, exactly?

    • The regional settings that I use on Windows are for Faroe Islands (https://www.iso.org/obp/ui/#iso:code:3166:FO).

      The error message does not give much information: “An error occurred when attempting to connect to the database”. However if I give it the wrong password it gives the reply “The database username or password is incorrect”, so the connection can send the password. But with the correct password “An error occurred when attempting to connect to the database” is returned.

      If I do the same in SQL Developer, by removing the previous mentioned lines in sqldeveloper.conf the received error message is:
      “An error was encountered performing the requested operation:

      ORA-00604: error occurred at recursive SQL level 1
      ORA-01756: quoted string not properly terminated
      00604. 00000 – “error occurred at recursive SQL level %s”
      *Cause: An error occurred while processing a recursive SQL statement
      (a statement applying to internal dictionary tables).
      *Action: If the situation described in the next error on the stack
      can be corrected, do so; otherwise contact Oracle Support.
      Vendor code 604″

    • After some digging into this issue I found that one way to override the settings from Windows is by setting the environment variable JAVA_TOOL_OPTIONS to -Duser.language=en -Duser.region=US.
      Variable Name: JAVA_TOOL_OPTIONS
      Variable value: -Duser.language=en -Duser.region=US
      This can be set for User or System. In my case I used User to only set it for my user account.
      The JVM then reads this variable on startup (Oracle SQL Developer Extension for VSCode) and works like a charm.

      It would be nice if these settings could be set on the settings for Oracle SQL Developer Extension for VSCode or a default fallback would be made to en-US when it fails on regional settings.
      Or best solution: fix it for all regional settings available 🙂

    • Oh that’s a brilliant solution. But, you shouldn’t need to do this, we’re going to look for something better.

    • Excellent! I look forward to a solution 🙂
      Thanks.

  2. Andreas Markus Reply

    Can I use JWT instead of oauth2 in combination with autorest enabled objects, like a view for example?

  3. Gus Crighton Reply

    Hi
    I have raised 3 questions on the SQL Developer forum under the name Angus123.
    Any chance you could look at them
    Thanks
    Gus

    • I wouldn’t be of any use to you, the custom formatting involves a parser language Arbori that i’m not familiar with, but i’ll ask our resident mad genius to swing by.

    • Hi Jeff
      Did you manage to talk to your colleague about my custom formatting issues

  4. I’m testing out the new VSCode extension. So far I have not had any luck with my current setup to authenticate a named user defined externally which uses RADIUS. I have the following two items in my sqlnet.ora file, in addition to a known working ldap.ora file…

    NAMES.DIRECTORY_PATH= (LDAP)
    SQLNET.AUTHENTICATION_SERVICES=(RADIUS)

    I have configured the default paths in VSCode extension to point to a 21.12 TNS_ADMIN directory containing all the .ora config files, but connections using the LDAP option with my RADIUS creds are not working. I have confirmed it works fine with a non RADIUS authenticated user. Is RADIUS currently supported via this extension?

  5. Hey Jeff, so on my Oracle SQL welcome page under Database Connection>Recent tab, that keeps the last five or so databases that I have accessed, I use this a lot to get to prod and non prod domains, but it has stopped working. Have you heard of this before, and if so, is there a reset/solution to get them working again? Any and all help greatly appreciated.
    Thanks, Cleve

    • Found it! If you do not have connections pulled up, it will not let you map to the databases under recent tab.

    • Look for this file
      C:\Users\JDSMITH\AppData\Roaming\SQL Developer\system22.2.0.173.2018\o.sqldeveloper

      Adjust your name and system folder based on you and your version of SQL Developer.

      Then look for product-preferences.xml

      With SQL Developer SHUT DOWN, open the file, and look for

      “MRUConnectionCache”
      hash n=”Connections”
      list n=”MostRecentlyUsed”
      string v=”IdeConnections%23jefe+free23c”
      string v=”IdeConnections%23hr”

      That’s where the UI is reading the data from. Maybe look for XML issues where the file is corrupted, or maybe clear it out, or…

      It does seem to be working for me in 23.1 though.

  6. Hi Jeff,
    When I want to generate links in the response (for request with GET Method) it is pretty easy if I use the handler like this (it is enough to use the proper alias i.e. “$.id”):
    begin
    ords.define_handler(
    p_module_name => ‘links.example’,
    p_pattern => ‘:id’,
    p_source_type => ords.source_type_collection_item,
    p_source => ‘select emp.empno “$.id”, emp.*, decode(emp.mgr, null, null, ‘^/managers/’ || emp.mgr) “$related” from emp where empno = :id’);
    commit;
    end;

    But I want to use PL/SQL procedure (not SQL SELECT statement ) for the handler for GET Method (not POST method)!!!

    In this case while defining the handler:
    p_method is ‘GET’
    p_source_type is ORDS.source_type_plsql
    p_source is ‘BEGIN get_emp_json2; END;’

    And the procedure itself is like this:

    create or replace PROCEDURE get_emp_json2(p_empno IN emp.empno%TYPE DEFAULT NULL) AS
    l_clob CLOB;
    BEGIN

    select json_object( ’employees’ value json_arrayagg(json_object (
    ’empno’ value e.empno,
    ‘ename’ value e.ename,
    ‘job’ value e.job,
    ‘mgr’ value e.mgr,
    ‘hiredate’ value to_char(e.hiredate,’YYYY-MM-DD’),
    ‘sal’ value e.sal,
    ‘comm’ value e.comm,
    ‘deptno’ value e.deptno ) ) returning clob ) as json_doc
    INTO l_clob
    FROM emp e
    WHERE e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

    OWA_UTIL.mime_header(‘application/json’);
    HTP.print(l_clob);

    END;
    /

    How can I generate the hyperlinks in this case ?
    Can I generate them only manually ? If YES, so how to generate server name:server port and schema name for such a link not hardcoding the names but rather generating them automatically?
    http://localhost:8080/ords/ordstest

    Best regards
    Krzysztof

  7. Hi Jeff,

    Sorry if this question has already been asked and answered!

    I’d like to know if there is a way to “migrate” my open-file-tabs from one SQL-Dev Version to another.
    I usually have a number of saved .sql-Files (Worksheets) open, and it would be great if it would be somehow possible to “copy” these open-files from one version to the next.

    I search for a file-name and found a reference in
    %appdata%\SQL Developer\system….\o.sqldeveloper\product-preferences.xml and
    %appdata%\SQL Developer\system….\o.ide…..\preferences.xml
    i tried to copy the XML-elements
    and

    from one Version to the next, but that didn’t work – no startup exception, but no open-file tab either.

    I hope I just missed something obvious an you can point me in the right direction (config-file).

    Windows 10
    SQL-Developer 21.4.2.018 -> 21.4.3.063

    Thank you in advance!
    regards,
    Tom

  8. Hi Jeff,

    Using SQL Dev 20.4.1 If I run a select * from v$version and run it using f5 that produces the necessary results. If I try and run the same query – but run it as a script, I get nothing in the output window, just a small red circle with a ‘!’ on the script output tab. The info next to the tab tells me that the task completed in nnn seconds.
    Am I missing something?
    Thanks
    Vic

    • Anything written in the log panel, errors or messages? Try re-starting SQLDev and try again.

    • Hi Jeff,

      Nothing in the log panels, or errors. I only get, on the script output panel a ‘Task completed in 0.141 seconds’.
      Vic

    • Hi Jeff,
      I hear you – been there done that, even downloaded SQl Dev again. I’m using the 19.3 Oracle client, which is set as ORACLE_HOME in the Windows variables.
      I’m a bit lost …..

    • Hi Jeff,

      Fixed it – I changed the setting to use the instant client I also had installed.
      Instant Client: file:/C:/app/oracle/product/instantclient_19_10/

      Thanks
      Vic

  9. I’m not sure where to post this so I went to the horses mouth. Sqldeveloper 20.2 has a strange bug where the results of a select * from fresh_table; returns a different result than select * from schema.fresh_table. if you alter the table after it’s initial creation or drop and recreate it and add a column the column orders are different. I can show you if you like but it’s pretty simple to recreate. Build a table. Drop it. Build it again adding a column in the middle somewhere. do the queries. The added field will be on the end and not where you put it in the statement. Very strange.

    • Strange, yes.

      SQL Developer – probably not. Run the same query in SQLcl, what do you see?

      The database gives us the results, we’re just the messenger.

    • After a lot of boots and table rebuilds I can’t get it to duplicate so It just must have been a 1 off. I’ll check back if it happens again with pictures.

  10. Hi Jeff,

    We recently switched from Weblogic to ORDS. I am attempting to create a custom error page to display the referrer url with package and procedure name where a 404 error occurs. I have attempted to use an html page with javascript document.referrer but it is blank. when reviewing the Fiddler response there is no referrer sent back. Any way to get this information?

    Thanks,
    Jon

  11. Win 10; Data Modeler version 20.2.0.167 Java 8 update 271
    Trying to share designs with a colleague. Sent them a “.dmd” file ( email NO shared network drive).
    Although she can open it NOTHING shows up in either Logical or Relational part of design (no entities, no tables)!!

    Have tried importing the dmd to an empty design, have tried “opening” the dmd file NOTHING works.
    Error that is in log consistently is :
    Unable to open object ExtendedMap.xml

    • Amin Adatia

      You also need the directory with the same name as the dmd file

    • You have to send the zipped up directory in addition to the dmd file.

      There is an option to put EVERYTHING ina single dmd file, but not advised except for the smallest models.

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

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

      BEGIN
          FOR x IN 1..10 LOOP
              NULL;
          END LOOP;
      END;
      /
  13. 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

  14. 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 “username@localDB”. 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

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

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

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

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

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

    • No, it’s just as you see it.

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

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

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

    • 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

  21. 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?

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

  22. 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?

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

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

    • 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?

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

Write A Comment