Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
7,802 Comments
Thank you for the good info on Ords here. I just upgraded Ords 21 to 23.4, while they look similar , the CLI for installation and running is different , the config files are different too, more over, the config files do not match latest Ords installation doc,
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.4/ordig/about-REST-configuration-files.html#GUID-3F19E9F2-13E6-42AC-958A-3DE50E3AF77D
# ords –config /etc/ords/config install
it knows how to upgrade and finished successfully. My 21.2 version used apex.war to change path to /apex and its config was /app/oracle/ords_config. Under the new /etc/ords/config, I only see 2 directories : global/, databases/ , under /global, I only see one file “settings.xml”, there is no other directories like /standalone. So far my Ords23.4 works fine, do I have something to worry about ? settings.xml works fine ( the only config file for me), I also noticed my certificate private key works fine without DER format
mySSL-private-key
in short, the interview questions from the CLI install seemed somewhat different and does not exactly match the 23.4 documentation , the java options -Dorg.eclipse.jetty.server.Request.maxFormContentSize=3000000 does NOT work anymore ( it’s still in the 23.4 doc but the CLI is ords , not java -jar ), it’s me or the 23.4 doc should get some minor updates? thank you
Hi Jeff,
We have a problem when we are installing ords 23.4 on oracle 19:
“ORA-00600 Error On ORDS_SELF_SERVICE_SCHEMA During Installation Of ORDS 23.2.x”
Is there a possibility to update ORDS on 19c or are we blocked ? because a bug in 19 was fixed on 21 and we can’t upgrade oracle DB at this time.
regards.
Why does it say 23.2.x ?
Because this problem exists since 23.2 and ORA 19
Are you using 23.4 or not?
Yes Jeff,
We have tried to update to 23.4 but we have made a rollback to 23.1 because the database update script can”t update ORDS_METADATA.
A new feature in 23.2 doesn’t work with ORA 19 :
“Self Service Schema registration improvements: USER name based on requested value, DBA can specify default privileges, delete rejected schema”
regards
It does work with 19c, i’m using it right now, but there’s an ORA-600 BUG that’s getting tripped on some instances. We’re trying to get the database bug fix back ported to 19c.
Our shop is a Windows ENV. We have a Oracle license that allows up to DL the zip files from OTN MOS etc.
I would like to get out of the business of downloading the zip file for people and emailing it to them. We have a large and diverse end user community and do not publish the Oracle CSI number to each of them.
We use Software Center as the Application Repository.
Is there a method to add the Current SQL Developer zip file to Software Center. Our guys that manage it are asking me for a ” unattended installation command line”
Other suggestions to make SQl Developer easier to get into the hands of our end users ???
Thanks everyone !
catharine
We’re moving it to the FUTC license, that’s just a fancy way of saying, ‘FREE’ – so no more clicking through EULA and agreeing to anything with an oracle.com account.
In the meantime what most folks do today is download to a central server and distribute it themselves internally.
I cant remember where in the docs (and I have looked) but maybe while not sober? I need to know how to set and reset the admin user from database actions. Hopefully a quick answer, which means I was heavy on the beer while I searched and its easy to find? Sorry I lost my password cache when I lost my computer and need some help.
[email protected]
Must stay away from the late night IPA, sorry about the noise, I remembered right after the coffee!!
CREATE USER ORDS_ADMIN IDENTIFIED BY adfasdfasdfasdfasdfasdf
GRANT DBA, PDB_DBA, resource, connect TO ORDS_ADMIN;
BEGIN
ORDS_ADMIN.ENABLE_SCHEMA(p_enabled => TRUE,
p_schema => ‘ORDS_ADMIN’,
p_url_mapping_type => ‘BASE_PATH’,
p_auto_rest_auth => TRUE);
END;
/
Thanks for your excellence and your patience, losing lots of brain cell every day…
VSCode extension is brilliant. Would be GREAT to a have list of things that are broken and/or missing to get a sense for where the development is.
For example, I couldn’t export anything… everytime I get an empty file!
There appears to be no way to import a .csv file or any type of file. In looking a table, you can’t seem to alter the DATA_TYPE, etc.
And is there a preferred place to report bugs/feature requests for the VS Code extension?
Most of the ‘broken’ things as well as things not yet included feature-wise, are all being discussed on the forums.
You should be able to export stuff. There are no known issues there. Biggest thought that comes to mind is an extension conflict, you will want to disable the Oracle Developer Tools extension (completely) or for your workspace to make make sure you get all the features.
The CSV/Excel import wizard hasn’t been built, yet. Although you could use the LOAD command in SQLcl.
Same for the Create/Edit table and other object dialogs, they will come later.
Officially, My Oracle Support is where you should go to report bugs, but we’re happy to take them on the Forums.
The thing I miss most when using SQL Developer Web are Keyboard Shortcuts. Are you aware of any plugins for Edge or Chrome that would let me map keys to buttons? All I really need is F9 to execute! I’m thinking not because it sounds cumbersome. Sorry if this has been asked before, Dr. Google isn’t returning anything useful.
Thanks,
Daren
We have kb shortcuts! Ctrl+Enter will do the same as F9
This is old but…
Hi
Am I out of luck running SQLcli if I have JAVA 21?
java -version
java version “21.0.2” 2024-01-16 LTS
Java(TM) SE Runtime Environment (build 21.0.2+13-LTS-58)
Java HotSpot(TM) 64-Bit Server VM (build 21.0.2+13-LTS-58, mixed mode, sharing)
[1]+ Exit 1 /opt/sqlcl-23.4.0.023.2321/bin/sql
Error: A JNI error has occurred, please check your installation and try again
Exception in thread “main” java.lang.UnsupportedClassVersionError:
oracle/dbtools/raptor/scriptrunner/cmdline/SqlCli
has been compiled by a more recent version of the Java Runtime (class file version 55.0),
this version of the Java Runtime only recognizes class file versions up to 52.0
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:756)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:473)
at java.net.URLClassLoader.access$100(URLClassLoader.java:74)
at java.net.URLClassLoader$1.run(URLClassLoader.java:369)
at java.net.URLClassLoader$1.run(URLClassLoader.java:363)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:362)
at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:601)
Yes, it’s not approved, you need to use Java 17.
Support for 21 will come later this year.
I wasn’t clear enough…. Looking for custom Key Mapping….
Also going blind. You have anything for that? lol
Yes, you can set your own defaults.
Hello, Jeff,
I have the script below. When I run it, I get the result with double quotes at the beginning of the first column and at the end of the last column.
How to remove double quote from the beginning of the first column and the end of the last column in a pipe delimited text file ran in SQLcl?
SET SQLFORMAT DELIMITER
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 9999
SET TRIMSPOOL ON
SET TRIMOUT ON
SET VERIFY OFF
–Turns off the Terminal Output
SET TERMOUT OFF
SET SERVEROUTPUT OFF
— Create a substitution variable for the current date
COLUMN current_date NEW_VALUE today_date NOPRINT
SELECT TO_CHAR(SYSDATE, ‘MMDDYYYY’) AS current_date FROM DUAL;
SPOOL C:\Test_Dixie_&today_date..txt
SELECT date_of_birth || ‘|’||
date_of_hire_most_recent|| ‘|’||
date_of_hire_original|| ‘|’||
ssn|| ‘|’||
employee_id|| ‘|’||
first_name|| ‘|’||
middle_initial|| ‘|’||
last_name|| ‘|’||
home_address_1|| ‘|’||
home_address_2|| ‘|’||
home_city|| ‘|’||
home_state|| ‘|’||
home_zip|| ‘|’||
FROM output_view;
— Turn off SPOOL
SPOOL OFF
I already answered your question where you asked it the first time (use GUI in SQLDev)
Thank you!!
Hi Jeff,
I have an issue with sqldeveloper 23.1.1. using oci/thick driver.
Client OS: Windows 10
Oracle Client: Instanclient 21.12
Windows PATH : The first entry is to my instant client 21.12
I copied latest (21.11) ojdbc11.jar into my instant client directory.
I copied ocijdbc.dll (21.12) from my instant client directory into my SqlDeveloper directory.
When I try to establish a connection to an Oracle 19 DB using thick driver, I get an error that versions of ojdbc11.jar and ocijdbc.dll are not compatible.
Unfortunately I’m not able to find either ojebc11.jar (21.12) or ocijdbc.dll (21.11) to fix this incompatibility.
I would appreciate any suggestion.
Thanks in advance,
Tobi
I agree, it’s not working, which is odd b/c i had it working earlier this year. Exact same rig is working in version 22.2 but is not working in 23.1.1.
Note i needed to download 21c jdbc driver (ojdbc11.jar) and drop into the instant client folder, first.
Our application developers are running SQL Developer on Windows and using the Windows 64-bit with JDK 11 included because we don’t have Java installed on our PCs. Prior to version 23.1.0.097.1607 (x64) we were able to run sqlcl from the sqldeveloper directory and it would use the embedded JDK. That no longer works and I’ve not found any workaround that allows us to use version 23.x.
Event viewer shows this:
Faulting application name: sql.exe, version: 23.1.0.0, time stamp: 0x6424c2f0
Faulting module name: msvcrt.dll, version: 7.0.22000.1, time stamp: 0x4078cfe5
Exception code: 0xc0000005
Fault offset: 0x00088d50
Faulting process id: 0xf5c
Faulting application start time: 0x01da50c33f645ae5
Is this a known issue? Can you suggest a workaround or are there any plans to fix this in the future?
23.1.0 isn’t current, go try 23.1.1.
You should still be able to, this is on my machine, Windows 11 –
sqldeveloper\sqldeveloper\bin❯ sql /nolog
SQLcl: Release 23.1 Production on Mon Jan 29 08:09:48 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> show java
Java Detail
-----------
java.home= c:\sqldev\23.1.1\sqldeveloper\jdk\jre
java.vendor= Oracle Corporation
java.vendor.url= https://openjdk.java.net/
java.version= 11.0.21.0.2
Ok so you have sqldev 23.1.1 not 23.1.0 – ok, well I can’t reproduce your issue on my rig, so I’m guessing something environmental on your machine…I would open a support case with My Oracle Support.
23.1.0 is the file version for sql.exe in the download for SQL Developer 23.1.1.345.2114-x64
Hello Jeff,
I did some GraphQL stuff before with some of my customers. So I looked into the new ORDS 23.3 graphQL support.
I like it! I also wrote a longish text about it, like how to use the ORDS graphql resolvers to “resolve” json documents and external REST services as an idea, since this is what graphQL is also about.
Heres the link to l´the longish blog I wrote:
https://blogs.oracle.com/coretec/post/graphql-pgqql-or-sparql
AND here are some findings and perhaps enhancement requests:
* The where and sort parameters only like database objects which are not in camelcase, the rest of the graphql query accepts that.
* i’d like to specify my own relationship names; most can be done by defining views with the names one requires. But how about supporting some table comments to specify custom relation’s names? Looks almost like my favorite Java annotations then 🙂 (currently, name of FK column + table name is used by default, sometime hard to pick the right names)
* Of Course I would love to see mutations. Can be non-complex for a start with an error message if too compley mabe ?
* Subscriptions are interesting too, But not that famous I think. But is that possible at all? Continuous Queries behind those ?
Some of my customrs will love the new graphql features, so the database developers could support the Java developers and generate some default rom the database ….!
Best regards,
Marcel
Just downloaded and installed SQL Developer 23.1.1.345. Imported connections from SQL Developer 22.2.0. We use RADIUS for external authentication. The connections that were defined for such (Custom JDBC) work. But several of us have tried to define a new Connection, also as Custom JDBC, and it constantly gave a ‘Status: Failure – Test failed: ORA-01017: invalid username/password; logon denied’ error. So we took one of the RADIUS connections that was imported and attempted to duplicate it manually; same Custom JDBC URL and Advanced Name and Value entries. When tested that too is constantly giving the same ORA-01017 error… yet the original works fine. Bug? Two of us did a Teams/Screen Share to verify our typing and all looks good. Thoughts?
The main thing that has changed between those 2 versions is the JDBC driver we ship.
You probably will want to open a SR wit MOS.
OK, but if it was the JDBC driver (ojdbc11.jar… correct?) then why would it work with previously saved/imported connections but not with new connections? Also for the new connection I enter under Advanced a Name of ‘oracle.net.authentications_services’ and a value of ‘RADIUS’. I click on ‘Save’ and then close the ‘New/Select Database Connection pane. I then re-open the pane, go back to my new connection and under Advanced it has the ‘oracle.net.authentication_services’ entry but under value it is blank…. it didn’t save the value entered. Somehow that doesn’t seem to be the driver but with the ‘New/Select Database Connection’ module within SQL Developer. Would you not agree?
I don’t have the time nor expertise to give you what you need, but SUPPORT can. I can make sure support is treating your Service Request with the care that’s deserved.
Hello Jeff,
I installed Oracle SQL Developer Extension for VSCode.
The connection is working.
Where is the schema browser? How can I switch to another schema easily?
Thanks Wouter
I’ve tackled your question in today’s post – you’re not the only one asking, but thanks for nudging me.
Hello Jeff, I like the new sqldevelopper extension in vscode. Would it be possible to group the connections under a group?
Absolutely, just not yet. We need to build it!
SQL DEVELOPER DATA MODELER Version 23.1
DDL generation has syntax errors:
Example DDL code having error:
— Generated by Oracle SQL Developer Data Modeler 23.1.0.087.0806
— at: 2024-01-23 09:18:09 CST
— site: Oracle Database 12cR2
— type: Oracle Database 12cR2
— predefined type, no DDL – MDSYS.SDO_GEOMETRY
— predefined type, no DDL – XMLTYPE
CREATE TABLE ccdm.action_item (
id1 NUMBER NOT NULL,
project_order_id NUMBER NOT NULL
);
COMMENT ON COLUMN ccdm.action_item.id1 IS
‘Comment by Ebrahim’;
CREATE UNIQUE INDEX ccdm.action_item_pk ON
ccdm.action_item (
id1
ASC );
ALTER TABLE ccdm.action_item ADD CONSTRAINT action_item_pk PRIMARY KEY ( id1 );
— After Create script example for table CCDM.ACTION_ITEMCREATE TABLE ccdm.admin_task (
id NUMBER NOT NULL
);
Last three lines don’t make sense. Have I turned on some settings that is having DDL creation show “After Create script example” ?
Thank you for your help, Jeff.
Can you ask on the product/community forum? Also, are you generating from dictionary or your design?
Thanks for a quick reply. I am generating DDL from the design. I use the icon next to the binoculars icon. The icon says ‘generate DDL’.
Frankly, I dont know how to generate DDL from dictionary. Also, if I use “journal tables” option, these extra lines don’t show up which is good, but then I get the triggers in the DDL that I dont want.
Hi Jeff!
I recently installed ORDS 23.2.3 standalone and then upgraded to 23.4, My problem is that when I connect to the server the port number (8443) gets stripped from the URL in the browser and I get “ERR_CONNECTION_REFUSED”.
If I add the port back in the URL, my URL adds a page number (not the login page though) Still get the same error though.
If I again add the port number I finally get a session number, reach my ORDS-instance and can login.
Though my browser (Chrome) says the certificate is not valid and the connection is not secure but doesn’t provide any useful info of course.
In 23.2.3 I only had to add the port number back once to reach the site. The certificate is provided by the customers operating supplier and is provided as pem, pkcs8. The certificate looks OK though I am definitely no expert with certs.
How could I fix this rather annoying problem?
What is the best practice to use Azur DevOps Git for version control for database development not oracle cloud, using Oracle database developer, or which IDE,
And how to apply ci/cd?
I’d suggest looking at our Liquibase feature baked into SQLcl, for creating changeLogs you could then version in Azure DevOps.
It seems we ran into a bug when doing a clean reinstall, see Doc ID 2972860.1. Have not found a patch higher than 23.1.4 so now we will reinstall that version and see if it helps.
Hi Jeff,
I have a general question regarding ORDS conneciton pools.
Does it make sense to configure multiple ORDS conneciton pools (in one ORDS instance) which connect to the same database and even to the same ORDS_PUBLIC_USER?
The background of this question is:
Lets assume we have multiple APEX applications & multiple ORDS based WEBSERVICES in the same database.(all APEX apps are in one WORKSPACE, all ORDS based REST WEBSERVICES are in one rest-enabled schema), all applications/webservices are accessed via the same middleware (same TOMCAT instance).
For me it would be a good idea to create a dedicated conneciton pool for each application.
Just to have a kind of decoupling – in case application A has a problem (in example a contention problem which blocks all sessions from the pool), application B will not be harmed in case it is using its own connection pool.
Same for ORDS based REST WEBSERVICES – for me it would be a good practice to have a dedicated pools for webservices which belong to the same module (or which belong logically to the same purpose of use).
An additional advanctage would be that configuration could be done application specific (in example – postProcess & preProcess parameters are very likely to be different on application level) ….
So the ORDS pool configuration may look like this (routing is done via path files – default routing seems not to be flexible enough)
├── default
│ ├── pool.xml
│ └── wallet
├── apex_app_A
│ ├── pool.xml
│ ├── paths
│ └── wallet
├── apex_app_B
│ ├── pool.xml
│ ├── paths
│ └── wallet
├── apex_app_C
│ ├── pool.xml
│ ├── paths
│ └── wallet
├── rws_A
│ ├── pool.xml
│ ├── paths
│ └── wallet
├── rws_B
│ ├── pool.xml
│ ├── paths
│ └── wallet
└── rws_C
├── pool.xml
├── paths
└── wallet
All above pools will connect to the same database (and the same database user).
The only purpose of the pools is decoupling and the ability to have application specifc pool configurations.
What do you think?
Thank you very much in advance!
Best regards
One workspace, one schema, nothing would stop a consumer from calling a REST API in any of those pools vs the one pool that you told them to, unless you put something in front of ORDS to re-direct traffic…or you’d have to implement security scheme via the preHooks on the pool to gate access.
I’m more worried about you having all of your apps and rest apis in a single schema. That means you can’t take advantage (easily at least) of database features like Resource Consumer Groups to say lock down CPU/resource allocation on a set of apps/apis. It also makes it easier for one app to see data from another app.
Also, dedicated pools per app/api is problematic, chewing up more db connections, leads to more load on db server…which negates having a shared pool
Hi Jeff,
Thank you for the quick reply!
Sometimes things are historically grown and we just try to make the best out of it 🙂 ….
But what do you mean by:
“unless you put something in front of ORDS to re-direct traffic…”
I thought the routing which pool is used for each individual request is done bei ORDS itself via the paths file.
Let assume we have two apex applications, wich are called in the following way:
https://host.name.domain.xx/apex/r/testws/f111
https://host.name.domain.xx/apex/r/testws/f222
Lets further assume we want to route calls to app “f111” to pool apex_app_A and calls to app “f222” to pool apex_app_B.
So in my understanding this would be achieved
by adding the following entry to paths file in pool apex_app_A: “r/testws/f111”
by adding the following entry to paths file in pool apex_app_B: “r/testws/f222”
├── default
│ ├── pool.xml
│ └── wallet
├── apex_app_A
│ ├── pool.xml
│ ├── paths –r/testws/f111
│ └── wallet
├── apex_app_B
│ ├── pool.xml
│ ├── paths –r/testws/f222
│ └── wallet
Is my understanding wrong?
Thanks again!
Hi Jeff,
Is it possible to use ORDS with obligated parameters? We do not see an option to enter this when we enter a parameter in the parameters tab.
Would be very helpful to know and understand how we can make these obligated, so the user gets a clear 400 message when a parameters is not used.
Thank you.
Best regards,
Paul
Have your plsql handler fail if the parameter is NULL, with the 400 errmsg of your choice.
Or instead of a parameter put it into the template
Something/:x
Hi Jeff –
I’m tearing my hair out trying to resolve an error running sqlcl (sql.exe) with a connection to XEPDB1 on a Windows box. The most recent error I’m getting is “Error Message = Incompatible version of libocijdbc[Jdbc:211100230512, Jdbc-OCI:213000”. I’ve searched, and seen you commenting on various stack overflow posts about issues like this, but no joy so far.
We have a SQL Server-based application of 24 years and would like to get it running with Oracle as the backend. So I’m experimenting. We are a windows shop. I’ve installed Oracle XE and SQL Developer, created a user account in the XEPDB1 container, and can connect using sqlplus to myuser/mypassword@XEPDB1 just fine. But sqlplus has various issues, like the 2499 character limit for commands, which causes a problem in porting some of our TSQL stored procedures that have long comments (I’m building publish scripts to create all our various objects, and was planning to use sqlplus or sql.exe). And sqlCL just looks like way more fun than sqlplus. I had a windows 11 box with everything working great, but after a security update and forced reboot, I was unable to connect anymore with sqlplus or sql to xe or xepdb1, and lsnrctl status showed no services other than CLRExtProc. I worked on that problem for 2 hours, no joy.
So I punted and am trying a fresh setup on a different Windows 10 box… installed Oracle XE, created user in XEPDB1 container, sqlplus works great for myuser/mypassword@XEPDB1.
1) sql.exe that comes with Oracle XE reports error “Error: Could not find or load main class oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli”
2) Installed SQL Developer. Sql.exe that comes with SQL Developer can connect to system/mysyspassword but connecting to myuser/mypassword@XEPDB1 throws “incompatible version of libocijdbc…” error.
3) Downloaded latest SQLCL. It says “this application requires a Java Runtime Environment 11.0.9”. Tried setting path to include java executable from SQL Developer… got slightly different “incompatible version of libocijdbc…” error.
4) Downloaded Windows MSI for JDK 21 and installed. Checked java-version to confirm proper path setup, tried sql.exe from latest SQLCL download, still get “incompatible version of libocijdbc…” error.
5) Saw your comment about SQLCL supporting JDK 11 and 17, so I downloaded Windows MSI for JDK 17 and installed. Switched path so JDK 17 is first. cmd> java-version reports “java version “17.0.9” 2023-10-17 LTS”. Using Sql.exe from latest SQLCL and attempting “sql myuser/mypassword@XEPDB1” gives me…
USER = myuser
URL = jdbc:oracle:oci8:@xepdb1
Error Message = Incompatible version of libocijdbc[Jdbc:211100230512, Jdbc-OCI:213000
USER = myuser
URL = jdbc:oracle:thin:@xepdb1
Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=4q1NjgyWTOGSUS9m5jyjBA==)
USER = myuser
URL = jdbc:oracle:thin:@xepdb1:1521/xepdb1
Error Message = IO Error: The Network Adapter could not establish the connection (CONNECTION_ID=SpYqn7diTkG35y0HGzmXQA==)
“Tnsping xepdb1” reports “ok” and I can use “sqlplus myuser/mypassword@XEPDB1” just fine… so it’s something to do with my particular setup and sql.exe.
Note that we are not (yet) an Oracle customer, so I don’t have access to open support requests. I submitted a request to Oracle sales about a month ago describing our situation and asking for someone to contact me about porting our application to Oracle… but no response.
I’ve written awk scripts to translate our 250+ table definitions to run on Oracle, and I have about 200 of our 700+ stored procedures converted to PLSQL. I’m liking what we can do in PLSQL. But… it’s so painful to get the tooling working, as a newbie! Am I missing something obvious? Should the sql.exe that ships with Oracle XE work out of the box?
thanks!
-Frank.
Unset oracle_home env var in your terminal session, the oracle client or database binaries are causing an issue with sqlcl
We should have that fixed with our next release (23.4)
ps. I may have solved it, but I would only bet $25 on it.
I went back to the Windows 11 laptop where something was messed up with the Oracle services, unset ORACLE_HOME and TNS_ADMIN, uninstalled Oracle XE, rebooted, installed Oracle XE, re-setup tnsnames.ora, re-setup myuser/mypassword with access to XEPDB1, confirmed I could connect to myuser/mypassword@XEPDB1 with sqlplus, renamed the sql.exe that comes with Oracle XE to be sql-not-working.exe so I could execute sql.exe from the (later in path) Oracle SQL Developer folder. And then… argh… sql.exe was failing with same error (incompatible version…) as on the Windows 10 box!
I went back to my original checklist and realized that one difference between state=”working” and state=”not working” was that I didn’t re-add ORACLE_HOME after the reinstall of Oracle XE. I was testing each thing (tnsping, sqlplus, etc) in sequence, and none seemed to require ORACLE_HOME. But once I set ORACLE_HOME, sql.exe started working. Did I unset it and test that it started failing, and then re-set it and confirm it’s working again? Nope 🙂
Any thoughts on why sql.exe would require ORACLE_HOME to be set when none of the other components (SQL Developer, tnsping, sqlplus, etc.) require it?
thanks!
-Frank.
Hi Jeff –
Shoot, I apologize I didn’t see your reply to my first question, thank you for that! As you can see from my second post, my workaround was the opposite – to set ORACLE_HOME! Darned computers. I will experiment on the second box and see if setting ORACLE_HOME on that box causes sqlcl to start working.
thanks for working on a Sunday!
-Frank.
We don’t require ORACLE_HOME to be set, what is happening is based on the ENV, SQLcl is trying to use the Oracle Client/Home drivers to make the connection vs the driver supplied with SQLcl, and it’s finding it’s not a compatible version, where minVer would be 21c.
Hi Jeff –
Thanks for the info – I’m still puzzled, but setting ORACLE_HOME (to C:\app\LocalAdmin\product\21c\dbhomeXE, where I have Oracle XE installed) fixed the problem on the Windows 10 box as well. This time I tried it repeatedly- with ORACLE_HOME set, “sql myuser/mypassword@xepdb1” works. With ORACLE_HOME not set, sql.exe throws the incompatible driver error. I’m happy because I can keep making progress on our migration.
Here’s my versions for various items if that’s helpful:
OracleXE213_Win64, installed to C:\app\LocalAdmin\product\21c\dbhomeXE
SQL Developer Version 23.1.1.345 Build 345.2114
SQLcl: Release 23.1.1.0 Production Build: 23.1.1.345.1443 (from SQL Developer folders)
Box 1: Windows 11, enterprise edition
Box 2: Windows 10, enterprise edition
Problem occurred when running under windows cmd prompt and in git bash shell.
Thanks for providing this service, and for the tools that you build!
-Frank.
Is Oracle working on the Oracle Database PL/SQL package UTL_HTTP to add support for http_versions: HTTP/2 and HTTP/3?
I’m not aware of that being in the 23c product release, but that doesn’t mean we’re not working on it.
Hi Jeff, I Have an issue with sqldeveloper 23.1.1.
S.O. : windows 10
Oracle Clien: Instanclient 21.12
Window PATH : The first entrance is to my instanclient 23.12
Sqldeveloper COnfigure Oracle Cliente TEST:
Testing the Instant Client located at Z:\instantclient\Windows\instantclient_21_12
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 21.11.0.0.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
the expected native library directory Z:
I have checked User Oracle Client.
The weird thing is I can connect directly, proxy user, without problems to an Oracle 19c database, but I can´t connect to the same database with a schema though proxy user. And I used to before I upgaded Sqldeveloper
I would appreciate any sugestion, thaks in advance
2 things
Windows 10 is no longer supported by MSFT, so no longer supported by us…although I doubt that’s an issue.
If the Thick Driver is failing, it should be failing back to THIN (JDBC) connections, and a Proxy Connection should also work.
Do you want help debugging getting your 21c client going with SQLDev for thick connections, or do you want to look at the Proxy User connection issue?
” but I can´t connect to the same database with a schema though proxy user. ”
What happens when you try, which error message do you get?
Thanks Jeff for the answer.
My concern is I can connect to a database through proxy user.
When I’m trying to connect via proxy XXX[YYY] get the following error:
java.lang.Error: Incompatible version of libocijdbc[Jdbc:2111000, Jdbc-OCI:2112000
at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:4589)
at java.base/java.security.AccessController.doPrivileged(Native Method)
connecting directly like XXX it’s ok
Database version “Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.12.0.0.0”. This is production version
Then, I try with develepment installation (“Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.8.0.0.0”) and all is ok. I can connect through proxy, XXX[YYY] and directly to XXX.
What could be bodering Client connection from database standing point ??
I really apreciate your time to this
kind regard
Mauricio Fernández
Reading others post could be an issue not usin instant client 21.8 with sqldev 23.1.1 …but I can’t find it ….
I have the exact same problem, with the same versions, except for the Driver version: 21.11.0.0.0. Mine is the one that came with SQL Developer and is version 21.8.0.0.0, see below.
I must use the OCI driver for some of the connections, since they must go through an Oracle Connection Manager proxy. Connecting from sqlplus connects successfully, and I tried putting several different versions of ojdbc11.jar in the Instant Client directory, including the one from sqldeveloper/jdbc/lib (Implementation-Version: 21.8.0.0.0):
Testing the Instant Client located at C:\apps\instantclient_21_12
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 21.8.0.0.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded.
…
The actual exception i get when trying to connect says a bit more:
ava.lang.Error: Incompatible version of libocijdbc[Jdbc:218000, Jdbc-OCI:213000
Now, where does the 213000 come from is what puzzles me. my previous instant client was indeed 21.3, but I removed it from the PATH entirely. Are there other placet to look for references to it?
Thanks in advance!
Flado
I downloaded and unzip the latest ords, when I run the $ORDS_HOME/bin/ords script I get an java error.
Looking through the script I don’t think it’s the script, but rather any invocation of the war file.
Simply asking the war for help directly is sufficient to product the error
I’m assuming it’s something simple I’m missing, but I don’t see it.
$ java -version
java version “17.0.9” 2023-10-17 LTS
Java(TM) SE Runtime Environment (build 17.0.9+11-LTS-201)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.9+11-LTS-201, mixed mode, sharing)
$ java -jar ords.war –help
null
java.lang.NullPointerException
at java.base/java.util.concurrent.ConcurrentHashMap.putVal(ConcurrentHashMap.java:1011)
at java.base/java.util.concurrent.ConcurrentHashMap.putIfAbsent(ConcurrentHashMap.java:1541)
at java.base/java.lang.ClassLoader.getClassLoadingLock(ClassLoader.java:666)
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClassOrNull(BuiltinClassLoader.java:651)
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:639)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:574)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)
at oracle.dbtools.launcher.executable.jar.ExecutableJarEntrypoint.invoke(ExecutableJarEntrypoint.java:42)
at oracle.dbtools.launcher.executable.jar.ExecutableJarEntrypoint.main(ExecutableJarEntrypoint.java:64)
The Java based CLI is gone, it’s been replaced by the ORDS script.
Simply do
ords installords help
ords config
...
Like I said, I ran the script I got the same error.
I showed calling the war directly in order to show the problem wasn’t something inside the script it was in the invocation of ords.war.
Simply running ords will produce the same error, it doesn’t matter what the parameters are
ords
ords install
ords help
ords config
ords anythingatalll
when the script reaches the point that it calls ords.war it fails.
It’s probably a bad ENV VAR, ORACLE_HOME’ish as shown here
Any update on when the updated version of 23.1 will be available? No updates on this thread in the past couple days – https://forums.oracle.com/ords/apexds/post/sql-developer-23-1-clob-view-truncate-long-strings-with-new-9064
(I’m unable to participate on the Oracle forums anymore – https://forums.oracle.com/ords/apexds/post/otn-forums-have-stopped-working-after-moving-to-oracle-apex-5272)
It’s live right now, sorry for the hassles with previous attempts.
Hi Jeff,
I’m trying to call the ORDS API via Java application and pass the SQL query to the API dynamically from the application, Is using CURL the only way to achieve this? Is it safe to use CURL when we are building lots of API’s and for every API calling from spring boot application, I’m sending an equivalent CURL command to ORDS with the query. I’m I doing right, I don’t want to hard code each API query in modules, templates. So, Is my approach with CURL efficient, are there any other ways to do it?
1. this is a built-in feature of ORDS – REST Enabled SQL, so there’s no reason for you to build your own API to do the same thing.
2. if you DO build your own API, you’ll want it to be a POST handler, where you send the ‘dynamic’ SQL to be executed, on the BODY of the request
cURL is just a CLI / utility for calling HTTPS
Hi Jeff,
I read somewhere that SQL developer and modeler applications were being rewritten in something other than Java. If true is there a timeline you can share? I use Modeler a lot and note that we haven’t had an update for a while to take advantage of the new 23c features.
Many thanks,
Mark.
You’ll hear more on this in 2024.
Hi Jeff,
I’m using SQLCL 23.3. I’ve set the SQLPATH variable, but the login.sql doesn’t get loaded/executed
What I’m I doing wrong?
It used to work .
I’m currently running on windows 11
Kind regards
Peggy
run ‘show login’
That will output where we look, and if we’ve found a file
SQL> show loginGlogin possible locations
--------------------
/opt/sqlcl/23.2/sqlcl/
Login possible locations
--------------------
/opt/sqlcl/23.2/sqlcl/
./
Super !!
Found the error
Many thanks