Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
7,791 Comments
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 …
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’?
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
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?
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!
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.
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.
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.
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
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?
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
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
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.
>>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.
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
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 ?
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
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?
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!
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;
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?
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?
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.
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.
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.
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
>> 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.
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.
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 –
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.
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
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.
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.
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
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/