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
Jeff,
There is no reply button/link in your comment/reply. So, I am using the new reply instead of replying to yours.
I don’t see this behaviour if I create or recreate the package with SQLPlus or SQL Developer worksheet.
Can you open a Service Request with My Oracle Support? Trading comments here is going to get tiring quickly for both of us.
Thanks Jeff. I will try opening a ticket in MyOracle.
Oops. Angle brackets in the script got chopped away.
Hi Jeff,
I have been reading your website/blog since may be 2012 and find it very useful. But, I have not posted any comment or question so far. So, here’s my first question.
Q.) Why does SQL Developer each time the package or the body is compiled insert white spaces (12 of them in 4.2.0) between package name and rest of the create or replace statement part before it?
Here is what happens with each compile
Original statement (1st line):
create or replace package my_pkg
is
end my_pkg;
After recompile:
create or replace package my_pkg
is
end my_pkg;
This happens with both package spec and body.
After a few recompiles and several spaces inserted by SQL Developer, the package name is way off the screen and not visible unless I scroll to the right.
Thanks in advance.
That’s definitely not supposed to be happening, nor is it a known issue.
How are you compiling it – as a sqlplus script in a worksheet, or in the code editor?
Are you keeping this in a file?
Jeff,
Thanks for the reply, This is happening when using stored procedure/package code editor to compile: the two gears toolbar button. Actually, I have seen this behaviour for a long time. Just never took the time to post about it.
Something is ‘afoot’.
If you use the View > Log > Statements panel, you can see what we send to the db on a compile and when loading the source code into the editor.
Jeff,
I don’t notice anything (like inserting a tab or spaces) going in the SQL or PL/SQL sent to the database.
However, when I query the dba_soruce using one of the statements from the statement log, I see spaces between “create or replace package” and package name.
I see the owner (in ALL CAPs) and a dot (.) inserted before the package name even though in my create statement I don’t include it.
Looks like something happening in the database or the statement log does not show actual statement for create procedure.
This is on a DB that I use everyday and is on 12c.
I just now tested this in another DB that’s on Oracle 11c and don’t see this behaviour. However, I have noticed this in Oracle 11c also.
Sounds like the issue is server side. Does this happen if you do this in sqlplus?
Hi Jeff,
I believe you have said many times that SQLcl supports all SQL*Plus commands (with certain documented exceptions). I have found this difference in behavior of the PROMPT command:
In sqlplus (version 12.1.0.2.0):
SQL> PROMPT “test”
“test”
in SQLcl (version 4.2.0.17.097.0719):
SQL>PROMPT “test”
test
What happened to the quotes under SQLcl? Why is it dropping them? I do not think sqlplus had a problem with unquoted strings and users had to use quotes – it was always the user’s choice to have or not to have them in the output.
This difference is important for some of our scripts. Is this a feature of SQLcl and is here to stay, or it may change in some future release?
I don’t know if that’s a recognized/identified difference.
Hi Jeff – new to ORDS. Have a good example working in our dev area. Wondering if you have a good suggestion about how to promote to a clustered weblogic production environment? I’m a little confused on what is stored where.
Thanks!
Hi Jeff,
In a similar vein to deferred constraints, how does one do function-based indexes in the Data Modeler to have them be captured correctly in the DDL that gets later?
You mean like this?
Hi Jeff,
Just upgraded to 4.2. I have a query I use that incorporates variables to be used so that I can run several different queries without having to put the same information in each query. In previous versions, I was able to “comment out the line” with the use of — and the Run Statement command would skip all the commented lines, and execute the desired line, regardless of how many commented lines were between the first line (with my variables) and the desired line. Now the lines have to be commented out using /**/ at the beginning and end of the line for the same effect. Is there a way to set it so that — will work as I utilize Ctrl+Slash as the key stroke to comment/uncomment the lines wanted.
WITH my_vars AS (SELECT ‘99999’ AS val1, ‘XXXXX’ AS val2 FROM DUAL)
–SELECT * FROM TABLE1, my_vars WHERE FIELD1 = val1 and FIELD2 = val2;
–SELECT * FROM TABLE2, my_vars WHERE FIELD1 = val1 and FIELD2 = val2;
SELECT * FROM TABLE3, my_vars WHERE FIELD1 = val1 and FIELD2 = val2;
In the example above, previously Ctrl+Enter would execute for Table3, but now it doesn’t. Is there a setting that needs to be changed?
Thank you,
David
Should be 2 dashes in front of the SELECT for TABLE1 and TABLE2
No, it’s a bug. Will be fixed in our next update.
So will it wait until a new version or like a hot fix? Is there a way to be notified if/when it gets fixed? Is there a bug number I can follow?
great questions – for My Oracle Support. New version will be avail SOON.
When I launch SQLCL, it stores its user related information to %USERPROFILE%\AppData\Roaming\sqlcl folder. Can it be stored some where else. For e.g. I can set IDE_USER_DIR environment variable for SQL Developer to store it in a different folder. But this environment variable does not work for SQLCL. Please let me know.
I found that if I set the APPDATA environment variable to appropriate path then it stores the values correctly. For now I am using this workaround for portability . I hope in future releases sqlcl will support IDE_USER_DIR environment variable.
This should work too http://krisrice.blogspot.com/2012/05/sql-developer-shared-setup-from-any.html
Thanks. The method mentioned by kris works for SQL Developer but not SQLCL itself. It seems that SQLCL does not use the SQL Developer options. It stores aliases.xml and history.xml in a sqlcl folder in APPDATA. Also using the environment variable allows me to quickly unzip the new version without having to modify the delivered file which will be overwritten. My goal is to install SQLCL and SQLDeveloper on a citrix maching where I want to write all config files to a shared nas drive for each user using %USERNAME% variable. So far setting APPDATA and IDE_USER_DIR before launching the SQLCL and SQLDEVELOPER works fine.
Interesting, that setup is done with Citrix in mind, that’s why we write to the Roaming profiles section.
Hi Jeff,
In SQL Developer Data Modeler, how does one do defferred constraints? I have an application table and a payment one. The relationship is 1:1, so I would expect a deferred constraint, but can’t find a way to draw one… Or is there a way to ‘inject’ it into DDL?
Also, I’ve noticed an occasional scenario when I have table X and want to create a new table with a foreign key to it. Although I enter the same exact values into the FK boxes, some end up with the ‘many’ crow foot in the new table and others end up with a ‘one’ two-bar in the new table… I can’t tell what’s causing this behaviour and/or how I cam change this. Could you please help?
I think I figured out the second one: driven by whether your FK is unique or not….
Hi Jeff,
I also cannot find “deferred” property or checkbox for a foreign key in the properties page in Oracle Data Modeler 17.2. I see ‘mandatory’, ‘transferable’, ‘in arc’, ‘deprecated’, and ‘generate ddl’…
Can you please point me to how to mark a foreign key to be “deferred” when generated in DDL scripts?
Thank you,
Milan
Go into the Physical Model, and open the foreign key constraint properties there.
Hi Jeff,
Wondering if this one got lost in the shuffle… How does one designate FKs as deferred? I am running SQL Developer 4.1.1.19.
Thank you!
I’m at the beach this week…but there should be a deferred checkbox on the relationship properties dialog.
Hi Jeff,
Hope you enjoyed the beach. Sounds nice! 🙂
On 17.2, I don’t see any checkbox like that…
I’ve got ‘mandatory’, ‘transferable’, ‘in arc’, ‘deprecated’, and ‘generate ddl’…
There is a ‘discriminator column’ dropdown on the same screen and ‘dependant columns constraints’ section in the nav bar, but that’s all that’s noteworthy… Would it be possible for you to post a screenshot of where that option is?
Thank you!
Thank you very much Jeff for your fast answer.
I’m using 4.2.0 version of SQL Developer and found a pretty weird behaviour about bind variable. After using following commands:
VARIABLE var1 NUMBER
EXECUTE :var1 := 10
SELECT * FROM scott.emp WHERE emp_id = :var1; — here I get a prompt to input bind variable
The steps same works in SQL*Plus.
Did I missed some condition to make it work?
It works for me.
I tried F9 with no success.
Right, F5 works.
Thank you for your quick help! 🙂
That was my guess…F5 calls the SQL*Plus engine…F9 does not.
I have to run data patches against production databases on a regular basis. For years I have used SQL*Plus without problems. Recently I decided to start using SQL Developer. The F11/Commit button has failed on a couple of occasions leaving a lock in the database and users complaining that their data has not changed. Do you have any idea what I might be doing wrong? The SQL Developer version is 4.2.0.17.089. The database version is 11.2.0.4.
No idea, at least not without more information. Like what does your script do, what sort of lock was left for your session, etc.
Understood. As I have a work around (i.e. using SQL*Plus) I will carry on with that for now.
I am using SQL Developer to import data from an Excel csv file into Oracle table. Works like a charm except when attempting to import a text cell with line breaks and formatted lists into a CLOB field. Is this even possible?
In a CSV?
Probably easier from an XLSX.
Pass me a sample file and I’ll see what I can do.
Hi Jeff,
We are planning to migrate a one TB sybase database onto Oracle 12c. We are thinking to use SQL Developer Migration Workbench. We will need to migrate a number of sybase databases. Surprisingly, I have not found any case study in the web regarding size of database migration, whereas, other third party tools publishing case study with size of databases.
My worry is: Will it be possible to migrate >1TB database in stipulated release window. Release window starts on Friday 20hrs and ends at Sunday 8hrs.
You need to do some test runs first. And you’ll want to use the offline data move, which will move the data much faster than the online data move. If you have GoldenGate licensed, then the data will move even faster.
Hi Jeff,
Thanks for your reply.
Unfortunately, we don’t have Golden Gate licensed. If I find any article/case study/document where oracle claims that the fastest way to move data offline is nnnn/sec using SQL Developer Migration Workbench, then I can take that as an example and can start communicating with stakeholders to provide them an idea about elapsed time. All metadata,procedures etc.. will be migrated in one weekend and next weekend data needs to be migrated.
SQL Developer is our migration platform. It’s not the fastest, it’s the only.
How long it will take will depend on the nature of the data, your hardware, and so many other variables. That’s why you need to do a couple, if not many, test runs. Then you can hit your launch day with confidence.
Also, moving data vs translating stored procedures is completely different. Testing the migrated stored procedures can take a long time, and will probably require some developer intervention to fix things that weren’t completely translated.
Hi Jeff,
Now, the client has changed plan. They first want to upgrade Sybase ASE from 15.7 to 16 and then later of the year, they want to migrate to oracle 12.2.
My Question: Does SQL Developer support ASE16 migration? I see, up-to Sybase(15) in the offline options window.
Maybe. I don’t think we’ve tested it from 16. It’s weird they want to do a major upgrade and then turn around and do an entire platform migration.
If nothing major has changed in 16 and with the drivers, you should be OK. But try it first for sure.
Hi Jeff,
I’ve been scouring your site for the last couple of hours, so please forgive me if this question is answered somewhere that I’ve overlooked. Basically – I’d like to a way to jump quickly to a pre-established certain table in a specific connection, whether open or not? I hate to use the terms Bookmarks or Shortcuts, as they already have meanings in SQL Developer that don’t match what I’m referring to. Perhaps “Favorite” or “Project” would be better. Actually, “Project” would be great – open a saved project, and specific tables (down to connection.schema.table) would open.
So – am I just totally overlooking this feature? Possibly available as a 3rd party extension? Even the ability to launch SQL Developer with those tables already open would be useful: configurable via command line parameters, perhaps?
Forgot to mention that I’m running 17.2
Thanks!
No, we don’t have anything like that…although you could create a report to do this.
In every database, create a WORK.STUFF table, then a report that queries that table that builds a list of object links. You could use that report as a navigator.
Thanks, Jeff – I can definitely do something with that!
Hi Jeff,
In our environments we are facing SCN issues due to DB links (SCN value suddenly increasing due to external db links) .So instead of db links we want to use ORDS .Could you please guide me how to do it?
Thanks in Advance.
Regards
Ramesh.D
Do you work for Oracle? If so, email me. We can go over your exact requirements.
In the SQL History for release Version 4.1.5.21 the TimeStamp shows as milliseconds even though the NLS for TimeStamp is “YYYY/MM/DD HH24.MI.SSXFF9”. How do I change SQL History TimeStamp to match the NLS TimeStamp?
Hey Jeff.
I’ve been trying to get a funnel chart to work and have not been successful.
I tried multiple variants, but here’s the current version of the query that I’m using…
select ‘Stage 1’ as Stage, 100 as Txns
from dual
UNION ALL
select ‘Stage 2’ as Stage, 50 as Txns
from dual
UNION ALL
select ‘Stage 3’ as Stage, 25 as Txns
from dual
The funnel chart that is produced shows all 3 stages as 0%.
Any suggestions on what I might need to do to get the funnel chart to show Stage 1 as 100%, Stage 2 as 50% and Stage 3 as 25% would be much appreciated.
Thanks,
Bill
Hi Jeff,
Just checking back to see if you can provide any insight on how to get the funnel chart working. Thanks!
Hi,
Oracle SQL Developer gurus . Somehow the query result tab is not opening when running a query though all possible options are checked in the preferences. Any clue why?
Thanks.
Is it possible it’s just hidden? There’s 2 triangle looking buttons you can hit to hide/expand the output panels. Can you see script output, how about an explain plan?
Go where?
Hi Jeff,
How do I ask a question about SQLcl here ? I am on the “Ask A Question” page, and I see your introduction with the three reminders, and a “Go!”, followed by a long list of comments from users with your answers, but no place to enter my question besides the “Leave a Reply” section at the very bottom. Is that the place to ask my question? Or maybe you have stopped accepting questions on this website?
I guess that was the place to ask the question. Then, here it is:
I do not have special settings limiting what protocols SQLcl should use to connect to my databases. When I try to connect with a wrong password, SQLcl seems to try at least twice, first using jdbc:oracle:oci8 and then using jdbc:oracle:thin (see below). Both attempts get rejected with “ORA-01017: invalid username/password”. Is it really trying twice? (I do not have privileges to verify that from the database side). If so, why try the second time if it is clear that the first attempt successfully found the database? This can exhaust my allowed attempts for logins with a wrong password and lead to getting the account locked. Is this a feature or something that can be improved?
SQL> conn myname/badpassword@mdmrep
USER = myname
URL = jdbc:oracle:oci8:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP
)(HOST = xxx.xx.xx.xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED
) (SERVICE_NAME = MMREP) ) )
Error Message = ORA-01017: invalid username/password; logon denied
USER = myname
URL = jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP
)(HOST = xxx.xx.xx.xxx)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED
) (SERVICE_NAME = MMREP) ) )
Error Message = ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
It’s the right place alright – well one of several right places, including My Oracle Support, the Forums, etc. That’s a bug. It should only be doing that when we can’t resolve the database, not when the username/password is wrong. Can you post this on the forums so I can have the DEV take a look? I can only partially reproduce it at the moment, but as soon as you get the 1017, it should immediately stop and ask you to re-enter your username and password.
Hi Jeff,
We have hit a strange issue with SQL Devleoper v4 (4.1.5.21 to be specific) which I am struggling to find much info about.
We have enabled sqlnet encryption with the following set in our DB server side sqlnet.ora:
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA256, SHA1, MD5)
We can connect to the database hosted on here fine using SQL Dev v3.0.04 but when using the above v4 version the connection fails with host checksum errors and sometimes hangs.
In the DB alert log we are getting this:
TNS-12599: TNS:cryptographic checksum mismatch
Very little info on this around other than some known OEM agent bugs.
As soon as I remove SHA256 from the checksum types in the sqlnet.ora file the connection works fine from both versions.
Are you aware of anything in SQL dev settings / configuration that might cause this ? I know it is a longshot…
Thanks
Paul
The two HUGE differences in v3 and v4.1 are, the version of Java, and the version of the JDBC driver.
I’m assuming you’re also using a THICK connection and are using two different versions of the Oracle Client?
This is an out of the box setup, we are actually accessing it via a remote desktop due to security, we have checked and neither oci/thick or oracle client are selected.
Trying a thick client is what I would try first – although interesting to see if that’s how SQLDev 3 is setup.
Hi,
My question is about ORDS in APEX and the oauth2 authentication.
I follow this example for ORDS in the Database and everything’s working fine.
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication
When i’m trying to do the same thing in ORDS in APEX, i’m stuck where i’m calling the oauth/token to receive a token
ORDS :
curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/my_user/oauth/token
ORDS APEX:
curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/sandbox/oauth2/token
When i’m calling the link https://localhost:8080/ords/sandbox/oauth2/token in a url, i get a 404 Not Found.
Do you know the equivalent to oauth/token in ORDS APEX ? Is there a setting that needs to be activate so that the oauth2/token url will work ?
Regards,
Martin
You’ll need to migrate your RESTful Services in APEX to ORDS 3.0 or higher.
Hey Jeff, quick question…
Is there any tool that you’re aware of (or plans to add to SQL Developer maybe?) that will export from an oracle database into a netcdf file?
If it’s an open format, you could build your own with SQLcl and a bit of js though.
Thanks Jeff – that’s what I needed to know! – Kevin
Hi,
My question is about ORDS in APEX and the oauth2 authentication ?
I follow this example for ORDS in the Database and everything’s working fine.
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-authentication
When i’m trying to do the same thing in ORDS in APEX, i’m stuck where i’m calling the oauth/token to receive a token
ORDS :
curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/my_user/oauth/token
ORDS APEX:
curl -i -k –user CLIENT_ID:CLIENT_SECRET –data “grant_type=client_credentials” https://localhost:8080/ords/sandbox/oauth2/token
When i’m calling the link https://localhost:8080/ords/sandbox/oauth2/token in a url, i get a 404 Not Found.
Do you know the equivalent to oauth/token in ORDS APEX ?
Regards,
Martin
Jeff. I read a post you had about exporting connection profile and from SQL developer. Do you know much about SQL Navagator? Well I just received a new PC old had SQL 6.2. New will have SQL 10. Anyway you know how I can bring this email connections over to new PC with passwords of course?
Ask the Quest people, I’ve not used it in about 6 years.
I ran EXEC SP_MY_PROGRAM(‘USER’);– SID 99
I am observing data insertion from another session(599) after some time I am not able to see PROGRAM RUNNING SESSION (99) but data is getting inserting into table.
May I know what might be the reason and how do i know when program got completed.