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,567 Comments
I trying to use to Autorest feature on a table with only 3 columns, Have everything working from Rest,, Get (All) Get One Record, Insert one Record, Update one Record and Delete a record. Struggling with trying to pass an array of entries in the JSON. Receive the message Expected an objecdt but got a: STAR ARRAY. Here is the JSON Body Can it work by passing multiple values and have it insert multiple rows? Here is my JSON I’m passing in the Body and did make a Content-Type = application/json which works if I just provide JSON for a single row
[{“batch_number”: “904”,”sales_order”: “100200”,”sales_order_item”: “10” },
{“batch_number”: “907”,”sales_order”: “100200”,”sales_order_item”: “10”}]
That’s not supported, out-of-the-box.
If you want to batch-load multiple records, we only support CSV payloads today.
You would need to write your own REST API to process the JSON array and do row-by-row table inserts.
Hello Jeff, regarding your link: https://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/
I have similar PL/SQL script that works in TOAD, but I cannot figure out how to get it to work in SQL Developer.
Here is a simple example of the PL/SQL that works in TOAD:
DECLARE
TABLECURSOR sys_refcursor;
BEGIN
TABLECURSOR := NULL; –Modify the code to initialize this parameter
open TABLECURSOR for
select sysdate from dual;
:TABLECURSOR := TABLECURSOR;
END;
When I attempt to run it SQL Developer, I get the following errors:
Error report –
ORA-06550: line 8, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored
06550. 00000 – “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:
I have several more detail queries that are written in this format. Any help is appreciated. Thanks.
variable A REFCURSOR;
BEGIN
open :A for
select sysdate from dual;
END;
/
print a;
Results are –
PL/SQL procedure successfully completed.SYSDATE
----------
05-12-2023
Jeff,
CyberArk PSM allows privileged access to production systems without issuing passwords to authorised users.
Have you plans to collaborate with CyberArk on connecting using SQLcl and SQL Developer?
Currently only TOAD and SQL*Plus (an old version, 11.1) are supported.
Regards, John
Submit an ER to CyberArk to support Oracle’s JDBC driver, then we can talk about SQLcl and SQL Developer supporting this.
Where can I get information about SQL Developer Data Modeler Scripting? I read the SQLDeveloperDataModelingScripting.doc and could not make sense of it.
JavaScript bits? There’s a ask of sorts that ships with the product but Heli also talks about it with examples in her Oracle SDDM book.
Hi Jeff !!
From sqldeveloper I would need to connect to a Oracle DB 9i and 19c, I tryed using using sqldev 23.1.0 (for 19c connection) without problems, and I tryed using a sqldev 4.3.1 for 9i connection but i couldn’t.
I test with differents ojdbc but nothing good.
Thanks in advance.
The best way I can help you is to encourage you to take that 9i database, export it, and import it into a new 19c instance.
Then turn off the 9i system.
Hi Jeff
We’re about to migrate from mod/plsql to ORDS, but we are lacking examples of how to produce nested JSON.
If we use sys_refcursor and ResultSet, can we then build a custom nested JSON output? There is no JSON columns in our database table, so we can’t just select a JSON column, we have to build it ourselves 😉
The ideal migration would be an app re-write where your new front-end is calling REST APIs, which return the JSON (nested or otherwise)….which comes back from your existing PL/SQL programs.
It sounds like that’s what you’re doing, kinda – you’re calling a SP with has an out sys_refcursor, and you’re telling ORDS to take that and send back as the response body?
I guess I need more info to give you a good answer.
Oracle’s “Generation of JSON data using sql” answers my question.
https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/generation.html#GUID-1084A518-A44A-4654-A796-C1DD4D8EC2AA
But I do have a couple follow up questions:
1. When using a REFCURSOR in a handler, how do we modify the “hasMore” variable in the response? I believe there is also a total row count in the JSON output?
If, for example, the handle is defined as:
select itunes_albums(:title) from dual
hasMore will always have the value false, since we do not set it.
2. We would like to use OpenAPI for generating TypeScript code in our client application, but it seems that handlers defined with REFCURSOR does not provide a detailed type for the response? ORDS doesn’t know the type returned by the REFCURSOR.
Can we somehow tell ORDS what the response object type would look like? Just so the OpenAPI definition would have the correct type.
REFCURSOR – it’s all or nothing, there is no, ‘has more’ .. hasMore is false b/c we fetch the entire cursor on the single call.
Unless you want to hand-code pagination to your plsql, such that it’s only grabbing specific window of rows per call.
Let me get back to you on the OpenAPI question.
“Unless you want to hand-code pagination to your plsql, such that it’s only grabbing specific window of rows per call.”
That is exactly what we’ll be doing.
Our handler looks more like:
select itunes_albums(:title, :offset, :limit) from dual
So is there any way to set hasMore and the total number of rows available?
yeah of course
Hi Jeff,
This is part rant, and part serious question. I read your work on and off for years, built apps in APEX professionally and found your writing helpful and inspiring.
I’m planning on building an AI enabled product in Oracle APEX. To start, I’m budgeting a modest amount for cloud costs.
I tried to open an OCI cloud account was declined several times after providing valid information and credit card. On one attempt the sign up process actually completed but then nothing, no email, no confirmation.
Support was unhelpful; to paraphrase, “we declined it, and wont tell you why”.
The general consensus I gather online regarding OCI from the standpoint of small business owners and developers is that Oracle has no regard for small customers and will routinely decline and terminate both free and paid accounts at will, and that the treatment I’m experiencing is a sign of things to come.
It seems like offering cloud services for free caused Oracle to deploy very aggressive abuse detection tech and processes. I’m having a hard time believing that a multi-billion dollar company can operate with such perverse outcomes on purpose. How could that be the case when Oracle selling the cloud as more reliable than running your own hardware?
I still want to use the Oracle Cloud. But how can I try to build a business around OCI & APEX while living in fear of my work or data being destroyed on some automated whim?
The amount of fraud, bad actors, and bots employed by folks to setup Bitcoin miners and the like…is INSANE.
That doesn’t mean legitimate people can’t use our services.
Your one successful sign up, should have left you with credentials you could use to sign in to Cloud console, is that true?
If you send me your details at [email protected], I can poke around with the fraud detection team to see what’s what with your account.
Also, sorry you’re first experience here isn’t a great one.
Hi Jeff, I asked this against https://www.thatjeffsmith.com/archive/2019/01/why-wont-my-insight-work/, but that’s an old post, so you may not have noticed it :
Hi Jeff, I’ve just moved from 21.4 to 22.2, and the above information (section 3) shows me why Completion Insight has broken for me.
The insight query has been changed to include “and owner in (‘PUBLIC’,’SYS’,user)”.
We use personal read only logins to access production for data mining, the logins do not own any of the objects – they are all owned by the relevant schema owners – so how do I remove this clause, or change the schema owners that are checked?
all_tables/ all_objects are already restricted to the objects that my user has access to, so I’m not seeing much value being added by the new addition?
It’s looking in PUBLIC, SYS to grab synonyms, and ‘user’ for things that belong to you.
There’s no way for the user to change the behavior.
This is the behavior in SQLcl and SQL Dev, latest versions, 23.x
user only has CONNECT role
user has select/read privs on 3 tables in HR schema
INSIGHT pulls up all 3 objects from HR schema, when logged in as LOW_PRIVS user
Apologies, I clicked the ‘reply’ below your post, rather than at the top of mine :
Thanks for the swift response Jeff, if you just try to autocomplete
‘select * from dep’
does it find ‘hr.department’ ?
We have tables under multiple schemas, and I generally recall the table name (or part of it), but not the schema.
So I used to type in
‘select * from aud’
and hit autocomplete, and get the options to complete to ‘library.aud_audit, or program.aud_audit_date_table’.
Is there any way to get this behaviour back?
The users generally have select_catalog_role, rather than individual grants for each and every table if that makes any difference.
Hi Jeff,
I am trying to use SQLPLUS in Windows to connect with Autonomus Databases (ATP) using wallet files.
I could connect to one DB without any issues but, another DB gives me some trouble.
———
— Log —
———
C:\Users\pmoganti>set TNS_ADMIN=C:\Pradeep\Softwares\Oracle_Instantclient_19.21\network\admin\Wallet_YLPATP**D
C:\Users\pmoganti>sqlplus ylpoictst@ylpatp**d_low
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Nov 17 11:48:56 2023
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter password:
Last Successful login time: Fri Nov 17 2023 11:43:18 -08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.21.0.1.0
SQL> SELECT * FROM dual;
D
–
X
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.21.0.1.0
C:\Users\pmoganti>set TNS_ADMIN=C:\Pradeep\Softwares\Oracle_Instantclient_19.21\network\admin\Wallet_YLPATP**MO
C:\Users\pmoganti>sqlplus admin@ylpatp**mo_low
SQL*Plus: Release 19.0.0.0.0 – Production on Fri Nov 17 11:49:52 2023
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
When I run the SQLNet trace, I see something strange
———————————
— Successful connection trace —
———————————
(29784) [17-NOV-2023 11:49:08:048] nnftmlf_make_system_addrfile: entry
(29784) [17-NOV-2023 11:49:08:048] nnftmlf_make_system_addrfile: system names file is C:\Pradeep\Softwares\Oracle_Instantclient_19.21\network\admin\Wallet_YLPATP**D\tnsnames.ora
(29784) [17-NOV-2023 11:49:08:048] nnftmlf_make_system_addrfile: exit
(29784) [17-NOV-2023 11:49:08:048] nnftboot: exit
(29784) [17-NOV-2023 11:49:08:048] nnftrne: entry
(29784) [17-NOV-2023 11:49:08:048] nnftrne: Original name: ylpatp**d_low
(29784) [17-NOV-2023 11:49:08:048] nnfttran: entry
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: — C:\Pradeep\Softwares\Oracle_Instantclient_19.21\network\admin\Wallet_YLPATP**D\tnsnames.ora TABLE HAS THE FOLLOWING CONTENTS —
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: ylpatp**d_tpurgent = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522) *** (security=(ssl_server_dn_match=yes)))
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: ylpatp**d_tp = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)***(security=(ssl_server_dn_match=yes)))
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: ylpatp**d_medium = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)***(security=(ssl_server_dn_match=yes)))
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: ylpatp**d_high = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)***(security=(ssl_server_dn_match=yes)))
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: ylpatp**d_low = (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)***(security=(ssl_server_dn_match=yes)))
(29784) [17-NOV-2023 11:49:08:050] nncpdpt_dump_ptable: — END C:\Pradeep\Softwares\Oracle_Instantclient_19.21\network\admin\Wallet_YLPATP**D\tnsnames.ora TABLE —
(29784) [17-NOV-2023 11:49:08:050] nnfttran: exit
(29784) [17-NOV-2023 11:49:08:050] nnftrne: Using tnsnames.ora address (description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)***(security=(ssl_server_dn_match=yes))) for name ylpatpd***d_low
—————————–
— Failed connection trace —
—————————–
(30732) [17-NOV-2023 11:50:15:357] nnftmlf_make_system_addrfile: entry
(30732) [17-NOV-2023 11:50:15:358] nnftmlf_make_system_addrfile: system names file is C:\Pradeep\Softwares\Oracle_Instantclient_19.21\network\admin\Wallet_YLPATP**MO\tnsnames.ora
(30732) [17-NOV-2023 11:50:15:358] nnftmlf_make_system_addrfile: exit
(30732) [17-NOV-2023 11:50:15:358] nnftboot: exit
(30732) [17-NOV-2023 11:50:15:358] nnftrne: entry
(30732) [17-NOV-2023 11:50:15:358] nnftrne: Original name: r!$2K9a9#5*Mo
(30732) [17-NOV-2023 11:50:15:358] nnfttran: entry
(30732) [17-NOV-2023 11:50:15:360] nnfttran: Error querying r!$2K9a9#5*Mo of attribute A.SMD errcode 408
(30732) [17-NOV-2023 11:50:15:360] nnfgrne: Query unsuccessful, skipping to next adapter
(30732) [17-NOV-2023 11:50:15:360] nnfgrne: Switching to HOSTNAME adapter
(30732) [17-NOV-2023 11:50:15:360] nnfhboot: entry
(30732) [17-NOV-2023 11:50:15:360] nnfhboot: exit
(30732) [17-NOV-2023 11:50:15:360] snlinGetAddrInfo: entry
(30732) [17-NOV-2023 11:50:15:364] snlinGetAddrInfo: getaddrinfo() failed with error 11001
(30732) [17-NOV-2023 11:50:15:364] snlinGetAddrInfo: exit
(30732) [17-NOV-2023 11:50:15:364] snlinGetAddrInfo: entry
(30732) [17-NOV-2023 11:50:15:364] snlinGetAddrInfo: getaddrinfo() failed with error 11001
(30732) [17-NOV-2023 11:50:15:364] snlinGetAddrInfo: exit
(30732) [17-NOV-2023 11:50:15:364] nnfhrne: Error forming address for r!$2K9a9#5*Mo, errcode 406
(30732) [17-NOV-2023 11:50:15:364] nnfgrne: Query unsuccessful, skipping to next adapter
(30732) [17-NOV-2023 11:50:15:364] nnfgrne: Switching to EZCONNECT adapter
(30732) [17-NOV-2023 11:50:15:364] nnfhboot: entry
(30732) [17-NOV-2023 11:50:15:364] nnfhboot: exit
AFAIK, the failed connection is taking pasword as original name or dbname. I am not sure why it is happening though the connection string is valid.
I also verified the tnsnames.ora file comes with wallet file and it looks accurate to me. Also, the connect with wallet is successful from SQL Developer but not for SQLPLUS.
Wanted to seek your feedback on this.
Why aren’t you using SQLcl? It’s so much easier, modern, and has more features than SQLPlus.
Thanks for the comment Jeff.
I tried SQLcl, I still see some errors while connecting to ATP DB.
c:\Pradeep\Softwares\SQLcl\bin>sql ADMIN@”(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g3255831702f194_ylpatpde***o_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))”
SQLcl: Release 23.3 Production on Fri Nov 17 15:41:57 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Password? (**********?) ***************
USER = ADMIN
URL = jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=g3255831702f194_ylpatpd***o_low.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))
Error Message = Listener refused the connection with the following error:
ORA-12529, TNS:connect request rejected based on current filtering rules
(CONNECTION_ID=rH/1Tx4iQmulVa/n+926Zw==)
I am sure this DB is a mTSL databases and it can be accessed anywhere.
Read the error message, there’s a network rule restricting access to your database based on your IP address, and your network location isn’t from the known, allowed list.
Hi Jeff, and it you just try to autocomplete
‘select * from dep’
does it work?
We have tables under multiple schemas, and I generally recall the table name (or part of it), but not the schema.
So I used to type in
‘select * from aud’
and hit autocomplete, and get the options to complete to ‘library.aud_audit, or program.aud_audit_date_table’.
Is there any way to get this behaviour back?
The users generally have select_catalog_role, rather than individual grants for each and every table if that makes any difference.
Type the schema you want to query from…
I’m away from the keyboard, I can check next week what current 2023 behavior is.
Other option, create synonyms in your schema to those tables.
Hi Jeff,
I’m currently working with ORDS API’s and I want to send a complete query along with the API dynamically to the handler instead of predefining it, can I do that? If yes how can I send complete query instead of mentioning just parameters?
Just use the REST Enabled SQL APi we ship with ORDS then, that’s your ticket to success.
The query goes in the body of the request, NOT via URL (parameters) — very very bad idea.
Thanks for your reply Jeff,
I’m actually trying to call the API via Java application and pass the SQL query to the API from the application, could you provide some more insights on how to do it?
Sure, use this built-in endpoint for running ad-hoc SQL
Docs
Hi Jeff,
We are using SQL Developer to connect to an ATP database and some queries. We have some confidential information in our system that is accessed by some of our engineers. Since SQL Developer requires a static password we share the passwords using secured applications. But, we cannot restrict the engineers to stop copying the passwords to different places. Our security team sees this as a risky approach.
To avoid such security issues, is there a way for us to authenticate with DB without static username/password? Something like IAM users etc..
Thanks,
Pradeep.
It should be possible now as jdbc driver supports it
https://blogs.oracle.com/cloud-infrastructure/post/oracle-autonomous-database-user-management-and-authentication-is-now-integrated-with-oci-iam
Also, users should never be sharing credentials/passwords, every person gets their own account.
Thanks for prompt reply Jeff!
I have gone through the article and its very interesting. The article covers the connectivity with SQL Plus and SQLCLi only. Is there a way we can use the IAM Authentication method with SQL Developer?
Sqlcl uses jdbc driver so should also work for sqldeveloper
SQLcl Ignores JAVA_HOME If Java Is Found In PATH (Doc ID 2985057.1)
Hey Jeff,
we want to config timeout on the ORDS rest services,
what is the way to do it?
and what is the base practices for the timeout value?
env:
tomcat 9
ORDS 20.04
thank you.
Best practice #1 – keep your ords up to date, 20.4 is from December of 2020, and current release is 23.3, from 2 weeks ago.
I don’t have a best practice for timeouts, you’ll need to share what problem you’re trying to solve, and then I can share some advice.
Jeff
Hi Jeff,
Regarding statusbar. Now if correct it has DBID which is actually connect string. Why not to have possibility to add to statusbar db name, instance name, maybe container name, database role , maybe current schema. To allow to configure status bar for different scenarios. Maybe it is too much or it maybe give ability to configure statusbar so, just by looking on it will be clear where your session is connected.
Thank you ,
Evgeny.
It’s not too much to ask. The feature has been around for several years now, and we’ve not received much feedback on the subject.
Hi Jeff,
I’ve got a weird problem with SQLD so I don’t expect you to have an answer, but I’m hoping you can point me toward one. I was just provided with a new computer. I managed to get everything set up relatively painlessly. When I open SQLD 23.1 it’s fine. I imported connections, snippets, and reports just fine. I can connect to databases just fine. But when I run a query and the query view has data the entire display flashes black and then individual windows go black (though some contents may display if I run the mouse cursor over them… just not always). The only way to get the screen to behave is to shut down SQLD.
I had thought maybe it was an issue with SQLD 23.1 so I installed 22.2.1 and I’m having the same issue with it. I was running 22.2.1 on my old machine without issue. This is a Windows 10 machine with jdk1.8.0_391 though I’ve got copes of 1.8.0_251 for the Oracle Client 19.3 (it wants the msvcr100.dll file) and I added a copy of jdk-11.0.21 as well as jdk17.0.9. I pointed SQLD at both of 11 and 17 via product.conf but it didn’t help. The tech services folks have already replaced the video drivers (and all other drivers).
The only thing that seems to trigger the problem is query viewer in SQLD so I’m hoping you can provide some information on what it’s doing in the background and something I might try/investigate to fix this. I need SQLD to work so….
In preferences try setting look and feel to native/Windows instead of Oracle.
Hi Jeff, that didn’t help. It took it an extra 3-4 seconds for he screen to black out and start flickering and other windows on the desktop to go completely black, but no real difference.
Ask for a new computer, maybe even a Mac?
Or seriously ask if they can get you up to Windows 11.
I work on a Mac at home, but this is my office machine (my office is still in NYC but I live in Jacksonville FL and haven’t seen my office in 3.5 years). I’ve considered the new computer request but this was a “special” upgraded machine they ordered for me. Not sure they’ll get another one. It’s got to be some kind of compatibility issue… I just can’t think of what it could be. If you do think of something else I could try please let me know.
It’s usually the video drivers.
Maybe the FX stuff is making it cranky, be sure to close the Welcome/Start page.
Hi All, I’ve done some searching but can seem to find the answer. Do i need to update java when using SQL developer? I just started updating my JRE’s for the latest CPU but can’t seem to locate guidance on SQL developer I’m on 23.1 of SQL developer with the built in JDK, I’ve updated the windows JRE with the windows installer.
I highjacked another thread above. Can someone delete that?
Hi Jeff,
In SQLcl statusbar is any possibility to work with color? Like have a background of statusbar set to one color and font in statusbar to other color?
Thanks,
Evgeniy
No but i like where you’re going there with that idea.
Hi Jeff:
Is there a way to recover connections?
Sql Developer hangs up frequently, especially when the query has some error. I posted question on OTN (link: https://community.oracle.com/mosc/discussion/comment/16963638#Comment_16963638), followed the suggestions as given on the forum.
Solution provided was to clear up data under roaming profile and increase Java heap space memory.
It did not resolve issue. However, I found my connections.xml is gone.
Is there a way to recover it back?
Appreciate your help,
Thank you,
Srinivasan
If you nukes roaming profiles, you deleted your connections.
That’s why I always suggest you RENAME that directory.
Also any recent copy of sqldev will have a connections JSON file, not XML.
Hi Jeff,
is it possible to using the JDBC drivers provided by Amazon for Athena, and using SQL Developer as the client to query Athena data?
I would be very grateful for every advise.
Kind Regards
Ilyas
Sorry, but the answer is ‘No.’
Where do my snippets get stored in SQL Developer?
In the app
No helpful. I mean where in the filesystem.
I recently changed laptops. I lost all my snippets because I didn’t know where to copy them from.
They’re in the SQL developer folder, somewhere.
turns out they’re in ~/.sqldeveloper/UserSnippets.xml
or
C:\Users\MyUserID\AppData\Roaming\SQL Developer\UserSnippets.xml
Why does sqlcl do this:
# Utility function to get java version
function jdk_version() {
local result
local java_cmd
if [[ -n $(type -p java) ]]
then
java_cmd=java
elif [[ (-n “$JAVA_HOME”) && (-x “$JAVA_HOME/bin/java”) ]]
then
java_cmd=”$JAVA_HOME/bin/java”
fi
instead of this:
# Utility function to get java version
function jdk_version() {
local result
local java_cmd
if [[ (-n “$JAVA_HOME”) && (-x “$JAVA_HOME/bin/java”) ]]
then
java_cmd=”$JAVA_HOME/bin/java”
elif [[ -n $(type -p java) ]]
then
java_cmd=java
fi
With the sqlcl ‘sql’ script as it is, you end up with:
[oracle bin][]$ echo $PATH
/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/bin
[oracle bin][]$ which java
/usr/bin/java
[oracle bin][]$ java -version
java version “1.8.0_361”
Java(TM) SE Runtime Environment (build 1.8.0_361-b08)
Java HotSpot(TM) 64-Bit Server VM (build 25.361-b08, mixed mode)
[oracle bin][]$ export JAVA_HOME=
[oracle bin][]$ $JAVA_HOME/bin/java -version
java version “17.0.6” 2023-01-17 LTS
Java(TM) SE Runtime Environment (build 17.0.6+9-LTS-190)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.6+9-LTS-190, mixed mode, sharing)
[oracle bin][]$ ls
dependencies.txt license.txt README.md sql sql.exe version.txt
[oracle bin][]$ ./sql /nolog
Error: SQLcl requires Java 11 and above to run.
Found Java version 8.
Please set JAVA_HOME to appropriate version.
should be
export JAVA_HOME=/path/to/jdk17
If you set Java home to 17, does it work?
I *AM* setting JAVA_HOME to jdk-17
Then it should be picking up the java17 home
It does pick it up. It just ignores it.
I’ll see about it tomorrow.
Hello,
I have installed the Apex 23.1 in one of my pluggable databases. I have also installed ords-23.2.3.242.1937 on a Windows 10 client. When I start ORDS standalone server and use url http://localhost:8080 , I get the ORDS Landing page and when I click on Apex box on that page , I get Apex’ login page and I can login. The problem is when I want to use Tomcat 9.0 as a webserver. I copy the Apex image files webapps/i folder in Tomcat root directory and I copy ords.war from ORDS folder to webapps folder as well. but when I try the url: http://localhost:8081/ords I get 404 error (The requested resource [/ords] is not available).
I have set the env. variables JAVA_TOOL_OPTIONS and _JAVA_OPTIONS to -Dconfig.url=C:\app\Conf which my ORDS config directory.
I should mention that it worked with previous versions of ORDS and Tomcat 9.0 but not with 23.1.
what Am I doing wrong? please help.
23.2 Doc for deploying to tomcat –
6.1.3.3 Deploying ORDS on Apache Tomcat
Tip:The Oracle REST Data Services file ords.war must be available before you start this task.
Note: When you run ORDS in standalone mode using the ORDS serve command, you can specify the location of the configuration directory. For deploying the ords.war in a supported container such as Apache Tomcat, you must specify the configuration directory location. To do this, use one of the following approaches:
The recommended approach is to generate a Web Application archive file with the configuration directory location specified for that war file.
Set the config.url system property in the managed server so that it applies to all the ORDS deployments.
To generate the war file, use the ords war command.
To set the config.url system property, before starting Apache Tomcat, run this command: export JAVA_OPTS=”-Dconfig.url=/scratch/my_ords_config”
Hey Jeff,
we are using ORDS 20.04 , we create war file and run it inside tomcat application server.
there is any default hatcheck that i can use? and what is the prerequisite for that?
Health health to check for what?
And endpoint to get a response?
I would say it depends what you’re using ORDS for, so what are you using ORDS for?
And when are you planning on upgrading, as 20.4 is 4 years old.
Hi Jeff,
We have a Oracle 19c CDB with 128 Go RAM, 8 cores / 16 threads. We are working with materialized views, these are requested by ORDS 22.4.
Load testing are limited when we simulate 50 concurrent virtual users. The response time increase until 30 sec and more.
Have you an idea ? a missing parameter in ords config or Oracle DB ?
Thanks for your reply.
Regards
How fast are the queries in general?
50 users doesn’t sound like much, but if it’s a 2-3 second query and your pool is only 10 users, you’ll quickly overwhelm the system.
Hi Jeff,
Our target is more than 250 simultaneous users. Queries are under the second and our pool is set to 60 with a max size set to 250 ! I don’t know if a typical configuration exist somewhere.
And we have randomly truncated json when we stress the APIs.
Regards
There is no such thing as a typical config pool.
Is it possible that database is coming under pressure as well?
Truncated responses are odd, do you see anything in ords server logs?
Less than a second isn’t really fast if that’s .75 seconds, you’ll still have connections pile up under load.
Is the pool actually growing to 250 max under load?
Finally, version of ORDS?
Sorry you said 22.4
That’s almost a year old now, definitely time to start planning an upgrade.
Hi ! thanks for your reply.
Is there an existing data cache ? or an Oracle solution. We are already using the cache metadata.
Regards,
Jérôme
We need to figure out what/why is slow, before we just start throwing silver bullets at your database.
Any news on SQL Developer in VS Code?
I can never get the text scaling in SQL Developer right, but having it in VS Code would fix that completely!
Several more weeks. I would say “I promise,” but I’ve already broken that too many times with this project. But soon!
Hi Jeff,
I work for a company that provides data protection through data tokenization/encryption. We have this JDBC interception tool, that tokenizes/protects data that is being moved from/to the database.
Recently, we have many Oracle customers asking if they could use our interception technology with SQL Developer.
During tests we managed to make the interceptor work (partially), as we can see (log) the JDBC calls happening.
One thing I noticed is that the actual query being executed isn’t issued directly. It seems that it is passed as a parameter to another query (or stored procedure?).
And the result set isn’t actually what we expected. It looks like the data is transformed and there are no clear column names/values being returned, but instead we always see those getString method calls for 4 columns that are not part of the actual query that was executed.
As we depend on the table names/column names to identify if the data needs to be processed, our detokenization/decryption process isn’t working.
Is there any way to tell SQL Developer to use
Could you help us , giving some hints on how could we intercept the actual SQL statement that is being issued and on how to map the result set to it? Or direct us to someone that could help us on this “mission” ? Maybe there is a way to make SQL developer execute the queries in a more standard way ?
Any help appreciated !
Thanks
Walcir
You can see in sqldeveloper’s log panel exactly what is sent to the jdbc driver to be executed in the Statements panel, including binds