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,555 Comments
Hey, Jeff,
I am running ORDS in standalone, with configuration mapped to replace our mod_plsql setup.
Running it localhost is ok, but when I put it on an actual server with a load balancer in front of it and a DNS name with URL that of course is different the actual host name ORDS is running on I get the Cross Origin error (with Chrome and FF; IE has no issue).
“The request cannot be processed because this resource does not support Cross Origin Sharing requests, or the request Origin is not authorized to access this resource. If ords is being reverse proxied ensure the front end server is propagating the host name, for mod_proxy ensure ProxyPreserveHost is set to On”
I am not finding much info about issue online or in the docs. Where/How is the best possible place/way to avoid this error. I don’t understand if this 403 error comes from Jetty or from ORDS itself.
I guess the safest would be to enumerate the allowed Origins somewhere in the ORDS configuration. But where and how?
I had several problems with returned “CORS” Error. I finaly found, that I had to escape some special charakters like the “curly bracket” in my URL to the service. Windows Tomcat was ok, Linux Tomcat didn’t work without escaping.
I have no braces in the URL and furthermore, there is no Tomcat of any sort involved I am running ORDS in standalone mode. I have found your question here (I searched for this before posting) and it does not apply to my case.
with a load balancer in front of it and a DNS name with URL that of course is different the actual host name
That says it all.
But, all is not lost.
Your loadbalancer should have some sort of directive/flag called ‘ProxyPreserveHost’ that will ensure the correct HOST header is propagated to ORDS (Jetty).
If the issue is simply your loadbalancer is getting HTTPS requests and it converts them to HTTP requests to ORDS internally, then you need to configure your loadbalancer for X-Forwarded-Proto to maintain the HTTPS, as HTTPS <> HTTP when it comes to CORS violations.
This is all more basic web config vs actual ORDS config – ORDS is just setup to obey the rules of the road when it comes to CORS.
Hey, Jeff,
you are right that LB is converting from HTTPS to HTTP.
I had the network team add x-forwarded-proto to the request header, but I still get the 403 error. They say your recommendations are in place. Yet, we are missing something.
Here is the updated Request Header:
GET /bg/!UPR.Dispatcher?ps_redir_proc=empty HTTP/1.1\r\n
Referer: https:/my.registered.domain/bg/!UPR.Dispatcher?ps_redir_proc=homepage\r\n
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.140 Safari/537.36 Edge/17.17134\r\n
Accept-Language: pl,en-US;q=0.7,en;q=0.3\r\n
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8\r\n
Upgrade-Insecure-Requests: 1\r\n
Accept-Encoding: gzip, deflate, br\r\n
Host: my.registered.domain\r\n
Connection: Keep-Alive\r\n
Cookie: inglan=2\r\n
SSL_PROTOCOL: TLSv1.2\r\n
SSL_CIPHER: ECDHE-RSA-AES128-GCM-SHA256\r\n
SSL_CIPHER_ALGKEYSIZE: 128\r\n
SSL_CIPHER_USEKEYSIZE: 128\r\n
REMOTE_HOST: xx.xxx.xx.xxx\r\n
REMOTE_ADDR: xx.xxx.xx.xxx\r\n
X-Forwarded-For: xx.xxx.xx.xxx\r\n
HOST_NAME: my.registered.domain\r\n
X-Forwarded-Proto: https\r\n
\r\n
and the site/domain/url is the same otherwise?
Yes, it is the same. I have just masked it here as “my.registered.domain”.
In all 3 places: Referer, Host, HOST_NAME.
Just commenting to share how I solved the Origin issue.
Well, I just switched to HTTPS end to end. No more errors.
Hi Jeff,
Sorry but another one of those SQL Developer formatter questions.
I know that later versions of the formatter allows users to paste their own code to “show” SQL developer how they would like their source code formatted. Unfortunately when I do it, it doesn’t honour most of my settings which is unfortunate.
The main reason for this is I want to tie the formatting engine of SQL developer into our source control pre-commit “hook” that automatically reformats the entire source prior to check-in. This way we can maintain code that conforms to visual code standards whilst developers concentrate on the actual code. There may also be a requirement to convert multiple source code files in batch from time to time.
I have seen a previous post on this site that goes into incredible detail about fiddling with the rules at each step of the parse, but I did find it a little hard to follow. Anyway, below is a snippet of PL/SQL code with my desired formatting (please convert into monospace font to see the indentation). I would appreciate some help and/or advice to get something like this automagically.
Kind regards,
Mark.
============
DECLARE
SUBTYPE identifier_st IS
VARCHAR2(128 BYTE);
FUNCTION camel_to_snake
(
ident IN identifier_st
)
RETURN
identifier_st
IS
v_retval identifier_st DEFAULT NULL;
BEGIN
v_retval := SYS.STANDARD.REGEXP_REPLACE
(
srcstr => SYS.STANDARD_REGEXP_REPLACE
(
srcstr =>
,pattern =>
,replacestr =>
…
)
,pattern =>
,replacestr =>
,position =>
,occurance =>
,modifier =>
);
RETURN(v_retval);
END camel_to_snake;
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE
(
a => camel_to_snake
(
ident => ‘convertToSnake’
)
);
END;
Oh dear… my source code has lost the formatting on post. 🙁
Hi Jeff,
In SQL you can create different types (array, object, table), how can we see in one overview to which category(array, object,table) a type belongs?
Thanks in advance.
does the TYPECODE attribute in ALL_TYPES help you?
Hi Jeff, first of all thank you for the great job on liquibase.
I have two problems using the lb update:
1.
The command
lb update tablename_table.xml false
leads to a tablespace-error: 00959. 00000 – “tablespace ‘%s’ does not exist”
Is it possible to ignore the tablespace name when I genobject the table?
2.
The command
lb update controller.xml false
brings the following error:
Liquibase konnte die Anforderung derzeit nicht verarbeiten (allgemeiner Fehler).
liquibase.exception.SetupException: Error Reading Migration File: Invalid byte 2 of 3-byte UTF-8 sequence.
Can you or anyone please help me with these problems?
Best regards René
For the first one, use the set ddl command to disable storage, tablespace, partitioning, etc as needed…
Then when you generate your changelog, that info won’t go into the XML
Thenks Jeff, that worked for the single lb genobject.
In case of lb genschmema, the set ddl doesn’t work. You know how to deal with that?
Kind regards, René
Yup, it’s a bug.
Good news though, developer found the issue and fixed it for v20.1
Thanks for the report!!
Hi Jeff,
I download sqlcl-19.4.0.354.0937. I’m try connect to oracle database 19.3.6 with Kerberos authentication and I have following error:
———————
USER =
URL = jdbc:oracle:oci8:@(description=(address=(community=tcp.world)(protocol=tcp)(host=xxxxxxxx)(port=1521))(connect_data=(sid=xxxxx)))
Error Message = no ocijdbc19 in java.library.path
USER =
URL = jdbc:oracle:thin:@(description=(address=(community=tcp.world)(protocol=tcp)(host=xxxxxxxx)(port=1521))(connect_data=(sid=xxxx)))
Error Message = ORA-01017: invalid username/password; logon denied
—————————–
Can you help?
Thanks Mary
I’m not the best Kerberos person, but others have blogged this, also should be the same process as sqldeveloper
Hi.
Migrating to Apex 19.x on 18c. On prototype box ORDS restful services OK.
But on migration target servers,while APEX via ORDS is fine, even oracle.example.hr restful services, instantiated with the register schema with ORDS click, get 404 page with reason blank. So far, I’m unable to detect a difference in configuration.
Can you please illustrate the probable diagnostic steps, highlighting those where SYSDBA privileges are required?
Hi Derek,
I have exactly the same problem. Did you find a solution?
first things to check – is the schema rest enabled and is the service published?
SQL Data Modeler v19.4.0
Database v19.3
I was trying to create the Repository for doing Reports. There is a bug in the script to create the Type (I added the / at the end and compile worked. The Package has an item, Objects_List showing as invalid. Perhaps database 19.3 is looking for something else
Hi, I’m trying to make an ORDS module of source type pl/sql to call a package-procedure that returns a out sys_refcursor from a query. I added a parameter of type RESULTSET, as ORDS documentation says that the result will be automatically formatted in JSON in the response. And that is what we want. We have hundreds of get-procedures we want to expose as a ORDS service. But: it doesn’t work: when I post a request to the ORDS service from Postman I get a 500 status. This is the example I rebuilt: BEGIN
ORDS.define_module(
p_module_name => ‘testmodule6b’,
p_base_path => ‘testmodule6b/’,
p_items_per_page => 0);
ORDS.define_template(
p_module_name => ‘testmodule6b’,
p_pattern => ‘get-employee-details’);
ORDS.define_handler(
p_module_name => ‘testmodule6b’,
p_pattern => ‘get-employee-details’,
p_method => ‘POST’,
p_source_type => ORDS.source_type_plsql,
p_source => ‘BEGIN
get_employee_details(
p_empno => :empno,
p_employee => :employee);
END;’,
p_items_per_page => 0);
ORDS.define_parameter(
p_module_name => ‘testmodule6b’,
p_pattern => ‘get-employee-details’,
p_method => ‘POST’,
p_name => ’employee’,
p_bind_variable_name => ’employee’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘RESULTSET’,
p_access_method => ‘OUT’
);
COMMIT;
END;
/
Should this way of returning a sys_refcursor as a resultset work? I really hope so!
I’m out today, but the auto PLSQL feature gives what you want with no code to write.
It supports packages and refcursors…
How about a new feature: Saving current working area regularly and without need to save every single worksheet separately.
I often run into situation to be forced for reboot my PC, but have opened many sessions and worksheets.
If I have the time, I check all important and not already saved worksheets and save them.
I know, that I can save all worksheets automatically to a generic filename with increased numbered suffix, but this causes over time many many different files and even more, I would have to delete them manually on OS-level, because I cannot delete them directly out of SQL-Developer.
Even more, this option doesn’t protect me from surviving crashes, like blue-screen or closed by any privileged installation with automatic killing processes during my absense.
I would appreciate a kind of workspace backup including all current and even unsaved worksheets, similar what browsers do (restore closed tabs), so that I don’t loose my last developed queries. This should save just for each running instance separately. I’m fine with “last saved wins”. Would be sufficient to have this optional (preferences) and to configure save intervals. I think, 5 mins is sufficient, but only if any changes were made.
Hello,
Do you think it might be possible to have permalinks for SQLcl as it is the case today for instant client?
Cheers,
Jean Remacle
Permalinks for what, exactly?
Hi Jeff,
How to audit only the sessions that is coming from sql develpoer?
I tried to define a audit policy but it is not monitoring the sessions coming from sql developer?
alter audit policy audit_revpro_bala condition ‘sys_context (”userenv”,”client_program_name”) in (”SQL DEVELOPER”)’ evaluate per session;
…in(‘SQL Developer’, ‘Raptor’)…
Also, beware of people changing their client_program_name for their session, to avoid auditing?
In SQL Data Modeler, on an Entity what does the the triangle on the Top Right corner indicate?
Figured out the reason for the triangle showing up in the top right corner of an Entity is when some attributes are hidden
Hello Jeff,
I am doing one POST operation that should insert only 1 record at the time in a table where a text file is inserted into the blob_document column. ( see table DDL here below. )
But instead it is creating 2 records.
One will have the blob column populated correctly with the text file and a second record where the blob_document column is empty.
I have here below the DDL of the sequence and table (with trigger) and the POST command sql statment.
—
CREATE TABLE “AL”.”DOCS_JSON”
( “SEQ_ID” NUMBER(10,0) NOT NULL ENABLE,
“LOADING_DATE” DATE DEFAULT sysdate,
“BLOB_DOCUMENT” BLOB
) ;
—
CREATE UNIQUE INDEX “AL”.”DJ_PK” ON “PAL”.”DOCS_JSON” (“SEQ_ID”);
—
CREATE OR REPLACE TRIGGER “AL”.”BIUD_DJ_TRG”
before insert or update or delete on docs_json
for each row
begin
if deleting then
null;
elsif inserting then
select dj_seq_id.nextval into :new.seq_id from sys.dual;
elsif updating then
:new.seq_id := :old.seq_id;
end if;
end;
—
—
— POST —
declare
l_blob blob := :body;
l_seqId number := 0;
begin
— nextval —
insert into DOCS_JSON( LOADING_DATE, BLOB_DOCUMENT ) values ( sysdate, l_blob ) returning seq_id into l_seqId;
—
:response := l_seqId;
:status := 200;
—
exception when others then
:response := ‘Status NOK.’;
:status := 400;
end;
Can you check if you can see what’s the problem?
Regards,
Anibal
The most obvious cause is your app is issuing a double HTTP POST request.
Hi Jeff,
I am just in a testing phase.
For this i am using POSTMAN and only click once on the SEND button!
Could i be a problem of the ORDS version?
Regards,
Anibal
I’d want to see a session trace to see exactly when the 2 INSERTs are happening.
Hi Jeff,
I have put an condition that the l_blob (:body) (in this version of ORDS) is not null then it is only inserting one record.
It is like ORDS re-evaluate the the :body tag twice!
Could this be true?
Regards,
Anibal
I don’t see that logic in the code you shared.
Hi Jeff,
I changed the code a little bit and putthe condition if the blob is not empty.
declare
l_blob blob := :body;
begin
—
if l_blob is not null
then
insert into DOCS_JSON( BLOB_DOCUMENT ) values ( l_blob);
end if;
—
commit;
—
:response := ‘Status is OK’;
:status := 200;
—
exception when others then
:response := ‘Status is NOK’;
:status := 400;
end;
It is like a re-evaluation of the :body is taken place.
Regards,
Anibal
see my example for file loading here … one post, one file inserted to my table
Hi, Jeff, I know you like questions and you do reposnd timely. For mine, I could not think of anyone more relevant to ask, but you.
We have a big application in PL/SQL with some frontend in AngularJS. We currently run this on Webtier 12.1.3 with mod_plsql.
Anywas, I have this task of migrating from mod_plsql to ORDS. I have two options here – one is to use Apache and Tomcat and deploy ORDS on Tomcat. The other one, and preferred by me, is to run ORDS in standalone mode. However there are concerns if ORDS standalone is prodution ready, is an adequate solution and whether it can cope with all the traffic (thousands of customers) in production.
What would you say?
ORDS standalone would be just fine.
The real question is would you have any particular implementation and management requirements for your system that Tomcat would provide out of the box?
ORDS standalone is a very specific implementation of Jetty. We run it in production here at Oracle, and it can handle the load. You may want to consider, whichever way you go, front-loading 2-3 ords/tomcats with a loadbalancer though, esp for HA benefits.
Yes, we definitely have a Load Balancer in front of equally configured webtiers with mod_plsql. So we will keep the same structure. As for Tomcat, the only reason to use it was for us to run ORDS on it. No other requirement. I am following the migrating-mod_plsql-ords.html doc.
Thank you for your answer. I now have a good direction to follow.
You don’t need to add Tomcat, but you always can later if you’d like. It’s very popular with our customers – it’s free, lightweight, and has a ton of features available for ‘extras.’
Hey, Jeff, I had long and sleepless nights fighting with ORDS. I have been through the docs inside and out and all over google. I cannot find help for my situation.
Basically, the problem is I must have context path “/”, not /ords and not /pls. Not a single example about this. The only way to have this is if I run java -Xms512m -jar ords.war standalone –context-path “/”
Through a lot of experimenting I found a few bugs when context path is root:
1) Even if standalone.properties file contains “standalone.context.path=/” I still have to start ORDS with –context-path “/” every time, or ORDS starts again looking inside \ords\config.dir\…
2) ORDS disregards config.dir and creates/looks for configuration files next to it, not below it. Now that is fine for me. It might only interest you as a bug. But the third one is a real show stopper:
3) document root does not work. ORDS says “The document root is serving static resources located in: c:\ords\www” but when I browse them, I just get 404.
I just edited the standalone.properties file to point to my static content folder, started ORDS, and had index.html served at the base URI with and without a slash.
Document root definitely does work – we’d have thousands of APEX customer raising Cain otherwise.
Yes, document root works. But you missed the important detail at the beginning – it does not work when context path is empty: /. Not /ords, not /apex, not /pls, etc. I must have it empty /. In that case, document root files are not served.
No I didn’t, see my screenshot, I pulled up both
localhost:8080 and
localhost:8080/
both serve up the index.html in my doc root
Right, my bad. I looked more carefully. Yet I am still getting 404 “The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured” when I browse localhost:8080.
I also have a db-name called “bg” in the pool and localhost:8080/bg/ does work properly, however it is made to load static resources, like images, which fail to load.
I also tried to simulate your case with …/standalone/doc_root/index.html but I still get this 404.
Is ORDS mistaken, by trying too look in the pool for a db connection? Could the reason be in an opton in defaults.xml? I don’t know. I am desperate 🙁
We can do a call on Monday, I’ll talk you through it.
However you said earlier you mapped / a routing pattern? That’s not necessary for what you’re wanting.
You’ve got mail.
Jeff, how can we tell that a column is virtualy defined in a certain table without looking into the DDL? Is there a symbol for it?
Also we want to see what is partitioned without looking into the DDL of the table, is there a way to do this?
Thanks!
DBA_TAB_COLS probably indicates that
For partitions you might be able to guess based on the high/low value of the partition, otherwise, yes you need to to look at the SQL page. I have a standing ER to make the partitioned ‘key’ more obvious in the Partitions tab.
Hi.
I just would like to ask what can I do to solve this problem . My run statement button has just stops working. The “Run” menu options are disabled and I have no idea why. I think that I mistakenly pressed ctrl + Shift+i or ctrl + Shift+r because I wanted to relpace a word, but now I can n ot run my query. I am so worried about it. If someone can help me, I really appreciate it.
Those will only be available during a debugger session as it’s running – except for the Toggle Breakpoint item.
Can you start and run the debugger?
Hi,
I want to connect with SQLDEV to kerebos;
I’ve configured
kerebos Thin Config
krb5.conf
credential cache File
However whenever I set Authentication Type Kerebos
it keeps asking me for username / password.
Have you seen this?
Hi Jeff – with your examples I got my utility working perfectly! The type=”SCRIPT” attribute for the sql tag was the magic incantation!
There is one last thing though: My script produces its output using dbms.put_line(…)-statements and the output in the Messages Log-window gets wrapped.
Since the utility is generating sql statements these MUST NOT be arbitrarily wrapped (esp. not in the midst of a string!) because then the result will be non-parseable and my tool gets useless.
Is it possible to prevent the log-window from wrapping lines? Or is there a different output channel or tab or windows where I could direct the output to?
Never mind. I found the answer elsewhere on your website.
Thanks for providing this! It’s a real treasure trove!
Regards and cheers,
Michael
Hi Jeff,
Is it possible to open SQL developer web from my custom web-app in other bowser/iframe/newtab. And does SQL developer web provides some web hooks through which another web can communicate with it ,i.e send/receive queries and perform other operations?
Thanks
First question, yes…it’s just a html5 web app, so I don’t see why not.
Second question, maybe. It’s a REST API too. What are you thinking about having it do?
We want to have some kind of functionality to open the text of an existing query or pl\sql script from our web application inside your tool. Since we want to have auto-completion support for database objects (like tables, views, columns, …), we need to somehow also provide you the connection (credentials) to the db. Additionally, we want to syncronize the changed query text back to our tool.
Unfortunately, we were not able to find a proper API for your tool to do so. Could you send us the REST API, you were talking about so we can checkout if it suits our needs?
Can you send me an email, from your work account, so I can understand who is asking?
Thanks,
[email protected]
>> I’ll take a look, but have you seen our GitHub repo with extension examples?
I found the repo but I was a bit overwhelmed by its contents. Is there some overview or table of contents?
Actually – I had first found a different repo. I believe that I now located the correct one (https://github.com/thatjeffsmith/oracle-db-tools/tree/master/sqlcl), but I fail to see any plugin examples there. Am I at the right place?
No, here
Hi Jeff – you may want to delete my question here – since without the example shown it’s pretty useless here. Please have a look at https://stackoverflow.com/questions/59976458/sqldeveloper-dynamic-drop-down-boxes-as-part-of-extensions instead.
Thanks and sorry for the bandwidth consumer here.
Regards,
Michael
I’ll take a look, but have you seen our GitHub repo with extension examples?
Thank you very much for the immediate response Jeff! Truly appreciate it. I just downloaded 19.4 version, that loads faster than 19.2 and as you mentioned, right clicked on the connection node (After using the suite for almost 4 months everyday!) for the first time to realize that you have a schema browser already available.
I do understand the JAVA platform implications, however, please let me to request you to add a feature which will allow us to pin frequently used folders to the folder browser left hand side panel, that randomly attaches folders. Thank you once again!
Should be the last 5 or so directories you’ve open or saved files to.. Not a random list.
Hi Jeff
Do you have plans to add a dedicated “Schema Browser” like in Quest Toad? We are switching from Toad for commercial reasons & after using Toad for last 12+ years, my developers are finding it bit difficult to deal with the current schema browsing approach SQL Developer has.
We would appreciate, if you can associate the Windows folder browser standards with folder browser as well. Thank you! you guys are doing a wonderful job
regards,
rajesh
We have one! Right click on your connection to open and use it.
We don’t have access to the windows features like that as a Java application, alas…
I’ve changed the NLS to DD-MON-RRRR without the time component, so that when I run the import, it should just import my dates… right? Yet when I hit the Verify button, my date fields get an error, so I hit the Back button and change the Format field to DD-MON-RRRR for each of my 6 date fields, and then the import works. Changing the format for each date field is not very automated. Why won’t changing the NLS parameter work for date importing? I can’t find posts out there specific to this issue. Thanks!
You’re describing the date format of the data in the file you’re importing…it could be different for each column.
Also, what are the odds your session parameters would match up exactly your import scenario?
Data Modeler 19.4.0.350, Build 350.1424 won’t restart after disabling 2 features: “Oracle NoSQL Database 19.3.12” and “Oracle SQL DEveloper – Cloud Support”. I was able to fix by removing the “oracle.sqldeveloper.onsd” and “oracle.sqldeveloper.cloud” string tags from the “disabledExtensions” list in “%APPDATA%\Oracle SQL Developer Data Modeler\system19.4.0.350.1424\ide-extension-prefs.xml” file.