1

I am facing a very different issue and not able to figure out what might have been causing it.
I ran a query in SQL Developer which is from a join of 4 different tables.
I then copied the output from the result grid and copied it to an Excel.

Now when I am looking at the output in SQL Developer and compare it with the data in excel, they are looking different.

For example, for the same row, there are different values in the columns. How is that even possible?
I am using Oracle 12C.

Improve this question
9
  • Can you post a test case that demonstrates the issue? Off the top of my head, perhaps you have a non-deterministic query. Perhaps you actually have a difference in the two queries. Perhaps you made a mistake copying data around or comparing the data. Perhaps you're connected to different databases/ schemas/ whatever. Perhaps you are expecting the data to be in a particular order but you're getting data in different orders. Commented Feb 24, 2016 at 4:25
  • Unfortunately, it is production data and so, I cannot produce it here but I can assure you the following things: 1. I am using a single query. The other dataset is just a copy and paste into excel. 2. Very less chance of a mistake since it was a full dataset copy. I did not change anything in between. Commented Feb 24, 2016 at 4:29
  • In Excel, go to the Data tab and press the text to columns button. With delimited selected, hit next. And on the second screen, look at the delmiters that are checked. Pasting data in follows those delimiters. So if the period is checked, it will split numbers with decimals. It usually works best if tab is the only one selected. Commented Feb 24, 2016 at 4:30
  • I checked that. Everything looks fine. The only selected value is tab. The issue is not with order of columns, the data in the columns itself seems to be wrong. For e.g. in SQL Developer, I am seeing column A and B's values as 6001 and 6001 but in excel, the values are changing to 6001 and 6002. Commented Feb 24, 2016 at 4:33
  • If you can't produce a test case, it will be very, very hard for anyone to speculate at what problem you might be having. I'd guess that you have a non-deterministic query or that you're making a mistake comparing the data potentially because you are expecting the data in a particular order but haven't provided an order by. Is it possible that you've encountered an Oracle bug? Sure. But I'd bet against it. Commented Feb 24, 2016 at 4:36

3 Answers 3

Reset to default
1

$ 0.02 more ... Copying data out of SQL Developer has always been clean for me. (@thatjeffsmith is correct; it's clipboard behavior)

When I copy to Excel, sometimes the default formatting in the target sheet impacts the resulting look of the data. If you eventually export that spreadsheet, (save as ... and select CSV for example), you'll have another transformation of the data.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I had same problem.
For what I understand it is a problem related to SQL Developer.

You are mentioning Excel, so I suppose you are on Windows.

When you say: copy, windows try to copy the content of an object.

The problem is: that grid on SQL Developer is not an object with all results. It just display a portion of data. So, actually, while it can have all results in memory, you cannot copy and paste from it, if not a small region.

The fastest way to go from SQL Developer to Excel is:

  • Run your query
  • Move to the end of the grid, to fetch all results
  • Right click on the grid and select: Export
  • Export as you wish

I understand this way is not as fast as copy & paste but, because of the limits of SQL Developer (or Java, I dont know), when you have more of few rows, it is the right way.

Improve this answer

3 Comments

ummm, what? If the rows are visible in the grid, you can definitely copy them to the clipboard, no problem. if you want the column headers too, just use ctrl+shift+C to copy it out. Once it's on the clipboard, the issue is between the OS and Excel as to how it gets into the spreadsheet
@thatjeffsmith, yes, but it works only for visible rows and some more in the buffer. Not every time, at list with a lot of data, on Windows, with sql developer >= 4.0
right, if copies what you have selected, which is what you have fetched down. ctrl+pgDn will force a full fetch. If it's a lot of rows, easier to just use the export wizard, although that will run the query again. if it's big, i just do spool table.csv select /*csv*/ * from table; and open in excel, voila
0

I finally got to find out the issue I was facing. It was indeed an Oracle issue after the upgrade to Oracle 12C. We talked to the Oracle Support and they responded back with the below: Queries involving hash joins were returning wrong results in cases where hash join would receive row sets as input and produced one row at a time as output.

Because of this, the query was becoming indeterministic and giving different counts at different instances. When the query was running in SQL Developer with where clause, SQL developer was providing a different output because of where clause, since it was changing the plan of execution but when I was pasting it in Excel and then filtering, it was showing different values since in this case, the query plan was different since there was no where clause.

Sorry if my question caused any kind of confusion. I just wanted to confirm if it was something else before asking DBA to reach out to the Oracle Support regarding this.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.