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,791 Comments
Why do can’t I set a datatype to timestamp with local timezone when I create a new table in SQLDeveloper. Sure can do it in the worksheet, but why not in the table editor?
you can though
Ah the old “tick the advanced box” trick…
Thanks Jeff
Ok I do not see the “advanced” checkbox option. I have the latest version and am on Apple Mac Sierra
You get that option only when you create a new table. When you edit a new table that option is not available.
It’s there.
Right click, Edit Table. Then go to the Data Type page on the bottom of the dialog to set the timestamp options.
The sad thing was that I did not see the scroll bar, to my defence it was midnight. Thanks
No defence required. Been there, done that!
Hi Jeff, is there a way to export data into different workbooks of single excel file.i have multiple sql queries and i want to spool data into single excel sheet
if you’re exporting data from tables, then YES. if it’s just a bunch of queries, then NO.
unless you created views for your queries, then it’s back to YES
Hi Jeff,
I have downloaded the SQL Developer 4.2 EA as I wanted to see the improvements to the PL[SQL] format feature. My intention is to try and integrate the formatting option into a git hook to ensure that the code is formatted before a commit to the repository. Do you have any advice or even perhaps some example code for this?
1) Obviously this relies on SQL Developer being used in a command line mode, and in the archive there is no BIN directory. Can you please indicate how to use the command line feature in 4.2 EA?
2) I have noticed that the format feature raises syntax errors presumably when it cannot parse the source.
/*** Syntax Error at line 10, column 44
Expected: identifier,
***/
We use the SQL*Plus application to install the database schema. To ensure that the installation routines are flexible, we make extensive use of SQL*Plus substitution variables. The schema owner is there to ensure that objects are placed in the correct schema when following best practise of the thick database paradigm. e.g.
CREATE OR REPLACE PACKAGE BODY &&owner..some_pkg AS
(it was this line of code that caused the syntax error. The double period is intentional – the first period is the end delimiter of the substitution variable and the second is the schema delimiter)
Do you consider this a bug with the format feature?
Many thanks and well done to you and your team for a wonderful tool.
Mark.
can you point me in the right direction to the database docs showing that use case of & and ‘..’ syntax? If it’s valid, it should be working.
Hi Jeff,
This link shows examples of using the & and && as well as the double period. https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_2
Ok, it isn’t official documentation but seems to suggest that this behaviour has been in SqlPlus for a while.
Many thanks,
Mark
Jeff,
Any idea on how to use the formatting function using the command line in 4.2EA?
Kind regards,
Mark
Use the FORMAT command – you can format the code in the buffer, or format the code in one or more files.
What are you trying to do exactly?
Hi Jeff,
Firstly a big thanks for you and your team on sqlDeveloper and sqlCl (I’m a convert).
Question: Re Real Time SQL Monitor
I’m presently user SqlDeveloper Version 4.2.0.16.260, build 16.260.1303.
Can you confirm that Tools| Real Time SQL Monitor is an licence pack add on?
Many thanks.
INDEED IT IS – RSTM is part of the Tuning Pack. You buy enterprise edition of the database, then you license the tuning pack.
When you first use this screen in SQL Developer – it warns you of this, so you can avoid licensing problems.
Many thanks on the prompt response.
This is as I thought.
Regards.
Hi Jeff,
I tried downloading the latest SQLcl today with the Nov 3 update and it gives me the below error. I tried in different browsers and i get the same result.
Error
Thank you for accessing the Oracle Software Delivery Cloud. Due to your country location, we are unable to process your request. If you have an active support contract, you may request physical media by either submitting a Service Request or calling Customer Support. If you wish to purchase or evaluate our products on a 30-day trial please contact the appropriate Sales Representative for your country.
Looks like its fixed now. Thank you!
using sql developer version
Version 4.1.5.21
connected to a db2 database, query is returning hugh numbers (2553845.67760000000) .. some numbers are so large that display grid is showing OE-11. Query is doing a sum (SUM( openingsales ) as openingsales). Is there a preference setting that will prevent this error, or is there a better way .. ?
Probably an issue with the db2 jdbc driver…nothing in sqldev to set to fix that I know of.
1. So for what I am gathering there seems to be a difference between a worksheet and a Code Editor.
I know how to create an empty worksheet.
How do I create a brand new Code Editor screen/tab?
2. Formatting.
I have this:
procedure SOS_CPYRAW_1 ( p_i_fpath in varchar2
,p_i_fname in varchar2
,p_o_fpath in varchar2
,p_o_fname in varchar2
,p_err in number default 1 );
procedure SOS_CPYRAW_1 ( p_i_fpath in varchar2
,p_i_fname in varchar2
,p_o_fpath in varchar2
,p_o_fname in varchar2
,p_err in number default 1 );
It does not seem possible currently.
Will it be improved to achieve that?
Formatting did not work here.
Basically I want the p_ aligned with the first p_ in the line, so that commas will be aligned with the blank space after (
Hello Jeff,
I am using Oracle version 4.1.3.20. I have a very large query that works when connected to a database, but then the same query fails when connected to a different one. As far as I am aware though, but databases house the same data/tables schema. The failed query has the following error: ORA-01792: maximum number of columns in a table or view is 1000.
My end result is no where near 1000, but I am connecting to some very large tables but am not pulling all the fields on any of them.
I cam across a possible solution but I am not sure how or what it is doing, so rather than potentially mess something up I wanted to see if you could provide any input. I came across the following code that would maybe prevent the error. Could you maybe provide some insight on what it is doing or why the error may be happening in the first place considering the tables/data should be the same as is the code being used?
alter system set “_fix_control”=’17376322:OFF’;
thank you
don’t ever, ever, ever, ever, EVER set an underscore db parameter unless My Oracle Support tells you to
that is precisely why I brought it up here. I don’t want to blow anything up. Do you have any idea on why there might be a difference between the two databases to cause this error or what I might be able to do then to avoid it?
you gotta go look at that other database and see what’s what – you’re asking me to guess
Jeff,
My question related to SQL Developer Data Modeler is…
How do I share an object (such as a table) from one relational model to the next (or from one diagram to the next) without making a copy of the object that can be separately modified and get out of sync? If I have the Companies table in model/diagram A (where it is maintained updated) and I simply want to reference it in model/diagram B for an FK reference, can I share the same version only for reference in B preventing modifications but still have updates from the object in A reflected in B??
You can use linked objects.
Perfect, Thanks!
I am using SQL Developer Version 4.0.0.13
When I open up a table and type in a filter to get exactly the record I want to look at… I can doubleclick any field to edit it. Works great.
However, there is 1 field that is greyed out and no editing can happen ?? I am not understanding why this happens and how to fix it ?
Thanks
what can you tell me about this mystery field?
also, your software is a few years out of date…you might just want to upgrade
I can tell you that it is a VARCHAR2 (4000 byte) field that holds comments.
I can also tell you it is frequently populated by a php form / webpage as well.
nothing weird about that…
could it be the length of characters?
or maybe different characters such as / or >
or maybe i just need an upgrade?
Hello,
I would like to ask if there is a way to enable the auto-complete feature of sql developer, for constants defined in a package specification, while writing plsql code.
Regards,
Dimitris
Not today, sorry.
Andrew, this oracle JDBC bug is filed some time ago. If you are coding JDBC then
http://stackoverflow.com/questions/26516204/how-do-i-escape-a-literal-question-mark-in-a-jdbc-prepared-statement suggested the following workaround:
ps.setString(1, “?”);
Thanks for the response, but I am not trying to code JDBC.
I am just trying to run this sql statement in SQL Developer. It works fine in SQL Plus.
When you run it in SQL Developer you get the error described error, that I believe is coming from the JDBC driver SQL Developer is using.
I could be very easily missing something simple. I switched to using the thick client and I get the same error which suggests that I am missing something.
I take back this stackoverflow workaround ps.setString(1, “?”) — the server won’t accept syntax where question mark is bind variable inside pattern clause. It appears there no way to submit your query via JDBC. Therefore, until that JDBC bug is fixed, there is nothing SQLDeveloper can do about it.
Is there a way to use the MATCH_RECOGNIZE clause with the ? character with SQLDeveloper and the JDBC driver?
If I try to use the ? character as a reluctant qualifier token in SQLDeveloper with this example query
with d as ( SELECT rownum true_rownum, floor(rownum/3) rn FROM dual connect by rownum <= 100 )
SELECT * FROM d
MATCH_RECOGNIZE (
ORDER BY true_rownum
MEASURES five.rn as five_rn,
five.true_rownum as five_true_rownum,
twelve.rn as twelve_rn,
twelve.true_rownum as twelve_true_rownum
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN ( five anything*? twelve )
DEFINE five AS five.rn = 5,
twelve AS twelve.rn = 12
) mr;
I get the following error "Missing IN or OUT parameter at index:: 1"
I understand that's from the JDBC driver.
I found this document about this and how to escape the character to let the sql engine process it
https://docs.oracle.com/database/121/JJDBC/apxref.htm#CHECHCJH
But when I try to do something similar
with d as ( SELECT rownum true_rownum, floor(rownum/3) rn FROM dual connect by rownum <= 100 )
SELECT * FROM d
MATCH_RECOGNIZE (
ORDER BY true_rownum
MEASURES five.rn as five_rn,
five.true_rownum as five_true_rownum,
twelve.rn as twelve_rn,
twelve.true_rownum as twelve_true_rownum
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN ( five anything{\*?\} twelve )
DEFINE five AS five.rn = 5,
twelve AS twelve.rn = 12
) mr;
I get an ORA-00911: invalid character error.
Is there a correct way to enter this syntax in SQLDeveloper?
I am using verison 4.1.5.21.
Thanks for your time. I enjoy reading your blog and using SQLDeveloper.
Hi Jeff
We are struggling with a large datamodel (3500 tables) and I am a bit desperate so I have to bring this question up again..
In the SDDM Reporting Repository (v415), is it possible to get or derive information about the remote/linked status of a table (I have looked all over dmrs/dmrv tables/views – but nothing)?
The intention is to use this status in a model quality report. Due to performance issues with Subversion and the complexity of many parallell Projects, the total model is splitt into 20 SDDM designs and we need to know which model is the Master when querying the Reporing schema. This is part of a model migration from ERWin (about 25 files) and we have a lot of mess to fix and the quality report is essential in this work.
Thanks – any suggestions or WA will be appreciated
Jo
I recommend asking on the Modeler forum.
SDCLI64 report output – How do I control the report/column output format? I copied the Free Space report to User Defined Reports. When I run it in SQL Developer the USED column is formatted nicely with a number and % like 83%. However, when I run the report from the command line with SDCLI64.exe, the output is in a HTML file and looks like this: 0.833984375. Can I set the format for this somehow?
Thanks
HI Jeff,
I have my SQL connections created trough ssh tunnels. Is there any way to export/import them as I do with the SQL conns?
Thanks!!
No, but we probably SHOULD enhance it to do this.
Hi Jeff, i´m Using SQl Dev 4.2 (Beta) and can use CLOB Columns with more than 80 bytes , cause i shows onyl the first 80 bytes. Is this a bug ?
Kind regards
yes, it’s a known bug
Hi there, Is there any way to view all invalid package body in SQL Developer?
write a query – turn it into a report
Hello once again,
I”ve got a problem with saving designs in SQL Developer. I’m using sqldeveloper-4.0.0.13.30-no-jre and Oracle DB11gXE.
I ‘m working on a project (I create logical, relational and process models) and save it as .dmd file.
When I open this .dmd file later – it doesn’t contain relational and process models I’ve just saved, only logical model is there.
Any idea how to fix it?
Thanks for any help
v4.0.0.1 is really old.
can you upgrade to v4.1.5?
hello,
I’m just starting with SQL Developer
I’ve got a problem with saving files. When I open a saved file it’s empty, there aren’t any models I’ve created and saved before.
Any idea how to solve that issue?
Thanks for any help
you mean saving models? if you’re trying to open the design, go to the .dmd file
I’m opening .dmd files, but they are empty after being saved. Actually, I can save and open logical models, but relational and process models aren’t there. When I save them and open the file, they’re empty.
those are all in the same design (file) – what do you mean, ‘open the file’ – you’re not using a text editor are you?
Data Modeler > File > Open…DMD. Everything should be there.
yes, the same design, eg. if I create logical, relational and process models in one design, I save it, and then open .dmd file, only logical model is there, relational and process models are blank like I haven’t done anything with them
yes, it’s the same design, e.g if I create logical, relational and process models in one design and save them, then I open .dmd file and only logical model is there
something’s ‘wrong’ – you can provide more details of what you’re doing – say using sqldev with dm or standalone dm, and the version of the tool you’re using on our forums, and we can get you better response
or if you have a db setup with My Oracle Support, you can open an SR with SQL Developer
Jeff,
I have a quick question that I have been trying to figure out for a while. I have built a new logical model in SQL developer. I have also been able to generate the relational model via ‘>>’. But i have not figured out how to specify that all these new tables should all have the same schema qualifier, i.e. ‘manuf.employee’. I see the dropdown on table properties, but their is nothing in the dropdown and I can enter it there (help panel does not even contain that dropdown). Google results talk about importing schemas and do not seem to apply to new models.
Thnx,
– rd
You need to create your schema(s) in the relational model first. Then you can assign them to your tables.
Hi Jeff,
is SQL Developer 4.0 also certified on Windows 10? It seems, it’s not.
And are the certifications of Data Modeler the same as for SQL Developer itself?
Thanks,
Klaus
yes, and it’s supported on Windows 10
the OS support derives directly from whether or not the JDK is supported for that OS.
Oracle JDK 8 is supported on Windows 10, so if you use JDK 8 – you should be good to go
Hi Jeff,
Is it possible to generate out of Data Modeler a script with all grants granted by a user to other user(s)? I know that they come out with the table ddls, but would like to be able to do them separately also.
Thank you!
Not that I can see.
If you use the reporting schema, you could use SQL to build your own scripts – I’m guessing the table privs would be included in the reporting schema.
Hi Jeff,
currently i try out the sqldev E/A Version 4.2.0. yesterday during work the toolbar vanished and i could net get it back. switching the main toolbar of and back on (hope i get t he english terms correct as i use the german translation of sqldev) did not bring it back.
when using the previous version everything seems to be ok.
hope i could make my problem clear. is there a way to bring the toolbar back?
Greetings
peter
try Windows > Reset Windows to Factory Settings (i’m hoping the application menu bar is still there)
Yup, that helped. Toolbar’s back.
Thx
Does SQL Developer support connections to Cloudera Hadoop – Hive/Impala with Kerberos enabled? We successfully connected prior to implementing Kerberos, cannot connect after enabling Kerberos on the cluster. Other client software like DBVisusalizer is able to connect. Thanks for the response. Not looking for support here, just an indication if this setup should be working or not.
if the cloudera driver supports it, it should
have we tested and doc’d that as officially supported – I don’t know