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.
In the Extension preferences, Database > NLS: Language, for example.
Jacob
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.
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″
Jacob
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 🙂
Ah, I see. My problem is that I try to have my own users & rights in Oracle, put behind my own identity provider based on ORDS and apex_jwt. But ORDS JWT profile expects the identity provider to accept RSA, whereas apex_jwt can only do HSA. I guess that’s because dbms_crypto doesn’t do RSA.
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.
Angus
Hi Jeff
Did you manage to talk to your colleague about my custom formatting issues
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…
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?
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
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…
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);
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
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).
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
Hate having to say this out loud, but try bouncing your machine.
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 …..
Vic
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/
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.
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.
Mark
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.
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?
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
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
Shoaib
Hi Jeff,
Any update regarding “How to remove old entries of DBIDs or incarnation from SQL Developer” question?
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
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
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
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?
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”;
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
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.
Martin
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:
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
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.
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?
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?
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?
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
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.
141 Comments
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.
In the Extension preferences, Database > NLS: Language, for example.
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.
Can I use JWT instead of oauth2 in combination with autorest enabled objects, like a view for example?
Yes.
Well, that’s dandy! Could you point me in the right direction. I don’t understand how to tie things together instead of granting the coupled role to the oauth client. I found this one but it’s usig the service/module/rescource approach: https://tm-apex.hashnode.dev/secure-user-authentication-with-jwt-in-oracle-apex#heading-task4-using-the-rest-apis-in-our-client-app
You will rely on the privilege, not the role. It’ll match up to the scope provided by the 3rd party.
Dermot talks a bit here
https://dermotoneill.blogspot.com/2023/10/jwt-profile-validating-jwt-access.html
Ah, I see. My problem is that I try to have my own users & rights in Oracle, put behind my own identity provider based on ORDS and apex_jwt. But ORDS JWT profile expects the identity provider to accept RSA, whereas apex_jwt can only do HSA. I guess that’s because dbms_crypto doesn’t do RSA.
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
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?
yes but you’ll need to set all this info via jdbc, so with either the JDBC url or advanced connection properties, see this conversation in the Forums
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.
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
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
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
Hate having to say this out loud, but try bouncing your machine.
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
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.
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
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
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.
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
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 –
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
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
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
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
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.
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
Sure you just need to use a different report template, or create a new one.
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
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.
Hi Jeff, is there any way to change the colors in headers in excel exports?
Sure, in Excel 😉
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
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.
Thanks for the confirmation.
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?
You can ask me, or you can open a Service Request with My Oracle Support.
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
Sorry using SQL Dev 4.2
That it’s compiled for DEBUG
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?
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