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,301 Comments
I am not sure why SDLDev give a different result than SQLcl
Both are version 23.1
with num_rows as
(select
1 + REGEXP_COUNT(””||’p~Keyword~String’||””,’\s*’||’~’||’\s*’,1,’i’) n from dual
)
,nums as
(select rownum rnum
from dual
connect by rownum <= (select n from num_rows)
)
select
'p~Keyword~String' Keyword_String
,rnum position
,regexp_substr('p~Keyword~String','([^'||'~'||']+)',1,rnum) keyword
from nums
order by rnum;
Output from SQLDev 23.1
KEYWORD_STRING POSITION KEYWORD
—————- ———- —————-
p~Keyword~String 1 p
p~Keyword~String 2 Keyword
p~Keyword~String 3 String
Output from SQLcl 23.1
KEYWORD_STRING POSITION KEYWORD
___________________ ___________ __________
p~Keyword~String 1 ~
p~Keyword~String 2 ~
p~Keyword~String 3
SQLcl 22.4 give the same result as SQLDev 23.1
Your code doesn’t run for me at all, in either tool/version
Error at Command Line : 3 Column : 18
Error report -
SQL Error: ORA-01741: illegal zero-length identifier
01741. 00000 - "illegal zero-length identifier"
*Cause:
*Action:
Is there a functionality in SQL Modeler when creating new tables to insert from a list of predefined columns (incl. column name, data type and RDMBS comments)? I see in the “domain”-functionality the possiblity to setup UDP for data types (which covers only small portion of the usual effort), but I dont see also the possiblity to setup column name and RDMBS comments.
This is the post you’re looking for.
Hi Jeff,
I’ve deployed ORDS 22.4 and am using the PDB Lifecycle APIs for automated deployment of dev emvironments (works a treat!). However I seem to have bricked the default pool in that I installed a new database pool but pointed it to the same database as the default pool.
I want to use the default pool for database connection reporting and was thinking of creating another database (ORDSADMIN) as the default pool and just used for ORDS admin activites.
So can I re-install the default pool. I have RTFM’ed
cheers
Tracey
Pools are just XML files the only thing installed in the database are database users, created for those pools, usually ORDS_PUBLIC_USER.
You can have as many pools (collection of connections) as you want to the same database.
What’s the actual problem you’re running into?
My problem is that the default pool no longer works. Using postman, I get an error about the connection pool as follows:
“The connection pool named: default is a template pool and cannot be connected to directly.”
In the ORDS log the section where the pools are listed has /ords/ => default => INVALID.
Checking database account passwords:
ORDS_PUBLIC_USER – good
db.cdb.adminUser – good
I have 3 other functional database pools configured for PDB Lifecycle management for other databases as well as the default pool database.
You have a cdb install, it’s expecting you to go to /ords/cdb/pdb1
Default pool to this cdb, there is nothing mapped to /
Yes it is a CDB install in all my databases and I wanted to use the ORDS reporting on database connections across all database pools as we could end up with quite a few.
My impression is that this reporting is accessed from the default pool?
Thanks
T
/pdbN/…/ Will be your uri if the default pool is a CDB
So following the example in doco about enabling instance API for monitoring ORDS they provide the following URI:
Send GET request to /ords/_/instance-api/stable/status
Is this reporting across all database pools only available from the default pool?
Thanks
OK. Changed the URL to the new pool and it worked!
as follows:
ords/cdbpool/_/instance-api/stable/status
But it only shows pool in cdbpool.
I was looking for the API that shows all pools. Will continue to play.
Hi Jeff,
The current SQL Developer PL/SQL table API’s that are generated are not as nice as the QuickSQL-based ones.
Is there a way to use SQL Developer (I’m using v23.1) to generate QuickSQL-like very nice PL/SQL table API’s?
Some trick or workaround to get it done if it’s not built in?
If not, please update the table API generator in SQL Developer to generate the nicer version of table API’s that QuickSQL in APEX generates?
Thanks, Wim
The SQLDev PL/SQL APIs are old, and the QuickSQL ones are less old. We want to marry the two in SQL Developer Next/Web/SQlcl, etc. Stay tuned.
Wonderful, thanks Jeff.
Ideally. from the Physical model’s DDL export, have an option to create the PL/SQL table API’s with all relations between tables already accounted for.
Cheers, Wim
Hi Jeff,
I am trying to comit my design changes using the git tool in SQL Developer Data Modeler, but a version control error appears with an *Error: Index 84 out of bounds for length 84* .
Do you have any idea what can be the problem?
Thank you,
Amine
Can you post this to the data modeler user forum and I’ll have the developers take a look.
Do you mean this forum? https://forums.oracle.com/ords/apexds/domain/dev-community/category/sql-developer-data-modeler
Yes
Thank you Jeff for the quick reply.
It is now posted in the forum with the screenshort.
Jeff: Thanks for all the great content. I am using sql developer 22.2. Is there a way to yellow highlight selected text within the code editor? If not, I would find it a useful feature. Thanks, Brian
Yes
Jeff: Thanks for reply. Not exactly what I meant. This mostly pertains to comment sections that accompany code in my *.sql files. I would like to permanently highlight certain important text so it stands out when I open it months later (bad memory). The same result that is accomplished in other editors (like Notepad++) by right clicking a block of text and selecting a yellow highlighter. Thanks, Brian
You want to permanently mark it, like with a highlighter? No, that’s not a feature, sorry.
Hi Jeff
I am trying to find where I can put the JDBCproperties below for all basic connections (and I assume basic uses JDBC right?)
OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL=REQUESTED
OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES=AES256
OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_LEVEL=REQUESTED
OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES=SHA512
I think I can put them in the advanced parameters for each connection, but would rather this a default for all connections I make?
I know how to do this with sqlnet.ora but I am not using the oracle client so want a similar behaviour with JDBC.
Many thanks
Neil
There’s no way to default that on a connection, it seems it would be easier to just setup a Client, and do your SQLNET setup with OCI/Thick connections.
Or setup a base connection, and clone/copy it for new ones so these are defined by ‘default.’
Hello Jeff
I am using SQL Developer – Version 19.4.0.354 Build 354.1759 accessing Oracle 19.15.0.0.0
Version databases.
IRS is requesting that we have a banner when opening a connection. I find something on Oracle by adding a file and having preferences/database/startup connection. However that is not a secure way and many other pitfalls.
Do you know of a way to pick up information when a connection is opened ?
Thanks
Frank
See “Is It Possible to Display a Warning Banner Before a Successful Login to 10g Database? (Doc ID 1216123.1)”
TL;DR – set these on your server
SEC_USER_UNAUTHORIZED_ACCESS_BANNER = /somewhere/secure/unauthaccess.txtSEC_USER_AUDIT_ACTION_BANNER = /somewhere/secure/auditactions.txt
Thank you Jeff for your quick response. We will give that a try. I have to work with another agency to put in your suggestions and that will take a long to test.
Thank you again
Frank
Great – thanks for the confirmation Jeff
Hi Jeff,
I have Oracle 19c on my windows server (normal, pdb not enabled).
I want to install ords 23.1 on this database and I do NOT want to install apex (just ords).
I want to know how to install it as most of videos on youtube copy “images” directory from apex and (it didn’t work with me anyway).
Thank you.
None of my videos talk about ‘copying images’ from APEX for installing ords. Have you seen/followed the Installation instructions in the Install & Configuration guide?
You effectively do this
ords --config /path/to/config/folder installThen answer the questions.
I have done this but I experienced the following issue:
“404 Not Found
2023-04-18T12:30:12.965698800Z | Lxr_a5T7gT3mbX5I-OvdsQ | ”
This issue happened when I tried to open the following URL:
http://localhost:8080/ords/hr
I am trying to start the service using the following command:
ords –config C:\oracle\ords\conf serve
I am using database 19C normal not container. I am also using “jdk-20_windows-x64_bin”
Thank you.
We haven’t tested or certified any java over version 17, FYI. – also what are you expecting to be mapped to /ords/hr ? The 404 response there is correct.
I have a question regarding ORDS connection pools which I can’t find referenced anywhere. I have done an extremely simple test of how the ORDS connection pool MaxLimit works and, as far as I can see, ORDS only ever uses *half* of the max limit before it gives 503 errors when acting as a PLSQL Gateway (ModPLSQL Replacement).
To reproduce
1. Install ORDS, obviously
2. Set InitialLimit, MinLimit and MaxLimit to 10 and a small ConnectionWaitTimeout (1s) so timeouts happen faster
3. Add one ModPLSQL procedure (this was raw htp.p rather than Apex, but I would imagine it is the same) which has a link to a second procedure
4. On the second procedure make it have a 10 second timeout (dbms_lock.sleep) to replicate something being slow
Now monitor the database active sessions whilst clicking on the link. On windows, ORDS 22.4.4 I see
1. 10 connections created initially, all idle, as expected
2. As I click the link one of the sessions goes to Active until I get to 5 active. The 6th attempt gets a 503 error.
Is this expected behaviour – I was thinking it might be one connection for fetching procedure metadata, but that seems very wasteful.
No, that pool let you borrow all 10 connections before you get that error.
Hi Jeff.
I realise that is what *should* happen, but isn’t what I see with a very basic test script. To save you the maximum time possible I have documented as much of it as possible below. The comments seem to mangle html / xml unfortunately
//create a user to hold the procedures
create user ucptest identified by ucptest;
//grant them dba – don’t do in production 🙂
grant dba to ucptest;
//grant execute on sleep
grant execute on dbms_lock to ucptest;
//create a user for the connection pool
create user ucpuser identified by ucpuser;
//allow them to connect
grant connect,resource to ucpuser;
//connect to the schema to create the procedures
conn ucptest/ucptest
//create a procedure to show links
create or replace procedure showlinks is
begin
htp.p(‘
Sleep
‘);
end showlinks;
/
//create a procedure to sleep for 10 secs and then say “finished”
create or replace procedure sleep is
begin
dbms_lock.sleep(10);
htp.p(‘finished’);
end sleep;
/
//grant execute on the procedures to public for simplicity
grant execute on showlinks to public;
grant execute on sleep to public;
//now simply set up a connection pool with the following properties
basic
localhost
1521
[service]
ucpuser
10
10
10
1
false
false
direct
Now open the URL in chrome i.e. http://[host]:[port]/ords/ucptest.showlinks and click on the link multiple times whilst monitoring the number of sessions showing as active – it should get to 5 and then stop going up any more. You should instead start getting 503 errors.
This would be perfect – for you top open a Service Request with My Oracle Support. ORDS support comes along with your Database License/Support contract.
Hi Jeff,
I have a procedure with table type as an out parameter (a table of object).
When I try to execute it using SQL developer, it returns an error If I try to show the table out parameter in the output.
Normal scalar parameters are successfully shown (if the table type is not shown).
Is there some way to display such parameter type in the output upon execution?
Thank you.
How about an example? I could work one up, but good chance it won’t be the same as yours. Plus I have many people coming for help, so if you can give me a running start, better chance i’ll have time to help help you.
Hi Jeff,
here is the example:
–In HR Schema, I created the following objects:
CREATE TYPE EMP_OBJ AS OBJECT (
F_EMP_ID NUMBER(12),
F_FIRST_NAME VARCHAR2(50),
F_LAST_NAME VARCHAR2(50),
F_JOB_ID VARCHAR2(50),
F_SALARY NUMBER(15,3)
);
/
CREATE TYPE EMP_ARR AS TABLE OF EMP_OBJ;
/
Then I created the following procedure:
CREATE OR REPLACE PROCEDURE GET_EMPS_BY_DEPT_ID (
I_DEPT_ID IN EMPLOYEES.DEPARTMENT_ID%TYPE,
O_EMP_ARR OUT EMP_ARR
)
AS
BEGIN
SELECT EMP_OBJ(
E.EMPLOYEE_ID,
E.FIRST_NAME,
E.LAST_NAME,
E.JOB_ID,
E.SALARY
)
BULK COLLECT INTO O_EMP_ARR
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = I_DEPT_ID;
END;
/
Now I want to execute the procedure in SQL developer using “Run” option and show the output of the procedure of parameter “O_EMP_ARR”.
Even in the auto-generated code, the line to show the output array is paused.
Here is the auto-generated code:
ECLARE
I_DEPT_ID NUMBER;
O_EMP_ARR HR.EMP_ARR;
BEGIN
I_DEPT_ID := 50;
GET_EMPS_BY_DEPT_ID(
I_DEPT_ID => I_DEPT_ID,
O_EMP_ARR => O_EMP_ARR
);
/* Legacy output:
DBMS_OUTPUT.PUT_LINE(‘O_EMP_ARR = ‘ || O_EMP_ARR);
*/
–:O_EMP_ARR := O_EMP_ARR;
–rollback;
END;
My question is how to display the output of such types in SQL developer.
Something rather than “DBMS_OUTPUT” of course.
Thank you.
To save you as much time as possible trying this, here is the majority of a script – obviously, can’t create databases or set up ORDS but this is the majority of a simple test
//create a user to hold the procedures
create user ucptest identified by ucptest;
//grant them dba – don’t do in production 🙂
grant dba to ucptest;
//grant execute on sleep
grant execute on dbms_lock to ucptest;
//create a user for the connection pool
create user ucpuser identified by ucpuser;
//allow them to connect
grant connect,resource to ucpuser;
//connect to the schema to create the procedures
conn ucptest/ucptest
//create a procedure to show links
create or replace procedure showlinks is
begin
htp.p(‘
Sleep
‘);
end showlinks;
/
//create a procedure to sleep for 10 secs and then say “finished”
create or replace procedure sleep is
begin
dbms_lock.sleep(10);
htp.p(‘finished’);
end sleep;
/
//grant execute on the procedures to public for simplicity
grant execute on showlinks to public;
grant execute on sleep to public;
//now simply set up a connection pool with the following properties
basic
localhost
1521
[service]
ucpuser
10
10
10
1
false
false
direct
Now open the URL in chrome i.e. http://[host]:[port]/ords/ucptest.showlinks and click on the link multiple times whilst monitoring the number of sessions showing as active – it should get to 5 and then stop going up any more. You should instead start getting 503 errors.
Hi Tom,
Seems that your comment has been added on this topic by mistake or I can’t understand it.
@jeff, I have shared an example above. Please advise how I can show the result output of this array in this procedure.
Thank you.
Hey Jeff,
we are using the ORDS v20.04,
in this version we created new ords instance by doing to command;
java -jar myinstance.war
and the enter all the data of our environment….
and after that we move this myinstance.war to our Tomat webapps,
in the new vesion we saw this way we operated has been changed,
can you please send me guide that explain how to create .war file with the new version of ords?
thank you.
You can find the directions here.
Hey Jeff,
Thanks for the 23free VM ! That’s great. Is there a recommended graceful shutdown procedure? Is a DBA shutdown required followed by a sudo shutdown -h now – required? Or is it safe to just shutdown the VMware from VBox?
Also, what rights/roles are required for a REST-enabled. schema in order get access to ALL of the SQL Developer Web features?
Thanks for your great posts !
Mark
It’s very robust, you can just shutdown the host and it’ll be fine.
It’s Linux on the VM, a ‘shutdown’ or hitting the GUI power button will bring it down gracefully.
You need DBA and PDB_DBA roles for all the features in SQLDev Web.
Hi Jeff, when I generate DDL with Oracle SQL*Data Modeler, it is adding “SUPPLEMENTAL LOG GROUP ” clause. I don’t wanted.
I can’t find where to set up DDL’s generation not include that clause
In Tools –> Preferences –> DDL there isn’t nothing like that
Thank you for your help
Try closing the physical model
Hi Jeff.
Is there a way to keep the code outline visible even when there are errors in the code? I really like this feature but it turn out almost not usable when editing the code.
Thank you
We try, but if the code can’t be parsed because of keywords or syntax issues, then we can’t generate the outline. It’s a chicken-egg or Catch-22 problem.
Maybe parsing pl/sql is harder than parsing, e.g. java, but taking a java class and removing a semicolon anywhere does not break the entire code outline, which is what unfortunately happens when you remove a semicolon in a sql developer editor.
I can tell the parser guy he’s doing it wrong but he literally has a degree and teaches uni classes on writing parsers.
An apology if I was perceived as rude. I am not criticizing your great work but would just like to suggest a way to improve the user experience.
No worries!
Hi Jeff,
I’m new at using ORDS/JSON and find the Oracele docs on REST query filter options a bit difficult to understand … I saw your beer-related post which was helpful. Could you post or point to a couple examples of stringing together a few AND conditions and one or more OR conditions in a query?
Can you also clarify additional REST parameters such as pagesize and offset?
Much appreciated,
Mark
I can do up a more complicated set of predicates for you, no problem.
What specifically are your questions around limit, offset, and pagesize?
Thanks Jeff,
I would like to know about any other parameters for a REST query in addition to query. I thought I read that there was offset and pagesize, but not sure about other parameters. If pagesize is set to 0, can I assume that the # of rows returned will equal the maxPageSize setting that has a default value of 500?
Thanks again,
Mark
To your question here, almost. misc.pagination.maxRows defaults to 10,000.
Hi Jeff,
Please don’t forget about a couple more query filtering examples please!
Like : field1 = a and field2 = b and (field3 = c or field3 = d) and field4 = e
Thanks again,
Mark
I spent hours on this already. How about you give it a try and let me know where you get stuck?
Hi, I’m working in an environment where developers are using TOAD, SQL Developer, and probably something like SQuirreL. I am trying to get everyone using Git for version control. Both SQL Developer and TOAD have built-in interfaces for Git. Are you aware of anyone using them together or any issues trying to get them to work in conjunction with each other as well as possibly a third, non-integrated tool interface? Basically, I wan’t to be able to work in any of them without stepping on each other. I hope my description and question make sense.
Thanks in advance!
Dennis…
They’re all 3 indpendently developed and produced software products/projects. The thing with folks using different IDEs to contribute code on a project comes down to
Hi, Is there a way to update an Oracle table by simply typing in the grid view of results after you’ve done a query of the table?
No, but you can open the table, and edit it from there. This is not a popular answer, but it is THE answer. Some of my best hate mail here, too.
Thanks!
Hate Mail? I’m so sorry to hear this, heck you are just trying to help people and they respond with hate?
Peace,
JuLayne
Welcome to the Internet 🙂
It’s all good!
Hi,
I’m trying to export a table with multiple columns. One of the columns has BLOB in it. The other columns have string of int values in them.
The BLOB seems to be in the XML format.
I tried exporting it using the procedure from your blog :https://www.thatjeffsmith.com/archive/2014/05/exporting-multiple-blobs-with-oracle-sql-developer/.
However, I only have .ctl, .ldr and .sql file in the zip file and I don’t have any file with BLOB data.
Is there an easy way to export the table with blob data (XML format) using SQL developper?
Thank you
Yeah what you read/tried should have given you what you’re needing with addition files representing the BLOBs in the output directory. If you’re not getting that, it sounds like a BUG.
I would need your scenario spelled out so I could test it locally to confirm what you’re seeing.
If you’re moving the data from one Oracle DB to another, then Data Pump is always a great route to travel.
Well, finally, I tried the same procedure without checking the compressed option and it did work. Don’t know why the compressed option created a problem.
Thank you again for you BLOG!
Antoine
I should/will take out the COMPRESS option.
I have a table with multiple CLOB columns that I export to a 2003 xlsx file format using SQL Developer. Works great, can save the xlsx as a csv if required. Unfortunately, when exporting as a CSV, then attempting to save as an xlsx doesn’t work as well. CLOBS seem to wrap, etc.
I want to automate the export of data to an XLSX file — can you add this SQLFORMAT option to SQLcl?
Thanks,
Mark
Search here for cart and excel, that’ll give you the path you COULD take.
Can the delete, put, and post verbs be disabled for an auto-rested table?
Thanks,
Mark
No, but put a VIEW over your table, and you get what you’re looking for…IF you don’t add a primary key constraint to the VIEW.
REST enable the view and you have just st the GET endpoints for your data.
Thanks Jeff!
I noticed a feature called CONNECTIONS/CONNS in the latest version of sqlcl that lets you import connections from SQL Developer, but it doesn’t seem complete enough to be useful yet. Any chance this is coming in 23.1 and is the timing of the next version linked to SQL Developer’s integration into VS Code?
In 23.1, you’ll be able to bring in your SQL Developer connections AND you’ll be able to make a database connection via the name of the connection.
And when you create new named connections, that will automatically be seen by SQL Developer Next (coming later this year.)
Yes that’s awesome! And good to have a name to refer to it by.
How do i find base url for ORDS on autonomous database?
Everything you need to know
Hi,
I just upgraded to 22.2.1.234 from 21.4.3.063. When I try to export anything, I get a very long hang (a couple of minutes). Any ideas? I have reinstalled a couple of times, so I am guessing it is something specific to my machine?
Also, when going to Preferences, Database, and then anywhere, it hangs for several minutes (maybe 10 or 15).
It’s a java bug. We’re going to pubish a fix next week, but you can fix it now by grabbing this JDK
https://www.oracle.com/java/technologies/javase/11-0-18-relnotes.html
See this –
BugId Component Subcomponent SummaryJDK-8208077 core-libs java.io File.listRoots performance degradation
Yes and no to suggestions.
Used:
curl -i -k http://localhost:8080/ords/sunseo/sign-in/?username=SUNSEO&r=_sdw%2F
Where I have defined:
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => ‘SUNSEO’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘sunseo’,
p_auto_rest_auth => FALSE);
COMMIT;
END;
And can see services are published in sql developer.
And get 404 error…
curl -i -k http://localhost:8080/ords/f?p=100:101.:0:::::
gives a 200 ok.
Am puzzled.
Thanks for help.
try curl http://localhost:8080/ords/sql-developer
Calling APEX apps (plsql gateway) is a completely different code path then REST APIs (and SQLDev Web)
> It’s a java bug. We’re going to publish a fix next week,
> but you can fix it now by grabbing this JDK
> https://www.oracle.com/java/technologies/javase/11-0-18-relnotes.html
I tried the public download link, and still got 11.0.18 “dot zero” from 17 January, 2023, not 11.0.18.2.
Per MOS Doc ID 1682574.1, 11.0.18.2 is a BPR build, not a publicly-available build.
11.0.18.2 is available on MOS as patch 35046030 (date: 14 February, 2023)
Regards,
Hi, I have a config built on AWS with an Ords standalone EC2 instance connecting to an Oracle SE RDS instance. Have an Apex app working fine. But can’t get either Web Sql Developer or access a REST published service. Have also tried configuring SQL Developer REST connection and in every case get the 404 error. I know this needs a more in depth analysis and have opened a case with AWS support, but wanted to be sure wasn’t missing any AWS EC2 to RDS connectivity specifics or user privs. Any gotchas that might apply to this config you might be aware of. Many thanks Geoff
First things I’d check are
1. ORDS config, is sqldev web enabled?
2. SSH into your machine, can your cURL localhost and get a rest API to work?