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
Do we have a shortcut for Single Record View in SQL Developer ?
Not really, but in windows you could do Shift+F10, and then Alt+V, but by that time you could have just picked up your mouse I’m guessing.
Hi,
I am trying to understand how the predefined ORDS role “RESTful Services” works / is intended to be used?
I thought perhaps that any user that is assigned this role might be able to access any protected resource.
(resource protected by being associated with an ORDS privilege that is itself associated with at least one role. And not the “RESTful Services” role).
Or perhaps on new privilege creation, that role would by default be assigned to the privilege.
I would appreciate any insight on this?
Thanks
Alex
You probably shouldn’t be using it at all 🙂
It’s a legacy artifact from apex based rest services. It is not associated with anything by default, and it is not granted to any user by default. No authenticated user will have it, nothing is protected by it. But if you need a role and don’t have one, it’s there…
I need to update the docs to properly reflect this.
I am using ORDS v19.4 to implement REST API returning custom JSON documents. Handler containing p_source_type => ‘resource/lob’ does the job perfectly: it can return any JSON document.
In some requests I need response returning JSON document in addition to response code. I need to do so for both cases, 200 OK and 404 Not Found. JSON document to return looks like this: {“result”: { “code”: 404, “message”: “Not Found”}}.
Unfortunately, p_source_type => ‘plsql/block’ cannot return such custom JSON document as response.
Solution for that case: POST handler with p_source_type => ‘plsql/block’ can set implicit variables (:status_code and :forward_location). Implicit variable :forward_location causes request be forwarded to GET handler of :forward_location: it can return custom JSON document (because p_source_type => ‘resource/lob’). This works fine.
Unfortunately this does not work for GET handler (p_source_type => ‘plsql/block’): the implicit variables :status and :forward_location do not forward the query to another GET handler (p_source_type => ‘resource/lob’) even when :status and :forward_location implicit variables are set.
QUESTION: Will there be added support into GET handlers to obey :status and :forward_location implicit variables same way as POST handlers do?
thanks in advance, Bandy
in your plsql block, you can use the the WPG_DOCLOAD package and the download_file() procedure to get your media content (LOBs)
Use Real Time SQL Monitor frequently in version 19.4.0.354 Build 354.1759.
Currently tracking a large piece of SQL which runs for 2 hours approx.
It is reporting: Status: DONE (ERROR) for the job but it continues to tick over Running Duration (currently): 4,096,000.0 ms
However the Start Time: 21-May-2020 09:16:43 and the End Time: 21-May-2020 09:16:43 are identical.
Have stopped and restarted SQL Developer. And reopened the connection to the database I am tracking.
Any thoughts on this as I will possibly kill the job and ask our customer to run it again before any reply? More for when I see this again.
Kind Regards
Steve
(ps Running Duration (now): 4,396,000.0 ms)
I think this is more of a question re the sql monitoring system itself. Opening or closing SQL Developer shouldn’t have any effect on the status of the query/and the resulting report.
Replied under the wrong action:
By your reply ‘sql monitoring system’ do you mean the java memory heap for SQL Developer or the setup for monitoring within the database itself. This same error has been reported for the same sql this morning. It has not occurred for any other query. So not sure what you mean by ‘sql monitoring system’.
Steve
No, I meant the database.
And just as a further update the very same query is now running again and is reporting no problems vi SQL Developer Real Time SQL Monitor. So I am at a loss to see where this is erroring on two runs two days apart and a third on the same day it errored as being OK. Is there any additional logging I could catch that might report differences in events. I have, under preferences/environment/log, recently turned on logging.
Regards
Steve
SELECT stuff FROM my_table
WHERE the_name = :myname;
The GUI prompts me for myname and bang, it works.
Can I do:
SELECT stuff FROM my_table
WHERE the_name IN (:myname);
I’ve tried various combinations of single and double quotes, commas, etc. Is this possible? If not, no biggie I have a workaround, albeit manual typing.
Sorry, cut off part of my comment when cutting and pasting. This should be at the top of the comment:
Question about the bind variable dialog and usage. I use the :bind syntax many times a day as an APPDBA to check on user issues. I’ve always used = or LIKE. Can I use some type of list? For example, assume the_name is VARCHAR2(nn):
I think what you’re looking for is this.
By your reply ‘sql monitoring system’ do you mean the java memory heap for SQL Developer or the setup for monitoring within the database itself. This same error has been reported for the same sql this morning. It has not occurred for any other query. So not sure what you mean by ‘sql monitoring system’.
Steve
Jeff,
I have been following your posts for many years and appreciate the effort you put in.
I have a question that I haven’t seen asked elsewhere. I want to log the [min, avg, max] of a 1:M relationship. I usually place this information in the comments tab on a relationship as a tuple as shown above.
How can I get this information displayed on the diagram? I don’t necessarily want all entity comments displayed, just this relationship data.
you could build that into the relationship name and then enable showing the labels in the diagram?
A question re. the UI: is there a GUI way to find out a few details about a DB table?
I would fancy something like right-clicking on a table and choosing “Properties” and get a list of, say:
– number of entries (aka size) of that table (same as “select count(*) from table”)
– number of columns
– name of the primary key(s) and the/each PKs’ minimal and maximum ids (same as “select min/max() from table”)
– etc.
I would have expected to find such info on a tables “Statistics” or “Details” tab, but didn’t find the above. Of course it’s “easy” to type that in, but a click is always faster. And implementing something like that should be outright trivial. A nice little job for a summer intern?
your idea of interesting data would differ from others. Doing select count(*) alone on a large table can take a very long time…so it’s easy, but not cheap. The stats will show high and low values on columns.
I am using ORDS to replace db links between an on-premise DB and DB in the Cloud.
When fetching a large amount of rows (> 300.000), duration remains 4 min, even after tuning parameters in defaults.xml, and adding a load balancer. The best results I get is with pagesize = 0, and misc.pagination.maxRows = 300000. Other parameters are changed, but without improving performance:
thin
20
10
100
20
1800
900
1000
Can you give advice how I can decrease duration time. Notice that compare to the use of DB links, ORDS is still 2 times slower.
Thanks for your help
Johan
Same advice I gave on the forums…try writing a CSV file or similar on the server and then download it.
You won’t necessarily be able to match the performance of a dblink
Dear Jeff,
thank you for the great content , and the helpful advice.
we have few more question regarding the roadmap of ORDS
a) What is the Release date for ORDS 20.2 ?
b) What are the plans for supporting ORDS on GraalVM ?
c) Is there any rationale to consider running ORDS on Helidon ?
Has it been tried before ?
we are trying to understand how all the middleware/api/ products would play along in the future.
Best Regards,
Nabil.
Hi Jeff, I hope I am not beating a dead horse here, but I *did* google a bit and didn’t find any satisfying answer.
Does SQLDeveloper support something like C’s #include files?
Reason: I have a couple of pretty complicated scripts and they share some functionality, so I would prefer to factor those methods out into a separate file which is then included by other scripts.
For that to work, SQL-Developer would need to embed that file verbatim into the script before sending it off to the DB for execution. Is that possible?
One of the search hits (https://stackoverflow.com/questions/22994146/including-procedure-written-in-different-file-to-package-in-pl-sql) seems to describe such a feature. I thus have set the script file search path under Database > Worksheet to “${file.dir}” as described there and then tried to include another script using a “@test.sql;” statement in my “main.sql” but that didn’t work for me. If that feature is supposed to work: is there a description somewhere describing that maybe a bit more precise?
@ aren’t includes per se, they just say, go run this right now
so if you’re refactored things out to smaller pieces, you could have them run as needed by calling them via @script_path/script_name.sql
Hi Jeff.
I just started a new job and I’ve been given a textfile with >500 “username/passwd@tnsentry” combinations. Is there a way to import this as connections in SqlDevloper? I think I’ll be able to transform that file into a json that can be imported. But what about all those passwords?!?!
I saw “ExportPasswordMode”:”Key” in an export of connections. Can I set it to “ExportPasswordMode”:”ClearText” or something like that?
Thanks.
you could build your own json connections file, but not the passwords
Hey Jeff,
I’m Rifat, the partnership manager for iCollege and I wanted to partner up with you.
We have over 500 courses on different tech-oriented fields, and we’re one of the leaders in our field.
We recently started our web syndication program along with some new features.
Please let me know if you’re at all interested, and I’ll get back to you with all the relevant information.
I don’t mind if you borrow my posts assuming you include my name and links back to the source material.
Hello Jeff
we are considering running ORDS on WebLogic and from the FAQ , we could see that one needs
to license Oracle WebTier to get a RUL for Weblogic Management Framework. on which we would run ORDS.
looking Oracle Webtier downloads. i can see that this is basically Fusion Middleware 12.2 product
the image is 1.9 GB == fmw_12.2.1.4.0_ohs_linux64_Disk1_1of1.zip
We would much prefer to use the new Weblogic images for Weblogic 141 and run this on a Docker Container.
Questions is :
1) Technically can we jut run ORDS with the ” Weblogic 14.1 ( Standalone)”installer ?
https://www.oracle.com/middleware/technologies/weblogic-server-installers-downloads.html
2) can we aslo use Weblogic 14.1 under the same RUL for ORalce WebTier ?
thank you
No idea on 1…I mean I would think so?
On 2, you should ask your Oracle account manager. I can only refer you back to the FAQ.
Is there a way to play a sound or make the PC speaker beep from sqldeveloper?
It would be extremely useful when executing long-running processes or queries.
Best regards,
Álvaro
Not really, but you can for SQLcl
Jeff ,
I have scenario where my collection is getting out as parameter from package .
This collection is having two inner collection .
PORTF_ACCOUNTING_OVRD_T
FORCE IS OBJECT
(
PORTFOLIO_ID NUMBER (20),
ASSET_DETAILS_COL ASSET_DETAILS_COL_T,
COMMENT_COL COMMENT_COL_T,
OVRD_EFFECTIVE_DT TIMESTAMP (6), –Start dt
OVRD_EXPIRATION_DT TIMESTAMP (6), –End dt _NULL for single day adjustment
SUBMIT_ACTION VARCHAR2 (5 CHAR),
SUBMIT_ACTION_HIST VARCHAR2 (5 CHAR),
COMPLIANCE_EFFECTIVE_DT TIMESTAMP (6),
OVRD_SINGLE_DAY_IND VARCHAR2 (1 CHAR) ,
OVRD_ASSETS_THRESHOLD_USD_AMT NUMBER(38,4),
OVRD_SHARES_THRESHOLD_QTY NUMBER(38,6)
)
In above ASSET_DETAILS_COL and commnets_col is again collection.
as per your blog we can write wrapper and assign the collection coming from package to sys ref cursor.
For normal scenario it is working but when it is having collection inside it is not working.
in collection i am getting message as oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@389680c9.
please advice . Is there any way we can see details in SQL DEVELOPER.
Thanks
Kundan
Can you send me an-easy-to-compile example?
Here you go Jeff
step 1
CREATE OR REPLACE TYPE COMMENT_T IS OBJECT
(
COMMENT_ID NUMBER(20),
COMMENT_TXT VARCHAR2(2048 CHAR),
DELETED_IND VARCHAR2(1 CHAR),
UPDATE_ID VARCHAR2(30 CHAR),
UPDATE_USER_NAME VARCHAR2 (100 CHAR),
UPDATE_TMSTMP TIMESTAMP(6),
SUBMIT_ACTION VARCHAR2 (5)
);
step-2
CREATE OR REPLACE TYPE COMMENT_COL_T
IS TABLE OF COMMENT_T;
Step -3
CREATE OR REPLACE PROCEDURE kun_test (
p_out_cur OUT SYS_REFCURSOR
) IS
BEGIN
OPEN p_out_cur FOR SELECT
9999 portfolio_id,
8888 portf_accounting_ovrd_hist_id,
‘APPROVED’ workflow_status_cd,
CAST(MULTISET(
SELECT
1, ‘testing’, ‘N’, ‘123’, ‘test’,
systimestamp, ‘X’
FROM
dual
) AS comment_col_t) comments,
sysdate effective_dt,
sysdate + 1 expiration_dt
FROM
dual;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in main procedure’ || sqlerrm);
END kun_test;
Step -4 I ran the above procedure
For comments I got value as oracle.jdbc.proxy.oracle$1jdbc$1proxy$1NullProxy$2java$1sql$1Array$$$Proxy@5079b61d
Step -5 Same cursor query when ran in sql window , getting required output correctly .
let me know if required any more details. Just for testing I don’t want to open PL-SQL developer .
Same code is working fine in PL-SQL developer.
Thanks
Kundan
Any update for issues reported.
Hi Jeff, Working in SDDM 19.4 I have a relational model with 45 tables and 6 subviews. I want to create an sddm model of only the 10 tables in one of the subviews. I use the menu path File.Export.Data Model Design, and select the subview I wish to export. The export that is produced contains the entire 45 tables and 6 subviews. The source model has been constructed over time by importing a number of submodels, and various merges. I have other models that can export subviews only so this appears to be a model specific problem. Are you able to finger a setting or other cause for this behaviour?… and a cure???
Greg
Hi Jeff,
Please try to paste the code below in an empty editor window, then try to add the missing quote to the last line.
What happens to me is quite crary, the editor hangs, the caret disappears etc.
select listagg(‘,:old.’||column_name) within group (order by column_id) from all_tab_columns
where owner = ‘XXXX’
and table_name = ‘YYYYY’
order by column_id;
select ‘insert into mytable
values (‘||’C’||listagg(‘,:old.’||column_name) within group (order by column_id) from all_tab_columns
where owner = ‘XXXX’
and table_name = ‘YYYY’;
select ‘insert into mytable (‘||listagg(column_name,’,’) within group (order by column_id)||’)
values (”’||’U’||”’,”B”,’||listagg(‘:old.’||column_name,’,’) within group (order by column_id) ||’)’
from all_tab_columns
where owner = ‘XXXX’
and table_name = ‘YYYY’;
select to_char(current_timestamp,’yyyymmdd hh24miss) from dual;
Hi That Jeff Smith
Firstly – thanks for your enormous contribution to our community. Great work!
I am having some issues after an upgrade of APEX to 20.1 and ORDS to 19.4.
Environment: ORDS installed on a separate server from DB.
APEX installed into 12.2 PDB and ORDS installed into CDB
Before upgrade, app was accessed by this URL: https://xxx.yyyyyyy.com/ords/vicasol and all worked fine.
…/ords/vicasol/…. The Vicasol part is Workspace.
After upgrade, I can still access the app with same URL, but all my customer static APP_IMAGES are not showing.
What I can see for these static files is that the URL has an additional ‘path’ included – like this (taken from page source):
https://extranet.vicasol.com/ords/vicasol/vicasol/r/files/static/v3Y/vicasol_firma.jpg (DOES NOT WORK)
The duplication of the ‘vicasol’ part of URL is what is causing it not to show. If I remove the duplication, I can view/access the file.
https://extranet.vicasol.com/ords/vicasol/r/files/static/v3Y/vicasol_firma.jpg (WORKS).
I have validated the ORDS installation and all looks good.
Any idea where this duplication of the URL path occurs?
Thanks and kind regards
Trond
Is there any way to add data level security in ORDS. More specifically, we have a table i.e. ITSystem that we are exposing as rest endpoint. We need to apply data level security on this table based upon the department/BU the login person belongs to
The DB gives you this via row level security, or virtual private database.
Thanks Jeff for the information. However, I have checked the option of using VPD but there aren’t much information of using VPD in OAuth scenarios. Can you guide me some good tutorial please
Scenario:
Whenever an employee logins through Login Page(OAuth in play), he should see information only for the department he belongs to ie accessing “/employees” should show employees of his department only
Hi,
How can we checkout a specific table for Git ?
Thanks,
Sulav
In a model, in a database…? Need more info to help you, Sulav.
How to search and jump to connections having same first characters (similar like “Connections” navigator for e.g. tables)?
I have saved > 70 different DB-connections within my local SQL-Developer.
So I can’t avoid having many connection names having same or similar first characters (like Test1, Test2, Testn).
Unfortunately, in the dialog-boxes for opening a connection, only the first typed character let me jump to the list of connections with this first character and I need to type this first character again and again until the required connection is selected.
Instead, I would like to start by typing the first characters of name of wanted connection, as it is within opened connections for searching objects.
Example connections:
1. AAA
2. ABC
3. AFA
4. BAA
5. FAA
When I want to open AFA, I would like to type “AF” only to jump directly to matching connections. In this example, it is already selective.
Currently, if I type “A” it jumps to any of 1-3 and when typing second character “F”, it jumps to “FAA”.
This is different to behavior of Connections navigator after a connect, when selecting any objects like e.g. tablenames, where I can start typing the beginning of the tablename and it jumps more and more precise to the object starting with typed first characters.
That’s a bug, i’ll file it for you. Slight chance we can get a fix in for 20.2…
Hi,
I regularly create my domains in Data Modeler, and many of them are value lists.
When I generate, these value lists are enforced by means of a check constraint. All very good.
BUT – Is there a way of generating these domains to what we (Oracle CASE*Designer users) used to call a “Code Controls Table”? – i.e. a table (something like) “DOMAIN, CODE, DESCRIPTION”, populated appropriately for nice easy queries to use the value lists as LOV’s? e.g. “select code, description from code_controls where domain =’SUITS’
CODE DESCRIPTION
D Diamonds
H Hearts
S Spades
C Clubs
Many thanks
John
Hi Jeff,
I have been using sqlplusw.exe for years and decided to try SQLcl. The set format ansiconsole looks good, but my headings are always right aligned which doesn’t look good.
e.g. (might need to change this to fixed font):
fred (TDH) SQL> select cust_code, cust_name from customer where rownum < 10;
CUST_CODE CUST_NAME
____________ ______________________________
TIM999 TIM999&DAN999'S HYPERMARKET
ZZABUN 011 ACTION JINDALEE
HAMO HAMILTON HOTELS
ABC GIANT HYPERMARKET – IMM
ANGE ANGE'S FRUIT & VEG
STA GIANT PIONEER MALL
FCOMP FRESH COMPUTERS RESTAURANT
ANDYH ANDYH
SCAL SCALZI
Also the heading underline doesn't show on the same line but takes a line underneath which looks worse than the original — (minus).
Any idea how to fix this or is there an option to control this?
I am using Oracle SQLDeveloper Command-Line (SQLcl) version: 19.4.0.0 build: 19.4.0.354.1736 on Windows 10, database version 11.2.0.1.0.
Thanks,
Tim.
right-aligned…doesn’t look good?
it looks OK to me, but if you don’t like it, you can disable it.
No options to control underline, but we do have plans to make that look more natural later this year. I can say it looks MUCH better on a Mac, so maybe some futzing with line width in CMD prompt would help.
Is there a way to start sql dev and not start all the features? I am running v18.2
We migrated away from Toad to using SQL Developer, I’m trying to setup a git repo connection with bitbucket not gitlab. I have a ssh key, but when trying to connect i’m getting java.io.FileNotFoundException: c:\Users\…\.ssh (Access is denied)
Is this i’m denied reading the ssh key? or am I denied from connecting to git. I have tried also using user/pass. I’m able to sign into bitbucket just fine using git command line?
Thoughts?