4.5+ million 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!
Comments 3,657
Any idea why TNSPING command from sqlcl is throwing this error for any db I try?
SQL> show version
Oracle SQLDeveloper Command-Line (SQLcl) version: 17.2.0.184.0917
SQL> tnsping mydb
Sep 22, 2017 11:29:50 AM oracle.dbtools.raptor.newscriptrunner.ScriptExecutor run
SEVERE: java.net.URLClassLoader.getAndVerifyPackage(URLClassLoader.java:634)
java.lang.SecurityException: sealing violation: package oracle.net.ns is sealed
at java.net.URLClassLoader.getAndVerifyPackage(URLClassLoader.java:634)
at java.net.URLClassLoader.definePackageInternal(URLClassLoader.java:654)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:689)
at java.net.URLClassLoader.access$400(URLClassLoader.java:95)
at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1184)
at java.security.AccessController.doPrivileged(AccessController.java:686)
at java.net.URLClassLoader.findClass(URLClassLoader.java:604)
at java.lang.ClassLoader.loadClassHelper(ClassLoader.java:850)
at java.lang.ClassLoader.loadClass(ClassLoader.java:829)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:329)
at java.lang.ClassLoader.loadClass(ClassLoader.java:809)
at java.lang.ClassLoader.defineClassImpl(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:346)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:154)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:729)
at java.net.URLClassLoader.access$400(URLClassLoader.java:95)
at java.net.URLClassLoader$ClassFinder.run(URLClassLoader.java:1184)
at java.security.AccessController.doPrivileged(AccessController.java:686)
at java.net.URLClassLoader.findClass(URLClassLoader.java:604)
at java.lang.ClassLoader.loadClassHelper(ClassLoader.java:850)
at java.lang.ClassLoader.loadClass(ClassLoader.java:829)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:329)
at java.lang.ClassLoader.loadClass(ClassLoader.java:809)
at java.lang.Class.forNameImpl(Native Method)
at java.lang.Class.forName(Class.java:278)
at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.createProtocolInstance(PingCmd.java:144)
at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.ping(PingCmd.java:98)
at oracle.dbtools.raptor.newscriptrunner.commands.PingCmd.handleEvent(PingCmd.java:57)
at oracle.dbtools.raptor.newscriptrunner.CommandRegistry.fireListeners(CommandRegistry.java:446)
at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:214)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:336)
at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:225)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:405)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:416)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1128)
at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:492)
Author
Do you have a ojdbc7 AND ojdbc8 in the lib directory?
Nope.
export CLASSPATH=~/sqlcl/lib
Nothing else in the classpath. Only ojdbc8.jar in the distro. Would the system path be in play as well?
Author
yeah maybe…you could nuke the jar from the sqlcl/lib and see if lets you connect anyway
That definitely did the trick. I’m running sqlcl from a user who also has dba rights and access to $ORACLE_HOME… and $ORACLE_HOME in the PATH…. but again, not CLASSPATH. I’m sure there are possibly several ojdbc variants as part of the standard db install. In my case, I’m using latest PSU of 11.2.0.4.
Author
Great…it’d be better for it to go with our jar as the driver is MUCH newer. Or if you’re OK for now, I’ll leave you be.
Hi Jeff,
You are doing great work, thanks.
I have a quick question, maybe it is already answered in your form, i tried little bit but couldn’t get the answer.
When I create a new connection, by default it goes to Basic Connection Type, however I want to use my tnsfile so I select TNS then it shows database (services) names, but they are multiples, I mean several entries for one database/service name, even though there is only one entry in tnsnames.ora file.
Please post a solution or workaround at your earliest.
Thanks and regards.
Author
How many TNS* files do you have in that directory? I’m betting, more than one.
We read and use all of them – because SQL*Plus does. It’s just not noticeable in SQL*Plus because there’s no GUI there to demonstrate it so easily.
It is. you are the man.
Yes, I had several tnsnames.ora files because we have one golden on a server and whenever it gets updated, and we reboot our own laptops the local tnsnames.ora is updated from the golden and before that a script renames the local file to something different (with time and date stamp). I moved the other files out of the Admin folder and I see only one entry. Thanks a lot.
So it reads from every file, even though it has different name like tnsnames_sep20.ora ?
Anyway, thanks for your reply and the fix.
SELECT name, usable_file_mb, round(usable_file_mb/(case when type=’NORMAL’ then total_mb/2 when type=’HIGH’ then total_mb/3 else total_mb end)*100,2) as percentage
FROM v$asm_diskgroup;
This might have been asked/blogged about before as it’s been a bug for years; but I’ve not noticed it when Googling. The percentage column shows null in SQL Developer. In SQL Plus it shows the real value. A bunch of other v$ queries have the same issue, is this fixed in a newer version of SQL Dev (I’m on 4.0.3.16)
New sql developer 17.2 hangs when I try to debug. It will not open the anonymous block and run the program. Shows like running, watch window pops up but nothing happens.
Previous 5 versions all took settings from each other and never had problems.
Author
Can you post to the forums? I’ll be able to help you better there.
I’m currently on release 3.2.2 of SQLDeveloper and would like to upgrade to the 4.2 release. I currently have a major application developed in my 3.2.2 version. Question is, can I upgrade to the 4.2 release with no effect on any of the Procedures, sequences, tables etc. to my current application or do I need to export and re-import the application after upgrading it.
Author
No worries, those things are completely independent of each other. Go ahead and upgrade.
Jeff,
This may seem very elementary but i am trying to find a video tutorial on how to build a RDBMS from scratch.
Im looking for the basics to building
I understand and took classes on sql plus
and querying and such but im lost in starting from scratch.
I have the oracle 12c
and also the express 11g
could you guide me??
pupmike
Author
Try this. I love his videos.
Previously there were more options to generate code (Advanced Format, now Embed/expose – Ctrl Shift F7). Is there any way to configure other formats like c#, vb like before or is it only limit to java, Python and pl/sql?
Author
We just support those 3 now. Are you missing both c# and vb, or would you probably just use one over the other?
for c# i can use the java though the @ santax for Strings is more nice.
So most needed is the vb
Hi Jeff!
I just started using SQLcl and it looks great! Running scripts and looking for error messages has always been a pain and I was thinking that if I were able to highlight the ORA-messages in the output that would be awsome. What are the chances that some form of output parsing rules/functions would make it into SQLcl?
Maybe there already is functionality in there that I could use for this?
Author
So…not easily. The best I could offer is to use something like this to ADD a note after an error.
Hello, Jeff. I would like to ask if there is a way to find my already stored database passwords on my Databases and if there is a way, can you please analytically explain the procedure step by step. I have tried through an Oracle Sql developer extension that exists on the internet and it didn’t work. Moreover, there are some python or java codes but didn’t work for me, too. These codes exist in the case there is an .xml export of the databases (in which the oracle sql developer asks a password for encryption). The version of Oracle Sql Developer is Version 4.2.0.17.089. Thank you, very much! BR. Telis
Author
You’ve lost your password, but SQL Developer knows what it is, b/c at one point you told it to change your password?
If this is the case, simply use SQLDev to change your password once you’re connected.
Thanks for the immediate answer. I forgot the password, but sql developer knows it b/c I had used the save option in order to get stored automatically and never need to insert every time I log in.
Is there any other way without the option of resetting it (the password)? Thanks again, Jeff.
Author
There’s a 3rd party extension that claims to recover passwords. I don’t endorse it, but if you google it, you will find it.
Hi Jeff,
Just looking at the DBA=>database=>tablespaces=>select tablespace=>sort by size tab.
The sort decreasing (of the objects) starts with 9 and then finishes with 1 (no matter how many digits). I think this is because it is attempting to sort a character string not a number. I’m using version 4.2.0.16.
Are there any settings I have missed out on to get the sort to function correctly?
Regards
Colin
Author
Yeah I logged a bug on that.
Hi Jeff,
I would like to use the Object browser / Data to change content of a view that is having insteadof triggers. Running update statements directly works of course fine but in the Object browser / Data, the cells are grayed out – not for editing. Are there any preferences or.. to make the view content (data) available for update?
Thanks in advance!
Jo
Author
We look to see if the view columns are avail for updates, see ALL_UPDATABLE_COLUMNS, then we try to pull a ROWID for each row. If we can’t, then you can’t update the view with the grid. You can also look at the ‘Use ORA_ROWSCN for DataEditor insert and update statements’ preference.
Thanks for the good explanation. The instead of trigger can bypass the ALL_UPDATABLE_COLUMNS. But still there is a need for a rowid/ora_rowscn in the where condition of the update statement from the grid to the database. My best option is to add a primary key constraint (novalidate..) to the view but I guess this is not good enough for the update statement.
Br
Jo
I installed Oracle Sql developer 17.2 on my mac, trying to add third party JDBC driver but there is no option of preference present in tools. So not able to add third party JDBC driver.
Please help.
Thanks Prasad
Author
Mac apps have their preferences on the main menu, so look under SQLDeveloper menu.
Hi Jeff,
I learned about the Db Doc functionality in SQL Developer thanks to your previous post “JAVADOC for the Oracle Database a la DBDOC”. I am running SQLDeveloper v17.2.0.188.1159. Currently the DB Doc will not generate for standalone functions or stored procedures, only those that are encapsulated in packages. Can you point me in the right direction for finding out if/when this feature could be available? It would be extremely beneficial to my organization.
Thanks for all the great information re: SQLDeveloper BTW, truly appreciated.
Author
Argh! That would def be a bug if I can reproduce it. It SHOULD be and was working previously.
Hi Jeff,
when exporting a cart using the “separate directory” option multiple times the exported files are never replaced. with every export a new set of files is created with a prefix to make the filenames unique. that is a bit cumbersome because i want to check in the changed file in our svn. in the moment i have to delete all files prior to exporting them.
is there an option to deactivate the behavior so that exporting will overwrite existing files?
Author
The cart has a CLI available using the SDCLI exe in your bin directory. Script it such that a bat or bash script deletes the old files first, then run your cart. Then check your stuff in.
Hello jeff,
Below query gives me complete table description, relationship, primary key foreign key etc.. could you please help me translating below sql server query to oracle
USE [Database_Name]
— ===============================
— Description: GENERATE DATA DICTIONARY FROM SQL SERVER
— =============================================
CREATE proc [dbo].[spGenerateDBDictionary]
AS
BEGIN
select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
is null THEN ‘-‘ ELSE g.name END [Ref Table],
CASE WHEN h.value is null THEN ‘-‘ ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT so.id,sc.colid,sc.name
FROM syscolumns sc
JOIN sysobjects so ON so.id = sc.id
JOIN sysindexkeys si ON so.id = si.id
and sc.colid = si.colid
WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id
left join sys.objects as g on e.referenced_object_id = g.object_id
left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
where a.type = ‘U’ order by a.name
END
Thanks and Regards
Sindhu
Author
We have a migration tool for sqp server…it will bring your table and data over…and translate your stored procedure. Have you tried that?
Hi Jeff,
Is SQLcl available in any open repository like Maven central, Github, some oracle repo ?
If not is there any ways to get the latest stable version in an automated way?
Right now I am downloading it manually from the oracle website, but if we decide to use it as a replacement of SQLPlus there must be some way to automate the installation.
Regards,
Mitko
Author
How are you automating your SQL*Plus installs today?
We’re working on open sourcing SQLcl, so hope to get it in our GitHub project soon.
To be honest I personally have the same problem with the current oracle client installation.
I hope one day we will have it in Homebrew, Apt-get or Yum.
In the java world the jdbc is already in maven, but it’s still quite cool to use the sql formater from SQLcl.
The main difference is that it’s well know and in a corporate world it either comes as part of the OS image or there is already an approved procedure to do it.
The opensource version would be quite cool.
Cheers
Hi
This is not a comment but a question: There used to be a spatial add-on called Raptor. We deal with a lot of spatial data and I am interested in using SQL*Developer to manage spatial data. Are there any spatial tools available that I can use within SQL*Developer ? If there are none then do you know IF there will be any available any time soon ?
Author
There’s a 3rd party extension for Spatial, but we also ship with Spatial support out of the box.
I’m trying to use the select /*csv*/ option to create a csv file and then load the file using bcp. My problem is something to do with line terminators. When I export the file from the grid using export everything works fine, but when I try to spool the file using the script option and try to load the file, I’m getting unexpected EOF issues.
I’m using version 4.2.0.17.089 build 17.089.1709
Author
Load it to SQL Server?
Actually – I’m trying to load it into Sybase – as I said, if I export the data from the grid into a csv file it loads fine using bcp. However, I have over 150 tables and I want to use the spool option
spool “H:\Migration\08282017\CATSDB_08282017.csv”
select /*csc*/ * from SAPSR3.CATSDB;
spool off;
Author
If you were trying to load it to another Oracle instance, I could help you. Spooling it to csv for SQL*Loader to Oracle is apparently working just fine.
OK – Thanks – I’ll keep trying
FYI – I added ‘set feedback off’ to my script and this solved my issue
Hi Jeff,
Is it possible to use expression to define foreign keys names in Data Modeler 17.2?
I would like to name my foreign key like this {model}_{table abbr}_SUBSTR(1,3,FRONT,{column})_FK_I_DECODE({relationship},SUBSTR(1,8,FRONT,{relationship},”,{relationship})
Best Regards,
Antonija
Author
Not in the UI but you could probably write a transformation script to do it. So it would run and dynamically rename all of your FK names. You’d have to write it in javascript, o you wouldn’t have access to oracle SQL functions.
Another question, can I apply transforamtion to just one table in relational model? If yes, how?
Best Regards,
Antonija
Author
Probably, you’d just have to code it that way. If name = …
Thank you for helpfull answers.
Best Regards,
Antonija
In SQL Developer Data Modeler, could you please tell me how the Overlapping Attributes part of the Entity Properties is supposed to work? I’ve tried checking various boxes and clicking Apply then OK, but have yet to see any affect from it. The boxes in the Folded column won’t allow me to check them.
Also, there doesn’t appear to be anything in the help which explains this feature.
I’m running version 17.2.0.188 Build 188.1159.
Thanks
Author
I don’t believe you’ll see anything ‘happen’ until you engineer your logical model to a relational model, see this
Jeff-
Application developers often have to copy data from our Production databases into our Test and Development databases to debug data errors happening in applications. Right now, application developers have to request that a DBA export Production data and import the data into Test and Dev. This often takes days because of the DBA’s workload. Developers have access to all the data in Production, so is there a way to use SQL Developer to copy the data instead of bugging the DBA’s all the time?
Author
Lots of things actually. Tools, database copy. Drag and drop. The cart.
Why in 17.2 don’t work copy and paste (to Worksheet) in list of database objects (tree Connections)? in previous version 4.x works ok. I know is work drag and drop but I like use C&P.
Author
Because bug. It broke in 4.1 or 4.2 I think…should be fixed in 17.3..stay tuned.
Re: How do I compare two query result sets by comparing grid to grid ?
If I run one query – the result set gets output to the grid – and then run another query with it’s result set output to another grid is there a way to compare the grid result sets and output the differences to another grid or window ???
Is there a plugin for Oracle SQL Dev 4.1 that will do this ??? I would LOVE to have this functionality as I’m always comparing data from one query versus another query. The export to Excel and it’s comparison functionality is painfully slow and NOT user friendly.
Thank You 🙂
Author
No way to do that, unless you want to use SQL to do it…check out the MINUS command.
Thanks Jeff, I’m familiar w/the minus command. I just want to select two grids, right-click, compare and see a third grid w/the differences.
Author
And what happens if each grid has 100,000,000 rows in it? That’s the conundrum i face each time I look at this feature request. Comparing a few hundred or thousand rows is no big deal.
Jeff,
I am trying to copy objects from one scheme to another across different connections. Table ddl statements fail due to missing table space. Is there a way to turn off table space option when selecting objects in Tools -> Database Copy Object Copy.
Thank you
Author
try dragging and dropping your tables from one connection to another, I’m pretty sure that skips over tablespace info
Thanks Jeff! That worked. However, I would like to point out few things for consideration:
1) constraint ddls fail because they are executed twice – once as part of table ddl, second time as an alter statement. It should be one or the other when both are selected.
2) Package bodies need to be explicitly selected otherwise only specs are copied. I have not found an easy way to select multiple package specs and bodies, short of clicking one by one.
3) Dragging could be impractical/cumbersome with tens of connections in your explorer window. It would be nice to right click on selected objects and select schema to copy to + options.
4) It would be nice to fix Database Tool copy to remove tablespace clause or make it selectable.
Thank you!
Author
I agree on #4…and I can’t remember why it’s like that now…but I know there MUST be a reason. I’ll take a look into it. #1 – can you provide an example?
How do you use Spool to export Query results to Excel? I specifically need it as Excel and not CSV.
Author
you’d have to spool to CSV and let Excel convert it…no way to spool a binary file format like Excel
Hey Jeff,
Love your info and product. My question is where did the equivalent to the /datamodeler/xmlmetadata/doc that a few folks seeking info on the SQL Developer Data Modeler object API for scripting were referred to end up for releases 4.2+ of SQL Developer Data Modeler? I cannot seem to find this folder or the docs in most recent releases.
We believe that some custom scripts for our shop may be helpful to streamline certain naming, standardization and generation workflows. How can we find out more about the objects, properties and methods available within scripts?
Thanks,
Jim
Thanks for the update! Please could you point me in the right direction to raise this ER
Author
You would need to create a Service Request (SR) with My Oracle Support (MOS)
Hi Jeff,
Thanks for the information – I have completed an SR for this enhancement.
I also mentioned in the SR a potential bug within the SQL developer unit tester when specifying the expected exception number. Shouldn’t this number be negative to be consistent with the PL/SQL routine “raise_application_error” and the PL/SQL compiler pragma “exception_init”?
Using a negative exception number in the unit test does not catch the exception. e.g. specifying -6510 or +6510 doesn’t work but 6510 does.
Many thanks,
Mark.
Hi Jeff,
MOS suggested I raise a SQL Developer Enhancement Request and the SR has been closed. Here is the number of the feature request: #45481
Hi Jeff,
I am using Oracle SQL Developer version 4.2.0.17. My question is : Does it support to migrate Sybase ASE 16 to Oracle 12.2, as I don’t up to Sybase(15) in the options.
Regards,
‘Deep’
Dear Jeff,
Could you please tell me how to get tab-separated column results under SQL Developer 4.2? Under 4.1, I was used to using the following script:
col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep “&TAB”
select * from table;
but that seems to no longer work in 4.2. Is it a bug?
Many thanks,
Matyas
Is there a way to script the export function?
I have a report setup that I can export to a xlsx file. I would like to script the report and export so I can run it as a scheduled task from the OS. Even better would be to send it via email.
I know there are many ways to accomplish this, just thought I would try with SQL Developer.
Thanks,
Rob
Hi Jeff,
I am new to the unit testing functionality within SQL*Developer v17.2.0.188 so forgive me if this question is obvious. When creating a new test, I must decided whether the desired outcome of a PL/SQL subprogram invocation is successful or an exception is raised.
When detailing the expected exception, the product allows a number or the word “ANY” which is similar to the WHEN OTHERS generic handler. Is it possible to handle PL/SQL named exceptions rather than relying on system or user-defined exception numbers?
Many thanks in advance,
Mark.
Hi Jeff,
Do you have an update on this please?
Many thanks,
Mark.
Author
if the UI doesn’t currently support it, i’m guessing the answer is no, but it sounds like an excellent ER
Jeff,
Will the webcasts you are starting be recorded for viewing at a later date?
Thanks,
David
Author
If I said ‘yes’ would that mean you’d not try 😉
But, yes.
No, I will be on, but a colleague that was looking forward to it has a conflict. Thanks for the quick response.
I use SQL developer pretty much every time I touch Oracle so kudos on your efforts. Do you remember working in the Honors office?
Author
Oh wow, of course I remember! Do you miss that Access system – I do not!
Hi Jeff. What happened to Snippets in SQL Developer V4.2.0.17.089? It doesn’t appear in the View menu like it did in 4.1.3.20.
Author
they’re there in v17.2 – pretty sure that wasn’t a known bug for version 4.2 though
Hi Jeff,
I need to create multiple data export files by executing 5 sql scripts in succession, each spooling to a different CSV output file.
I can do this in SQLDeveloper v4.1.5.121 but it prefixes the output with the CarriageReturn and LineFeed characters ( CR + LF. )
How can I get rid of these ?
Below is a listing of one of my scripts and the top 2 lines of the output
I was hoping sure this is a simple task but I searched for hours for this fix without success.
Took me 4 hours to figure out how not to get the Prompt lines (SQL >> @ C: … ) at the beginning of the file !!!
Thanks for the help.
Aubrey
<>
set SQLFormat CSV
set echo off
set feedback off
set HEADING off
set verify off
spool C:\WorkInProgress\text1.txt
select * from VDS_USER;
spool off
<>
“ID”,”NAME”,…
<>
@ C:\WorkInProgress\testSQLDev.SQL
Author
a TON of work went into the script engine (the SQL*Plus stuff in sqldev) between version 4.1 and version 4.2. In the latest SQLDev, I don’t see any leading blank lines when running your script. Can you try upgrading?
Please provide a walkthrough for creating data flow diagrams in the Data Modeler.
I cannot find anything in the documentation. Usage is certainly not intuitive.
Author
could you post this on the modeler forum? I’ve never used it, so I would have to learn how to do it first
SQL Dev Version 17.2.0
Database 12.2
I have 3 separate “apps” running mainly because if I have a long running job then SQL Dev basically freezes the app and I cannot multi-task on the same connection. It seems every day I get connection reset
“Your database connection has been reset. Any pending transactions or session state has been lost” pop-up on one of these 3 SQL Dev apps running.
I would like to be able to submit a query or insert or update in the worksheet and at the same time look at another table or view or Package.
Regards
Author
use an unshared worksheet for your long running queries
Your database connection has been reset. Any pending transactions or session state has been lost – you shouldn’t be getting these on your v17.2 connections…you’re seeing it frequently or…?
Every 20 – 30 minutes
But we are also running into ORA-00600 Error in the same session. So may just be that error which is the culprit
Author
If you’re getting 600’s you have a serious database problem.
I have the same issue but it occurs repeatedly and not due to long running DML and not tied to an ORA-600 error that I am receiving, though it happens on those as well. I typically have two instances of SQL Developer running on a Windows platform, each with multiple tabs and multiple instances connected. The lost connection issue when it occurs does not affect open connections to other instances. I have re-installed 4.2.0.17.089 Build 17.089.1709 and it still occurs. Searching the web shows others with the same exact issue but no real work around. This should be an active bug that is being addressed. What can we do?
Author
This should be an active bug
If you can describe a reproducible scenario in 17.2, then probably. Assuming it’s not the DB or Network kicking your connection.
And, then you should open a service request with My Oracle Support.
I am trying to set up SQLDeveloper to use LDAP, I have SQLPLUS working great but SQLDeveloper gives the error when every I select the LDAP Servers.
“Status : Failure – [LDAP: Error code 32 – No Such Object].
Configuring an advanced connection using “jdbc:oracle:thin:@ldap:/………” also works.
Any idea what I am missing?
Thanks
Roger
Author
SQL*Plus doesn’t use a thin jdbc driver, so if you want SQL Developer work with ldap as you have with SQL*Plus, you have to have SQL*Plus use a THICK connection so it will see your sqlnet.ora/etc.
Thanks for your quit response,
Do you mean have SQL Developer use a THICK connection?
If so have tried setting “Use OCI/Thick Driver” and still the same error.
Testing the Oracle Home located at C:\oracle\product\12.1_64bit\client_64bit
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … OK
Success!
Thanks
Roger
Author
that should be enough, assuming you only have the one client on your machine, and you’re using the right tnsnames file…I would open a ticket with My Oracle Support
Hi Jeff,
2 questions for you:
1. Identity. I read your post about it (http://www.thatjeffsmith.com/archive/2014/01/defining-12c-identity-columns-in-oracle-sql-developer-data-modeler/), but can’t get mine to work….
I created a relational model. Checked the following preferences (main ones, for the app):
* preferences -> data modeler -> model -> rdbms -> 12c
* preferences -> physical -> Oracle -> default identity ddl -> identity clause
* preferences -> physical -> Oracle -> default auto increment ddl -> default clause (though I also tried it with ‘none’ and ‘trigger’, but didn’t see a difference)
When I create a new table, add a number (12,0) column, designate it a primary key, and preview DDL, there is no mention of identity… I can double-click the column to see general properties. Neither “auto increment” nor “identity column” are selected automatically… (why not? would’ve expected that as a default for foreign key…)
I select both “auto increment and identity column”. Apply. Preview DDL. A trigger is generated. Click the column again, go into “auto increment” preferences. The “generate trigger” checkbox is selected. Unselect. Generate DDL again. Get a sequence…. It doesn’t seem to be able to get identity going….
I added a physical 12c model (though not doing anything with it at the moment). Looking at column properties from there, I see “auto increment”, but “generate DDL” is empty. I select “identity”, save everything, but DDL preview still generates a sequence, not an identity clause…. Could you please help?
(As an aside, main preferences seem to be a bit busted on a Mac. There is no scroll bar on the windows with the checkboxes–you can see them scroll into the box, but you can’t get to what’s below the window line….)
Author
on the relational model, table properties. select the column, hit the properties button – do you have ‘auto increment’ and ‘identity’ selected there?
It’s working for me in 17.2
Yes, have both checkboxes. Both are unchecked. Check them. Then if the “trigger” checkbox under “auto increment” is checked, I get a trigger, else I get a sequence. Been struggling with this most of the day today, can’t get it to work in 17.2. Could it be getting overwritten by a higher level property or some such? What is your “preferences -> physical -> Oracle -> default auto increment ddl” clause set to (though I’ve tried every option…)
Author
There is no ‘trigger’ checkbox under ‘Auto Increment’ – what dialog exactly are you looking at?
No-no,
I’m saying I’ve checked all 3 levels of preferences where this is mentioned…
1.
– Create a dummy table. Column name = ‘id’, logical, numeric, (12,0), check ‘PK’ box.
– Double-click column. Click ‘general’. The ‘auto increment’ and ‘identity’ check boxes are not checked. Check them. Apply.
– Click at the ‘auto increment’ link. Note that there is a ‘generate trigger’ checkbox at the bottom, and it’s checked.
What I’m saying is that if I leave that ‘trigger’ box on the ‘autoincrement link’ page checked, I get a regular non-identity column and a trigger. If I uncheck it, I get a regular non-identity column and a sequence….
2. I looked at the relevant preferences (main ones for SQL Developer):
* preferences -> data modeler -> model -> rdbms -> 12c
* preferences -> physical -> Oracle -> default identity ddl -> identity clause
* preferences -> physical -> Oracle -> default auto increment ddl -> default clause (though I also tried it with ‘none’ and ‘trigger’, but didn’t see a difference; what do you have here?)
3. I made sure that I have a 12c physical database.
Looking at column properties from there, I see “auto increment”, but “generate DDL” is empty. I select “identity”, save everything, but DDL preview still generates a sequence, not an identity clause….
Hi Jeff,
Identity not generating is still a problem… Do you kno what it might be down to? What are your global preferences set to (see my last response for mine).
Thanks again!
Author
No idea. It should be working. Try opening a post on our modeler forum, and the developers can delve deeper, and we can share screenshots back and forth more easily.
Yes, other changes are persisting… Upgraded to 17.2 this morning. Deleted tables/FKs. Changes persist after re-opening, so yay.
However, from clicking “open” menu to getting the box to select model to open took about 10 mins. Tried a few times, same deal. Running on a Mac (Sierra) with 5-6G memory free (8G total)… A bit puzzled by that…
Also, opening DM (or SQL session), top icon bar is pretty bare, and I have to click “Window -> Reset to factory settings”. How do I make it remember it? Similarly, opening a DM, the file selection box defaults to an old directory. How do I make it remember the location of the last file opened?
Many thanks!
Author
Ok, we’re talking about multiple problems now. Let’s stick with the model one.
Can you create a new design, from scratch. Then add 2 tables. Save it. Then delete a table. Save it. Close the design, and re-open – is your table ‘gone?’
If so, then I think there’s something wrong/corrupted with your other design.
Hi Jeff,
Following the upgrade to 17.2, the deletes were working correctly (so perhaps a 4.2 issue on a Mac??). The problem was how long it was taking to load a model.
I found your article about resetting to factory settings permanently (http://www.thatjeffsmith.com/archive/2015/08/how-to-reset-your-sql-developer-preferencessettings/). Having done that, the icon bar now shows up properly AND the time to open the model is now only a few minutes. Whatever was causing the long delay looks to have been cleared. (Default directory also wiped clean!)
This whole thing with deletes and upgrade looks to be fixed now. Thanks again for your help!
May have spoken too soon… Was fine quitting and restarting earlier (in terms of clicking “open model” and getting the directory window to select model)–was taking about a minute after resetting preferences. Just started it up again, and for no good reason it’s back to taking 15 minutes to give me the selection box (to open the dmd file)….
I read a post about an Oracle SQL Developer user that was sick and tired or it….but you were kind enough to email him and you addressed most of the items he was frustrated with. (This was a post from about two years ago.) He listed one of the items that has been bugging the “heck” out of me…but he did not include any of your advice/suggestions.
When I am working in SQL Developer, on a data grid, I can press the tab key to advance to the next column…then, for no apparent reason, the next time I use the tab key, it will cause the “edit value” dialog button to appear in the field and I can tab from column to column anymore. The only way that I have found to “restore” the tab key is to exit out of the application and then re-launch it. Is there a setting or preference that can control this behavior? Thanks in advance for any feedback.
Author
On Windows, in version 17.2, I can use BOTH the tab and left/right arrow keys to move around the cells of a result set data grid.
Hello Jeff,
this might be a simple Task and I’ve tried to find the answer, but it’s still not clear.
We’re using Windows user profiles with space restriction for Appdata.
Now the SQL Developer (Version 4.1.2.20.64) is storing the System Cache data within the roaming Profile.
I’ve found a Workaround to add “AddVMOption -Dide.user.dir=C:\sqldeveloper” at “\sqldeveloper\bin\sqldeveloper.conf”.
Do you know any other way to solve the Problem?
Many thanks in advance!
Bastian
Author
Increase your quota? I’m on vacation this week. This could be a bug…have you tried a newer version such as 4.2 or 17.2?
Hello Jeff,
this might be a simple Task and I’ve tried to find the answer, but it’s still not clear.
We’re using Windows user profiles with space restriction for Appdata.
Now the SQL Developer (Version 4.1.2.20.64) is storing the System Cache data within the roaming Profile.
I’ve found a Workaround to add “AddVMOption -Dide.user.dir=C:\sqldeveloper” at “\sqldeveloper\bin\sqldeveloper.conf”.
Do you know any other way to solve the Problem?
Many thanks in advance!
Bastian
Hi Jeff,
After I delete tables from a relational DM (right-click, “delete object”), save the model, and exit SQLDev, when I start it up again and open the model, all those tables come back!! I don’t seem to be able to delete them permanently… How do I do that?
Even worse, when I delete foreign keys from a table, they *also* come back!! If I rename/recycle the tables these were once keyed to, the FKs resurrect themselves under the old names and an empty relationship.
Happens every time… How do I make deletes permanent, esp. around FKs? Is there some kind of magic “purge” option?
Running SQL Developer 4.1.1.19 on a Mac (Sierra). I had a newer version on a different laptop late last year, and it was having the same problem.
Thanks in advance!
Author
Could be a bug…there’s been a 4.1.2, 4.1.3, 4.1.5, 4.2, 17.2 since.
You might me removing them from the diagram but not the model?
I know, I had 4.2 before, and I remember seeing the same behaviour.
Not sure what you mean by removing from diagram but not model… Right-clicking in the diagram has “remove object” and “remove from view”. When I select the former, the table disappears from the navigator dropdown of tables….
(Right-clicking tables in the navigator and clicking “delete” also doesn’t stop them from coming back after restarting the application 🙁 )
Author
If you’re saving the design and it’s ‘coming back’ on a reopen, there’s something wrong with the file. Are other types of changes persisting?
Hi Jeff,
I just got a new Lenovo Yoga 910 with a 13.9″ UHD (3840 x 2160) display. Unfortunately, SQL Developer does not scale UI components (Menu, Buttons/Icons, Font). I’ve tried Ide.FontSize but it does not really help. I mean the fonts get bigger but everything else remains tiny. It is impossible to work with the tool.
How can I make SQL Developer properly scale on UHD displays?
Sorry, submitted accidently, I was not finished…
Thanks for your time and help!
Beat
Author
Change the look and feel from Oracle to Windows…that might help.
I’ll try, but I’m running Fedora 26. Any suggestions for that?
Thanks!
I’ve only got “Metal” and “Oracle” look and feel options. Both don’t scale. Will there be UHD support in a future release?
Had this same issue, take a look at
http://www.thatjeffsmith.com/archive/2013/12/how-to-change-the-ui-font-size-for-oracle-sql-developer/
Hi,
Thanks, I already followed the advise in the link. It just makes the fonts a little bigger but anything else remains tiny (Menu, Buttons/Icons, etc.). And with the bigger font, some items are overlapping and get unreadable.
I was hoping for a proper high DPI scaling solution…
Best regards,
Beat
BREAK ON … DUPLICATE does not work ?
16:26:42 NFP2TST1:[email protected]>BREAK ON SECTION SKIP 1 DUPLICATE;
16:26:42 NFP2TST1:[email protected]>select ‘test’ section , 1 from dual
2 union
3 select ‘test2’, 2 from dual
4* union select ‘test2’, 3 from dual;
SECTI| 1
—–|———-
test | 1
test2| 2
3
EXPECTED OUPUT IS
SECTI| 1
—–|———-
test | 1
test2| 2
test2| 3
Author
I’ve never used Duplicate before, but it should work. If not, it’s a bug.
COLUMN LIKE is not supported ?
COLUMN FORMAT 999,999,990.00 is not supported ?
— begin of transcript —–
13:16:48 NFP2PRD1:[email protected]>col
COLUMN posted_flag ON
FORMAT a11
COLUMN account ON
FORMAT a10
13:16:30 NFP2PRD1:[email protected]>col posted_flag like account;
SP2-0158: unknown COLUMN option “like”
13:16:50 NFP2PRD1:[email protected]>col d2 format 999,999,990.00
SP2-0246: Illegal FORMAT string “999,999,”
Hi Jeff,
When I format the code, Is there any way to keep multiline comments intact?
I use Java-style code comments:
/ **
* Retrieve the value from the associative array
*
* @param p_array the associative array
* @param p_key the array key
*
* @return the value if exists
* /
And SQL Developer formats it to:
/ **
* Retrieve the value from the associative array
*
* @ Param p_array the associative array
* @ Param p_key the array key
*
* @ Return the value if exists
* /
Dear Jeff,
I installed APEX & ORDS on a test DB and they work well togheter. I created a workspace in APEX, some sample tables, then configured Restful services, and I can access them using the url:
http:///ords/test/hr/employees/
I own another Oracle DB with a user, a tablespace and few tables filled with data I want to access via REST from a Webapplication. I don’t want to install APEX here, I don’t need it and I don’t want to create a new tablespace like APEX_xxxxxxxxxxxxxxxxxx.
I just want to create REST handlers (GET, PUT etc) for those existing tablespace/tables, even by plsql calls (it’s not a problem for me, I don’t care about a GUI) using ORDS.
Is it possible?
Thanks for your attention. Best Regards,
Mario.
Author
Absolutely. Just install ORDS – no need to install/configure APEX. Then use SQL Developer or SQL*Plus to setup your RESTful Services. Click on the ‘ORDS’ link up top and you’ll see my tutorials on the subject.
break on .. duplicate also does not work.
16:26:42 NFP2TST1:[email protected]>select ‘test’ section , 1 from dual
2 union
3 select ‘test2’, 2 from dual
4* union select ‘test2’, 3 from dual;
SECTI| 1
—–|———-
test | 1
test2| 2
3
expected ouput is
SECTI| 1
—–|———-
test | 1
test2| 2
test2| 3
COLUMN LIKE is not supported ?
COLUMN FORMAT 999,999,990.00 is not supported ?
— begin of transcript —–
13:16:48 NFP2PRD1:[email protected]>col
COLUMN posted_flag ON
FORMAT a11
COLUMN account ON
FORMAT a10
13:16:30 NFP2PRD1:[email protected]>col posted_flage like account;
SP2-0158: unknown COLUMN option “like”
13:16:50 NFP2PRD1:[email protected]>col d2 format 999,999,990.00
SP2-0246: Illegal FORMAT string “999,999,”
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.
Author
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.
Author
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.
Author
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.
Author
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?
Author
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?
Author
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
Author
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?
Author
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.
Author
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.
Author
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,
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!
Author
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!
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?
Author
It works for me.
I tried F9 with no success.
Right, F5 works.
Thank you for your quick help! 🙂
Author
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.
Author
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?
Author
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.
Author
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.
Author
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.
Author
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!
Author
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
Author
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.
Author
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/[email protected]
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.
Author
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
Author
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.
Author
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
Author
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?
Author
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?
Author
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.
Hi Jeff
I am receiving the “SELECT LIST INCONSISTENT WITH GROUP BY” warning on my entire Select statement, but cannot determine why, since I am not using Group By anywhere in the script. My code is 162 lines, so I didn’t think you would want it posted here, but I can – or can separately – provide it if you would need it to help.
I am hoping that you have simply seen this behavior before and might be able to point me in the right direction to resolve.
I appreciate any assistance that you can give.
Best regards,
PS – I just upgraded to Version 4.2, and that is when this started …
Author
can you make it happen with a 10 line query instead? there should be an underline squiggle on your code SOMEWHERE to show where the parser is getting upset.
You can of course just completely ignore those ‘helpers’ if they’re not actually helpful
The warning squiggle is under the entire 94 lines of the Select statement, but I tried your suggestion of reducing the code … and I found the ‘line’ that SQL_Dev doesn’t like:
case when (select count(*)
from tpsdba.validation_exceptions
where cust_no = tpsdba.shipment_header.cust_no
and validation_no = 6020) > 0 then ‘Yes’
else ‘No’
end validation_exception_6020,
As you noted, it’s just a warning, and runs perfectly fine anyway – but is there another way to write this that won’t get SQL_Dev upset?
TIA,
I am never a fan of count(*) to find out if the value is or is not zero. And it is the count that is fooling the sqldev parser thinking it needs a group by
Here is an example using exists from scott/tiger
SELECT D.* ,
coalesce( ( SELECT 'Yes' FROM dual WHERE EXISTS
( SELECT 1 FROM scott.emp e WHERE e.deptno = d.deptno ) ) , 'No' ) has_emps
from scott.dept d
Thanks TR. I agree that using EXISTS is better than COUNT when I don’t need to know the actual number, but I’m not sure that the COUNT is where the parser is confused – I changed the code to remove the CASE and just leave the subquery (including the COUNT) and the warning went away.
(select count(*)
from tpsdba.validation_exceptions
where cust_no = tpsdba.shipment_header.cust_no
and validation_no = 6020) validation_exception_6020,
The users can figure out Yes/No by the results of Zero or Non-Zero.
But thanks for replying.
“Select list inconsistent with group by” can show up in reports involving “union all”:
select null, count(*), avg(sal)
from emp
where comm is null
union all
select deptno, count(*), avg (sal+comm)
from emp
where comm is not null
group by deptno
adding “group by null” clears the warning.
I do not prefer to add “group by null” because:
– it is not required
– it leads to proliferation of clutter in the code base
– it spreads misinformation and misunderstanding
Jeff,
I’ve used SQL*Developer to import spreadsheet data into Oracle tables for projects. Recently did an import and it created 1,332 rows…but functional user kept saying there were only 1,205 rows in the spreadsheet. The difference was that 127 rows were hidden within the spreadsheet, but apparently the import found them… like lost sheep. Would it be possible to add a selection to the import feature to ‘ignore hidden rows’?
Author
Probably not. I don’t think the Excel project we use, APACHE POI, is that sophisticated.
According to the Apache POI documentation for Excel you can call getZeroHeight() for a row read. If it returns ‘true’ the row is hidden. If it returns ‘false’ the row isn’t hidden. Wouldn’t that work?
Dan
Author
Sounds like it. You could submit an ER to support that. Or delete rows out of a spreadsheet you don’t want to import 🙂
Hate to ask, but what are the steps to open an ER for this item?
Author
unofficial way, go to sqldeveloper.oracle.com and open an item – your friends can vote it up
official way, go to my oracle support and open a service request on sql developer
I recommend route #1
Hi Jeff,
A feature suggestion for the Cart. Add a checkbox to disconnect from the Destination Connection after a Copy Objects operation is completed. Not having an option like this leaves these connections open. A related comment, when a tree is collapsed and connections are in folders there is no indication where any connections are opened.
Later there is a risk of e.g. doing something against objects with the same name in the wrong database. A SQL Worksheet window remains open to that connection and it is possible to inadvertently paste code & execute code in it. I also wish there could be a better way to visually discern different connections. I use now the colored frame that can be setup for a connection, but e.g. a different background color or pattern would be probably better.
Thanks!
Author
We nixed the bg color for connections b/c we thought it looked horrible. We did try it though.
Perhaps it could be something very subtle that could be used for alerting that it is production, etc. We don’t work there most of the time anyway.
What about the other thoughts I had, for the cart and to indicate that folder has open connections? Perhaps you want me to post this on the forum instead? I wasn’t sure where is the better place.
Author
But we already have something very subtle – the connection color feature. It draws a border around the windows AND it colors the connection text in the worksheet and tree.
I don’t like the idea of automatically closing connections to be honest
Having a decorator on a folder though to show it has one or more open connections under it isn’t a horrible idea. I’ll mention it to the developer.
Jeff,
I know what you saying. However, consider this scenario. There is a frame around around the worksheet in production (these are very tiny and sometimes almost not noticeable if one gets carried away with some work). At some point you switch away from that worksheet (actually not really relevant). The connections are still open. Say, all connections have all tables expanded and there is a good number of them (doesn’t have to be a lot). Maybe you want to rename a table in development. By mistake you pick the table in production with the same name …….. The font and color are exactly the same. There is no way to tell in which database/schema the table is located. You have to scroll up to check the location. The color is only different for the connection name.
Author
Right, but consider this – maybe if production is involved, you don’t even have it in your SQL Developer list of connections. You’d only ever push changes there via automated jobs/sqlcl/sqlplus…or if you know PROD is lurking around, you slow down, and double-check, every time.
We could do more to show connection stuff in the tool, but folks would still need to learn how to stop running with scissors. I’m willing to look at this again, but I’m not seeing it as a critical issue today in terms of feedback from users.
Jeff, I am not sure that I agree with that point. There is so much more functionality in SQL Developer than there used to be few years ago, including the DBA tab, Change Management, etc. I think this functionality conflicts with your suggestion not to include production connections. Also all connections are managed through one common place, i.e. Connections. Let’s forget for a moment about production and consider other pre-prod environments. Perhaps you could consider, in addition to changed connection text color, a colored frame around the expanded objects (the area in the browser) under a particular connection that needs caution or to be different for some reason.
Author
I’m saying if you’re having problems remembering if your connection is PROD or not – consider having a locked down copy of SQLDev that’s ONLY for PROD.
At the moment, we’re not looking at doing more to call out specific connections with color themes.
Sqldeveloper unit testing : How do I check values of a user defined collection used as a parameter to a procedure or function as part of the assert outcome. The collection is at the database level as an object. I’d like to be able to assert along the lines of coll(1).rec.item = somevalue
Thanks for any help
Author
If you can post this to the forums, I’ll ask the developer to take a look at it.
Thanks for response, I’ve posted this on the community under SqlDeveloper.
Title: SQLDeveloper Unit Testing Collections when used as parameters to program units
Tag: unit test
Regards
Andrew
Sql Developer 4.2.0 has one issue that prevents me from migrating the team to using it. There are cases where we develop a sql statement then open a sys_refcursor based on the string of that SQL statement. In 4.1.5 the Crtl-Shift-F7 brought up a dialog with an option to build a concatenated string. In 4.2.0 there is no such option in the dialog.
What would it take to bring that feature back?
In Version 4.2.0.17.089.1709 the “File > Compare With” options are grayed out. Is this feature no longer supported?
Author
It’s a bug. Workaround, open the Files dialog. Select one or both files and right-click, compare.
Hi Jeff
Thanks to your explanation about SQL Editor Code Templates (August and November 2014) I was able to setup some nice templates and more will come.
But what’s about the second table on the SQL Editor Code Templates pane. The one with the four rows, starting with Id #time#. How could it be used and why is this table not editable?
I am trying to use JDK 1.8.0_131 with sql developer 4.1.5.21.78 windows 64 bit.
I get error
Incompatible version of libocijdbc[Jdbc:121020, Jdbc-OCI:122010
Anybody have any help please? I have spent 3 hours on this issue so far
Author
You’re doing something to attempt a thick connection to your database, and your Oracle client isn’t compatible. Tell me about your connection properties.
username=system
pwd= test12
hostname=
port=1521
SID=test12
connection type=basic
role=default
Hi Jeff,
Is it possible to include the contents of a bind variable in a graph title in a SQL Developer chart report?
If so, how?
Thanks,
Bill
Author
not that i know of
if you used a dbms_output/html style report I reckon you could do it, but you’d have to build the graph yourself using js probably
Jeff,
I want to setup Unit testing module without using the GUI for 100+ users so that they dont have to go to the screen every time .
Is there any API or script made available by oracle to setup unit testing ( from creating user for the repository , provide grants to the user, create repository and providing access to users ) so that we can customize as per the needs or its not possible at all.
any suggestion is appreciable.
Author
>>so that they dont have to go to the screen every time
Go to the screen to do what?
You’re asking for a way to configuring the unit test repository connection automatically for a group of users so they don’t have to?
requirement is to create the setup (create users connections, repositorys for the unit tests and drop the same) on demand. As the number of users and multiple databases, is there any way to do same via script or command by providing the details .
Thanks Jeff.
Author
spinning up the repository(ies) could definitely be scripted. Create it with the GUI, then export it out to whatever script type you’d like.
There is a sdcli command to create a connection, I’ve not used it before though.
Having your users associate the connection to the UT repos, they’d have to do that.
Hi
Could this be made a feature in SQl Dev:
Datamodel like view on a view
Ie. View xxxx – includes 4 tables x,y,z,v, and the x is a view again …..
So that you from one view could see in DM format all the tables involved and what links them together (where/on) clauses ?
really usefull when troubleshooting
Author
You can kinda do that today.
Hmmm … but if I do ie
LECT session_key, NVL2 (MIN (incremental_level), ‘:’ || MIN (incremental_level), NULL) co
FROM V$BACKUP_DATAFILE_DETAILS d
GROUP BY session_key
)
SELECT command_id AS “Backup Id”,
b.status AS “Status”,
TO_CHAR (b.start_time, ‘dd.mm.YYYY HH24:MI:SS’) AS “Start Time”,
TO_CHAR (b.end_time, ‘dd.mm.YYYY HH24:MI:SS’) AS “End Time”,
b.time_taken_display AS “Duration”,
b.input_type || d.co AS “Type”,
b.output_device_type AS “Device”,
b.input_bytes_display AS “Input Size”,
b.output_bytes_display AS “Output Size”,
b.output_bytes_per_sec_display AS “Output Rate (Per Sec)”
FROM V$RMAN_BACKUP_JOB_DETAILS b, SUMMARY d
WHERE b.start_time > TRUNC (SYSDATE) – 14
and b.session_key = d.session_key (+)
ORDER BY b.start_time DESC
I just the 2 “tables” involved – not the underlying views – I would like a model of whats going on underneath in a modellike drawing.
Ideal when debugging
Hi, Is it possible to migrate Sql Server 2008/2012 database to Oracle using sdcli feature of Sql Developer 4 ?
Author
Yes, but you wouldn’t do that for the first migration. You’d use the SQLDev GUI to run through the migration first. Then if you needed to automate steps of that migration going forward, THEN you’d use SDCLI.
Hi Jeff,
I have successfully migrated Sql Server db to Oracle using GUI. But when I execute below mentioned command I get “Error:java.sql.SQLException: Driver class not found. Verify the Driver location”.
sdcli migration -actions=capture -conn=sqlserver
Driver Imported to sql developer – jtds-1.2.jar
Author
I think you might need to open a support ticket, or maybe post this on the Migrations Forum page, so you can say exactly what you’re doing in full detail.
Also, you will get better migration with jtds 1.3
Hi Jeff,
I am interested to see if there is a way to save a project that has multiple worksheets as a single project that can be opened (and all the separate tabbed worksheets come up when the project is opened in SQL Developer). is there a way to do this?
Author
No, but you’re not the first to ask about it.
Hi Jeff,
Could you please help me to understand which are the unsupported datatypes that can be passed as arguments for an oracle procedure when trying to test using the Unit Test of SQL Developer (3.2.20.10). Thanks in advance.
Hey Jeff,
I wasn’t able to find an email to reach out to you with. I am reaching out here because you are using an embed from our website and unfortunately it is now in violation of Google’s terms and conditions. In order to avoid disavowing your website we will need to have it changed as soon as possible. Sorry for the inconvenience, please reach out to me at the provided email within the next 72 hours so we can help get it fixed before submitting our disavow. Thanks!
Author
I took that post down, but maybe Google could still bring it up. It’s nuked from orbit now, so everything should be good to go now. If not, [email protected]
Jeff,
Thanks for the site. Solving question before being asked, like http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/
But in testing some code I tried as I would in Sql*Plus. Example at https://community.oracle.com/thread/888365.
This examples show printing the output of a refcursor and that the refcursor is closed after the print statement. To see the issue change the select in line 4 to ‘SELECT * FROM all_objects’
Two problems I experienced
1 The print command ( borrowed from Sql*Plus) does not honor the pagesize and linesize settings. Other session variables do honor these setting, so it is only refcursor with an issue.
2 In Sql*Plus print closes the refcursor, while sqlcl and Sql Developer do not.
set linesize 32767; not working in logon.sql on windows 7 sql-devleper 4.2 (no jdk ver-jdk8u131). any suggestions?
ignore. duplicate on ask tom.
sorry not a duplicate. please respond.
full script…
alter session set nls_date_format = ‘YYYY-MM-DD HH24.MI.SS’;
alter session set nls_timestamp_format = ‘YYYY-MM-DD HH24.MI.SS’;
alter session set nls_timestamp_tz_format = ‘YYYY-MM-DD HH24.MI.SS TZR’;
set linesize 32767;
set newpage NONE;
set pagesize 50000;
set trimout OFF;
Author
We auto set linesize to the width of the output panel…Use a show linesize and resize the window to see for yourself.
Add the set linesize to your script to override the dynamic setting
i did set it in my script, but it does not stay the value i set it? i have wide tables and want to see data without wraping. how do i get it to set in the script so it stays instead of reseting to width of window?
Author
Or run in SQLcl or SQL*Plus where things like LINESIZE make more sense
why cant i put it in my login.sql and forget it? why the convoluted script for sqldeveloper?
Author
I already explained it to you, once the script output window opens, we see how wide it is, and then set that to = linesize.
if you want a terminal experience, open a terminal…or force linesize to what you want in your script
there is got to be a way to set it once instead of on every sql? the setting in the login.sql should override settings even after the ui load.
Author
If you don’t futz with the output panel it shouldn’t change once you set it
so, i have to set linesize every time after i make a new connection to solve this?
can we get an word-wrap option in sqldeveloper? giving a word-wrap option, like other developer programs, would be a great help. this way when someone unchecks the word-wrap option it sets the the linesize to maximum for that system.
please give this serious consideration for an option in future versions. i am sure it would be much appreciated by the sqldeveloper community.
thanks for your time though, i was going crazy thinking it was me.
as a side note, doesnt adding display code in with sql code breaks separation of concerns (SoC)?
ps. i donloaded sqldeveloper4.2 no-jdk, installed jdk131. ran sqld and it wraps by default. ran select * from table and i didnt play with output.
Hi there,
I am on Windows 10 64-bit and SQL Developer 4.2.0.17.089. In Preferences -> File Types I set the .sql file type to be opened with SQL Developer, a green marker is set in the “Open with SQL Developer” column, so I click OK. But my .sql files aren’t opened with SQL Developer and when I enter Preference -> File Types again the green marker has gone. Is this a bug?
Thanks,
Bud.
Author
Is windows getting int the way?
Hi Jeff,
Found it. One has to run sqldeveloper.exe as an Administrator to make and keep the File Types settings.
Still learning my way around. I coded a package that does not do any commits (or rollbacks). I made sure auto-commit is not checked in my Preferences. However, when I used the editor to run an anonymous pl/sql block to execute my procedure (in my pkg) — the data was committed to the database. I found this out when my rollback did not work.
Thoughts?
I really want to be in control of when commits are done. I often use the same data in my test and rollback between each execution.
Author
Do you have any DDL in your code, like a CREATE TABLE?
Hi Jeff,
After reading one of your post on SQL Developer reports, i started using them. Undoubtedly they are un-explored powerful feature of sql developer.
Using these reports, i started automating many of my daily tasks and analysis queries. This saves a lot of time for me.
I am using V4.1.5 and having an issue with bind values in child report.
I have a report which is using couple of bind variables. When i configure this report as a child report to other, on first instance it’s invocation works correctly by taking bind variable value from the parent report. But when tried to open same child report as standalone or from another report, still it shows the result for the value passed from the first report.
When i checked the bind values for the child report, it’s storing the value for bind variable from first report (in place of NULL_VALUE) and hence any later report invocation is giving same result.
So, every time i need to clear bind value of the child report before running it again.
Is there any setting which controls this?
I tried with V 4.2 but still having same issue.
Thank you
Author
>> But when tried to open same child report as standalone or from another report,
I’m not clear on this, exactly how are you making your child report available somewhere else?
One of the report has few bind variables in it’s where clause and this report is set as child report for 2-3 other reports.
When this report is invoked from a parent report, it’s storing the passed in bind variable in the child report bind value section.
Later run of the report as standalone or from another report, still it’s giving same old result.
Only when I go and reset that bind value in child report to NULL_VALUE, then it’s either prompts for value (standalone run) or accepts it’s value from parent query correctly.
If we do not have sqlldr utility, is there a way to load local text files into a table.
Author
Yes, if they’re delimited text files.
I have noticed the same with v4.2
Hi Jeff. I’m a huge fan of SQL Developer and I’ve been using it since it started as the Raptor project. Right now I’m facing an issue with the new release 4.2 as it keeps dropping the opened sessions, when running a simple query from a SQL window or trying to edit some object. I have installed also version 4.1.5 and have no issue with that one. This started to happen with 4.2 EA version, and I thought it was some sort of a bug that should be solved on the final release, but I’m still facing it with this new release. I’m using Win10, have installed Java 1.8.121 and doing the connections using JDBC (Basic Connection Type) and it’s the same I used for version 4.1.5.
I also tried adding : AddVMOption -Doracle.net.disableOob=true to the sqldeveloper.conf file, but it didn’t help. Any ideas on how can I troubleshoot and fix this issue? Thanks in advance for your reply.
Author
It’s a known issue and we’re working on a fix.
Some, but not all customers are running into it. Here’s how to avoid it, if you have a 12c Instant Client available, USE IT. That should avoid the issue, which lies in the 12.2 JDBC driver itself.
Otherwise, I would go back to using 4.1 until our fix is available.
Thanks for the quick answer Jeff, I wasn’t aware it was a known issue. I already went back to 4.1.5, and will check periodically for the fix.
I am NEW to sql*developer but NOT NEW to pl/sql.
Just started a new job where I want to use sql*developer – I’ve been enjoying your videos.
QUICK QUESTION: I was surprised to find out that compiling a package automatically writes the package to disk. Is there a way to change that? I only want to write to disk when I choose “file -> save” or “file -> save as” . I had removed a bunch of code from a pkg to try something crazy and was surprised my pkg on disk had been modified automatically. I can’t find a discussion on this.
Is what I want to have happen, possible? How?! Thanks –
Author
No i don’t know of a way…but you could undo the changes and save the file..that won’t do a compile.
If you look at the History panel, you could also get back where you had been before.
Thanks for the quick reply and groovy answer – no one writes about this! I will definitely use the History panel.
Today I pinned things and created my own short cuts and even floated a window to my other monitor – all based on info I got from your site. It’s fun.
Signed,
I might be a nerd.
Semicolon at the end of the line comment of SQL query ends the scope of query?
For example this
select *
from table
–where 1=1;
where 1=0;
with the cursor before the semicolon selects all.
Is that the desired behavior in the new Oracle SQL Developer 4.2?
I dont think so. At least in older versions was this different.
If i put some chars behind the semicolon on the same line, the scope of the query will be processed correctly ant the result is nothing.
Author
It does in SQL*Plus, it shouldn’t be in SQLDev, that’s a bug.
Hi Jeff,
I have an issue pasting into sqlcl.
When trying to paste multi-line sql statement into sqlcl, it is pasted into multiple commands.
For example, copying the following sql statement:
SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM scott.emp e , scott.dept d
where e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname;
it will end splitted into 5 separate commands not one command as follows:
SQL> SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM scott.emp e , scott.dept d
where e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname; SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
Error starting at line : 1 in command –
SELECT /*+ MONITOR */ d.dname, LISTAGG(e.ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
Error at Command Line : 1 Column : 96
Error report –
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 – “FROM keyword not found where expected”
*Cause:
*Action:
SQL> FROM scott.emp e , scott.dept d
Error starting at line : 1 in command –
FROM scott.emp e , scott.dept d
Error report –
Unknown Command
SQL> where e.deptno = d.deptno
Error starting at line : 1 in command –
where e.deptno = d.deptno
Error report –
Unknown Command
SQL> GROUP BY d.dname
Error starting at line : 1 in command –
GROUP BY d.dname
Error report –
Unknown Command
SQL>
ORDER BY d.dname;
Error starting at line : 1 in command –
ORDER BY d.dname
Error report –
Unknown Command
Any inputs/workarounds to fix this?
Regards
Ahmed
Author
That works great for me, I’m on Windows 7, using boring old CMD window. What are you using?
I’m using win 10. I tested from CMD, CYGWIN and MobaXTerm.
It didn’t work from all the three terminals.
If I opened a sqlplus session from the same CMD , and tried exactly the same sql, it will work.
Note that this sql statement is copied from somewhere in the internet.
If I write it myself in Notepad, It will work from sqlcl on CMD, but not from sqlcl on Cygwin or MobaXTerm.
Author
I copied your SQL from my browser – this comment – and pasted into CMD window. It works.
Try this:
SELECT sql_id, status, sql_text
FROM v$sql_monitor
WHERE username = ‘SCOTT’;
This worked from sqlplus on CMD, but not from sqlcl from CMD.
Author
How do you mean ‘worked’ – how is it not working in SQLcl?
Please see the screen shots at the following location:
https://1drv.ms/f/s!ArrpKxPFc2APuQrRKWSBKUeZu10f
Author
Yeah, that’s just weird. All that works for me.
If I may jump in here I have seen this before, This is related to charset and or line termination. Look at the pasted code with a hex editor and compare the end of lines with code that works. The end of line should be CRLF or just LF ( 0x0a ) .
Thanks Rudkin for your inputs.
I noticed that for the code that works, each line end with CRLF, but for the one that is not working , it is ended up with LF only.
It worth mentioning that sqlplus absorbs this difference without issue, but sqlcl error out with lines that end up with LF only.
Also, it worth mentioning that if you just copy/paste the code that has this issue into a text editor like “Notepad++’, it will automatically adjust the end of lines to CRLF, then if you copied that code from Notepad++, it will work fine.
I’ll have a separate post about this point shortly, I’ll share it with you once completed.
Thanks Rudkin & Smith for your support.
Ahmed
https://ahmadfattah.wordpress.com/2017/05/04/sqlcl-multi-line-paste-issue/
I would love a way within SQL Developer to switch connections in the script. One that used the SQL Developer stored connections with the password, since including the password in the script is a no-go. Something like
conn !my_connection_name_here
I use this constantly on Microsoft SSMS using the syntax
:connect server_name_here
Any chance we’ll see that in SQL Developer or the cl?
Thanks Jeff for clarifying that repository can be created only by a highly privileged user.
what DB privileges are needed for the user to have the ability to create the repository?
Hi Jeff
the user I am using has all elevated privileges including
Select on DBA_TAB_PRIVS
Execute on DBMS_LOCK
however, still I am getting the error.
is there any other role / priv we are missing.
thanks in advance
I am trying to use Oracle SQL developer 4.2 for UNIT testing. And would like to create repository with the same user that I am logged in. I get a message “Incorrect repository version: you must upgrade this repository to use it”
how can I update this or create a new one?
do I need DBA privileges for this purpose
Author
You need to associate your connection with the unit testing repo, then it should prompt you to create the objects.
can you guide how to connect the unit test repo with the connection
Author
open Unit Test panel
then on Tools Menu, Unit Test – Select Current Repository
Hi Jeff
I am missing something ….
Our Oracle DB is 11g and am using Oracle SQL Developer 4.2.
created the connection to the DB
Then View–>Unit Test
this created a repository Unit Tests
when I tried to associate this to the main DB connection, gives a message
“No repository was found on the selected connection.
would you like to create one now ?
when I click Yes, the msg is
“Required roles do not exist
You will now be prompted for connection info to grant needed permissions”
and then it prompts for sys pwd.
is it necessary that only sys has this role to create another repository?
is there any other way to create the repository.
Also I am not able to get the
UNIT TEST NAVIGATOR window
can you guide step by step as to how I can start creating repository and test cases ?
Author
you need a highly privileged user to create the repository, yes
We have been using the modeler read all the objects in an APEX schema (tables, views, constraints, indexes, sequences, triggers) for this project and we have run across some issues:
• The table FCI_L_MAJOR table is invisible within the model. I learned of this because the FCI_ENROLLMENT table has a foreign key constraint with the FCI_L_MAJOR table that link does not appear in the model either. So I created another version of it in the model and when I tried to create the foreign there in the Referenced Table dropdown you see both tables
• When I exported the SQL from the model it’s fine except that the sequences don’t have the START WITH clause which is important since there is data already in the tables.
• When I imported the schema I tried to suppress the schema name from the model what happened is that no objects came across into the model.
hi Jeff
We are using SQL Developer version 4.1.3.20
The problem for some developers is that when they open in SQL developer file with extension ‘pkg’ – the file is open in text editor and SQL developer does not give the options to compile it
The other developers can open the same file type in pl/sql editor.
None of developers have pkg file type defined in Tool – preferences – file types.
What is the problem here and how can we resolve it to be consistent for all developers.
TIA
Author
we have pks and pkb not pkg in the extension preferences.
What happens if they open a pkg spec or body from the DB and then save it as a pkg file?
If I do that, and then close it, and open the file, it goes into the pl/sql editor with the compile and other pl/sql specific features available.
Hi, struggling with the way SQL-DEV (SD) displays dates vs TOAD in the query results . I rely on timestamps! So I changed my SD pref to add HH:MI:SS AM to the date format in DB / NLS setting. When running a query that truncs other date fields that I only need to see the short date, SD displays DD-MON-RR 12:00:00 AM.. In TOAD, the trunced date displays as expected, just the date…
any guidance is appreciated…
Author
They’re artificially hiding the time from you – if you ask me, they’re training you to learn a bad habit.
If the time component is important to you, then build it into your query, and don’t rely on what NLS is set to. Even if the time is midnight, it’s still there – either you want to see the time, or you don’t.
Hi Jeff, I have read several tips on making SQL Developer faster, but I have not found the problem I am experiencing as being addressed.
In short this is NOT a SQL running slowness. I am experiencing a 1-2 minute delay when I click on the export option on a SQL result set? There are additional delays in traversing the explore file structure. Once I get a file type and name selected I am fine, but getting there is becoming progressively slower.
I am running Windows 7 on a Lenovo with 4GB mem.
Author
With SQL Developer CLOSED, try this
I went under this folder – “AppData\Roaming\SQL Developer\system4.1.5.21.78\o.sqldeveloper.12.2.0.21.78” and opened “product-preferences.xml”.
Went to the line
hash n=”URLFileChooserPaths”
list n=”DEFAULT_CONTEXT”
url protocol=”file” path=”/G:/Queries/Reference.sql”
and under list n=”DEFAULT_CONTEXT” I had LOTS of file paths. I deleted them and kept couple of them which I really need. That made ALL the difference.
Thanks! I had about 75 URLs in there that I didn’t need. It sped up some from that change. I still have about a 30 -60 second wait from when I right click export, before I see the export wizzard screen.
Author
I’m guessing you still have a network drive or some directory in there that’s taking windows a long time to respond to our java dir/url request.
Yes, that might be it. Anyway, the response time did improve with your recommendation. Thanks!
Hi Jeff,
I have entered an Oracle support bug last year, b/c we are migrating 20+ TB of Sybase databases to Oracle using SQL Developer. We have found that single column primary keys (which is over 90% of primary keys) don’t migrate correctly.
Here is the support ticket:
SR 3-12869054401 : SQL Developer migration tool omits column name in single column primary keys
Here is the response from Oracle support:
“The workaround is to use 2 keys or more for the PK to get passed this issue. ”
What are your feelings on this? Going back and adding 2 columns to each primary key in our legacy Sybase databases just to migrate them to Oracle doesn’t sound like a solid plan.
Thanks,
Brian
Author
That’s a crazy workaround for an obvious bug, however R&D isn’t able to reproduce that behavior. Can you send in a test case for your SR?
Thanks! They kept trying to close the ticket, but I’ll try to work with them some more and post the outcome here.
Author
They should be contacting you soon for a test case. If you don’t hear from them by EOB tomorrow, write me back.
Yes, they have contacted me for a test case. Thanks for helping to facilitate!
Author
Anytime. It’s easy to get lost in the machine, I’ll help for as long as I can remain somewhat sane 🙂
Hi Jeff
First, *many* thanks for your contribution – you’ve been a lifesaver more times than I’d like to admit!
Sorry if my post is a bit long, but I want to give you as complete a view as possible.
I’m trying to build a dev environment on my Ubuntu 16.10 box. I’ve set up an Oracle VM with Centos7 and installed Oracle 11.2 in it.
Since the version that comes with the DB is *way* too old, I’m trying to install sqldeveloper-3.2.20.09.87 (I need this version due to a plugin requirement).
I unzipped the file into /opt. I then ran the following and copy-paste the output here:
$ java -version
openjdk version "1.8.0_121"
OpenJDK Runtime Environment (build 1.8.0_121-b13)
OpenJDK 64-Bit Server VM (build 25.121-b13, mixed mode)
$ which java
/usr/bin/java
$ ls -la /usr/bin/java
lrwxrwxrwx. 1 root root 22 Mar 1 16:08 /usr/bin/java -> /etc/alternatives/java
$ ls -la /etc/alternatives/java
lrwxrwxrwx. 1 root root 73 Mar 1 16:08 /etc/alternatives/java -> /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
$ ls -la /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
-rwxr-xr-x. 1 root root 7344 Jan 20 19:37 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
$ /opt/sqldeveloper-3.2.20.09.87/sqldeveloper.sh
Oracle SQL Developer
Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.
Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64
Error: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/bin/java not found
Additionally (as root):
# find / -type f -name java
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131-2.6.9.0.el7_3.x86_64/jre-abrt/bin/java
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131-2.6.9.0.el7_3.x86_64/jre/bin/java
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.121-0.b13.el7_3.x86_64/jre/bin/java
/var/lib/alternatives/java
/ora01/app/oracle/product/11.2.0/db_1/jdk/bin/java
/ora01/app/oracle/product/11.2.0/db_1/jdk/jre/bin/java
/ora01/app/oracle/product/11.2.0/client1/jdk/jre/bin/java
/ora01/app/oracle/product/11.2.0/client1/jdk/bin/java
So, what is it? Do I have an SDK for sqldev to work?
What must I do to make sqldeveloper-3.2.20.09.87 start, *without* messing with the DB (and its java) installation?
Thanks in advance for your time
Greg
Just to clarify: I’m trying to install in the VM (Centos) environment – not in host (Ubuntu).
Greg
Author
Yes, you need a JDK.
For a version that old, Java 6 is probably ok…But we don’t support open JDK, only Oracle Java. You could probably point it to the jdk in the Oracle home bin.
Hi Jeff and many thanks for your answer.
I wasn’t aware that only Oracle Java is supported – as a matter of fact, I’ve installed sqldeveloper-3.2.20.09.87 using /usr/lib/jvm/java-8-openjdk-amd64 and it works fine – but it’s in the host (Ubuntu), not in VM (Centos).
In the VM, pointing to either db or client’s jdk (they are the same) leads to error (the same error for both):
$ /ora01/app/oracle/product/11.2.0/db_1/jdk/bin/java -version
java version "1.5.0_51"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_51-b10)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_51-b10, mixed mode)
$ /ora01/app/oracle/product/11.2.0/client1/jdk/bin/java -version
java version "1.5.0_51"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_51-b10)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_51-b10, mixed mode)
$ /opt/sqldeveloper-3.2.20.09.87/sqldeveloper.sh
Oracle SQL Developer
Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.
Type the full pathname of a J2SE installation (or Ctrl-C to quit), the path will be stored in ~/.sqldeveloper/jdk
/ora01/app/oracle/product/11.2.0/db_1/jdk
Exception in thread "main" java.lang.UnsupportedClassVersionError: Bad version number in .class file
at java.lang.ClassLoader.defineClass1(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:621)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:124)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:260)
at java.net.URLClassLoader.access$100(URLClassLoader.java:56)
at java.net.URLClassLoader$1.run(URLClassLoader.java:195)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
at java.lang.ClassLoader.loadClass(ClassLoader.java:252)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)
If I install Oracle java(jdk) 6 or 8 in the VM, what must I do to make certain that the db is not affected (PATH etc)?
Thanks once more
Greg
Author
Don’t run sqldev on the VM…Save those resources for your database.
Install sqldev on your host, and grab version 4.2 with Java 8, and just connect to the database on your VM. Then you have nothing to worry about.
Thank you very much for your time – much appreciated.
Greg
Hi Jeff
Downloaded Sqldeveloper (V4.2.0.17.089, Build 17.089.1709) and every now and then getting Connection reset message
And Code Outline, it does not shows outline for some of the packages, can see below messages in logging page
SEVERE 17410 655 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.dbtools.raptor.plsql.structure.OutlinePanel.getPopupMenu(OutlinePanel.java:385)
SEVERE 17409 200 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.dbtools.raptor.plsql.structure.OutlinePanel$2.mouseClicked(OutlinePanel.java:153)
Author
I’ll need code samples to test with, open a thread on the forums if you can.
The table is there. The table does not exist. sqlcl-4.2.0.17.096.0933
SQL> SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ‘DIM_FCTS_CMC_CLMD_IDCD_CD’;
TABLE_NAME
——————————
DIM_FCTS_CMC_CLMD_IDCD_CD
SQL> DESCRIBE DIM_FCTS_CMC_CLMD_IDCD_CDERROR:
——————————————————
ERROR: object DIM_FCTS_CMC_CLMD_IDCD_CD does not exist
Author
Does info work?
What kind of table is this?
DESC on HR.EMPLOYEES works for me
SQL CL v4.2.0.17.073
Windows 7
I have a local variable for TNS_ADMIN
in SQLDev I can use the TNS alias
In SQLCl I get an error where SQLCl is looking for jdbc connect string
If i use the jdbs connect string with /Service_name then I can connect.
What am I missing in making the tnsnames alias to work?
Author
Show me what you’re doing.
And when you’re in SQLcl, run
SHOW TNS
It will tell you what tnsnames files we find and what entries are available.
HI Jeff,
I am using SQL Developer Data Modeler 4.15 and I am using the compare feature of the modeler but I am getting some odd results when I read the data dictionary from an APEX 5.0 instance. What I am getting is additional tables that are not in the schema, any idea why this is happening?
Author
I’d have to see what you’re talking about to even hazard a guess.
What tables are being shown that aren’t actually there?
The list of tables are below and they are the same the two tables that are not in either model are agency and AGENCY_CONTACT . These two tables are not there.
FCI_ADDRESS
FCI_ENROLLMENT
FCI_HOUSING
FCI_INIT_ENROLL_INFO
FCI_INTERNSHIPS
FCI_L_AGENCY
FCI_L_CODE
FCI_L_COLLEGE_DEPT
FCI_L_CONTACT
FCI_L_COUNSELORS
FCI_L_INTERNSHIP_TYPE
FCI_L_MAJOR
FCI_L_MEETING_TYPE
FCI_L_PROGRAM
FCI_MEETING
FCI_STG_ROSTER
FCI_STUDENT
FCI_STUD_CONTACT
Author
I don’t have those tables in my APEX 5.0 install.
The tables I am talking about are not APEX tables but user created tables that I am comparing to the model. I selected the application tables I created in the in my workspace for the FCI application and compared it the FCI model in the modeler version 4.15. What I got is two tables that are not in the model or in the schema I was comparing. That is AGENCY and AGENCY_CONTACT which are in neither. Why?
Author
I don’t have your tables, so I can’t test.
When using the Sqlcl “ddl” command to retrieve the source for a proc or package, empty lines within the source are being dumped. Is there some-or-another option that controls this?
Author
Blank lines?
There are options for how the DDL is shaped.
A proc that should have empty lines..
SQL> create or replace procedure
2 uselessproc(
3
4 — What a useless argument
5 LevelOfUseless varchar2
6 )
7 as
8 begin
9
10 /*
11 This proc has plenty of open lines in it
12
13 Open lines are important to the readability of code
14 */
15
16 dbms_output.put_line(‘I do nothing useful ‘);
17
18
19 end;
20 /
.. is returning without them..
SQL> ddl uselessproc;
CREATE OR REPLACE EDITIONABLE PROCEDURE “SCOTTM”.”USELESSPROC”
(
— What a useless argument
LevelOfUseless varchar2
)
as
begin
/*
This proc has plenty of open lines in it
Open lines are important to the readability of code
*/
dbms_output.put_line(‘I do nothing useful ‘);
end;
/
If you have setup the formater to your liking, you can do:
SQL> ddl uselessproc;
SQL> format buffer
Not prefect but better the eating blank lines.
Hi Jeff
Thanks for your site. it is really useful.
In Oracle SQL Developer, how can I increase the number of entries shown in the
File -> Reopen menu?
Thanks in advance
Regards
Vadi
Bengaluru, India
Hi Jeff,
we’re attempting to integrate Oracle SQL Developer unit testing with Bamboo, and in doing this we have to first setup the DB connections. The Bamboo script can run on multiple agents, so it’s best to create a connection first (passwords can be stored securely in Bamboo), then import and run the tests, then remove the connection. So here’s what we tried for creating the connection (Windows PowerShell):
.\sdcli migration -actions=mkconn -connDetails=”MyConnection:oracle:TEST_USER/[email protected](DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBSERVER.SOMECOMPANY.COM)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB123.SOMECOMPANY.COM)))”
…or with TNS:
.\sdcli migration -actions=mkconn -connDetails=”MyConnection:oracle:TEST_USER/[email protected]”
In both cases we get the following error message:
Error:Problem creating connection:Test failed: IO Error: Connect identifier was empty.
Do you have a clue what we’re doing wrong? Thanks a lot for any help.
Jan
Jeff,
I am trying to improve my workflow by reducing the number of times I export data from SQL Developer into Excel. I often find myself copying data from SQL Developer to Excel in order to quickly locate a column in a large set of data.
When working in SQL Developer’s Query Result window, CTRL+F only evaluates the data returned from my query, excluding column headers.
Is there a way to quickly search in the Query Result window for a column name? I do not see anything listed in the Shortcut Keys section of Preferences.
Connor
Author
Column name, no.
Have you tried the single record view? It can make looking at wider data a bit easier on the eyes.
Thanks Jeff. Single Record View does help a tad. It would be great to have “Column Names” as selection in the Options section of the Find window.
Author
You’re not the first to ask, I’ll see what we can do.
Just following up on the improvement of searching in the query results window. My workflow is constantly disrupted when needing to quickly find data in a large set.
Author
Nothing yet. It’s not scheduled for a release, but that could change. If you want formal updates for an enhancement request, please submit a Service Request to My Oracle Support.
When exporting ODDM (4.1.5) designs to reporting schema, it seems that only the logical diagram can be save as pdf while any subview diagrams can not. I checked DMRS_DIAGRAMS.DIAGRAM_PDF column and records for subview diagrams are empty. Where is the problem or this is the expected feature for this version. The previous version 3.1.2 can creates pdf diagrams for logical or subviews diagrams when export to reporting schema.
Author
Sounds like a bug.
Hi Jeff,
I came across a response from you to my exact issue at http://theoracleemt.blogspot.com. I have posted your response below. I am using version 4.1.5.21 and I normally return queries with several hundred thousand rows. These queries often take a while so before I export to .xlsx I bring all of the data into the grid as to not run the query again. I normally have no problem doing this and then exporting for several hundred thousand rows and roughly 20-30 columns. However lately, Oracle has been hanging, freezing, crashing and I get the memory error. I’m confused as to why this is happening all of a sudden when I am normally able to do it without an issue. Your response is from 2015 so I am wondering if anything has changed since then or do I just need to run the query 2x to export it.
thank you
thatJeffSmithOctober 6, 2015 at 10:23 AM
So from what I can tell, this refers to the scenario where the user attempts to fetch ALL the data from the database to the grid before doing the export.
There’s only one reason I can think of for doing this – the query takes a very long time to run, and you don’t want to run it again to do the export.
If the amount of data is high enough to overwhelm the JVM as it’s put into the Grid, then it’s going to ‘hurt.’
If you’re doing exports, you should never run into memory issues…UNLESS…you’re exporting to Excel and you’re using the older XLS format instead of the newer XLSX format.
Our library for creating XLS files doesn’t support letting go of the data as it’s written to the spreadsheet, so after 100,000 rows or so the JVM is exhausted and the app will ‘hang’ – or in newer versions it will complain. So almost always:
+ don’t fetch all the data down to the client first
+ use XLSX not XLS
Author
Are you writing to XLSX?
Unless the query takes 10 minutes to run, I wouldn’t fetch the data down to the sqldev grid first – asking it to keep 100k+ rows of 20-30 columns is excessive.
And even if the query takes 10 minutes to run, I’d write a script to spool the data out to a CSV file and run that instead.
I am writing to xlsx. The individual query takes anywhere from 20-30 minutes. I just attempted to export directly instead of filling the grid first and it was running for over an hour so I killed it. I have attempted to spool the data previously and could never get it to work properly. Like I mentioned I normally fill the grid without an issue with a lot of data, it is only recently that it has been a problem.
Author
So, I’ll assume the query can’t be tuned.
Have you tried increasing the JVM max heap size?
>>I have attempted to spool the data previously and could never get it to work properly
What did you try?
I would use SQLcl, it’s much lighter weight and shouldn’t have any issue handling that amount of data.
The Query is a bit of a beast but I have tuned it as much as I think is possible. My DBA’s do not allow the creation of temporary tables so it involves a lot of sub queries.
I was reading about the JVM max heap size but I must admit that is way over my head and I’d be fearful I would break something. Maybe I will have to find someone to assist me with that.
The spool process appears to be restricted by my DBA’s as well.
SQLcl is not something I have seen before. I was looking at it on the Oracle site and the key features didn’t seem to be relevant, so not sure what I would use that for.
It looks like the heap size may be where I need to go, so I will try and hunt down some assistance with that.
thank you for your assistance as always!
Author
SQLcl is a better SQL*Plus. Not a GUI. Just a command line interface. So it requires less resources, runs faster, but not as click-button-y as SQL Developer.
To add memory to SQLDev, just find your product.conf and edit it such that you have this near the bottom
AddVMOption -Xmx2048m
That’ll let SQLDev take 2GB of RAM when its running.
I talk about this in detail here.
Hi Jeff,
in a sql devloper report i have a bind parameter of the date datatype. i wonder if there is a chance to set the actual date as default vaule (sysdate)?
Hi Jeff,
I am trying to pick up changes made between models or a model and a database for a materialized view. I want to do this to get the DDL for the mat view – ie the change that is going to be applied to a previous model. When I get to the Pending Changes screen the mat view is not marked with the yellow caution triangle icon as other changes are. So, it appears to not recognize the change. But, if I drill down into the object I know changed until I see the properties in the bottom half of the Pending Changes screen and look at the query by double clicking the elipses it shows me the change. Clicking the generate DDL button does not put the query in the DDL. Is there a setting somewhere that will allow the query changes to be picked up?
Hi Jeff,
I’m having a weird issue with completion insight on sql developer 4.1.5
As i’m typing my table aliases and the period that follows, the completion pops up no problem. Same when i type the first letter of the table/view/whatever i’m looking for. However, as soon as I type any more letters, autocomplete goes away and will not return unless i completely erase what I’ve typed and start over. It also will not appear with ctl+space.
Am I overlooking something silly?
Thanks
Author
No, that sounds like a bug.
So if you
that is correct. i would then have to delete what i’ve typed up until the popup would normally appear (so until the period or the first letter).
Author
This scenario works for me
Hi Jeff,
Does SQLcl have some sort of buffer that holds the results of queries? It seems that way given the weird results I’m getting . If so is there some way to clear it?
In order to demonstrate the different time datatypes in Oracle for some managers, I created a table having a column of datatype TIMESTAMP WITH LOCAL TIME ZONE and inserted some rows using the localtimestamp function. Doing a query on the table without changing the session timezone gives the expected results. However, in the same session, changing the session time zone then repeating the query gives the same results as the first query!!! If I create a new session, change the session time zone and query again, this time I get the expected results.
In short, the first query on the table returns the expected results given the session time zone but subsequent queries always return the same result regardless of the current session time zone.
I’ve tried this experiment using SQLplus and a SQL Worksheet in SQL Developer – both of these return the proper result for the current session time zone.
Am I missing something?
Thanks.
Norm
Author
it might be the nls timestamp TZ format…if you include that in your queries, is the data displayed as expected?
Unless I’m mistaken, NLS_TIMESTAMP_TZ_FORMAT doesn’t apply here – the issue I’m seeing is with the TIMESTAMP WITH LOCAL TIME ZONE datatype – that NLS format only applies to TIMESTAMP WITH TIME ZONE.
Just for grins and giggles I tried changing both formats (with and without the TZ) – no difference. Remember, I’m only seeing this behavior in SQLcl, not with any other client I’ve tried.
Author
we don’t ‘listen’ for when date/timestamp formats change in the session – we catch them if you run an ALTER SESSION for example, but if you’re changing it via a script or stored proc, the client won’t know about it and continue to display the time/dates in the old format
i think to help you i need specific examples of what you’re talking about with a test-able scenario
Here is the results using SQLplus (instantclient 12.1 on Windows 7):
SQL> desc date_table
Name Null? Type
—————————————– ——– —————————-
TIME_STAMP_TZ TIMESTAMP(6) WITH TIME ZONE
TIME_STAMP_LTZ TIMESTAMP(6) WITH LOCAL TIME
ZONE
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
—————————————————————————
-04:00
SQL> select time_stamp_ltz from date_table;
TIME_STAMP_LTZ
—————————————————————————
17-MAR-17 05.42.30.596926 PM
17-MAR-17 05.42.55.639511 PM
SQL> alter session set time_zone=’-6:00′;
Session altered.
SQL> select time_stamp_ltz from date_table;
TIME_STAMP_LTZ
—————————————————————————
17-MAR-17 03.42.30.596926 PM
17-MAR-17 03.42.55.639511 PM
SQL>
Note how the returned timestamp values are now 2 hours earlier due to the change in the session time zone. This is what I expected.
Now using SQLcl (latest version – again on Windows 7)
VIENS @ patd >select sessiontimezone from dual;
SESSIONTIMEZONE
America/New_York
VIENS @ patd >select time_stamp_ltz from date_table;
TIME_STAMP_LTZ
17-MAR-17 05.42.30.596926000 PM
17-MAR-17 05.42.55.639511000 PM
VIENS @ patd >alter session set time_zone=’-6:00′;
Session altered.
VIENS @ patd >select time_stamp_ltz from date_table;
TIME_STAMP_LTZ
17-MAR-17 05.42.30.596926000 PM
17-MAR-17 05.42.55.639511000 PM
VIENS @ patd >disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
@ >connect [email protected]
Password? (**********?) *********
Connected.
login.sql found in the CWD. DB access is restricted for login.sql.
Adjust the SQL_PATH to include the path to enable full functionality.
VIENS @ patd >alter session set time_zone=’-6:00′;
Session altered.
VIENS @ patd >select time_stamp_ltz from date_table;
TIME_STAMP_LTZ
17-MAR-17 03.42.30.596926000 PM
17-MAR-17 03.42.55.639511000 PM
VIENS @ patd >
Note the sequence of events:
Connect to the database.
Query the table – get the expected results for the current session time zone.
Change the session time zone.
Repeat the query – note the results are the same as the first query when they should be 2 hours earlier.
Disconnect from the session and log in again.
Change the session time zone
Repeat the query – this time the results are 2 hours earlier as expected.
Author
also, we don’t cache data
I’ve tried a number of experiments over the last couple of days (including accessing the test table via a database link) that has me convinced that this is a bug in SQLcl (which BTW also exists in a previous version – I tried). Given this I guess I need to create an SR with support.
Thanks for listening.
Norm
Author
It’s not ‘us’ per se, it’s another team – from what I can tell, the JDBC driver doesn’t support that.
Author
Have you tried using a thick client connection? I’ll test it later this morning.
If it really is JDBC, this is really disturbing!! This means that JDBC doesn’t support a datatype that has existed since (I believe) Oracle 9i!! I wonder what else isn’t working right.
I believe I’m using a thick client:
VIENS @ patd >show jdbc
— Database Info —
Database Product Name: Oracle
Database Product Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Database Major Version: 12
Database Minor Version: 1
— Driver Info —
Driver Name: Oracle JDBC driver
Driver Version: 12.1.0.2.0
Driver Major Version: 12
Driver Minor Version: 1
Driver URL: jdbc:oracle:oci8:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = pimsdb2.ext.ray.com)(PORT =
1521)) ) (CONNECT_DATA = (SERVICE_NAME = patd.pimsdb2.ext.ray.com) ) )
Driver Location:
resource: oracle/jdbc/OracleDriver.class
jar: C:/Users/94884/Documents/instantclient_12_1/ojdbc7.jar
JarSize: 3698892
JarDate: Fri Aug 29 06:14:11 EDT 2014
resourceSize: 2285
resourceDate: Thu Aug 28 19:14:38 EDT 2014
I did try this testing using a SQL Developer SQL Worksheet using both a thin and thick client connection – this WORKS in both cases. This really has me puzzled since from what I read in your blog SQL Developer and SQLcl use the same engine – why would one work correctly but the other not?
Norm
Author
I didn’t research it deep enough to see if JDBC driver supports it or not – just found that link I shared.
Depending on version of SQLcl and SQL Developer, you could be using a different JDBC driver.
I would open a SR with MOS.
Oh – another thing – I see the same problem running SQLcl on Linux (RHEL6) so it’s not a Windows specific issue.
Norm
Not sure it’s worth the effort to pursue this issue with support at this time (don’t get be started about the royal pain is has become to create tickets on MOS – I and my coworkers only create tickets when in dire need of support). Guess I’ll just stop using this tool since I don’t trust it and will advise my coworkers not to use this tool.
Author
I have the answer.
It’s a SQLcl bug. We’re not catching that the timezone has been updated. I’m looking for a workaround for you now until we can patch SQLcl for you. I know if you change the TZ on your machine it will reflect that for your data…but I’m looking for a way to pass the TZ to the JVM at start-up time.
Hi Jeff,
I’d like to know what in background process when connection to oracle database with Kerberos authentication check box checked. What sqlnet parameters sqldeveloper use it…? No Oracle client involved setup. How it created TGT? It is looks to me it does not do anything in client, because I didn’t specify any krb5 configuration on client machine. database on 12.1 on windows 2012; running sqldevloper on windows 7, 2008r2, 2012. all successful connection. Tried use sqlnet trace, no details. I really need to know, I need the same way in my applications.
Thanks Marina
Author
I’m not a kerberos guy…but if you didn’t provide any thin details, then it must be coming from sqlnet.ora – you don’t have anything on tools > database > advanced page configured?
Hi Jeff,
Thanks for quick response.
tools > database > advanced — no values configured or sometimes I use tnsnames directory. Both ways it is working fine.
Hi Sir,
We are planning to use SQL Developer unit test utility for testing PL/SQL programs. We are able to test successfully procedure contains data type char,number and date with dynamically value passing. But I am not able to find a solution testing dynamically with PL/SQL record type input as well as passed. We are successful in doing the test with static values. Only problem with Dynamic Value Query.
Can you please help me how to do this?
Regards
G. Srinivasa Rao
Author
Please post your scenario on the forums.
Hi Jeff,
I am struggling to find a way to change the cardinality in the model. By default I saw that is set as one to many and I want to change in 1 to 1.
I found only a solution do add in the comment cardinality=1..1 but for me it doesn’t work. Version 4.1.5, build 907
Thanks a lot,
Vlad
Author
you define that in the logical design
Hi Jeff,
Please bear with my newbie question,
I just installed sqlcl for mobaXterm on my win7 _x64 and it appears to not like queries being pasted with line-breaks. What am I doing wrong?
I don’t have this issue with sqlplus and thank you in advance.
example:
[email protected]> select sysdate
from dual;
Error starting at line : 1 in command –
select sysdate
Error at Command Line : 1 Column : 15
Error report –
SQL Error: ORA-00923: FROM keyword not found where expected
00923. 00000 – “FROM keyword not found where expected”
*Cause:
*Action:
Elapsed: 00:00:00.228
Error starting at line : 1 in command –
from dual
Error report –
Unknown Command
hi Jeff,
To create some custom tools, we are looking for a coherent formal grammar for plsql and/or sql.
Do you think Oracle could publish such a grammar?
(see also https://community.oracle.com/thread/4021505 )
Many thanks.
Jan Vervecken
Hi, Jeff
Is it possible to configure preferences so schema identifier is shown along with the name when i open procedure or package code.
I know it is possible for tables. But for PL/SQL objects i did not find it.
Thnks.
Mike R.
Author
No.
Modeler & Roles – Modeler has numerous database properties that can be added & edited. I added some roles, but don’t see a way to generate a script from Modeler?
Thanks!
Author
do you mean a database role, like DBA?
Yes, database roles.
The ones that can be setup under:
-> Physical Models
-> Oracle Database 12c
-> Roles
I run into a somewhat unexpected behaviour with SQLcl when using “sqlformat delimited”. I’m trying to generate CSV output importable into Excel and I need to escape certain columns with =”xyz” instead of just plain “xzy” for that.
set sqlformat delimited ; =” ”
does the trick very well except for the first column of the result set.
select to_char(1), to_char(2) from dual;
returns
=””1″;=”2″
instead of
=”1″;=”2″
as I would have expected. Is there any reason why SQLcl does treat the first column differently here?
Author
mmmmmmm, no, looks like a bug.
You could try this as a workaround.
It was great seeing you at the last UTOUG Fall Symposium! Hope the rest of your Spartan races for the year went well! Aroo!
I know the functionality exists to export a single query result to a MS Excel worksheet (with option to name the worksheet) as well. I also understand the functionality exists to export data from multiple objects (tables/views/materialized views) data to separate worksheets in a single workbook file.
But is there a way to export multiple custom query results to a single MS Excel workbook with each query result in it’s own worksheet?
This would be very useful (extra credit for providing some metadata in the query comments to override/name the worksheet tabs).
Thanks so much!
Alex
Author
No way to do that outside of copying and pasting, sorry.
I’m signed up for a super and sprint in WV for August. My foot is bothering me, hopefully I can get ready!
Been exploring sqlcl as and alternative to Sql*Plus for some of the users. I am having two issues. First is returning a resultset of about 2000 rows about 2 minutes in sqlcl and 5 second in sqldeveloper , using F5 run script.
To test if changing sqlformat would change the speed I discovered an issue that really concerns me. When I use set sqlformat default I properly receive an error I knew is in the view. When I use set sqlformat ansiconsole I retrieve a portion of the rows but not error.
I was testing various sqlformats and json fails to show error but csv does.
Is this a bug or a feature?
Author
What error?
It could be any error in the select but in this case it is an ORA-01427, I can be reproduced with :
SELECT o.*,
( SELECT created FROM all_objects A WHERE A.object_name = o.object_name ) new_date
FROM all_objects o
run as both default and ansiconsole. The failure is seen in default mode, but just a partial resultset is seen in ansiconsole. This fails because the same object name exist in two schema and at least one buffers worth of data is returned before the errant row.
Hello Jeff,
Before I ask my question, I should disclose I am completely new to Oracle SQL developer, (& to databases in general).
Question: How do I export a query output to SPSS (Statistical Package for Social Sciences)?
(I saw your /*csv*/ post, and ran it successfully, but have been unsuccessful when importing it into SPSS). My apologies if this is a remedial question…. but thank you in advance for the assistance.
Kiki Martin
Hi Jeff,
In my table to insert Persian characters so when I want to retrieve data then got wrong characters. How to set character set or get correctly data? in other tools such as SQL Developer or Toad everything is right without any setting.
Author
Check your display font.
Hi Jeff,
I forgot to mention that I have issued in SQLcl.
I’ve checked in Linux although conversely displayed Persian character and next in windows XP/7/8/10 but I could not display correctly however I have followed these links:
http://www.thatjeffsmith.com/archive/2016/08/unicode-and-oracle-sqlcl-on-windows/
http://stackoverflow.com/questions/14109024/how-to-make-unicode-charset-in-cmd-exe-by-default
https://ss64.com/nt/chcp.html
http://www.wikihow.com/Customize-the-Font-in-Windows-Command-Prompt
For check you can use:
@https://gist.githubusercontent.com/Saeed-Hassanpour/8b299844aea7cfc27450a6af4bdc71ad/raw/5e39163bb2da9db0e91e2bae683bd9ce5e290486/sqlcl.sql
Right result:
Welcome to Parto Pardazesh Fartak(IRANAPEX)
به شركت پرتو پردازش فرتاك (ايران اپكس) خوش آمديد
Is it possible to run a query against two different connections at one time? If so, how? PS-I love SQL Developer. <3
Author
with one click? No, but you can open a connection on each db and run the query in 2 worksheets concurrently.
Unit Testing:
I’m wondering if there is reason for not allowing Variable Substitution in the teardown phase? I’d like to have that functionality. (I’m on 4.2 EA)
Author
Maybe? It was built out in version 2.0 and has seen bug fixes and small tweaks since then. Describe your use case on the Exchange, and maybe we can work it in.
Hi Jeff,
I am trying to use the latest SQL Developer 4.2.0.16, but I am unable to launch it. I am getting complains about msvcr100.dll file. I have followed the instruction (found on the net) to edit sqldeveloper.conf file and comment out SetJavaHome. I never get prompted for Java Home and for whatever reason it’s looking under ~sqldeveloper\jdk\bin instead of ~sqldeveloper\jdk\jre\bin.
Do you have any trick to make it work?
Thanks
Author
yeah, it’s a bug with the EXE – you need to copy/create that directory manually and put the DLL in there
it’s already been fixed for production release, sorry for the confusion/inconvenience
Hi, Jeff.
One more question.
I want to use the ‘SQLDeveloper Navigate ShortKey’ for moves cursor on the SQL-Worksheet
(go to marks, go to the last edit), but it works only in EditorWindow
I often edit large scripts and ‘bookmarks’ can really help me in my work.
Why does it not work?
Maybe i misunderstand the idea?
Author
probably a bug
I also would like to use bookmarks in SQL Worksheet to navigate within a long script. Googling and Oracle documentation do not help me enable bookmarks. I can toggle the view for bookmarks, but cannot determine (or find) how to create a bookmark in my code.
Help?
john3
Hi Jeff, firstly thank you for an awesome website!
I’ve decided to try the query builder, and have run into a problem –
I drag a couple of tables in, but when I right click on a table in Query Builder, the click doesn’t do anything – I don’t get any context menu with an option to uncheck all.
For comparison’s sake, I can double click the table title to change the alias, or left click other areas to get a context menu no problem.
So unless I want to use all the columns (not likely) it’s not of much use to me!
Is there a preferance or setting I’m missing?
Running vs 4.1.3.20
Thank You!!
Jeff,
In SQL Developer a pop up box appears where values can be entered and bound to parameters. I want to pass a parameter that binds to a CSV, i.e. value1,value2 … etc., so that I can write my where clause as:
WHERE app.attribute1 IN (:P_CSV)
so that the parameter becomes
WHERE app.attribute1 IN (‘value1′,’value2’)
Is this possible?
Author
yes, but don’t use a bind, use a sql*plus style substitution variable
Hi, Jeff. Thank you for thatjeffsmith.com 🙂
I am using Oracle SQL Developer in my daily work. Please tell me whether you can change the position of the “Query result area” between the right and the bottom of the SQL-worksheet or make a “float window”?
Author
Sorry Sergey, the results panel is locked to the bottom portion of the worksheet window. You can minimize it, and click/drag it up and down, but you can’t move it to the ‘right’ or undock/float it from the Worksheet itself.
Oh, I suppose it..
Hotkey to minimize/display ResultsPanel could help me, but i not find this in Preferences -> ShortKeys 🙁
Author
I’ve logged a bug on that, for the moment you have to use the mouse.
Ok!
Thank you very much, Jeff.
I like this product and i wish it become better and better.
Trying very hard to get all developers onto one tool viz SQL Developer however we also want to start managing our database as code. I cannot find an equivalent to Devart’s db projects?
We have 4.15 and 4.2 installed. Does this functionality exist?
Author
No, we do some bits of that tech, but not all of it.
I have a SQL which I use within SQL*Plus
select * from &owner..emp;
Using the same SQL in SQLDeveloper, I replaced the “&” with “:”
select * from :owner..emp;
I tried various combinations and I can get it to work.
For eg: select * from :owner.emp;
select * from “:owner”.emp;
select * from ‘:owner’.emp;
Is it even possible?
Hi Jeff,
I have a question regards IOTs (Index Organized Tables) and the “Database Export Wizard”:
My IOTs are not shown after lookup under “Specify Objects”. Just all “normal” heap organized tables.
Exporting single SQL by selecting IOT in schema browser tree works fine. But I would like to do a FULL schema-ddl-export including IOTs with the export wizard.
Anything I am missing?
Version 4.2.0.16.356
Build 16.356.1154
All the best,
Bernd
Oh now I now what I am “missing” -> They will be created with the creation of the index-type CTXSYS.CONTEXT on the Base-Tables. Correct?
Eye see you’ve reformatted your web page – and from the looks of your picture you haven’t reached the age yet where grey on white is hard to see. Here’s a wish we can get grey replaced with black for older eyes. The contrast really helps. Love your site and your sense of humor!
Author
Darker text on the post body? I think I can do that for sure. If it makes you feel any better, I officially turn ‘old’ this weekend.
Author
Ok I darkened the text, it’s not ‘pure’ black, but it’s still darker and has a bit of contrast from the titles. Let me know if that helps.
Looks better, thanks!
Hi Jeff. I love the way you do search and replace in SQL Developer and wish every program worked the same way, entering the sought text into the replace box and highlighting it. That saves time! 🙂
Keep up the good work.
Author
I love this too – so much that I about lost my mind when someone re-mapped Ctrl+R to ‘refresh’ instead of replace in 4.2. I logged a bug and that SHOULD be fixed for when we go prod.
I use Code Blocks for C++ programming and there is great shortcut: “Copy line”.
Without selecting a text just copy current line. In default shortcut is CTRL+Shift+T,
but i changed it to CTRL+D, because I use it very often.
I read your blog, looked into SQL Developer and googled a lot, but didn’t found that option.
Could you tell me if there is a way to define such action and assign shortcut for it in SQL Developer?
Note: using version 4.1.
Maybe it is a good idea to introduce such feature in next release? 🙂
Author
Best I can offer, you can set a kb shortcut for ‘select line’ – you’d still have to do the copy though, so 2 keystrokes instead of 1.
Thank you for your answer – ‘select line’ shortcut is helpful.
In fact that shortcut, which I described (and desire in SQL Developer) do a bit more: copy current line, create new line down there and paste this line.
So 1 shortcut for 4 operations, nice deal.
I will be happy to see that available, maybe in a future 🙂
Author
Have you tried to record a macro for this? It’s not something I’ve spent any time with in SQLDev, but they are supported.
Thank you for that idea.
I tested that and it works, it’s clever way to save some effort. But downside is that I can have only 1 macro, while more of them will be useful (for example as User-Defined shortcuts).
Thank you!
Regards,
Adam
Is there a way to force SQL Developer to load package body and specification from server upon open (double click or Edit)? Or is there a way to check for definition change on the server before save? We have run into situations where package has been update by somebody else, but other developer overwrites it.
Thank you,
Ilmars
Author
force both when you open just the one – no
way to check if source has changed – it depends
Are you using Source Control? Hopefully the answer is ‘Yes!’ – then say if you’re using Subversion, you should see incoming changes in your SVN client or in SQLDev if using it there, and then you can check-in/merge.
Jeff, let me clarify the first question. Users A and B open up a connection, navigates to Packages. User A modifies package X – compiles. User B right away opens up package X by right clicking package Edit. User B does not see the changes made by user A. Most of the times, changes are reflected in a specification and not in a body. User B either needs to close the package and open it again with Edit or do a package refresh and then open with Edit.
Does SQL developer caches any package definitions? Trying to understand if this is a database or SQL Developer issue. Using 11g XE, SQL Developer version 4.2.0.16.260.
Thank you!
Author
View > Log > Statements.
Open your package.
You’ll see the queries go across the connection to get the code.
Close the object.
Open it again. You’ll see the queries run again.
I don’t think we’re caching pl/sql source in the code editors, although we do cache OTHER things, like the DDL we generate on a table – I THINK.
Good Day, Jeff
A trivial question:
I updated my SQL Developer from 4.1 to 4.2. I work with 2 separate connections and have the worksheets side-by-side vertically.
With the previous version, i save my worksheets and close the Developer, then when i reopen it, the worksheets open in the vertical orientation.
In 4.2 it is not the case. Am i missing a setting to always open in vertical view?
Thank you in advance.
Author
can you share a screenshot of your 4.1 setup?
I’m sorry, Jeff but I do not see where there is an option to attach a file and paste in the text window does not seem to be an option for this site.
Author
There’s not, but just use a free internet site, and post the link to your image back here.
http://imgur.com/a/8sOts
I think i may have found the resolution.
I just tried this out:
In the Preferences >> Database >> Worksheet
Check the “Open a Worksheet on Connect”
Now every time i open Developer, my connections tile vertically.
Thank you for such quick replies, Jeff.
You are amazing for having this discussion board that links the community to you!
Author
Ah, thanks Stasia. I can’t do my job w/o talking to our users, and this makes it a bit easier – I hope!
I have SQL Developer 4.1.3.20. When I run a script that I’ve stored under the User Defined Reports and spool the data to a csv file, the output has the query in the file. How do you omit the query from the file?
Author
Use the appropriate sqlplus SET command.
I think SET ECHO OFf will do it
Hello Jeff,
I recently updated my Oracle to Version 4.1.3.20 and it lost all of my passwords for my Database connections along with all of my preferences.
Is there an easy way to restore them?
thank you
Author
Delete your system4.1.3.. directory. Start sqldev again, this time say YES to importing your preferencees from an earlier version
thanks, Jeff, i actually see you have an official post about this too. I’m sorry i asked it again. I will pre-empt keeping my passwords for the future.
Hi Jeff,
I am using SQL Developer 4.1.5 on Linux. When working with files (View ==> Files), is there a way for hidden files and folders to remain hidden in the SQL Developer File navigation tree? I have many hidden files and folders and they are cluttering up the navigation tree and making it hard to find the files I really want to work on.
I am trying to determine a way to modify SQL Developer to read a PKI CAC and use that information (with the prompt for the PIN) for access to an Oracle Database. I know that the docs talk about Strong Authentication and storing certs on the server, but that literally disconnects the cert from the process, and what I am talking about is Multi-factor authentication. Oracle provides tools to do this for Web based applications, but it seems that MF Authentication is missing in connection to the database. Do you have any suggestions on how SQL Developer could be used to do this?
Author
I know we can connect using stuff like YUBIKEY…so you have options
Thanks Jeff. Since this is a DoD customer I don’t think the dongle would be acceptable. I will pursue other options.
Author
we should support pretty much anything the oracle JDBC driver supports – i know there are 2 factor authentication schemes avail
Hi Jeff, I recently upgraded to Windows 10 and downloaded the latest version of SQL Developer(4.1.5). When I launch SQL Developer it is automatically closing at different times. It does not stay open for longer than about 5 minutes and sometimes closes down in seconds. I attempted to launch from the command line and collect the error but it did not provide any information. Can you provide some additional information on what I can try to narrow down the issue?
Thanks,
Jon
Author
open a cmd window, go to the sql developer BIN folder. start the EXE from there
see when it closes, if it prints any info there.
this most closely sounds like an issue where the video driver crashes the JVM – you might just want to update your driver.
Jeff,
When using the instance viewer in Version 4.2.0.16.356, is there a way to show local time instead of GMT on all of the metric graphs?
In Verdion 4.2 of SQLDeveloper, there is a new behaviour in code editor. Just one click is needed to mark a word, which leads to unwanted results:
i.e. when You mark long sections (click at the begin, scroll down hold shift and then click at the end), or when you just want to position the cursor at the end of a word (here the word is marked and you have to click a second time).
Is there a preference to bring back the old editor-behaviour?
Author
no, but i have a bug logged on that
Jeff, Can we join query results and a table to get new results ? for example if i ran one sql and got query result1 now i need to run another sql by using query result1 and table B…is this possible in Sql developer ?
Author
No, you’d what to join your original query to your 2nd query.
SO if query one was
select * from hr.employees
And your 2nd query was
select * from hr.departments
Your 3rd query COULD be
select * from hr.departments a, hr.employees b
where b.department_id = a.department_id
Thanks Jeff, In hyperion we can use results and join with a table that why i asked but any way , it could be the new feature in your new version release.
Thanks
Deepak
This is the closest thing I’ve found to a SQL Developer community, so I thought I’d ask here. I’m trying to get SQL Developer 4.1.3.20 connected to SQL Server 10.50.2500.
I’ve tried both the jtds-1.2 and 1.3 drivers. Due to network and SQL Server configurations outside of my control, I know from connecting other tools (e.g. Oracle Data Integrator) that I have to start any Java-based processes with the -Djsse.enableCBCProtection=false option. I’ve put this in my sqldeveloper.conf file and I see that option reflected in the command that launches SQL Developer.
When I configure the properties in the “SQL Server” tab for a connection and test, I don’t see a database in the dropdown tab and when I test the connection anyway I receive a “DB host closed connection” message. This is the message that I would receive in other tools or test programs when the enableCBCProtection option doesn’t make it to the JVM launch.
Is there something I’m missing here?
Thanks.
Author
Try adding that to the product.conf file. In general, JVM properties are set there.
Official SQL Developer community is here
Latest version of SQLDev, we recommend you use jTDS 1.3.1 JAR
Thanks for the link.
I checked my SQL Developer installation (on Linux) and don’t see a product.conf file. In any case, I verified in Help/About/Properties that the option is being used so I think the issue might be something else.
Author
ahhh, sorry. on *NIX, that would be in your $HOME/.sqldeveloper folder.
you might also be able to edit the connections.xml file and edit the connection entry and insert a connection property to the JBCC URL
Is there any way to quickly jump to a particular column in SQL Developer? I’d like to “Select *” to view the entire row as it is, but scroll over to say column 43 (Brand_Type_Code) to see what the data looks like there. I don’t believe I’ve run across this request before, but it seems like it would be a handy feature.
Thank you!
Author
Nothing like what you’re asking – but the Single Record View might give you some relief?
Yeah, that’s what I thought, but it doesn’t hurt to check.
Thanks again for all you do!
To Whom it may Concern :
Having issues with SqlDeveloper keeps crashing after a few minutes with me loggin in. It just pretty much disappears. Was able to copy debugging info.
Also include the following information:
• The version of SQL Developer you are running
• The version of the JDK you are using
• The OS you are using
• The version of Oracle you are connected to
OS : Windows Server 2008
Java Version 1.8.91
Version Sql Developer : 4.1.3.20….. But really I’ve tried several different versions and I get the same issue.
Oracle version :
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for 64-bit Windows: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production
Below is the debugging info I was able to get. Thanks in advance for your awesome work with sqldeveloper….Unfortunately I couldn’t paste everything because of the character limit..
LOAD TIME : 407UsersCache.fillIn() time = 93 ret==null?: true
2016-12-01 14:09:03
Full thread dump Java HotSpot(TM) 64-Bit Server VM (25.91-b15 mixed mode):
“Background Parser” #63 prio=6 os_prio=0 tid=0x000000001c72d800 nid=0x1354 waiting on condition [0x000000002282f000]
java.lang.Thread.State: TIMED_WAITING (sleeping)
at java.lang.Thread.sleep(Native Method)
at oracle.dbtools.raptor.plsql.BackgroundParser$1.construct(BackgroundParser.java:126)
at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
at java.lang.Thread.run(Thread.java:745)
“TextBufferScavenger” #61 prio=6 os_prio=0 tid=0x000000001c72c000 nid=0xb10 in Object.wait() [0x000000001b81f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:143)
– locked (a java.lang.ref.ReferenceQueue$Lock)
at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:164)
at oracle.ide.model.FacadeBufferReference$PollingThread.run(FacadeBufferReference.java:145)
“oracle.jdbc.driver.BlockSource.ThreadedCachingBlockSource.BlockReleaser” #58 daemon prio=4 os_prio=-1 tid=0x0000000016908800 nid=0x1b78 in Object.wait() [0x000000001ecdf000]
java.lang.Thread.State: TIMED_WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at oracle.jdbc.driver.BlockSource$ThreadedCachingBlockSource$BlockReleaser.run(BlockSource.java:327)
– locked (a oracle.jdbc.driver.BlockSource$ThreadedCachingBlockSource$BlockReleaser)
“Timer-2” #55 prio=6 os_prio=0 tid=0x000000001f01e800 nid=0xfcc in Object.wait() [0x000000002410f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:502)
at java.util.TimerThread.mainLoop(Timer.java:526)
– locked (a java.util.TaskQueue)
at java.util.TimerThread.run(Timer.java:505)
“status-0” #51 prio=2 os_prio=-2 tid=0x000000001f01c000 nid=0x1ae0 waiting on condition [0x0000000023c0f000]
java.lang.Thread.State: WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
– parking to wait for (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
at java.util.concurrent.DelayQueue.take(DelayQueue.java:211)
at oracle.ide.status.StatusExecutor$StatusQueue.take(StatusExecutor.java:338)
at oracle.ide.status.StatusExecutor$StatusQueue.take(StatusExecutor.java:300)
at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1067)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
“IconOverlayTracker Timer: null-jdbcNodeInfoType” #46 prio=5 os_prio=0 tid=0x000000001f018800 nid=0x1b6c in Object.wait() [0x0000000023b0f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:502)
at java.util.TimerThread.mainLoop(Timer.java:526)
– locked (a java.util.TaskQueue)
at java.util.TimerThread.run(Timer.java:505)
“Timer-0” #45 prio=6 os_prio=0 tid=0x000000001f01a800 nid=0xc18 in Object.wait() [0x000000002325f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:502)
at java.util.TimerThread.mainLoop(Timer.java:526)
– locked (a java.util.TaskQueue)
at java.util.TimerThread.run(Timer.java:505)
“WaitCursor-Timer” #44 prio=6 os_prio=0 tid=0x000000001f01a000 nid=0x1350 in Object.wait() [0x000000002315f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:502)
at java.util.TimerThread.mainLoop(Timer.java:526)
– locked (a java.util.TaskQueue)
at java.util.TimerThread.run(Timer.java:505)
“WeakDataReference polling” #43 prio=1 os_prio=-2 tid=0x000000001f019000 nid=0x1338 in Object.wait() [0x0000000022f2f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:143)
– locked (a java.lang.ref.ReferenceQueue$Lock)
at java.lang.ref.ReferenceQueue.remove(ReferenceQueue.java:164)
at oracle.ide.util.WeakDataReference$Cleaner.run(WeakDataReference.java:88)
at java.lang.Thread.run(Thread.java:745)
“RaptorTask: Usage Data” #40 prio=5 os_prio=0 tid=0x0000000016908000 nid=0xe9c waiting on condition [0x000000002272f000]
java.lang.Thread.State: WAITING (parking)
at sun.misc.Unsafe.park(Native Method)
– parking to wait for (a java.util.concurrent.SynchronousQueue$TransferStack)
at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
at java.util.concurrent.SynchronousQueue$TransferStack.awaitFulfill(SynchronousQueue.java:458)
at java.util.concurrent.SynchronousQueue$TransferStack.transfer(SynchronousQueue.java:362)
at java.util.concurrent.SynchronousQueue.take(SynchronousQueue.java:924)
at java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1067)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
“Thread-9” #39 daemon prio=5 os_prio=0 tid=0x0000000016907000 nid=0x1448 in Object.wait() [0x000000002242f000]
java.lang.Thread.State: WAITING (on object monitor)
at java.lang.Object.wait(Native Method)
at java.lang.Object.wait(Object.java:502)
at java.util.prefs.AbstractPreferences$EventDispatchThread.run(AbstractPreferences.java:1482)
– locked (a java.util.LinkedList)
Jeff, Wondering if it is possible to execute unittest via sdcli64.exe for a PostGres target e.g. our unit test repository is in Oracle (fine) and works for Oracle target -db (also good) and our Postgres JDBC driver to allows a SQL*developer connection to PostGres ( good/fine/great) BUT : we cannot run a unit test that is testing a PostGres database , from the command line or anywhere else. Works fine for Oracle -db but bot Postgres. We get this sort of error via sdcli64.exe from command line :
“Could not connect to database postgres on localhost. The error encountered was: The connection subtype SDPostgreSQL is not recognized.”
Will the “SDPostgresSQL” connection subtype be supported ever for sdcli64.exe ?? Is there a way for us to extend this ourselves via configuration ?
Author
Ummmm, no.
Ummmm, if you’re trying to be Tom Kyte it helps if you’re Tom Kyte.
Author
I’m just trying to be me.
Hi Jeff,
I’ve started using the SQL Developer Data Modeler tool (4.2.0) and have created a logical model which I want to use with the business users. But some of the attributes have names longer than 30 chars which is a problem when I go to generate the DDL from the relational model. I’m wondering if there is a way (similar to the display entity synonym) to allow long names in the logical model but then have columns names for the relational and physical models.
Also can you recommend any blogs etc (apart from your own obviously!) for new users please.
Many Thanks
N.
hi, found it so please disregard the question.
Can be done via the attributes properties.
Need to go to Logical Model/Entities – expand the entity to show the attributes and then double click the attribute to show the properties.
Regards,
N.
Author
oh good!
btw, other modeler bloggers…
you don’t get better than
Heli
Kent
I am trying to use ‘Oracle SQL Developer’ migration assistant but it errors out without any messages been inserted into ‘MIGRLOG’ table. I have posted the question on stackoverflow where you replied asking for error details which I added but that question was put on hold by some users hence posting it here if you could advise.
Link : http://dba.stackexchange.com/questions/160560/sybase-to-oracle-db-migration
**What has been tried so far:**
Tried using Oracle SQL Developer’s “Migration Assistant” without any luck. It shows errors, not successful.
*Specific questions*:
1. What tools can be used for such migration which converts Sybase objects to Oracle to some extent? Even if we could get partially converted scripts, it can be reviewed and modified manually?
2. Can ‘Sql developer’ be also used or provide any data reconcillation utilities after the migration.?
*SQL Developer Migration assistant errors*
When I run ‘oracle sql developer’ migration assistant, I get error as:
> “Capture failed. Refer to MIGRLOG table in the repository for details”
When I see ‘MIGRLOG’ table, there are no messages at all.
It creates `MigrationLog.xml` file which has below error (same as above). This is just snippet as other info in the xml shows just some class file names. Hence I am unable to research further.
**ERROR**
oracle.dbtools.migration.workbench.core.MigrationLogResourceBundle
SEVERE
oracle.dbtools.migration.workbench.core.logging.MigrationLogUtil
Capture
Enterprise Capture
Capture failed. Refer to MIGRLOG table in the repository for details
oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doOnlineEnterpriseCapture(FullMigrateTask.java:758)
oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doCapture(FullMigrateTask.java:601)
oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doMaskBasedActions(FullMigrateTask.java:400)
oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:314)
oracle.dbtools.migration.workbench.core.ui.FullMigrateTask.doWork(FullMigrateTask.java:147)
oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
java.util.concurrent.FutureTask.run(FutureTask.java:262)
oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:554)
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
java.util.concurrent.FutureTask.run(FutureTask.java:262)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
java.lang.Thread.run(Thread.java:745)
[email protected]
oracle.dbtools.migration.workbench.core.ui.FullMigrateTask$FullMigrateTaskException: Capture
Enterprise Capture
Capture failed. Refer to MIGRLOG table in the repository for details
Author
what version of SQL Developer?
what version of Sybase ASE?
We have a forum setup JUST for migrations. Suggest you post this there, with the details I asked for.
Also make sure your db user has the privs needed to get all the information out of the database data dictionary.
Ok will post it there too.
The info just for reference is as below :
SQL Developer version : Version 4.0.3.16
Syabase ASE version : 15.7
Oracle version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
Author
that’s a really old sqldev…might want to try 4.1.5
I would like to copy and share your Oracle ER Model image, and the link to the article associated with it in a ‘helpful’ document I’ve created for my DB Design and SQL classes. Here is the URL: http://www.thatjeffsmith.com/archive/2013/11/how-to-create-views-for-all-tables-with-oracle-sql-developer/. Please email me with permission or rejection. Thank you kindly.
Author
Absolutely, feel free. Just be aware I’ve been known to make a mistake here and there, so feel free to correct those as you find them 🙂
When I connect SQL Developer to a SQL Server database and run a query, it appears that in the results I cannot double click on the header to sort or click on the filter icon to filter. Are these features disabled when working with SQL Server data?
Author
Maybe/probably. Many/most of the features in SQL Developer are written specifically for an Oracle (or TimesTen) database.
The connectivity for SQL Server allows you to do many things, but it’s there primarily to help you migrate it and its data to an Oracle Database.
Hi Jeff,
Happy New Year and thank you for maintaining this site. I started using SQL Developer on Mac OSX a week ago and find your site very useful. I was a TOAD user on Windows for a long time but find the Mac version of TOAD to be sadly lacking in features. I have mostly been able to find suitable replacements for features and shortcuts that I used in TOAD in my current version of SQL Developer for Mac. But I am facing a peculiar problem. In TOAD for windows, I am able to popup describe packages in the production instance where I have read only access by just typing the package name and hitting F4 (for popup describe). In SQL Developer, however, even though I am able to view the package code using popup describe in the development instance where I have read write access, I get the “No Object Found:” error message when I try to popup describe the same object in the Production instance. Is there a setting that I need to change in SQL Developer that will allow me to popup describe objects in read only databases? This also seems to be the case with custom tables where I have read only access in Production, but I am able to see the table details if I precede it with the schema name. This approach doesn’t seem to work for packages though since the popup describe shows the details of schema user name, user ID and Creation Date instead.
Author
can you give me a scenario…such as
login as user x with y privs to z objects
open worksheet
type ….
try shift+F4
observe code fired as shown in View > Log > Statments
that might be enough for me to see if there’s a problem, or you might see for youself.
You can also try to ctrl+click vs DESC
I am not sure I follow your question completely. In our dev environment, I log in as ‘apps’ user with read and write access. I type in the name of a custom package and use F4 to popup describe the package contents so that I can view the code within the package. In the production environment, I log in with my individual user login to which an IS_USER role has been granted. I believe the access is read only/select for this user role. When I try to use F4 to popup describe, SQL Developer on my mac, it shows me a message that says “No Object Found: “. When I use TOAD on windows, I am able to popup describe the custom package in production. I did notice that SQL Developer automatically opens the popup describe of a package in a new worksheet in editable mode in the development instance whereas TOAD always opens it in read only mode. Could this be causing the issue?
Note: I have mapped the F4 function key as a shortcut for Popup Describe. ctrl+click does not really do anything other than opening the context menu at the cursor from which I can again choose Popup Describe if needed, but this gives the same result. I can see an option to choose View > Log, but there is no submenu under log to choose ‘Statements’. When choosing Log, no additional information screen pops up. The logging page at the bottom of the screen does not have any additional messages either.
Thanks for the help
I tried using SQL Developer on Windows and was able to extract the log Statements from there. Same issue on Windows for Dev vs. Prod. I have the log details in a spreadsheet. Is there a way for me to share the spreadsheet with you?
Author
look at the statements yourself – you should see where we are querying the data dictionary, looking for the object you want to do an DESC on.
run those queries for yourself in a sqlworksheet – is the object you’re trying to DESC coming back from the query? If not, there’s an issue with your security/priv levels in the data dictionary. Or maybe you found a bug.
I have been having this issue with my SQL Developer for several months now. I will open the software and try to connection and it will freeze. I then have to shut the program enough times or shut of computer five plus times. The last time this happened I noticed a error message in the logging page. message “Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3”. This error does not show when you open the program it only showed up when I happen to open the second session of it. I was not in the process of running anything. This is when I am opening the software first thing when I get to work and it starts that freezing issue.
Thanks
Author
try unchecking
preferences > usage reporting > allow automated usage reporting to Oracle
Next time the application freezes, you can use JSTACK program in your JDK/bin folder to grab a stack dump. This can be read to find what the UI blocking process/operation is.
Why does the latest Oracle 12c (Windows 64-bit) include SQL Developer version 3? Why not a newer version?
This makes life very confusing since version 3 does not have the data modeler built in (and some other features like SQL Server connectivity that are discussed widely elsewhere).
Author
Please define, ‘latest.’
But basically, we get to contribute the latest and greatest version of sql developer available when a major new revision of the database is released. when 12c came out, v3.2 was the latest and greatest.
Most of our users don’t run sqldev out of an $ORACLE_HOME, they get a newer version from OTN
Also, SQL Server connectivity is provided by a JDBC driver that you can download from sourceforge – we don’t ship those 3rd party drivers for licensing and other reasons
Happy New Year and thanks for this tremendous service. The CIO is asking me “is there are any java license issues because we use SQLdeveloper”. He’s normally a stay out of the way kind of guy, so someone must be asking him for a response. Is there a tool to check for java lcense compliance? Thanks
Author
Absolutely no worries.
Read this. You fall into category #2 – gratis 🙂
Keywords in licensing that puts you in the ‘free’ bucket are ‘for the sole purpose of running Programs‘
I have reverse engineered a database that has common columns in the tables: CreatedBy, CreatedDate. I want to add a RDBMS Comment to those columns without having to hit each table.
One of the things I did in the past when using Oracle Designer is bulk updates to the metadata repository. In ERwin, this can be done by importing the definitions from a spreadsheet using a macro. ER Studio will interpret these as domains and you can update the domain and the columns will inherit the definition.
I searched to see if anyone had found a way to do this in SDDM. I was thinking that it could be done using a table_template but this will add the columns to tables that do not have them, and it will change the properties from what was RE. I was thinking it could be done with a custom transformation. I also considered editing the XML files directly using Grep for Windows.
Do not want to re-invent the wheel if you have a solution.
Author
I think the best way to go about this today is to do a search for those attributes/columns. Then export that to Excel. Then change the values in Excel, and then import that back to your model – it will update the comments as you’ve entered them in the spreadsheet, back to your data model.
Hi Jeff,
We have installed the Oracle SQL Developer version 4.0.3 in the Citrix Hosted Shared Desktop environment running on top of Windows Server 2008 R2. We have been receiving calls from users that their application is getting crashed less than 2 mins after they launch it. Below is the event viewer log for your investigation. Being a Citrix admin, it is bit challenging for me to fix the issue. Any help from your your end be much appreciated.
Oracle SQL Developer Version : 4.0.3.16
Java(TM) Platform : 1.8.0_112
Log Name: Application
Source: Application Error
Date: 1/3/2017 3:58:30 PM
Event ID: 1000
Task Category: (100)
Level: Error
Keywords: Classic
User: N/A
Description:
Faulting application name: sqldeveloper.exe, version: 0.0.0.0, time stamp: 0x520cfeb5
Faulting module name: ntdll.dll, version: 6.1.7601.23572, time stamp: 0x57fd02d3
Exception code: 0xc0000374
Fault offset: 0x000ce8fb
Faulting process id: 0x6b38
Faulting application start time: 0x01d265ab80e74c27
Faulting application path: C:\Program Files (x86)\Oracle\SQLDeveloper 4.0.3\sqldeveloper.exe
Faulting module path: C:\Windows\SysWOW64\ntdll.dll
Report Id: 5e9fdaf7-d19f-11e6-877d-0050569d0128
Author
Easy peasy – go update the video driver.
It’s a virtual machine running on top of VMWare ESXI. However i do see only “VMWare SVGA 3D” as a driver. Also when i try to update the driver software it says the best driver software for your device is already installed. Please advise.
Author
the ‘best’ driver is crashing Java
So, what next?
Author
go get a newer driver, or open a support ticket on your side to have it investigated
Hello Jeff,
Great blog! I have a question on SQL Developer –> “Is there a way to add an option in the right-click menu while on SQL Worksheet?”
Regards,
Bibhuti
Author
Not without coding a Java extension to do it.
Thanks for the suggestion !! Could you please suggest me some tutorial/ documentation that I can refer to create the extension using JAVA (considering that I have prior knowledge in JAVA programming).
I have read your articles on how to create extension using XML and I have tried searching the internet for creating extension in JAVA but have failed to get any head-start.
Thanks in advance 🙂
SQL*Developer’s ability to view a .png or .jpg saved as a BLOB is super-convenient.
Is there a way to add this same ability for .svg?
I would like to modify the {child}_{parent}_FK default naming convention as in my specific data model it exceeds the 30 character name limit (Oracle 11g XE).
I have found this 2009 white paper:
http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodelernamingstandards-167685.pdf
It states that this template can be found in General Options, but I can’t find in my SQL Datamodeler (Version 4.1.3).
Could you please let me know where to modify that naming convention in recent versions?
Thanks
Author
right click on the design in the tree, they’re in the design properties now, so you can set them at the design level vs entire applications
Thanks Jeff
Can we use sdcli to run just a simple query and spool the output to a file?
I was hoping to do this with sdcli so I can use the connections setup in SQL Developer(rather than using SQL Plus).
I’ve seen the blog entry about “sdcli64.exe reports generate -report”, but was hoping to append to text rather than html.
That is, append the results for one database, then another, then another.
Hopefully just a quick question.
Using git with
Oracle SQL Developer 4.2.0.16.260
Version 4.2.0.16.260
Build 16.260.1303
Whenever I am pushing to our master repository the user name gets an appended to it. This is flagging the commit as being from an unknown user. Is there something I missed in the setup – or someplace I can correct this?
Author
I’d ask the JDev team – we get this code from them.
OK – the string got swallowed by the HTML – what gets appended is ‹›
Hopefully that will show up.
The link you had in your reply goes to a facebook picture of a sandwhich – do you have an alternate contact link for the JDev folks?
Author
Oops. Musta pasted the wrong clipboard item. Try this.
Hi Jeff, I’m using SQL Dev DM 4.1.5. When I foward-engineer a logical subview to a relational model, the names are not being converted from logical to physical. I exported the names from the Glossary Editor to a CSV and imported the CSV into the tool via Name Abbreviations. All of the options are checked.
What am I missing ???
Thank you so much for your help !!
Sincerely, Ed.
Was trying to get results from a select on dbms_sqltune.report_sql_monitor via SqlDeveloper (4.2 in this case), but only get the very beginning of the results (~73 chars). Can’t do any set commands, so what can I do to get the full sql monitor report? Am I missing something really simple?
Example:SQL Monitoring Report
SQL Text
——————————
select * fro
Your site has been VERY helpful over the years.
Thanks!
Author
>>Can’t do any set commands
Why not?
You’re gonna need to, you’ll need to do SET LONG at least I think…
Have you seen this?
I looked at your reference, was doing something similar (queries running longer than x secs as the parent, then child reports based on the sql id for futher info). But when I tried adding a “set long 200000” at the beginning of the child query, I keep getting ORA 922. Same with any set option. What am I missing here?
set long 200000
select DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
sql_id => :SQL_ID,
report_level=>’ALL’) as report
from dual
Author
using SET commands – you need to make your report type, SQL Script
Yep, that did it. I missed that option.
Thank you sir!
Still having an issue with setting linesize. I can run the child statement via sqlplus and it is formatted fine. But when running via SqlDeveloper, it still chops it off at 82. Even tried setting a login.sql with linesize at 200. Is it something to do with the call to dbms_sqltune maybe and sqldeveloper isn’t able to reformat it?
Sample code from SQL Worksheet:
set LONG 1000000
SET LONGCHUNKSIZE 100000
SET LINESIZE 200
set pages 49999
set trimspool on
SET ECHO OFF
SET FEEDBACK OFF
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id => ‘&SQL_ID’, type=>’TEXT’,
report_level=>’ALL’)
from dual;
Author
test it in the sqldev worksheet with F5
you could also try selecting it into a CLOB and printing it via dbms_output like i did in my example
Ok, I got it now that I see how the CLOB output get formatted. This opens up a lot of possibilities for t/s issues with this tool.
Thanks!
Hi Sql dev version 4.2 ; this error seems to just sit there
Your database connection has been reset. Any pending transactions or session state has been lost.
i repeatedly cancelled it or clicked ok on the message but it stuck takes couple of minutes to go away. This issues started with new version only.
Author
We have a new jdbc driver for the next EA – those issues appear to be resolved.
I’m normally an Access/Excel user and the ease of switching between them, such as copy and paste, is highly useful. Is there a quick way to paste rows of data from Excel into a select statement “Where In” portion into Oracle SQL developer?
Author
if you can figure out how to copy out a comma separated value list, then yes
otherwise you’d have to use a macro or regex search/replace to format a list of values to be comma separated and/or quoted if not numbers
I recently switched to sql dev 4.2 version. Since then all of my connections are dropping very frequently. i have to reconnect every minute or so. Earlier my connections used to persist for at least 30 minutes. I checked with DBA and no change was done at their end. Any help will be highly appreciated.
Author
It’s an issue on our side – we’re working on it.
Thanks. Can i expect a fix with next version ?
I just restarted my sql developer and machine.connections seems to persists now. Hopefully issue does not reappear.
A simple one, where slqcl stores the alias?
Author
Depends – Windows or NIX?
If *NIX, in your $HOME/.sqlcl directory.
If Windows, it’s in your AppData, Roaming Profiles, SQLcl folder.
Thanks! Found it is aliases.xml on .sqlcl on the ~.
I guess I can put all my scripts on sqlcl and have this aliases.xml as a backup?.
Author
you mean, convert your scripts as aliases, and use that as backup for your scripts?