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,563 Comments
Using TO_CHAR(charfield,’00’) on a character field that are numbers (‘1′,’2′,’10’). I’m doing this to be able to sort on this field correctly. When I produce script output, this field ends up being real long and makes copying output from script window difficult.
if it’s a character field, the database will automatically sort those ‘numbers’ as characters…so why do you need to to_char a string?
‘1’,’2′,’11’ sorts as 1,11,2 as characters when I want a numerical sort.
Hey Jeff,
Can you provide an example of adding an action to the worksheet menu.
Thanks
No, that’s not my expertise, but we do have a place you can ask for those sorts of things.
Why does Oracle Sql Developer show me tables that I can not query? When I look under connections then tables under the correct connection, I see tables that I do not have access to. Why is that?
you’re seeing the tables the database has decided to let you know about based on your current user’s privilege level in the database. SQL Developer is only querying the data dictionary and showing you what the database gives us to show you.
Hi Jeff, I upgraded from ODM 19.2 to ODM 20.4.
To publish models in 19.2, I went to File | Reports, and chose options. PDF was my preferred output.
PDF is no longer an option in 20.4. Can you bring PDF output back to ODM 20.4?
Thank you, BillC
Can you try using your printer driver?
Hi Jeff, thanks for the quick response. ODM recognizes the “MS Print to PDF” driver, and I can print individual diagrams to PDF.
It’s in the File | Reports dialogue box that PDF is missing.
==> ODM 20.4 output format options are now limited to HTML, XLS, and XLSX
==> ODM 19.2 output format options included HTML, PDF, and RTF
If I misunderstood your guidance on ‘using a printer driver’, please advise.
we had to drop the pdf feature due to security issues with the library we were using to provide said feature…we reckon most people today have printers that support printing to PDF that can suffice for your pdf needs vs native PDF support
Hi Jeff, Thanks for the reply.
It’s an unfortunate situation. I’m limited to the options in the File | Reports dialogue box. You had to remove PDF (and RTF). HTML is not faithful to the diagrams. I’ll recommend that we move off the ODM software.
Why not just have the diagrams printed separately? I’m sure there’s a free solution here somewhere, you just need to change a small thing vs tossing the entire application out. If you choose to do that, I am sorry.
Hi Jeff,
Our model has 57 logical & physical sub-views that are aligned with our major subject areas and minor subjects.
Your recommendation would require individual printing of 57 diagram, then additional software to merge the PDF images and generate a table of contents.
We lose the business/technical metadata that used to come with the File | Reports printing into a PDF, would have to generate that separately into Excel, then print into a PDF, and then merge with the images in the big PDF.
The loss of “File | Reports into PDF” creates a busy Rube Goldberg implementation. ODM is not suitable in our situation. But, thanks for responding.
Hi Jeff,
I am deploy ORDS on the microshapes on OCI as stand alone ORDS deployments. I have to compute nodes, behind the OCI Loadbalancer, with one stand alone ORDS server on each.
For the OCI loadbalancer to work I need an unauthorized healtcheck URL, that does not ping the database. I have looked into the instance_api, but the api requires basic Auth, and the OCI loadbalancer does not have that opportunity for health check URL. Is there any way to deploy a simple page, or is there a simple page/URL that can be used without accessing the database just for checking if the ords server is alive?
Thanks and regards,
Not checking to see if the database pools are available seems somewhat…useless in terms of a health check? If you truly want a static resource to check on the ords process itself, have it look for something in the jetty docroot folder, an html file or image even.
Hey Jeff,
I am wanting to create an action that adds a connection to my web application from its database connection in SQL Developer
I have succesfully added an action that accepts username and password, but im not sure how to actually get it to test a connection to my web app.
Is there where i might use some rest service integration? Can i do that in the action XML?
Hi Jeff,
In Windows 10, installed v18, when run the loading window opens but it hangs on ‘loading’. Any ideas? Thanks.
Upgrade to 20.4
Rename your system18.x.y.z in your AppData folder under Roaming Profiles
Hi,
SQLcl per default filters some commands from history like exit and help: i found this very annoying and i would like to get them back to history. Can this be done? And is there a list of this per default blacklisted commands?
I really would appreciate some kind of a nonhist.xml that is created on first run and that does contain all these blacklisted commands… 😉 Or some config commands inside SQLcl that i can use in my login.sql 🙂
Peter
There’s a setting for it, see ‘show history’ or ‘help set history’
Thank you for your answer, but “exit” e.g. is not listed as a filtered command but IS filtered and i found nothing i can do against it. Even after “set history filter none” it does not appear in the history so this seems to be something special.
Peter
I’d file a bug but I’m not sure why anyone would want to pollute their sql history with ‘exit’
Hi Jeff,
I have i problem with version 20.2.0.175, Systematic insertion of double quotes in the SQL editor causing difficulty to insert text with quotes, character blanc is shifted right.
exemple
select * from table where col = ‘▒
Thanks and best regards
Exactly what character is that? It seems to work for me, i mean, i can copy/paste that string ok…
Hi Jeff Smith,
In our case we have not installed Apex. old environment we had Ohs with mod_plsql module. We are migrating now but in new Ohs version mod_plsql deprecated. As oracle suggested Instead of mod_plsql we are installing Ords 20.3 and ords war file deployed in weblogic 12.2.13.
We getting below SEVERE Error in between while doing installation process. any how we continued to complete installation Process. we should have get message like below “Completed installation for Oracle REST Data Services version 20.3” but we didn’t get this message.
SEVERE Error :
2021-01-25T10:35:29.165Z SEVERE Error cannot determine if an install or upgrade is required because the container list is empty.
Version details FYI
JDK version -jdk1.8.0_181
WebLogic Version – 12.2.1.3
ORDS version -20.3
Oracle – 19c
Detailed OutPut:
Detailed Output FYI.
Enter the location to store configuration data: /wls_domains/ords/config
Enter the name of the database server [localhost]:***.dci.bt.com
Enter the database listen port [1521]:****
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:1
Enter the database service name:**
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Requires to login with administrator privileges to verify Oracle REST Data Services schema.
Enter the administrator username:sys
Enter the database password for SYS AS SYSDBA:
Confirm password:
Connecting to database user: SYS AS SYSDBA url: jdbc:oracle:thin:@//*.dci.bt.com:61***/*****
2021-01-25T10:35:29.165Z SEVERE Error cannot determine if an install or upgrade is required because the container list is empty.
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:1
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:2
Enter a number to select a feature to enable:
[1] SQL Developer Web (Enables all features)
[2] REST Enabled SQL
[3] Database API
[4] REST Enabled SQL and Database API
[5] None
Choose [1]:1
2021-01-25T10:36:41.462Z INFO reloaded pools: []
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2
=========================
Is there anything missing while doing installation of ORDS. Kindly do need full, what might causing issue here?
Thanks,
Rajesh
Tell me about your database, are you using multitenant and is that a CDB you’re connecting to for the install?
Using SQL Developer you can take DDL:s for different tables and take out the data for the same tables. But it’s tedious to repeat the tables every time. There exists a CLI for SQL Developer that I want to use. However, I haven’t found documentation that describes the CLI for SQL Developer (sometimes called SQLcl) in detail. Where is the detailed documentation which for instance describes what happens when you invoke “set ddl segment_attributes on”?
Docs
SQL> help set ddlSET DDL
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
] | ON | OFF ]
These are transforms for DBMS_METADATA which are documented here.
OK, here’s what happens with SQLcl. (I am not sure how this will be rendered on this page, though)
When I connect to a UTF8 instance, the output is returned fine. When I connect to a 1251 local charset instance, it is broken.
—–
SQL> select ‘ж’ from dual;
‘ж’
_______
ж
—–
SQL> select ‘ж’ from dual;
‘?▒’
_______
?▒
Hi,
I have read:
SQL Developer: BLOBs and the External Editor
I am looking for a solution to the more basic question:
I have a BLOB as a field in my oracle database, and I want to extract this
to a PDF file.
How is that done ?
With code? In the GUI you can just hit the download/save button and make sure you use a pdf file extension.
Hello Jeff,
With sqldeveloper Version 20.2.0.175, where is the option to specify the autosave tabs and how often it should save
Thanks
Paul
Hey, Jeff,
We have a problem with character format when it is sent from ORDS to the Oracle DB.
We have ORDS 20.3 under Tomcat 8.5.
When we connect to an Oracle 11 DB, text is sent in the local encoding of the DB (not UTF8).
When we connect to an oracle 19 DB, text is sent as UTF (expected).
We need to force ORDS to work in UTF8 mode when connected to Oracle 11.
Is that possible and if yes, how do we do it?
ORDS is a java application, so everything it does by it’s very nature, is unicode.
I believe you have an issue, and ORDS might even be at fault somehow, but I’m going t need a test case.
Yeah, having a test case is quite complex to arrange.
I was just wondering if ORDS (via jdbc) is somehow determining the character set in which the DB is created. It does say in the ORDS docs that it only works in Unicode.
We have a database in Russian charset 1251. And we expect from OWA to get the letter in Unicode, but it comes as a single byte. Let’s say Ж (sent from a web form) is coming to the database as C6, but when we connect to Oracle 19 or even to Oracle 11 instance which is created in UTF8, this same letter shows up (properly) as D096.
We don’t know whether this conversion is happening on DB side or is done by ORDS/JDBC.
We tried starting tomcat with some java parameters like “-Duser.country=us -Duser.language=en” but at no avail. We also tried setting a global “NLS_LANG=American_America.UTF8” on the Tomcat host.
There is this ORDS parameter thin.
Would it help if we change to “oci8”? However there is no info or example in docs what else we need to set to make it working. Right now it says the pool is misconfigured.
Sorry if all this sounds confusing!
so you’re using the plsql gateway, and you’ve pointed it to a db with charset 1251, and the output coming back is ‘garbled?’
what happens if on same machine as ords, you connect with SQLcl and run a query against the same database? does the same thing happen?
OK, here’s what happens with SQLcl. (I am not sure how this will be rendered on this page, though)
When I connect to a UTF8 instance, the output is returned fine. When I connect to a 1251 local charset instance, it is broken.
—–
SQL> select ‘ж’ from dual;
‘ж’
_______
ж
—–
SQL> select ‘ж’ from dual;
‘?▒’
_______
?▒
Why blame the tool? Seems like the issue is in the database.
OK, If it is in the DB, then even better. But what should we do with it? How to force OWA to read the values as Unicode?
BTW, when we were using Webtier with mod_plsql, this all worked fine. We sent UTF8 from the browser and we received UTF8 in the DB, regardless of its encoding. ( then we do validaton and convert to 1251, but that’s another story).
use a database characterset that supports your stuff…this is a very deep subject, see the Globalization Support Guide.
Jeff – We need to write a unix script to call ORDS API and for that we have to store client_id and client_secret somewhere to be able to pass them to script in order to generate token. Is there any way to protect client_id and client_secret as we do don’t want to store them in plain text. Will wait for your feedback Thanks.
I’m sure there are ways, but this isn’t really an ORDS question…there are lots of solutions out there for doing similar with oracle username passwords in unix shell scripts for say SQL*Plus/SQLcl, this would be similar, yes?
Hi Jeff,
I am using Sql developer 20.2 and, upon disconnection I get sometimes the popup dialog asking me to commit or rollback. The strange thing is that I am not updating or deleting anything. I am sure that I issued only Select statements. How this can be explained?
Best guess is at one point you queried over a DB_LINK, which triggers the session flag that you have uncommitted work.
that defintely could be, thank you very much
Hi Jeff,
oracle db 12.2+apex 20.1+ords 20.3;
I register 2 schemas through apex restful services: a1 with database schema a1 and a2 with database schema a2.
running http://<server_name/ords//a1/metadata-catalog — I see list of all examples and I can test any example;
running http://<server_name/ords//a2/metadata-catalog — no lists of examples and test any example give error 404;
I already twice de-registry a2 and registry again. No fix. I checked oradata_metadata schema data looks good. maybe it is some registry in sys or system
Thanks Mary
APEX RESTful Web Services have been deprecated and soon to be desupported completely.
You should be moving those over to ORDS…now, based on your URLs it sounds like you’re just just using the APEX Web interface for managing ORDS for a schema, so that’s OK.
Your URL should be
http://server_name/ords/a1/metadata-catalog/Database 19.3 and ORDS 20.2 and APEX 20.2
Why does it feel as if the connection requests go to Timbuktu and back before a response comes through. I am trying to work only on my Desktop. What settings am I missing?
Try enabling trace for your apex sessions and you can see where the db work time is being spent.
Is the goal of being able to save an entire database as scripts in version control, to be able to be created in an automated or semi automated reproducible manner through script a realistically achievable good practice?
It seems that DBAs often painstakingly maintain and nurture base versions of databases in binary form and evolve them in ways somewhat analogous to bakers maintaining a sourdough mother to bake new batches of artesian bread, or abbey monks maintaining rare stains of yeast to make expensive Belgian ale. Lose you starter or yeast, you ain’t backing bread or brewing beer for a while– then when you get back to it, your product might not ever be the same.
So, deploying new instances means copying and plugging in the base database a new site as a binary. If a developer does not have access to one for local experimentation and testing, the are SOL.
It seems with DB links; materialized views; cyclic dependencies between oracle column aliases, materialized views, package procedures and other items between schemas make saving and recreating your base DB as code next to impossible.
Should we be happy just to rely on an previously created base kernel DB to be able to make new ones? I suppose one can always save exported DDL, but will never be able to create a new one easily from scratch.
What are your thoughts?
Is the goal of being able to save an entire database as scripts in version control, to be able to be created in an automated or semi automated reproducible manner through script a realistically achievable good practice?
Yes, but it takes work, discipline, and good communication. Our tools are attempting to make this more viable, and we’re spending a lot of time and resources to make it as easy as possible.
‘in binary form’ – sounds like you’re talking about a cold backup?
Thank for responding.
Yes, something like that. The ‘base’ has all the code, possibly static data. Might be a backup that gets restored, or a pluggable database that gets unplugged and plugged back in again under a different root container. I think the latter. My Oracle experience is not that deep and I am learning on my own. I’ve worked with SQL Server and others though. Basically the base is what you need to plug in, restore, etc. into a new server in order to create a new copy of the database. Where the DB and datafiles are all kept and maintained I am not sure.
We have a similar ‘plug’ data model where your database can be cloned for dev/test… etc
Is Database DIFF available through command line to do this on connection a + b for more than 60 schemas?
No
Hi Jeff,
I am working on one automation to copy data from PROD to lower environment. I see oracle copy command is used underline by sql developer also. Currently COPY command support only CHAR,DATE,LONG,NUMBER,VARCHAR2.
But when i ran copy command from sql developer where one of the column have datatype as timestamp it worked but same command not working from sql plus. I want to know how it is working from sql developer when it is not working from sql plus. Does sql developer have some underline logic for COPY command ? Please let me know , I googled lot but no luck.
Thanks
Kundan
No, the logic/code is completely different.
HI Jeff,
I tried liquibase within SQLCL it doesn’t work for me tablespace names are committed and there are several other issues.
How would you go about programmatically apply changes from DEV / TST / ACC / PRD?
Duke
I found the database diff, I’d like to use that just programmatically so I can do that in an overnight build.
Use the SET DDL command to have the tablespace omitted.
Hi Jeff,
Wondering if you tried to use the JavaFX within Oracle SDDM Scripting. I was trying the same but got stuck in one place. I was trying to execute a simple Hello World example using JavaFX. Please find the code below –
load(“fx:base.js”);
load(“fx:controls.js”);
load(“fx:graphics.js”);
function copyErrorToClipboard(msg) {
var clipboard = java.awt.Toolkit.getDefaultToolkit().getSystemClipboard();
var stringSelection = new java.awt.datatransfer.StringSelection(msg);
clipboard.setContents(stringSelection, null);
}
function start(primaryStage) {
primaryStage.title = “Hello World!”;
var button = new Button();
button.text = “Say ‘Hello World'”;
button.onAction = function () print(“Hello World!”);
var root = new StackPane();
root.children.add(button);
primaryStage.scene = new Scene(root, 300, 250);
primaryStage.show();
}
try {
start($STAGE);
} catch (error) {
copyErrorToClipboard(error);
throw new Error(error);
}
Based on the Nashhorn documentation, the $STAGE is available by default but when I run the same in Oracle SDDM, I get the error that the $STAGE is not defined.
I have not, the thought never even occurred to me – yet that’s not a good reason not to try 🙂
If you’re interested in having a go at this, suggest you post this to the Modeler forums.
Using SQL Data Modeler 20.3
I have several SubViews in the Logical Model.
How do I generate an All Entities Report for just one SubView